Building Report Models on top of Analysis Services
There is a new capability within Reporting Services that allows ad-hoc reporting for end users directly. That tool is called Report Builder and can be invoked through the Report Manager web interface. In order to deliver this capability though to the end user first thing you need to do is provide the user with a Report Model. A Report Model is a very intelligent semantic model that can currently be built on top of multiple SQL Server or Analysis Services sources. The goal of the Report Model is to provide a subject area definition of the data that a user has access to along with friendly names and description of data for easier Report Creation.
The Report Model is a specific type of a project available in the Business Intelligence Development Studio ( BI Debv Studio), the development environment shipped with SQL Server 2005.
A lot of customers are excited about this capability and rightly so. Report Builder is a fantastic tool and can add a lot of value to the overall BI stratgey of an organization. The question people usually ask me is how do I build a model on top of Analysis Services.
Well, if you try and create a new Report Model in the BI Dev Studio, you will notice that it only allows you to create a model on top of a SQL Server source. It does not allow you to create one on top of Analysis Services. The primary reason is that an Analysis Services cube already has a lot of the model capability built in it, like understanding of hierarchies, attributes etc that the model can leverage. Therefore in order to create a Report Model on top of Analysis Services this is what we need to do:
1) Open the SQL Server Management Studio. This is the management tool inlcuded with SQL Server 2005 that manages all the components of SQL Server. This includes the relational database, Analysis Services, Reporting Services, Integration Services and SQL Server Mobile databases. Register your Report Server in the Management Studio. TIP: To register your Report Server with the Management studio provide name like : http://<servername>/reportserver
2) Once registered you will be able to traverse the objects in Reporting Services like your Data Sources folder, ReportS folder etc. Rigtt click on the Data Sources Folder and make a new connection to your Analysis Services cube.
3) Once the connection is made, right click on the connection and one of the options you will now see is to Generate Model. Thats it.
Once the model is generated a user can use Report Builder to use all the rich hierarchies and attribute availability to build their own reports.
