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.
Select the folder and click on the name of your model or on the “edit” icon next to it.
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.
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.
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.
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.