SSRB - SSAS Report Models
Recently two of my colleagues asked me if it was possible to give users access to SSAS cubes via Report Builder (SSRB). My answer was an immediate no. I had always thought that report models had to be built in BIDS (Business Intelligence Development Studio) and be based on a DSV which we must first create. Because we're basing the model on a DSV we then can't take advantage of all the dimensions, perspectives, KPIs etc.etc. created through the cube designer. I have since discovered that I was wrong, completely wrong. We can generate (and regenerate) a Report Model based on an SSAS cube.
SSAS-based report models can be created using "Generate Model" either in Report Manager or in Management Studio (when connected to the Report Server). Generate Model is a function exposed on data sources which have been deployed to the Report Server. Both relational and SSAS models can be generated this way.
In Management Studio right-click the data source and select "Generate Model"
... or in IE through Report Manager. Go to the properties of a data source and you will find the Generate Model button at the bottom of the screen.
The resultant SSAS report model contains structures based on dimensional relationships to measure groups as well as perspectives defined in the source cube. Even KPIs (and access to their display images) are included in the model. One thing that we do not get, however, are structures which mimic the attribute hierarchies. Nonetheless the attributes that make up the hierarchies are all there.
Does the newly generated model interrogate the OLAP cube on which it was based, not just simply back to the original base DSV? A quick Profiler trace while using the model in Report Builder proves that MDX is being generated and sent to the Analysis Server - no relational queries.
If the cube structure ever changes on the server just hit the "Regenerate Model" button on the properties page of the model in Report Manager or "Update Model" in Management Studio. This will bring the model back into synch.
11 comments:
Nick!!!
Great blog!!!!
I have been trying to find an example of this for weeks, especially on how to get the KPI images out, and this shows it perfectly. Thanks for taking the time to put this together!
Scott Barrett
Moffitt Cancer Center
Tampa, FL USA
Thanks Scott, much appreciated!
In Management Studio right-click the data source and select "Generate Model".
Why The "Generate Model" could not show up in my Management Studio ? is there any prev conf ?
Do you mean that the Generate Model option is not showing up in the right-click menu? Are you connected to your reporting services instance in SSMS?
Yes.. The Generate Model option could not show when i right clicked the menu. and i have connected my Reporting Services instance in SSMS.. maybe there is a previous configuration that must be done... can u give me a detail explanation about the setting, cause i'm a beginner for the SSRS,SSAS,SSMS. Thanks.
What items do you see when you right-click on the SSRS data source? Please list them.
Great Blog!!!
I also have the same problem, "Generate Model" doesn't appear on the context menu.
Is the data source you are clicking on an Analysis Services one?
AND if your using reporting services in Sharepoint integrated mode, How would you generate the model?
Hi, I am unable to create the model itself from either of the optoins you have mentioned. It is giving the same error.
"Cannot create a connection to data source 'InkjetCube'. (rsErrorOpeningConnection) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors
"
Please help me out.
Nice to see that it can be generated from SSMS, but... the model cannot be saved locally :-(, and I guess same limitation for SSRS 2008?.
It's a pain in the neck, that means... if you want to have a report model in a production server somebody has to give you admin right on SSAS, change the ds connection string, generate the model and then go back and change again the connection string... that's not serious.
I was searching for a way of just generating the model in my development instance, upload it to report managener and then point to the right datasource... but no way that only works for relational models :-(.
Post a Comment