Geeks With Blogs
Andrea B. Williams The Pragmatic Optimist

To address user dynamic security for a specific dimension, we have used the technique we found from the article “Implementing User-Specific Security in SSAS” from Hitachi Consulting Microsoft BI found at This approach defines 2 new tables to the AdventureWorksDW database, DimUser and the fact-less fact table FactResellerUser. The DimUser table contains the UserName column which will be set to the domain user. The FactResellerUser table contains relationship between User and Reseller. It contains the UserKey and ResellerKey. The AdventureWorks cube was defined with a new measure group called Reseller User which contains the association of User and Reseller. We defined a new role which uses dimension data security for the Reseller Attribute hierarchy. The MDX expression for the Allowed set is the following:

EXISTS([Reseller].[Reseller].Members, STRTOSET(“[User].[UserName].[“+Username+]”), ‘Reseller User’).

If you log into SQL Server using the cube browser or Excel as the new role and you select Reseller dimension, you only see the Reseller defined for that user. This works wonderfully. We can get Excel to use a Report Filter for UserName. We then defined a current user web part to pass the current user name to the Excel Web part for the Excel report thru the Report Filter parameter called UserName. This works as well.  Hurray so far...

Now, we need to render the Scorecard using Business Scorecard Manager showing data specific to the user logged into the Sharepoint site. Oops - no love here.  We cannot get the BSM scorecard to work using the user specific login (on the site). It continues to show the KPI’s across all Resellers.  From what we can tell, BSM is using the hardwired user set up in its IIS app pool.  The only way we can get Business Scorecard Manager to show the single reseller set up for that user is to define a row member specific to the reseller in the scorecard view. This approach means we would need a specific scorecard and specific Sharepoint site for each end user we want to have access to the scorecard view. Surely there is a better way of having Scorecard manager display only the Reseller associated with the user logged in to the Sharepoint Site.  We are now off to look at the latest Performance Point Server server version that includes BSM in it to see if it solves our problems, but if anyone out there has any suggestions, we'd love to hear from you!

Posted on Friday, October 19, 2007 2:24 PM Microsoft BI | Back to top

Comments on this post: Microsoft Business Scorecard Manager and User-specific Security

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Andrea B. Williams | Powered by: