If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.
When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:
You will then be able to select on or more of the roles available on the database via a dialog:
This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity
The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:
Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example
Sometimes, just sometimes, I plunge right into using a tool without reading too much documentation. This has some advantages and some drawbacks. Trying to decipher error messages without the right background knowledge can be a hard nut to crack. Just now I found myself in one of these rare occasions, where I had to make the best of seemingly cryptic error messages.
The task at hand was to deploy a new database on a newly installed server using the vsdbcmd tool. The server came with just the rudimentary OS and software installed, hence I was bound for trouble.
My first bump on the head was when I first tried to run this command:
I received this error message (rather a critical dialog indicating application crash):
This issue was solved by installing the .Net Framework 4.0
I then ran the same command line, and received another error:
In regards to this article about named calculations in SSAS, I had the perculiar finding today, that NULLs are handled somewhat differently than I would have expected.
In my case I had constructed a named calculation, just as in the article, by concatenating two columns, both of type string and allowing NULLs.
The perculiar part was, that when the second value was NULL, the whole expression would result in a NULL.
So, in order for the concatenation to succeed, the expression should be as follows:
[Marital Status] + CASE WHEN LEN([Has Children]) > 0 THEN ‘ ‘ + [Has Children] ELSE ” END