Well I guess congratulations are in order because I finally got around to using a report builder in my project. Turns out that building a report model based on a cube is as easy as 1-2-3. Still I had a problem, as for some unknown reason the translations were showing for my dimension names and my measures, but not for the dimension’s attributes… At first I thought maybe this is a problem solved by Service Pack 2 (as my tests were done on a Server with Service Pack 1). Alas, no. So, a further look in the MSDN library, came up with a more detailed example as to how to incorporate translation in your cube based report model. I admit, this was the first time I had ever used the “LocaleIdentifier”. But as I have a different translation than Chinese in my cube (which was the example in the relevant article), then what should I put in the connection string of the data source? So the next search on the web was for a table mapping the different languages to their respective Locale Identifier code. Looking at what the report model generated, I saw I still needed to add a few things to make it easy to use. So I added the following things:
- I used in most of the attributes the InstanceSelection property which maps to the relevant application, just how the user may choose a value for that dimension attribute. It even seems that Microsoft has published a recommendation for the use of this property in its Best Practices for Analysis Services 2005 whitepaper. I found though that the report model shows me the same sort of list whether I choose “List” or “FilteredList”, and it didn’t show me anything when I chose “Mandatory Filter”. What I liked here was the fact the in a list, Report Builder enables the user the filter for values even within the attribute value itself. The Panorama Web Access we’re using as a cube viewer, only lets my user filter for values in the beginning of the attribute value.
- I also found that the report model shows all of my attributes (even if I chose them to be “AttributeHierarchyVisible: False”). Also, as it represents a flat model, it didn’t show the User Hierarchies I created in my SSAS project. So I ended up making 2 perspectives in my cube: one for the Panorama Nova View and one for the Report Model. I gave “AttributeHierarchyVisible: True” to all of my attributes, and the same translation as they have in their User Hierarchy. I also gave all the attributes in a relevant User Hierarchy their User Hierarchy’s name under their “AttributeHierarchyDisplayFolder” property, so that the user would easily recognize from where to pick out that attribute in the Report Model. I also later saw that if you choose the attributes in your User Hierarchy to be visible even in the Panorama Nova View perspective, then you’ll see those attributes also in the folder you defined for them. Apparently, Panorama also recognizes and knows how to use the AttributeHierarchyDisplayFolder property. In the Panorama Nova View perspective I marked the User Hierarchies I had from before and unmarked the attributes that were hidden before and now visible for the use in the Report Builder. In the Report Builder perspective, it was vice – a – versa.
- What gave me the most grief was my Parent Child Dimension. As I described in a previous post, I use the dimension to describe my organizational hierarchy as I need to connect to it at different levels for my Budget fact table. In a parent child dimension the key attribute shows in the level with which it is linked, without the ability to aggregate up the levels. So I found myself adding 4 more attributes, as the number of levels in the organizational hierarchy. I chose not to show them in my Panorama Nova View perspective, as they look awful in that viewer, (whereas the key attribute performs beautifully). In the Report Model perspective I’m showing those 4 attributes and they look just like the real key attribute. The relevant attribute has a value for its level and the corresponding parent has a value in its level. Where I skipped a level, I had “null” as a name.
- Calculated measures I defined in my cube were not shown in the Report Model. I later found out this was because I needed to connect each calculated measure to a relevant measure group. So I did this through the “Calculations Property” button. The scopes I defined in MDX also take effect in the Report Model.
- I also saw that the Report Model was defining drillthrough reports built according to its own choice (based mostly on the keys in the relevant fact table). Now, I didn’t want my user to be looking at keys, but rather at the relevant attributes connected to those keys. So I decided to create my own report builder clickthrough reports using report builder generated reports which I saved on the server and linked to the report model. I am also considering adding a few Reporting Services reports which will be based on the cube, thus enabling me quite easily to link an SSRS report to another SSRS report that will be saved on the Report Server.