SQL Server Reporting Services 2005 (SSRS) has a new feature called Report Builder that allows end users to create their own reports in a user friendly interface. In Reporting Services 2000 users had to build reports using Visual Studio. They also had to understand the underlying database query language to get back a result set. With Report Builder, users are presented with an easy to navigate object model that represents database objects. The objects can be dragged from the object explorer and dropped onto the report surface. In this blog, I wanted to talk a bit about how these user friendly database representations are created.
To build a user friendly representation of a data model, you need to create some sort of semantic layer that describes the data. In SSRS, this semantic layer is called a Report Model. Think of it like a logical layer that allows a client tool to communicate with a database using a common interface.
SSRS offers two options when creating a Report Model. You can create Report Models from SQL Server relational databases or Analysis Services multidimensional cubes. Down the road, you might see support for other database platforms, but to my knowledge there are no solid plans. So, the question is, do I use SQL Server or Analysis Services. In my opinion, you should use Analysis Services. I’ll talk a little about creating a SQL Server Report Model and then hopefully you will understand why I feel the way I do.
To create a SQL Server Report Model, you use the Report Model Project in Visual Studio 2005. This project is fairly straight forward to create. You start by adding a data source that points to a SQL Server database. Second, you create a Data Source View that is a logical representation of your relational database. Finally, you create a Report Model based on the Data Source View. It is this last step that takes the most amount of effort.
When creating a Report Model, you run a generator that creates Entities and Attributes. Generally, Entities represent tables and views and Attributes represent columns. When you run the generator, aggregates are created for numeric columns as well as some min and max functions. The generator is a great start, but it leaves a lot of work to be done after the fact. Once you have generated the model, you will most likely have to rename a lot of your objects. You will also have to remove some aggregates and items that might not make sense. This process can be quite time consuming to get your Report Model just the way you want. Once you step through creating your first Report Model, you will understand the amount a effort involved to do so. I’m not saying this is bad, it’s just a reality.
On the other hand, you could do a lot of the same steps as mentioned above when creating an Analysis Services cube. Analysis Services can use SQL Server data sources (as well as Oracle and any OLEDB or ODBC provider). Analysis Services also uses Data Source Views. From the Data Source Views, you can create dimensions that represent information from your underlying table. Once the dimensions are created, you can relate them in a Cube structure that gives you a much richer amount of data.
So, creating a SQL Report Model or an Analysis Services cube takes roughly the same amount of work. Why would I then suggest using Analysis Services? Here’s a quick list:
- SQL Server Report Models are only usable in Report Builder
- Analysis Services cubes can be consumed by Report Builder, Excel, and third party tools like Panorama and Proclarity
- Analysis Services cubes can be built from SQL Server or any OLEDB or ODBC database provider
- Analysis Services stored aggregates and can greatly increase query time
- Analysis Services offers much more flexibility when building your semantic model.