CU6 for SQL Server 2005 Service Pack 2 was released last week. The knowledgebase article detailing what's inside is available here and the download itself is available only after requesting CU6 from Microsoft here.
Chris Webb had just written in his blog, that apparently this still does not necessarily lead to a SP3 for SQL Server 2005.
Well as some very good people in the community have managed to say even before me, SQL Server 2008 has shipped a new CTP version just yesterday. As I have mentioned before, this is the feature complete version which includes all the functionality planned for the final release in Q3 of 2008. I think that for most people, the big change here will be the integrated Full Text Search which should really enhance performance.
Go ahead and download it here!
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.
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…