RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Miscellaneous C#, SQL, and Business Intelligence Development Tips : Page 4

Windows Communication Foundation is one of the most important features in the history of .NET. Not only does WCF provide a unified programming model for communications, it also promotes the use of interfaces and various recommended programming practices.


Tip 8: Defining Data Access Roles in Analysis Services/OLAP Environment

Do any of the following scenarios sound familiar?

  • User X should see data only for Accounts A, B, and C.
  • User Y should see data only for Market A and Product B.
  • For Product A, User Z can see all Markets. But for Product B, user Z should see data only for Market C.
Organizations expect software systems to filter data based on user roles and definitions—and will react very harshly if users are allowed to see data beyond their authorization levels! In relational database systems using SQL Server, developers usually must build their own authorization methodology—SQL Server does not contain any built-in functionality for row-level authorization. That means developers must modify SQL queries to take into account any user definitions.

By contrast, Microsoft Analysis Services 2005 makes it a breeze to define user authorization roles in OLAP databases. Here's an illustrated walkthrough for defining access to the Bikes product category in the AdventureWorks DW database:

Figure 4. New Role: New Analysis Services database roles appear in the tree view.
  1. Connect to SQL Server Analysis Services (in SQL Server Management Studio). Then navigate in Object Explorer to the OLAP database you want to access, expand the database, and right-click on the Roles list to add a new role (see Figure 4).
  2. In the edit role dialog box (see Figure 5) provide a Role Name and a description. Also, set the database permissions (for most users, only the read definition will be checked).
    Figure 5. Defining a Role: Fill out the dialog to add a new role.
    Figure 6. Adding Users/Groups: To add users and/or user groups for the role, enter or select them in this dialog.

  4. In the membership page (see Figure 6) add the users and/or user groups that will be part of this role. (The user names/groups come from the list of active Windows user names.)
  5. Select the database and cubes for access (see Figure 7 and Figure 8).
    Figure 7. Setting Access: You can restrict access levels to the data source.
    Figure 8. Cube Access: You can restrict access levels for specific cubes.

  7. Important! Go to the Dimension Data page (see Figure 9) and define the dimension/attribute/dimension member associated with the role. In this example, I've chosen the Bike category. (I also could have selected the Geography dimension to further refine access by any level in the geography hierarchy.)
    Figure 9. Dimension Member Access: You can even define access levels to specific dimension members (such as Bikes and all data under Bikes).
    Figure 10. Access to Pre-Defined Measures: For each role, you can allow or deny access to pre-defined measures.

  9. Finally, I can also restrict access to specific measures in the OLAP cube (see Figure 10).
For anyone who has ever had to write custom SQL code to implement user authorizations, the capabilities in Analysis Services to utilize the UDM (Unified Dimension Model) make this task a breeze!

Tip 9: Custom OLAP Date Dimension and Handling Seasonality with MDX

Without question, one of the many reasons that organizations seek OLAP functionality is to analyze performance over a period of time. Therefore, period (i.e., date)-handling functions in MDX are critical.

MDX contains several functions, such as PeriodsToDate, ParallelPeriod, LastPeriods, etc., for analytical purposes. The AdventureWorks DW sample database contains examples of these functions, as well as a good example of multiple date hierarchies (Year, Quarter, Month, for both a fiscal calendar and a regular calendar).

Those capabilities are sufficient for many organizations. However, suppose a company analyzes data by seasonality, where the exact dates differ every year. For example, Thanksgiving (and the famous shopping day, "Black Friday") fall on a different date every year—sometimes closer to December 1 than others. Companies might want to analyze sales across years for the week before/week after. Similarly, sporting events such as the Super Bowl and World Series fall on different dates every year.

An even larger example is Lent; the week of Lent begins at a different time every year. A company that tracks sales by Lent (i.e., fish products) cannot simply compare sales from February/March of 2007 to those in February/March of 2006.

To solve such problems, those responsible for maintaining OLAP databases must define additional information in the source of a date calendar dimension. One solution would be to add a LentWeekNum numeric column into the source staging area for a date calendar, where the value represents the Lent Week number associated with that specific date/week.

With all the supporting data in place, a developer could write an MDX query like the one below, which lines up Lent Weeks for 2007 and 2006 using the LentWeekNum and the MDX ParallelPeriod function.

   // MDX code to handle seasonality (Lent weeks)
   WITH MEMBER [LastYearLent] AS
       ([Measures].[Sale Amount], 
          ParallelPeriod([Date Calendar].
                        [Bz Calendar Hierarchy].[Year],1))
   SELECT {[Measures].[Sale Amount], [LastYearLent]} 
      [Date Calendar].[Lent Week Num].[1] :
      [Date Calendar].[Lent Week Num].[8]  ON ROWS
   FROM  [Test Cube] 
   WHERE [Date Calendar].[Bz Calendar Hierarchy].[2007]  
   Lent Week   Sale Amount     LastYearLent
   =========   ===========   ============
   1      213468     (null)
   2      210668     131795
   3      (null)     198342
   4      180849     (null)
   5      184585     166994
   6      (null)     191179
   7      192178     (null)
   8      174046     213207

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date