Wednesday, March 12, 2008

Dimension Security Issues with Excel 2007 and SSAS 2005 Parent-Child Hierarchies

I've seen and read a lot of different posts and blog entries related to Excel 2007 and SSAS 2005 issues. Mostly things that don't work as planned with regards to displaying and using calculated members, incorrect query results given Excel's subcube-based filtering and its interaction with default members, etc. But here's one that I don't think I've run across yet that actually causes an error to occur while browsing a cube...

Suppose you have dimension security set up on a parent-child dimension. This is a common scenario in a sales cube or any other cube that contains a parent-child dimension related to people within an organization. In most cases, individuals should be restricted to just seeing themselves plus their subordinates within the parent-child hierarchy. Typically, I've seen this done by including AD logins as an attribute in the dimension and then using an MDX expression to restrict the allowed set of members within the parent-child hierarchy to just the user currently logged in (which then includes their subordinates by default in a parent-child hierarchy).

Here's a quick example using Adventure Works. I've created a new role in the Adventure Works SSAS solution named "Employees Security.role". This role specifies dimension data security based on the [Employee] dimension within the [Adventure Works] cube. Security is enabled on the [Employees] attribute within the dimension using the following MDX expression for the Allowed Member Set property:



Filter(
[Employee].[Employees].Members,
[Employee].[Employees].CurrentMember.Properties("Login ID") = "adventure-works\amy0"
)


Note that in this example, I'm referring directly to the AD login for the employee named Amy E. Alberts; normally, the MDX expression would use the UserName function to return the AD login of the current user. Doing it this way allows me to easily test the security role. Also note that I have Visual Totals enabled for the security role. Here's what the security role looks like:

Dimension Security Settings for Employees Security Role

With this in place, I can browse the [Adventure Works] cube. When I browse the cube without using the security role, I can see all of the employees in the [Employees] parent-child dimension as shown below:

All Employees Visible with No Security Role

However, when I connect to the cube through the security role (using the Change User button on the browser toolbar in either the BIDS cube designer or in the SSMS cube browser window), I'm relegated to seeing just Amy (along with her superiors -- required so that I can drill down to Amy) and her subordinates as shown below:

Only Secured Employees Visible with Security Role

Works great. Notice that Visual Totals has kicked in with the role so that I cannot see any numbers beyond those of Amy. This even works well within Excel 2003. If I connect and create a pivot table using the standard features in Excel 2003, I can see all of the employees when not connected through the security role:

Excel 2003 PivotTable with No Dimension Security

If I update the .oqy file created by Microsoft Query within Excel 2007, I can add "Roles=Employees Security" to the end of the connection string to get Excel 2003 to connect through the security role. After doing this, when I create the same pivot table as seen above, dimension security kicks in as I would expect it to:

Excel 2003 PivotTable with Dimension Security

Notice that Visual Totals is working correctly and I'm limited to seeing Amy and her subordinates (and her superiors again in order to provide a drill-down path to get to her). Fabulous!

However, when I try this same thing using Excel 2007, it just doesn't work. First, I'll create an Excel 2007 pivot table without any dimension security in place. This works fine using the parent-child hierarchy:

Excel 2007 PivotTable with No Dimension Security

Okay so far. Now, if I update the connection used by the pivot table (highlight the Data option on the Excel ribbon, click on the Properties toolbar button in the Connections group, then add "Roles=Employees Security" to the end of the Connection String on the Definition tab of the Connection Properties dialog box), I can attempt to recreate the pivot table. When I add the [Employees] parent-child hierarchy along with the [Reseller Sales Amount] measure (as done above), things look promising (and you can see that Visual Totals has kicked in):

Excel 2007 PivotTable with Dimension Security

However, as soon as I try to drill-down to get to Amy, boom!!

Error in Excel 2007 PivotTable with Dimension Security

Ugh. And to date, I've not been able to find a work-around. Anyone out there who has ideas on how to handle this, please let me know. I've tried defining the MDX expression in the security role in a couple of different ways and I've tried to tweak the pivot table settings in Excel 2007 in various ways. All to know avail. Uh, Microsoft, can we get this fixed (along with all the other ugly issues that exist when using Excel 2007 as a client for SSAS??)

12 comments:

Marco Russo said...

I got the same issue just today!
I still haven't investigated about possible workarounds, please keep me updated if you find something.

marco.russo@sqlbi.eu
http://www.sqlbi.eu

Frederik Vandeputte said...

Dave,

I have spent half a day on exactly the same issue. Still haven't found a solution yet :-(

Dave Fackler said...

Maro and Frederik,

Thanks for your comments confirming the problem! While I haven't found any workarounds or ways to resolve the issue, I have posted this to Connect, so feel free to comment on the issue there:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342319

Dave F.

Nicolae Mogoreanu said...

The query generated by Excel looks like the following:

SELECT NON EMPTY Hierarchize (
DrilldownMember (
{
{
DrilldownLevel (
{ [Employee].[Employees].[All Employees] }
)
}
}
, { [Employee].[Employees].&[112] }
)
) Dimension Properties PARENT_UNIQUE_NAME
, [Employee].[Employees].[Base Rate]
, [Employee].[Employees].[Birth Date]
, [Employee].[Employees].[Department Name]
, [Employee].[Employees].[Email Address]
, [Employee].[Employees].[Emergency Contact Name]
, [Employee].[Employees].[Emergency Contact Phone]
, [Employee].[Employees].[Employees]
, [Employee].[Employees].[End Date]
, [Employee].[Employees].[Gender]
, [Employee].[Employees].[Hire Date]
, [Employee].[Employees].[Hire Year]
, [Employee].[Employees].[Login ID]
, [Employee].[Employees].[Manager SSN]
, [Employee].[Employees].[Marital Status]
, [Employee].[Employees].[Pay Frequency]
, [Employee].[Employees].[Phone]
, [Employee].[Employees].[Salaried Flag]
, [Employee].[Employees].[Sales Person Flag]
, [Employee].[Employees].[Sales Territory Key]
, [Employee].[Employees].[Sick Leave Hours]
, [Employee].[Employees].[SSN]
, [Employee].[Employees].[Start Date]
, [Employee].[Employees].[Status]
, [Employee].[Employees].[Title]
, [Employee].[Employees].[Vacation Hours] ON COLUMNS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] ) CELL Properties Value
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

On AS 2005 with the custom dimension security in place it gives the "Errors in the OLAP storage engine: Either you do not have permission to access the specified member, or the specified member does not exist." error.

If we comment out the line which contains Dimension Properties "[Employee].[Employees].[Employees]" then this query returns the correct result.

There should be a workaround ...

AS 2008 works correctly in both cases.

Will think about that.
Nicolae.

Anonymous said...

Hi, guys!
After some investigation I found a decision.
So you should off checkbox in pivot table settings: page Output, checkbox show properties…

Valeriy

Dave Fackler said...

Valeriy,

Looks like that works! For those who have read this post, you can get the example I provided to work following Valeriy's suggestion. Here's how I did it:

After updating the connection string in Excel 2007, I opened the PivotTable Options dialog box and reviewed the Display tab. On that tab, if I uncheck the "Show properties in tooltips" option and close the dialog box, I can then drill down to see the Employees hierarchy correctly (as it works with SSMS and Excel 2003). Terrific!

Note that if I go back and check the "Show properties in tooltips" option, trying to drill down on the Employees hierarchy throws the error as previously described.

Thanks Valeriy!!

Dave F.

Teerapan L said...

I encountered the exact same problem 3-4 months ago. We had to work around by (1) removing all authorization at dimension level (at database level) and (2) setting authorization at dimension in the cube level instead. The drawback of this workaround was that you will have to set security for every cube eventhough the same the same dimension. However, it seems to be the only choice at the moment.

Regards,
Teerapan

Alex said...

Last time when I edited my working table in MS Excel,this file was corrupted.And I was worried.But after that my uncle said me about his like condition,and advised-xls file repair software.As he said tool is free and helped him very good.To my surprise utility helped me too and it can as well recover worksheets in Microsoft Excel format.

Alexis said...

On one forum I saw-viewer xlsx,tool was very interesting and recovered all my old mails!To my surprise it executed it in a minute for free.Moreover application showed,how recovery of corrupted worksheets in xltm, xlsx, xlt, xls, xlsm, xlam and xltx formats.

Alexis said...

Today I opened my MS Excel and understood that my file is empty.Moreover I couldn't know what to do.But a girl advised-unable to read file Excel,this tool helped for her.To my surprise I was lucky too.And the problem was decided in seconds and for free.

gooma said...

thanks guys! i spent whole day trying to get this to work. Thanks to you my problem i solved!

Adam Gilmore said...

Thank you. thank you. The 'Show properties in tooltips' worked for me. Note.. this is still an issue in Excel 2010