Setting default member in role playing dimensions
Reposted from Jason Thomas blog with the author's permission.
Today I got a call from one of my colleagues asking how to set a default member for a dimension attribute . I was a little surprised as I thought setting a default member is quite straight forward and was even more surprised to learn that the dimension had processed successfully but the cube had failed with errors.
P.S. : If you are a SSAS beginner, please read this msdn article on how to set the default member of a cube to grasp my blog contents completely – Specifying the Default Member
I straightaway went to my colleague’s desk as I clearly saw it as an opportunity to learn something new. First thing I did was to check up the dimension in the dimension designer and to verify that the default member was set up correctly.
The default member looked to be correct and then I processed the cube to see the error message.
The error message clearly mentions that the level [Brand Family] (which is the name of the dimension) was not found in the cube when the default member string was parsed. So this could mean two things:-
a) either the default member would have been misspelt
b) there would be no dimension / level called [Brand Family]
But as I could see, there really was a dimension called [Brand Family] and the default member was not misspelt also. That is when it struck me to have a look at the dimension usage tab.
As you can see from the above screenshot, the Brand Family dimension was set up as role playing dimensions, and later I understood from my colleague that the requirement was to setup a common default member in all those 5 role playing dimensions. Now it made sense why the error was coming, because the [Brand Family] dimension is not existent in the cube. Instead, it is being referenced by the role playing dimensions [Main Brand], [Dualist TMC Main Brand], [Other Brand Family]. [Awareness Brand] and [Seg ClassF Brand]. So I suggested to set the default member in the format of <level>.value instead of <dimension>.<level>.value. Since the level names are unique across dimensions, I presumed it should get resolved properly.
As expected, with this workaround, the cube processed successfully and we were able to get the same default member set correctly in all the role playing dimensions.
After this, I also chanced to view a line from Teo Lachev’s book – Applied Microsoft Analysis Services 2005
“You cannot set the DefaultMember property of a role playing dimension in the Dimension designer because it is not clear which specific role version it is intended for. Instead, the only way to set up the default member is to do it programmatically by using the ALTER CUBE statement.”
Syntax of Alter Cube is
ALTER CUBE CurrentCube | YourCubeName
UPDATE DIMENSION <dimension name>,
DEFAULT_MEMBER='<default member>';
Eg:
ALTER CUBE CURRENTCUBE
UPDATE DIMENSION [DATE].[CALENDAR DATE],
DEFAULT_MEMBER = [DATE].[CALENDAR DATE].&[729]
The default member would be set up at run time if the ALTER CUBE statement is used and hence, the dimension designer would not show the value of the default member.
Even though I haven’t tried, I think it should be possible to set different default members to each role playing dimension through the ALTER CUBE script. This would not have been possible with the particular workaround that I suggested in the beginning.
Update
I found an old link in the forums in which Deepak Puri (MVP) confirms that it is possible to set different default members for each role playing dimensions using the ALTER CUBE statement.
 |
Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com |
Tags: design