Updating your Report Model

February 9, 2008

I know I had written of the simplicity and ease of creating a report model based on a cube, but I too would have to admit that even a good thing can be made better.


Just in case you hadn’t noticed, when you enter the report site you can manage your models. If you can’t see the “Models” folder, simply click the “Show details” and you’ll see it.


RM1


RM2


Select the folder and click on the name of your model or on the “edit” icon next to it.


RM3


You now have a chance to “Edit” or “Update” your model. Choose the “Edit” button and then save the model that was automatically generated down to your computer.


RM5


Now, you can start your Visual Studio and choose to create a new Report Model Project. Add to the project the Data Source and the Data Source View that you have defined for your cube (and in that order). Add the Report Model you have just saved from the report site. You can now start editing the report model.


I used this technique so as to delete from my model all the perspectives I created in my cube, leaving only the one that I made specifically for the report model. After all, I didn’t want my users to get confused with what perspective they should choose. I also used this to hide the measure group I created to manage my many to many relationship, as though I had hid all its attributes, it could still be seen as an entity in the model itself. Last but not least, I also used this model to edit the default detail attribute for each dimension and measure group. As it turns out, when you create a report model based on a cube, the model being generated will show you the keys that relate to a measure group when you click it for a clickthrough report. When you choose to update the “DefaultDetailAttributes” of an entity – whether it’s a measure group or a dimension – you not only chose for your user the attributes he gets automatically when he double clicks that entity, but also the attributes he gets when he clicks a measure or dimension key to get a clickthrough report on them. As most of my dimensions had surrogate keys that connected them to the relevant fact tables, seeing them in the clickthrough reports would be meaningless to the user. And so instead of them, I chose to show my business keys in the in the “AttributeReference Collection  Editor” dialog box in the “DefaultDetailAttributes. For more information on what else you can do to update your report model, please refer to the relevant pages on the MSDN.


Now that you’ve finished improving your model, you can add it to the one that’s on the report site. Go again to your model on the site, choose it and this time, click the “Update” button.


RM4


Now, you’ll need to browse your computer and add the report model you have worked on. Click “OK” on this page and then “Apply” on the main page for the report model. You can now view your updated model in the Report Builder itself.


RM6 


Still, we’re not through yet. I must add a word of caution. As every plus must have its minus, so is the case here. Once you decide to update the report model that was automatically generated for you, you can no longer regenerate the model based on your cube. Moreover, as you may know, when you change a report model, report saved on that model by your users may no longer work. So I would conclude with a word of advice – I still recommend that you refine your report model, but only after you have finished with your cube and it is stable. Else, you’ll have to go through all of the process all over again, and so will your users.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

11 comments

  1. DaveshFebruary 27, 2008 ב 23:12

    Hi Ella

    I am new to Report Builder, I have created the model and deployed it on localhost and created a few Reports

    What I am looking to do embed this into a web application.

    The user logs on to the web application (not the server) providing user id, password and a company name using the credentials stored in the database.

    Once the user has logged on they can click the Report Builder and it will launch the application and get data only for the company they are logged into

    Is it possible to pass some parameter when clicking ‘Report Builder’ or is there a way to program or pass parameters when we create the Report Model.

    DD

    Reply
  2. Ella MaschiachFebruary 28, 2008 ב 14:12

    Hi Davesh, 

    Well as far as I know, Report Builder can get the user credentials logging into it if you define for the Data Source of the Model either:

    • Credentials supplied by the user running the report (and then the user has to retype his user name and password, even after entering the web application)
    • Credentials stored securely in the report server and under it click the check box for:
    1.  Use as Windows credentials when connecting to the data source
    2. Impersonate the authenticated user after a connection has been made to the data source

    •  Windows integrated security
    • For more details, please refer to the MSDN page on how to configure a Data Source. Though the page explains how to do this in the Management Studio environment, you can do the same on the Model folder on the Report Server itself. 

      Hope that helps, 

      Ella

      Reply