Advanced Dimension Data Security with SQL Server 2008

 

On a recent project, my client asked if we could secure members of a dimension by Active Directory groups.  For example, in a dimension named “Region”, there might be a “Southeast” region.  Members of the A/D group named Southeast should only be able to view the “Southeast” member. Below is an example of this, built using the AdventureWorks database.  In this example, the Geography dimension is secured.

The Dimensional Model

The goal is that when a client application (like Excel) issues the below query, Analysis Services returns only specific geographies the user is authorized to view.

SELECT
[Measures].[Sales Amount] ON COLUMNS,
[Geography].[City].MEMBERS ON ROWS
FROM [Adventure Works DW2008]

To secure the Geography dimension, two additional tables are added to the model: FactGeographySecurity and DimUser.   Here’s the DDL:

CREATE TABLE [dbo].[DimUser](
	[UserKey] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC
	)
)

CREATE TABLE [dbo].[FactGeographySecurity](
	[UserKey] [int] NOT NULL,
	[GeographyKey] [int] NOT NULL,
	CONSTRAINT [PK_FactDistrictSecurity] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC,
		[GeographyKey] ASC
	)
)

Now insert some test data into the new tables.  The below DML adds my user account, and then grants me access to cities in Great Britain and Canada.

INSERT DimUser (UserName) VALUES('blackd820\black')

INSERT FactGeographySecurity (UserKey, GeographyKey)
	SELECT 
		(SELECT UserKey FROM DimUser WHERE UserName = 'blackd820\black')
		,GeographyKey
	FROM DimGeography
	WHERE CountryRegionCode IN ('GB', 'CA')

In BIDS, add the DimGeography, DimReseller, and FactResellerSales tables to your Data Source View from the AdventureWorksDW database.  Also add the two new tables, DimUser and FactGeographySecurity.

 

Create database/cube dimensions for Geography, Reseller, and User.  Create Reseller Sales and Geography Security measure groups.  Your cube structure should now look like the below:

 

Applying Security

In BIDS, create a new role.  Mine is named “DimensionSecurity”.   On the “Membership” Tab add your user account (I added my “black” account).  On the “Dimension Data” Tab, open the “Advanced” sub-tab.  Select the “Geography” dimension, and the “City” attribute.  Then type the below MDX expression into the “Allowed member set” text box.   You can also check “Enable Visual Totals” here if totals should be tallied based on the filtered set, not the entire set.

Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[" + Username + "]"), "Geography Security")

The exists function returns a set that is based on filtering the first set by members of the second set.  And because the “Geography Security” measure group is specified, the second set, [User].[User Name].&[black], must also have associated rows in the FactGeographySecurity fact table.  Now it should make sense why we added FactGeographySecurity as a measure group, and DimUser as a dimension. 

 Note: this only secures the City attribute.  For each attribute in the drop-down list, type in an allowed member set similar to the one above.  Only the first parameter would change.  Ex., for the Country-Region attribute, the first parameter becomes [Geography].[Country-Region].MEMBERS.

Testing in BIDS

In BIDS, go to the cube browser, and click the “Change User” icon .  Select the “Roles” option, and choose “DimensionSecurity” from the drop-down list.  Expand Geography and drag the City attribute into the rows area.  Drag any measure from Reseller Sales into the totals area.  You should see, as in the below, that only cities from Great Britain and Canada are displayed.

 

Testing via MDX

Run the below MDX script in SSMS.  Notice how the “Allowed member set” MDX from above is used on the row axis, with the username hardcoded.  This might be useful if you just want to verify that the dimension/fact data is setup correctly. 

SELECT 
[Measures].[Sales Amount] ON COLUMNS, 
Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[blackd820\black]"), "Geography Security") ON ROWS 
FROM [Adventure Works DW2008]

 

Testing in Excel 2007

When connecting via Excel, you might expect it to just work.  After all, your account is in the “DimensionSecurity” role.  But if you are a server administrator, you will still see all of the City members, not just ones for Great Britain and Canada. But similar to the way you explicitly chose “DimensionSecurity” in the BIDS cube browser, you can test other roles by listing them (comma-delimited if more than one) in the connection string:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=blackd820;Initial Catalog=SSAS Security;Roles=DimensionSecurity

To do this, (I’m using Excel 2007), go to the “Data” tab and select “Properties”.  You’ll find the connection string on the “Definition” tab.  Add the part highlighted in yellow above.

Note: If you’ve only set security on the “City” attribute, expand “More fields” and select “City” from the PivotTable Field List pane.

Populate the security table from Active Directory

You probably noticed that security is defined in the FactGeographySecurity table at the grain of the individual user, instead of the Active Directory role.  This is because there is no IsUserInRole MDX function.  So now we have to maintain data in the FactGeographySecurity table to keep it in sync with Active Directory.  To do this, we need a way to query an A/D group and return a list of users. 

A utility application scheduled to execute nightly could be written to accomplish this.  The code snippet below retrieves a list of users in the specified A/D group; this list could be used to insert records into FactGeographySecurity. 

        /// 

        /// Gets a list of members in the specified group.
        /// 
        public static List GetGroupMembers(string groupName)
        {
            List users = new List();

            string domain = "somedomain.com";
            string filter = string.Format("(&(ObjectClass={0})(sAMAccountName={1}))", "group", groupName); // principal);
            string[] properties = new string[] { "fullname" };
            DirectoryEntry adRoot = new DirectoryEntry("LDAP://" + domain, null, null, AuthenticationTypes.Secure);
            DirectorySearcher searcher = new DirectorySearcher(adRoot);
            searcher.SearchScope = SearchScope.Subtree;
            searcher.ReferralChasing = ReferralChasingOption.All;
            searcher.PropertiesToLoad.AddRange(properties);
            searcher.Filter = filter;
            SearchResult result = searcher.FindOne();

            if (result != null)
            {

                DirectoryEntry directoryEntry = result.GetDirectoryEntry();
                foreach (object dn in directoryEntry.Properties["member"])
                {
                    DirectoryEntry member = new DirectoryEntry("LDAP://" + dn);

                    //Add users from other groups within this group (only go 1 level deep).
                    if (!IsGroup(member))
                    {
                        users.Add(member);
                    }
                }
            }
            return users;

        }

        /// 

        /// Determine whether the object is a group.
        /// 
        private static bool IsGroup(DirectoryEntry de){
            return de.Properties["objectClass"][1].ToString() == "group";
        }

Finally

Using this approach, each dimension can be secured by creating only a single role.  This satisfied my client,  who did not want to manually create a role per geography, then go into the role definition and manually select dimensions available to that role.

Hope this helps!

SSIS Error from DTEXEC: Login timeout expired

When executing an SSIS package that is stored in the MSDB database (i.e. stored in SQL Server) you may experience an error message like this:

Could not load package "\PACKAGENAME" because of error 0xC0014062.Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.

In my situation the SQL Server database where the SSIS package is deployed is a named instance. There is no default SQL Server instance on the server. The command line that I used to execute the package was:

DTEXEC /SQL "\PACKAGENAME"

The problem is that the above command is trying to load the SSIS package from the MSDB database in the default instance of SQL Server and there isn't one. The solution is quite simple; add the /SERVER command line parameter with the server\instance of the SQL Server; e.g.

DTEXEC /SQL "\ADSECURITYSYNC" /SERVER "SERVERNANE\INSTANCENAME"

Dynamic Set for MDX Calculations

PROBLEM

I have been running into a problem trying to get the MIN, MAX and MEDIAN MDX calculations to work against a dynamic set as opposed to setting a static set in these Calculated Members in my cube. However, there was a wrinkle to my problem, the dynamic set would go across attributes of the same dimension and this is where I was running into problems.

For simplicity, I have the following tables in my warehouse: LOAN, GEOGRAPHY, LOAN_FACT, TIME

• In the LOAN, I have an ID and a LOADING SYS field. The LOADING SYS field describes what source system the Loan came from
• In the Geography table, I have a Geography ID and Geography Name (very simplistic for this post)
• In the LOAN_FACT, I have the Loan Id, Geography Id, Time Id and EOM BAL.

When I create my cube, the LOAN FACT table is the FACT table and the other tables are the Dimensions of the cube.

So far, so simple.

Now, I want to create calculated members within the cube that gives me the MIN, MAX, and MEDIAN EOM BAL across whatever is selected by the user with the understanding one principal, the LOADING SYS is a very important breakdown of the data.

SOLUTION TAKE 1

I started with the following calculation:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN([LOAN].[LOAN].children, [MEASURES].[EOM BAL])

Then a select statement like the following would be expected to give me my result:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *

[LOAN].[LOADING SYS].CHILDREN

) on ROWS

FROM [LOAN CUBE]

However, this did not work as it went through and game me the lowest EOM BAL for every loan within a Geography regardless of the LOADING SYS and then repeated that value for each LOADING SYS even if the LOADING SYS did not exist for a GEOGRAPHY. A Really Bad Answer!

SOLUTION TAKE 2

Taking another stab at the calculation, I updated it to read:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), [MEASURES].[EOM BAL])

This answer came close as it would give me the lowest loan value within a GEOGRAPHY and only present those LOADING SYS values that actually existed. However, it would still only give me the MIN EOM BAL within the GEOGRAPHY regardless of the LOADING SYS, so it still was incorrect.

CORRECT SOLUTION

After a little research, I found that I could setup my CURRENT LOADING SYS as a CALCUALTED MEMBER against the MEASURES Parent Hierarchy. Once I had that, I could setup my LOAN set to use this member. The following two CALCULATED MEMBERS were then created and viola, my answer that I wanted:

CREATE MEMBER CURRENTCUBE.[MEASURES].[CURRENT LOADING SYS] AS

[LOAN].[LOADING SYS].CURRENTMEMBER.NAME

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS
MIN(
(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), IIF([MEASURES].[CURRENT LOADING SYS] = 'ALL', [MEASURES].[CURRENT LOADING SYS], STRTOMEMBER([MEASURES].[CURRENT LOADING SYS]))),
[MEASURES].[EOM BAL])

Now, if I run the following again:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *

[LOAN].[LOADING SYS].CHILDREN

) on ROWS

FROM [LOAN CUBE]

What I return is the MIN EOM BAL within a GEOGRAPHY and LOADING SYS. If I take GEOGRAPHY out of the SELECT, then the query will return the MIN EOM BAL within a LOADING SYS. If I add another dimension to the query, maybe TIME.YEAR for instance, I get the proper results as well.

A couple of things to notice:

• I had to check for the ALL member as the STRTOMEMBER function would not return a result so my Grand Total would error out without this check.
• I still used the DESCENDANTS function to get to the lowest loan grain within the context of the query

CONCLUSION

One can take advantage of the CURRENTMEMBER of a dimension's query context to obtain a different attribute's value within that DIMENSION. This value can then be used in a later calculation for basically creating a DYNAMIC SET according to the query context and essentially grouping by that value for your calculation.

Using SSIS to store and retrieve SharePoint List Data

Out of the box, SQL Server Integration Services provides a rich platform for building data integration and transformation solutions. The included SQL Server Business Intelligence Development Studio allows those who might not consider themselves 'coders' to create complex ETL packages that target the heart of most businesses - their data.

However, one thing seems missing from the out of the box SSIS experience; That is the ability to use SharePoint Lists as data flow sources and destinations.

Some might find this puzzling, given the great integration story found built into the Office suite of products - allowing Excel and Access to connect and retrieve data contained in SharePoint lists.

Fortunately, there's a great community that surrounds SharePoint and provides solutions for common scenarios such as this one.

The SharePoint List Source and Destination sample available on the Microsoft SQL Server Community Samples: Integration Services page on Codeplex provides a solution for SSIS Package creators to use SharePoint as a source as well as a destination in their SSIS data flows.

In use, the sample provides SharePoint Data Flow Source and Destination components which allow the user to the SharePoint site URL and list name to use. Once configured, these Data Flow components can be used in conjunction with any other SSIS Data Flow Components.


You can find these components, available for both SQL Server 2005 and SQL Server 2008, as well as sample instructions at the following locations:

SharePoint List Source and Destination sample
MSDN Article