MDX Queries and Syntax

SAS OLAP Security Totals and Permission Conditions

As part of the SAS security model, SAS OLAP cubes can have member-level authorizations applied as permission conditions. Permission conditions limit access to a cube dimension so that only designated portions of the data is visible to a user or group of users. These permission conditions can affect the rolled-up values for measures at query time. In order for a cube to control the roll-up values for designated members, the PROC OLAP option SECURITY_SUBSET = YES must be set when the cube is built. In addition, users who access the cube must have the necessary permissions to see the members in the roll-up values. If the PROC OLAP option SECURITY_SUBSET = YES is set for a cube, then the rolled-up values will only include those members that the user has permission to see.

When you create MDX queries for security totals, there is no designated MDX code that needs to be written in order to apply security totals to a cube. The only difference between a query written against a cube without the SECURITY_SUBSET option and the same query written against a cube with the SECURITY_SUBSET option is in the values of the output.

Example 1-- Applying the SECURITY_SUBSET Option to an MDX Query

Below is an example of an MDX query:

SELECT measures on_columns,
dealers.members on_rows
FROM mddbcars

This query has the following applied permission condition:

{[dealers].[all dealers],
descendants([dealers].[all dealers].[smith])}

Here is the resulting data table if the SECURITY_SUBSET option has not been set.

[untitled graphic]

However, if the SECURITY_SUBSET option has been set to YES, then here is the resulting data table:

[untitled graphic]

Note that themembers displayed in both resulting data tables don't change. This is because both data tables were built with the same permission condition. It is the final value for the All Dealers member that changes from $229,000 in the first table to $108,000 in the second table and shows the sales value of Smith only. The $229,000 in the first table includes sales figures for all dealers.

Example 2-- Applying the SECURITY_SUBSET Option to an MDX Query

Here is a second example of an MDX query:

SELECT measures on 0,
date.members on 1
FROM mddbcars

This query has the following applied permission condition:

{[dealers].[all dealers],
descendants([dealers].[all dealers].[smith])}

Here is the resulting data table:

[untitled graphic]

However, if the SECURITY_SUBSET option is applied, then the resulting data table is as follows:

[untitled graphic]

Note that the members in the output data are the same for both queries. It is the date member values that are different. The table values in the first data set reflect sales values for All Dealers, even if the dealers are not displayed. The table values in the second data set reflect sales values for Smith only. Specifically, you can see that there were no sales for Smith during May. It is when the SECURITY_SUBSET option is applied in the second data table that the sales values reflect only dealer Smith.

Default Member and the All Member

Every dimension for a cube has a default member. That member is implicitly used if no other members of that dimension are explicitly selected in a cube query. In addition, if you don't have permission to see the default member, then the default member will be the first member in the permission condition set. Usually, the All member of a dimension is also the default member.

Note: The All member (parent of the highest level node in the cube) is a system-generated member. It does not have a corresponding column in the underlying data table. [cautionend]

Virtual Members and Security Totals

Virtual members are associated with those records that have missing values in one or more columns. The values associated with virtual members will be included in the roll-up for security totals if you have permission conditions set to see the virtual parent of the virtual member.

For example, here is a sample data set.

DISTRICT  REGION      ACTUAL   BUDGET
.         .             30      5
Atlantic  .             10     35
Atlantic  Eastern US     5      .
Atlantic  Great Britain 12      .
Atlantic  France         8      .
Atlantic  Spain          5      .
Pacific   .             -5     30
Pacific   Western US     8      .
Pacific   Japan         12      .
Pacific   Korea         10      .

In this data set, if the user has a permission conditions to see the following:

{[salesregion].[all regions],    
[salesregion].[all regions].[atlantic],    
[salesregion].[all regions].[atlantic].children}

then the value for the [salesregion].[all regions] member would include records from rows 1 through 6.

Here is a possible query of that data:

SELECT    
    measures on 0,   
    salesregion.members on 1 
FROM nonleaf

Here is the resulting data from that query:

             sum of actual
  all regions            70
     atlantic            40
   eastern us             5
great britain            12
       france             8
        spain             5

These are the values you will see when the permission conditon is set and the SECURITY_SUBSET option is set to YES. Note that if the permission condition is set, but the SECURITY_SUBSET option is not set, then the values will be different.

Top of Page