Wednesday, October 05, 2005

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.

7 comments:

  1. Just thought I'd let you know about a site where you can make over $800 a month in extra income. Go to this site   MAKE MONEY NOW  and put in your zip code..... up will pop several places where you can get paid to secret shop, take surveys, etc.  It's free.  I found several and I live in a small town!

    ReplyDelete
  2. Your blog is great . If I can help, let me know. If you ever need any printing done, I'm sure you'd be interested in Banners Try Banners

    ReplyDelete
  3. Thanks a lot Sameer for this information. I needed this info so badly and came across ur site.....Wanted to thank the blogger for that and came to know that u are a GEC product...:) Me too...
    Keep Blogging:)

    ReplyDelete
  4. Thanks for this article. Did you succeed to concatenate the Report Model and Data source view programmatically using Reporting Services web service ?

    ReplyDelete
  5. Thanks for this article. Did you succeed to concatenate the Report Model and Data source view programmatically using Reporting Services web service ?

    ReplyDelete
  6. I succeeded to deploy the report model (without removing the attribute or add xsi:type="RelationalDataSourceView"). But the Report Model is not functional.
    I’ve got the error: The type of the data source has not been specified.
    If I remove the attribute from DataSourceView Node and add: xsi:type="RelationalDataSourceView"
    I get this error:
    Error : rsModelingError (The semantic model is not valid. Details: This is an in
    valid xsi:type 'http://schemas.microsoft.com/analysisservices/2003/engine:Relati
    onalDataSourceView'. Line 362, position 4.)
    And don’t succeed to deploy.
    What I’m doing wrong?

    ReplyDelete
  7. I am sorry Grig... I don't have a clue... I have stopped working on Reporting Services since long back.

    ReplyDelete