Protect UDM with Dimension Data Security, Part 2

At this point, you might be concerned with the performance implications of the factless fact table approach. After all, thousands of authorized members could exist for each user, which might cause the fact table to increase considerably in size. In case you're wondering whether the SSAS storage engine can evaluate large allowed sets efficiently, I did a performance study for a real-life UDM with several million rows in the fact table; my study showed that the server was capable of constructing the allowed set in several seconds!

Excellent performance is the most important advantage of the factless fact table approach. Because the server applies security when each user connects, minimizing the time to initialize dimension data is essential. You might find other ways to further boost performance based on your requirements. For example, if an employee has access to all resellers, don't import all members into the fact table. Instead, consider adding a new attribute to the Employee dimension that flags power users. Then, shortcut your allowed set expression to bypass the Exists function and return all members.

Using SSAS Stored Procedures to Externalize Security
What if the factless fact table approach isn't an option? For example, if you need to secure several dimensions, importing security policies into a fact table might be counterproductive. Or, you might have no other choice but to obtain the allowed set from an external security service at runtime. SSAS stored procedures can help in these situations. Although SSAS stored procedures might not be as efficient as the factless fact table approach, they give you more flexibility. To illustrate this benefit, let's create an SSAS stored procedure to return the authorized resellers.

Implementing SSAS stored procedures. An SSAS stored procedure is nothing more than a method in a .NET assembly that's deployed to the SSAS server. Web Listing 3 shows the GetSecurityFilter stored procedure from the Extensibility VB.NET class library project.

GetSecurityFilter returns a Microsoft.Analysis-Services.AdomdServer.Set object for the allowed set. For the sake of simplicity, GetSecurityFilter queries the AdventureWorksDW relational database to obtain the authorized resellers for the interactive user. Next, GetSecurityFilter enumerates through the dataset and constructs the allowed set.

StrToSet vs. SetBuilder. Two options exist for creating an MDX set in an SSAS stored procedure. The first option should be familiar to readers experienced with SSAS 2000. It uses the StrToSet function to convert a comma-delimited string of tuples to an MDX set. The second option is new with SSAS 2005. It uses the Microsoft.AnalysisServices .AdomdServer.SetBuilder object to construct the set. SetBuilder far outperforms StrToSet. Table 1 shows performance statistics gathered from a reallife project that compared both options.

As the number of rows increases, SetBuilder's performance advantage becomes clear. Note that 40,000 records took 272 seconds to convert to a set with StrToSet and 144 seconds with SetBuilder (for a 47 percent performance increase). However, even with SetBuilder the SSAS stored procedure approach is much slower than using a factless fact table.

Note that the server invokes the security stored procedure repeatedly when initializing a user session, once for each attribute hierarchy in the secured dimension. To avoid repeated calls to the external security service, cache the allowed and denied sets per user (e.g., by using the .NET HttpRuntime.Cache object for in-memory caching.

Setting up and testing the database role. To set up and test a database role that uses the GetSecurity- Filter stored procedure, follow these steps:

1. Add a project reference to the Extensibility assembly in your SSAS project. To do so, rightclick the Assemblies folder and select New Assembly Reference.
2. In the Add Reference dialog, click the Projects tab and select the Extensibility project. Click the Add button to add the Extensibility project to the Selected Projects and Components pane, and click OK.
3. In Solution Explorer, select the Extensibility assembly and change its Permission Set property to External Access.
4. Create a new database role called External. Grant the role read access to the Adventure Works cube. Use the following allowed MDX expression for the allowed set of the Reseller Name attribute:

Extensibility.GetSecurityFilter
(Username)

5. Deploy the project and test the External role as you tested the Factless role.

What if the stored procedure discovers that the user has rights to see all members? From a performance standpoint, letting the server bypass the set expression makes sense. Unfortunately, no method exists for shortcutting the MDX expression (e.g., by returning a null set from the stored procedure). Instead of returning a set with all members, consider assigning power users to a privileged database role. Or, if possible, add an attribute that flags these users and use the IIF function in the set expression to check the flag and bypass the stored procedure call.

You can use the following MDX query in SSMS to test the GetSecurityFilter stored procedure:

SELECT [Measures].[Reseller Sales-
Sales Amount] ON 0,
Extensibility.GetSecurityFilter
(â??adventure-works\stephen0â??) ON 1
FROM [Adventure Works]

As with the factless fact table approach, the query should return only the resellers that Stephen Jiang is authorized to see.

Secure UDM Data
Dynamic dimension security lets you construct allowed and denied sets programmatically and apply user-specific security policies at runtime. The cornerstone of dynamic data security is the Username function, which returns the interactive user's Windows identity. If you need to obtain the security policies from an external system, use the factless fact table approach for maximum performance. Using an SSAS stored procedure lets you meet most demanding integration requirements.

Download the associated code.