SQL Server Magazine

As expected, the report shows data only for Stephen Jiang and the AdventureWorks employees who report to him. For example, Amy Alberts isn't shown on the report because she's at the same level as Stephen in the Employees hierarchy. You might be surprised to see that the report shows Stephen's managers (Brian Welcker and Ken Sanchez). This is because enabling a member in a parent-child hierarchy automatically grants access to the member's ancestors. Consider enabling Visual Total on the Advanced tab (Dimension Data tab in the Cube Browser) if you want the ancestors' totals to be contributed only by the allowed descendants.

As you can see, MDX expressions and the Username function give you a lot of flexibility to secure UDM data. Now let's discuss two more advanced scenarios for dynamic dimension security.

Importing Security Policies into a Factless Fact Table
Suppose that the AdventureWorks online transaction processing (OLTP) application has complex authorization rules that dictate which resellers an employee is authorized to see. Your task is to propagate the same rules to UDM. One option is to replicate the security infrastructure from the source application. However, doing so will result in duplication of the management effort necessary to maintain the security policies in two places. Instead, consider importing only the authorized members into a fact table that acts as a security filter. This would require importing a dataset that contains the authorized resellers per employee. The necessary steps to use this approach include implementing the fact table, implementing the measure group, implementing the database role, and testing the database role.

Implementing the fact table. A new fact table
(FactSecurityFilter) is required to store the authorized resellers for each employee, as Figure 2 shows. Its schema is simple. The table has two columns that store the identifiers (primary keys) of employees and authorized resellers. For example, if Stephen Jiang has a primary key of 272 in the source database and he is authorized to see resellers Associated Bikes (primary key 7) and Finer Mart (primary key 38), the fact table will have the following rows.

The FactSecurityFilter fact table doesn't have any numeric facts (hence the term "factless" fact table). Note also that FactSecurityFilter doesn't keep an historical record of the security policies (i.e., it doesn't join the DimDate table). The assumption is that the last security policy prevails. For example, if Stephen is authorized to see a reseller in the current period, he will be granted access to that reseller even though he might not have been authorized in the previous period.

The SQL script FactlessFactTable.sql lets you create and populate the FactSecurityFilter fact table. An extraction, transformation, and loading (ETL) task could refresh the FactSecurityFilter fact table on a regular basis (e.g., as part of the data import process).

Implementing the measure group. Next, you need to map the Fact-SecurityFilter table to a UDM measure group as you would any other fact table, by following these general steps:

1. In BIDS, add the fact table to the data source view (DSV).
2. Create a new Security Filter measure group that uses FactSecurityFilter as a source, as Figure 3 shows.
3. A measure group must have at least one measure; therefore, leave the system-generated Security Filter Count measure. Don't set the Visible property of the measure to False in an attempt to hide the Security Filter measure group from the end user. If you do so, the MDX expression for the allowed set won't work (reported as a bug).
4. Select the Dimension Usage tab in the Cube Designer to verify the dimension relationships to the Security Filter measure group.

At this point, the Cube Designer should have created two dimension relationships (Reseller and Employee) to the Security Filter measure group. (More detailed instructions for working with DSVs and measure groups in BIDS can be found in the SQL Server Books Online - BOL - SQL Server 2005 Analysis Services Tutorial, at http://msdn2.microsoft.com/en-us/library/ms170208.aspx.) Implementing the database role. With the Security Filter measure group in place, you're ready to set up a database role.

1. Right-click the Roles folder in the Solution Explorer window in BIDS and select New Role.
2. In the Solution Explorer window, rename the new role in place to Factless.
3. In the Role Designer, switch to the Cubes tab and grant the Factless role read access to the Adventure Works cube.
4. In the Dimension Data tab, select the Reseller cube dimension and the Reseller Name attribute.
5. Enter the MDX expression for the allowed member set, as Figure 4 shows. This expression uses the Exists function to select the associated resellers for the interactive users over the Security Filter measure group. Note that you don't have to use the attribute hierarchies that map to the keys in the fact table. For example, I use the Login ID attribute hierarchy in the expression instead of Employee Name.

Testing the database role. To quickly test the new role, replace the Username function with a member. For instance, use the following expression to test the database role for Stephen Jiang:

Exists([Reseller].[Reseller Name].
MEMBERS,
StrToMember("[Employee].[Login
ID].&[adventure-works\stephen0]"),
"Security Filter")

Follow these steps to test the Factless role:

1. Deploy the project.

2. Right-click the Adventure Works cube and select Browse.

3. Click the Change User toolbar button in the Cube Browser and select the Factless role.

4. Create a report that has Reseller Name on rows and Reseller Sales-Sales Amount measure on data.

Figure 5 shows the report results for Stephen Jiang. The report shows only 45 resellers (out of 702 total), because Stephen Jiang is associated with these resellers only in FactSecurityFilter. In SSMS, you can use the following MDX query to verify the expression against the Dimension Security database:

select [Measures].[Reseller Sales-
Sales Amount] on 0,
Exists([Reseller].[Reseller
Name].[Reseller Name].MEMBERS,
[Employee].[Employee Name].&[272],
"Security Filter") on 1
from [Adventure Works]

Continued on page 3

Prev. page     1 [2] 3     next page