Well I thought today we’d go over visual totals in MDX and also see how they may have an impact on how you assign role based security in you SSAS project.
Visual Totals in MDX are there to give you just what they describe – a sum on the children in a certain set. Child members which are not in the specified set will be ignored during the calculation. For instance, if I have a set that consists of: USA, New York, Washington and California, when I look at the total for USA, I will only see it’s sum being consisted of that which belongs to New York, Washington and California. All the other states, such as Texas, Florida, Louisiana etc etc will not go into the calculation being run for the total for USA.
The basic syntax for visual totals would be: VisualTotals(Set_Expression)
In which case the “Set_Expression” would be the set you would like your calculations to run on.
I admit that though I had known of visual totals in MDX before I did not have a chance to use them until I added a new kind of role to my cube.
I had built a cube which shows all the matters regarding HR: the positions in the organization, the workers that hold them, the budget for the different units, the salaries being paid and the amount of money written down for them in bookkeeping.
All of my users thus far could see all of the information. Some of them may not have been granted to look at salaries, and so they could not look at salaries at all. This demanded only that I uncheck the boxes next to the measures of the workers salaries.
Now, I was asked to add a new sort of user. My new user was head of HR for the municipality’s IT department and should only see data regarding the IT department. In the scope of the IT department, my new user should be able to look at all the available data.
At first what I did was to assign a new role for that user and in his “Dimension Data” tab, I chose the radio button for “Deselect all members” allowing the role to view in my organization only the relevant unit.
As my organization is a Parent Child dimension, this also struck a V sign next to the entire organization and all the children of that unit.
If to be frank, I thought that was all I had to do. But I was wrong… I was looking at my cube through the cube’s “Browser” tab and using the “Security Context” of the new role I defined.
When I dragged the organizational structure dimension all was good – I could open it only to the unit I defined and when looking at the entire municipality, I could only see amounts for the various measures stemming just from the organizational unit I selected. But when I brought over the position (a dimension all of its own), I could see all of the positions in the municipality and to each it’s own measure. Not good…
So I went back to the definitions of my role. In the “Dimension Data” tab I again selected the organizational unit dimension. I clicked the “Advanced” tab and checked the box next to “Enable Visual Totals”.
Though visual totals may slow down the performance of the cube, they are the only way I had left to ensure that my new role won’t be able to look at anything which was not connected to the unit he’s allowed to look at. I deployed my new definitions and went back to the cube “Browser” tab, again simulating my new user. This time when I dragged the position dimension I could only see the relevant positions and their measures. Same went for all the other dimensions.
Just goes to show that marking a little check box can go a long way…