Securing a Dimension with Members Having the Same Name

Here’s an SSAS security issue that doesn’t come up very much at all. In fact, when this recently came up, I had forgotten about a solution I provided way back in 2005 (which was the only other time I’ve encountered this). The problem is that when securing an attribute member (a role’s Dimension Data tab) using the member’s name and not key (using the ”Allowed Member Set” text box of the “Advanced” tab), and there is more than one member with that same name, only the first one will be secured.

For example, if I have two customers named “John Smith” (but with different key IDs) and I place the MDX in the “Allowed Member Set” text box, [Customers].[Customer].[John Smith], only the first John Smith will appear. This is consistent with the behavior of what happens in a select statement when referring to members by name. If there is more than one, only the first will show. Please note that this is different from what will happen if your Axis SET references the members’ key IDs (which means using functions such as members, children, etc – something like [Customers].[Customer].Members). In that case, “John Smith” will show up twice.

Before continuing to the solution, I should explain why the names instead of the IDs were referenced (which also explains why this is such a rare occurrence). The underlying data sources of the dimensions did not maintain static dimension keys. What happens is that the underlying data source (data warehouse or data mart) was completely repopulated when it refreshed. This means that any MDX referring to those members by key (ex: [Customers].[Customer].&[1]) are no longer valid; or worse, that key now refers to another member for which security should not be applied. Therefore, keys wherever they are referenced (security, calculations, KPIs,) must be manually changed.

Additionally, this also means is that SSAS dimensions must be fully processed, as opposed to incrementally processing with ProcessUpdate, since it now cannot map those dimension keys to the internal keys it creates when the dimensions are processed.

It’s these terrible side-effects of not maintaining consistent dimension keys combined with the relative infrequency of two members having the same name (especially companies, products, etc) that make this situation I describe so rare.

The solution is to refer to the member as the result of a FILTER statement. For our John Smith example, instead of simply composing the MDX, [Customers].[Customer].[John Smith], we would compose something like:

FILTER([Customers].[Customer].Members,INSTR([Customers].[Customer].CurrentMember.Name,”John Smith”)<>0)

This will return a set consisting of the two John Smiths. If we wanted to secure John Smith and Eugene Asahara, even though there is unlikely to be more than one Eugene Asahara and we could just state [Customers].[Customer].[Eugene Asahara], just to be safe we could write:

FILTER([Customers].[Customer].Members,INSTR([Customers].[Customer].CurrentMember.Name,”John Smith”)<>0)+FILTER([Customers].[Customer].Members,INSTR([Customers].[Customer].CurrentMember.Name,”Eugene Asahara”)<>0)

I’d like to note as well that another reason I suspect I do not see some situations very often is not just because they don’t happen often for one reason or another, but because customers may give up on the technology when they run into a wall. That’s a terrible shame since there is hardly ever a perfect product and often a solution is just one little insight away.

About Eugene

Business Intelligence and Predictive Analytics on the Microsoft BI Stack.
This entry was posted in BI Development, SQL Server Analysis Services. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s