SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How do you generate Report Model for Reporting Services Report Builder based on SSAS database?

A: To generate model based on SSAS database you have to first create or publish data source connection that points to that SSAS database.

Data Source

To create and publish data source from BIDS:

• Create new Reporting Services project in Business Intelligence Development Studio
• Add new shared data source. Choose connection type for this database source as "Microsoft SQL Server Analysis Services".
• Set project properties(TargetDataSOurceFolder, TargetReportFolder, TargetServerURL)
• Deploy your solution.
• Done

To create new data source in reporting services:

• Start IE browser and enter address for your reporting services server. Example: http://MyServerName/Reports.
• Choose/create folder where new data source will be located.
• From the toolbar click on the button "New Data Source".
• Enter name and description for new data source.
• Choose connection type "Microsoft SQL Server Analysis Services".
• Enter Connection string. Example: "Data Source=MyOLAPServerName;Initial Catalog=MyOLAPDBName"
• Choose security type
• Click OK.
• Done

Report Model

After data source connection is published, steps to generate report model are very simple:

• Start IE browser and enter address for your reporting services server. Example: http://MyServerName/Reports
• Navigate folders/subfolders and find data source connection that report model will be based on
• Click on your chosen data source connection. This will open data source properties window
• At the very bottom of data source properties window there will be button "Generate Model". Press this button.
• You will be asked to enter Name and Description for new model. You will also have an option to change location where this model will be published. After setting values, click OK button.
• Usually report model generation takes less than a minute. After that you will be directed to report model properties window.
• You are done.