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.

by Kevin S. Goff

Mar 18, 2009

Page 4 of 5

WEBINAR:

On-Demand

Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

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:

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).

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).

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.

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.