Speaking the language of business intelligence with an Australian accent

Sunday, October 9, 2005

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:

Anonymous said...

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

Nick Barclay said...

Thanks Scott, much appreciated!

Anonymous said...

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 ?

Nick Barclay said...

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?

Anonymous said...

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.

Nick Barclay said...

What items do you see when you right-click on the SSRS data source? Please list them.

Anonymous said...

Great Blog!!!

I also have the same problem, "Generate Model" doesn't appear on the context menu.

Nick Barclay said...

Is the data source you are clicking on an Analysis Services one?

Anonymous said...

AND if your using reporting services in Sharepoint integrated mode, How would you generate the model?

Anonymous said...

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.

Braulio said...

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 :-(.