PPS Data Connection Security with CustomData

There are two well-documented data source security methods available in PerfornancePoint Monitoring & Analyzing: the default Application Security model and Per User Connections. However, as a wise green creature once whispered, "No. There is another..."

More often than not security requirements for a BI solution are based around certain users only being able to access specific members of several important dimensions. CustomData security functionality in PPS serves this purpose very well and provides a solution that sits between the two already well-known data security methods.

Application Security - a single application pool account is used to make all connections to data sources.

• Advantage: because one account makes the data source connections the monitoring server caches results providing greater speed responsiveness to all users.
• Disadvantage: no data security is possible based on who the user is because all users are connecting via one the account; the database does not know who they are.

For more details on the common connection pitfalls of application security please see this post.

Per User Connections - each user's credentials are used to connect to data sources.

• Advantage: highly secure, naturally this security is dependant on there being some sort of existing security framework within the data source itself.
• Disadvantage: little or no caching of dashboard data can occur as each data request is made with the individual user's credentials. Dashboard responsiveness and speed can take a hit.

Security is important to all the clients I work with but I always advocate careful thought and consideration before simply switching on Per User connections on a PPS Monitoring server. There is definite performance hit that comes from the fact that each user gets his or her own data. For more information on the performance difference between the two principal security methods please see the PPS Performance Tuning and Capacity Planning whitepaper.

At the time of this writing, not much official documentation exists on CustomData security, hence the reason for this post. The only reason I knew of its existence originally was this post on the PPS team site some time ago. In that post lip service is paid to the CustomData option but we're really told much more than "it's possible".

What is CustomData Security?

CustomData security is a middle ground between application and per user security. We still use an application pool account to connect to the data source, but we also have access to data about the Domain\Username of each user who is interacting with the deployed dashboard. We use this user-specific data to drive security dynamically using information configured in the relational database the cube sources data from. This alleviates having to configure and maintain many SSAS database roles tied to specific domain accounts or groups. Instead we configure a single, dynamic MDX based role which will be detailed later.

NB: there is no discernable data caching advantage to using CustomData Security. The performance is much the same as when using the PerUserSecurity model. If performance is the most important factor in a PPS deployment then Application Security is certainly the best performing option.

What is Dynamic Dimension Security?

Limiting user access to certain members of a particular dimension attribute hierarchy based on their windows credentials is nothing new. For example, dynamic dimension security may decree that when I look at a dashboard I can only see Australian states in the State/Province dashboard filter.

...while on the other hand Adrian should only see a list of Canadian provinces.

The majority of work to set up CustomData security involves the creation of a dynamic dimension security framework in your multidimensional data source and the relational source that feeds it, not PPS. If you already have security of this kind set up on your UDM then you're already most of the way there. The set up is minimal on the PPS side of things.

How I set up SSAS dynamic dimension security?

Providing any user-driven security access to PPS data cannot function without some form of back-end, SSAS and relational security infrastructure. There's a little bit to do to set this up, so instead of going into a long-winded walk-through I will instead point to two excellent articles/posts on the topic. Both of these documents cover similar ground. Using a dimension containing user details and a factless fact table to relate each user to the specific members of the dimension they are allowed to see e.g. Reseller Stores. Once the relational database infrastructure and data is in place, a SSAS role is set up containing MDX code to return a set of the allowed dimension members for the current user. I highly recommend reading at least one, if not both in order to full understand the remainder of this post.

• Brian C Smith of Hitachi Consulting wrote this blog post that contains sample T-SQL code and steps to set up a simple UDM.
• Teo Lachev wrote this article for SQLMAG in which he covers a number of different techniques including the factless fact approach.

These are just two examples of how to implement dynamic dimension security. I'm sure there are many other creative ways to implement this kind of functionality.

How do I turn on CustomData security?

As mentioned earlier, the PPS configuration is minimal. Bpm.UseASCustomData is a boolean attribute that can be found in the same web.config files as the Bpm.ServerPerUserConnection attribute. Change the BPM.UseASCustomData attribute from False to True in the appropriate web.config files as listed below.

• Preview Site: %\Program Files\Microsoft Office PerformancePointServer\3.0\Monitoring\PPSMonitoring_1\Preview\
• Web Service: %\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\
• SharePoint: %\Inetpub\wwwroot\wss\VirtualDirectories\80\

NB It is very important to remember that the Bpm.ServerPerUserConnection be set to False when using CustomData security. We want to connect to the data source using the Application Pool account, not each individual user. Turning on Per User connections completely defeats the purpose of the security method being discussed here.

What does UseASCustomData do?

When Bpm.UseASCustomData is switched to True any PPS data source connections made to Analysis Services 2005 contain an extra attribute on the connection string: <CustomData>. The CustomData connection string attribute was added to SSAS 2005. The Monitoring server will populate this attribute with the Domain\Username of the user making the request. The data source connection itself is still being made as the application pool account.

This can be particularly useful in multi-server environment where Kerberos is not configured and the double-hop issue is encountered. The information in the CustomData field can now be used to identify the user without the need for Kerberos.

How do I confirm that CustomData is switched on?

Once you have made the adjustments to the appropriate web.config files, set up a SQL Profiler trace on the SSAS server where your cube is located. Use Dashboard Designer and run a Test Connection on one of your SSAS data sources.

As you can see from the shot below the connection to the cube is being made by my Application Pool account, NETWORK SERVICE (in a production environment you would want to be using a specific domain account for the app pool identity). More importantly note that there is a <CustomData></CustomData> attribute that has been added to the connection meta data containing my Domain\Username details.

How do I make use of the CustomData attribute?

In order to get at the Domain\Username string that PPS provides within CustomData we use the CUSTOMDATA() MDX function. The CustomData connection string attribute does nothing more than hold a string of characters. The CUSTOMDATA() MDX function simply returns that character string to us, we can do whatever we want with it from there. There isn't a great deal of information out there about this function but Chris Webb posted about it a while back. In that post he pondered what this function could come in handy for. Hey Chris, I think this is a good one.

In MDX you can gain access to a string containing the Domain\Username of the currently connected user by via the USERNAME MDX function. USERNAME is the MDX equivalent of the T-SQL SUSER_NAME() function. Try running this query in SSMS to return your Domain\Username.

WITH MEMBER [Measures].[MyUsername] AS USERNAME
SELECT
[Measures].[MyUsername] ON 0
FROM [Adventure Works]

The USERNAME function is used in the articles listed above to access the Domain\Username string for the connected user to drive the dimensional security. The functionality detailed by Teo and Brian is relevant when a user connects to the cube "as themselves" i.e. they are accessing the cube with their individual Windows credentials via Excel, SSMS, ProClarity or even PPS with Per User security. Because of this SSAS knows who each user is.

With PPS CustomData security our aim is a little different: we want to avoid connecting to the cube with individual user credentials. Under the application security model the connection is always being made by the same application pool account. In this case the USERNAME function is useless to us. It will always return the same value: that of the application pool identity account (in my example trace above it would return NT AUTHORITY\NETWORK SERVICE every time). When UseASCustomData is switched on, the Monitoring Server sneakily adds the Domain\Username string to the CustomData attribute for each individual user who interacts with a dashboard. Now, if we don't do anything else the string in the CustomData attribute will simply sit there benignly minding its own business.

Here's the trick: using the same techniques detailed by Teo and Brian we replace the USERNAME MDX function with CUSTOMDATA(). This way we are able to access the Domain\Username value in the CustomData connection string attribute to implement the same dynamic functionality without connections being made on a per user basis.

Testing CUSTOMDATA() within Dashboard Designer

Here's another way of testing that CustomData security is configured on the Monitoring server. Within Dashboard Designer create an analytic grid report and enter the following MDX in the Query pane.

WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()
SELECT
{ [Measures].[MyUsername] } ON 0,
NON EMPTY { [Geography].[Geography].[All Geographies] } ON 1 
FROM [Adventure Works]

Switch to the Design pane to check that your own Domain\Username is returned.

Typically you will not use CUSTOMDATA() in an MDX statement within Dashboard Designer. It is best to keep the MDX code that drives the security within the roles managed by SSAS as described in the referenced articles.

How do I set up dynamic dashboard filters?

Once the Bpm.UseASCustomData attribute is set to True and your back-end SSAS security framework is set up the creation of dynamic filters could not be easier. The heavy lifting is being done by the SSAS roles. The easiest filter to create is an MDX Query filter type. All that is needed to populate the example State/Province filter that limits Adrian and my access to the states/provinces of our respective countries of birth is the following, very simple, MDX.

Note that I do not need to reference the CUSTOMDATA() MDX function here. All that logic should be contained in the SSAS role that defines the security behaviour.

A word on roles

If you have gone to the trouble of setting up a user-driven security infrastructure in your SSAS cube the last thing you want to do is limit the security access to just PerformancePoint monitoring. All other cube browsing applications should be able to take advantage of what has been created. The simple way is to create at least two roles: one (or more) for all users to connect with user/role based Windows security for Excel, ProClarity, SSMS etc.etc. and a second role for PPS data source connections.

The Windows based role/s grants access to whichever domain groups and users require it. This is the general windows security access role for non-PPS connections. Users are recognised by their windows credentials and the MDX USERNAME function is referenced to apply security in the dimension security MDX statements.

The PPS role need only provide access to the domain account used by the PPS application pool identity. This role uses the CUSTOMDATA() function in the dimension security MDX statements instead of USERNAME. The role name is then referenced in the PPS data source definition as seen below so that we can be sure the connection to SSAS is made using the permissions defined therein.

 

A role security MDX tip

I have learned from reading Mosha's blog that you should try to avoid using STRTOMEMBER or STRTOSET functions if possible for performance reasons. The sample code in the dimension security articles use these functions in the dimension security MDX. I have provided some sample MDX code that alleviates the need for the STRTO... functions. Note that the sample code contains references to both CUSTOMDATA and USERNAME functions, you can only use one at a time.

For example, instead of

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
STRTOMEMBER("[Employee].[Login ID].&[" + CUSTOMDATA() | USERNAME + "]")
,"Security Filter"
)

Try using this

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
FILTER(
  [Employee].[Employee].[Employee].MEMBERS,
  [Employee].[Employee].CURRENTMEMBER.PROPERTIES("Login ID") = CUSTOMDATA() | USERNAME
 )
 ,"Security Filter"
)

UPDATE: 29th Feb 2008. David Gustavson, author of the Performance Tuning and Capacity Planning for PerformancePoint Monitoring Server whitepaper has informed me that there is virtually no caching benefit when using the CustomData data connection method described here. The original version of this post indicated that CustomData provides similar caching performance to Application Security - this is incorrect. In fact the cache performance is not much better than using Per User Connections. I have altered the post to reflect this.