Reporting Services : Understanding Semantic Models

One of the new features of SQL Server 2005 Reporting Services is the capability of building reports based on models.

Steps are simple…

Use the Business Intelligence Development Studio of SQL Server 2005 or Visual Studio 2005 and start a new Report Model Project. Create a Data Source (*.ds) by providing connection information to get data from an SQL Server. Create a Data Source View (*.dsv) based on this data source, which is nothing but a mapping of all the tables and views from the data source. Then create a Report Model (*.smdl) based on this data source view.

Now, you can right click on the Report Model Project and choose ‘Deploy’ to publish this model into a Report Server.

Go to Report Manager, and you will find this model under a folder named ‘Models’. Click on the link ‘Report Builder’ to launch the new report building application, where you can choose this model to make reports. It’s as simple as dragging and dropping the fields from model. Once the report is done, you can save it to the Report Server.

So far… so good…..

But things will become tougher, if you want to do all this programmatically or even manually, without using Visual Studio. Earlier, if you wanted to deploy a report file (*.rdl), you just need to go to Report Manager, and upload the *.rdl file.

Things wont work like that with model files (*.smdl). If you try to just upload a model file, you will get the following error:

“The DataSourceView is missing for the SemanticModel. SemanticModel must have exactly one DataSourceView element. (MissingDataSourceView).”

So you need to have the information about data source view inside your model file.

The report model file is an xml file containing information about the model in a language called ‘Semantic Model Definition Language’. Data source view file is also in xml format. You need to take the whole contents of the data source view (*.dsv) xml file and put it into the model file exactly after the ‘Entities’ node, ie, just before the closing tag of semantic model.

After that, you need to remove all the attributes of the node except the last one (xmlns=http://schemas.microsoft.com/analysisservices/2003/engine), and then add the xsi type attribute (xsi:type="RelationalDataSourceView").

Now you can safely upload this model file to the Report Server using Report Manager and then associate a data source to make it work.

All these steps can be done programmatically by using Reporting Services web service. I will cover that in my coming posts.