Putting MDX Scripting to Work

Now that you have a basic understanding of MDX scripting, we can take a look at some of the places it is used in Analysis Services. We do this by completing two Learn By Doing exercises. First, we define a security role in an Analysis Services database. Second, we create additional calculated members in the Max Min Manufacturing DM cube. Along the way, we utilize three additional MDX functions to get the job done.

Cube Security

In Chapter 9, we saw how to create perspectives that can divide a cube into more manageable pieces for our users. Perspectives are helpful for organizing information in a cube, but they do not limit users' access to only those items in a particular perspective. To secure portions of a cube from unwanted access, we need to use security roles.

Security roles enable us to restrict or permit access to items within the cube, measures, dimensions, and dimensional members. We can also allow or prevent users from doing certain administrative tasks, such as processing the cube or viewing the definition of its structure. The roles are associated with Windows logins and Windows groups to provide access to the cube information.

Learn By Doing—Setting Security Within an OLAP Cube

Features Highlighted

• Create a security role

• Use an MDX expression to limit access to cube members

Business Need Maximum Miniatures would like to provide access to the Max Min Manufacturing DM cube to the plant manager at the Kawaguchi plant. The plant manager should be given access to the Total Products and Percent Rejected calculated members, but none of the other measures in the cube. This plant only produces products from the Guiding Lights and Woodland Creatures product types. Therefore, access should be limited to these two product types. Further, the Kawaguchi plant only operates Machine #6, so personnel at that plant should only be able to see this machine.

Steps

1. Open the Business Intelligence Development Studio.

2. Open the MaxMinManufacturingDM project.

3. Right-click the Roles entry in the Solution Explorer window and select New Role from the Context menu. The Role Design tab and a Role.role entry in the Solution Explorer window appears.

4. Double-click the Role.role entry in the Solution Explorer window.

5. In the Properties window, enter Kawaguchi Plant for Name. On the General tab on the Role Design tab, enter an appropriate description for Role Description.

6. The items under Set the Database Permissions for This Role determine whether members of this role can perform these administrative tasks or read the definition of the cube. Do not check any of these items for this role. The General page of the Role Design tab is shown in Figure 10-21.

7. Select the Membership tab. This is the place where the Kawaguchi plant manager's Windows login would be added to this role. If you have a test Windows login that you can use for testing this role, click Add and add the logon to the role. Otherwise, move on to Step 8. The Membership page of the Role Design tab is shown in Figure 10-22.

8. Select the Data Sources tab. Here, we control the access to each data source. Select Read from the Access drop-down list for the Max Min Manufacturing DM data source. We do not want this role to be able to read the definition of the data source, so leave Read Definition unchecked. The Data Sources page of the Role Design tab is shown in Figure 10-23.

9. Select the Cubes tab. Select Read from the Access drop-down list for the Max Min Manufacturing DM cube. We do not need to allow drillthrough or rights to process this cube, so do not make any changes to these two items. The Cubes page of the Role Design tab is shown in Figure 10-24.

10. Select the Cell Data tab. This is where we restrict access to the measures in the cube cells. Make sure the Max Min Manufacturing DM cube is selected at the top of the page.

11. Check Enable Read Permissions.

12. Enter the following MDX expression for Allow Reading Of Cube Content:

     [Measures].CurrentMember IS [Measures].[Total Products] OR [Measures].CurrentMember IS [Measures].[Percent Rejected] 

    When creating expressions here, you can click the button next to Edit MDX to use the MDX Builder dialog box for assistance in building your MDX expressions. The Cell Data page of the Role Design tab should appear as shown in Figure 10-25.

    Note 

    This MDX expression used the CurrentMember function along with Measures. This syntax uses Measures as a pseudodimension in the cube. The current member of the Measures pseudodimension is whatever measure we are trying to query information from. We are also using the IS operator to determine if the current member is the specified member. The expression here will return true and, thus, allow access only if we are querying information from the Total Products measure or the Percent Rejected measure. If any other measure is queried, the current member of the measure will not be equal to either of the two we have specified and the MDX expression will return false. When the MDX expression is false, access is denied.

13. Select the Dimensions tab. On this page, we set the access rights for the dimensions. Leave the Access drop-down list set to Read for all of the dimensions. Do not allow this role to read the definitions or process any of the dimensions. The Dimensions page of the Role Design tab is shown in Figure 10-26.

14. Select the Dimension Data tab. Here is the place to limit access to members of a particular dimension. The Basic tab enables us to use the brute force method and check or uncheck dimension members to determine access, In large dimensions, this quickly becomes unworkable. The Basic tab of the Dimension Data tab is shown in Figure 10-27.

15. Select the Advanced tab on the Dimension Data page. Here, we can create MDX expressions to determine access. Expand the Dimension drop-down window at the top of the tab. Select The Dim Machine dimension under the Max Min Manufacturing DM cube and click OK. (Select the Dim Machine dimension in the cube, not the Dim Machine dimension definition in the project. The dimension you want is probably the second occurrence of Dim Machine in the drop-down window.)

16. Select Machine Name from the Attribute drop-down list.

17. Enter the following expression for Allowed Member Set:

     Descendants([Plant].[Maximum Miniatures—Kawaguchi], [Machine Name]) 

    This MDX expression returns all of the Machine level members that are descendants of the Kawaguchi plant. The Advanced tab of the Dimension Data tab should appear as shown in Figure 10-28.

18. Expand the Dimension drop-down window at the top of the tab. Select The Dim Product dimension under the Max Min Manufacturing DM cube and click OK.

19. Select Product Type Name from the Attribute drop-down list.

20. Enter the following expression for Allowed Member Set:

     {[Dim Product].[Product Type Name].[Guiding Lights],  [Dim Product].[Product Type Name].[Woodland Creatures]} 

    Here, we have created a set containing the two allowed members. The Advanced tab of the Dimension Data tab should now appear as shown in Figure 10-29.

21. Select the Mining Structures tab. We do not have any data mining structures defined in this cube, so we do not need to make any entries here. Click the Save All button in the toolbar. Close the Role Design tab.

22. Select Build | Deploy MaxMinManufacturingDM from the Main menu to deploy the changes to the Analysis Services server.

23. Next, we use the Browser tab to test our security settings. Double-click the entry for Max Min Manufacturing DM.cube in the Solution Explorer window. The Cube Design tab appears.

24. Select the Browser tab on the Cube Design tab. You are browsing the cube with your credentials, which have administration rights.

25. Drag the Dim Machine dimension and drop it on the Drop Column Fields Here target. Notice all three machine types are present.

26. Drag the Dim Product dimension and drop it on the Drop Row Fields Here target. Notice all four product types are present.

27. Expand Measures, and then expand the Manufacturing Fact measure group.

28. Drag the Accepted Products measure and drop it on the Drop Total or Detail Fields Here target.

29. Drag the Total Products calculated member and drop it next to the Accepted Products measures. The Browser tab appears as shown in Figure 10-30.

30. Click the Change User button on the Browser tab toolbar. The Security Context dialog box appears as shown in Figure 10-31.

31. Select the Roles radio button. In the Roles drop-down window, check the Kawaguchi Role. (The All Roles item is also checked.) Click OK to exit the drop-down window.

32. Click OK to exit the Security Context dialog box. The Browser tab resets. A message under the Browser tab toolbar informs us that we are browsing while using the credentials of the Kawaguchi Plant security role.

33. We can build the same query as we did before. Drag the Dim Machine dimension and drop it on the Drop Column Fields Here target. As our security role stipulates, only one machine type is present.

34. Drag the Dim Product dimension and drop it on the Drop Row Fields Here target. Again, as expected, only two product types are present.

35. Expand Measures, and then expand the Manufacturing Fact measure group. All of the measures still appear in this list. However, watch what happens when we try to query from a restricted measure.

36. Drag the Accepted Products measure and drop it on the Drop Total or Detail Fields Here target. We receive # N/A for each cell because our security role does not allow querying from this measure.

37. Drag the Total Products calculated member and drop it next to the Accepted Products measures. Our security role does allow querying from this calculated member, so we do receive our results. The Browser tab appears as shown in Figure 10-32.

Figure 10-21: The General page of the Role Design tab

Figure 10-22: The Membership page of the Role Design tab

Figure 10-23: The Data Sources page of the Role Design tab

Figure 10-24: The Cubes page of the Role Design tab

Figure 10-25: The Cell Data page of the Role Design tab

Figure 10-26: The Dimensions page of the Role Design tab

Figure 10-27: The Basic tab of the Dimension Data page of the Role Design tab

Figure 10-28: The Advanced tab of the Dimension Data page for the Dim Machine dimension

Figure 10-29: The Advanced tab of the Dimension Data page for the Dim Product dimension

Figure 10-30: The Browser tab using administrative credentials

Figure 10-31: The Security Context dialog box

Figure 10-32: The Browser tab using the Kawaguchi Plant security role

Note 

You may recall that the Total Products calculated member is the sum of the Accepted Products measure and the Rejected Products measure. Even though our security role does not provide access to these two measures, the calculated member still works. This lets us provide a user with calculated members without having to expose the underlying data.

This Year to Last Year Comparisons and Year-To-Date Rollups

Two bits of business intelligence are often requested by users. The first is the comparison of a value from this year with the same value from one year ago? It is a natural desire to know how the organization is doing versus where it was one year ago. In many cases, this is also a telling statistic, providing beneficial insight into the organization's health and performance.

The second bit of business intelligence that is often requested is the year-to-date total. Monthly and quarterly figures are fine, but users also want to know the grand totals as we move through the year. If the year-to-date numbers are not monitored, the year-end figures could be a big surprise to management, which is usually not a good thing!

In this section, we add two calculated members to the Max Min Manufacturing DM cube. One calculated member returns the Percent Rejected from the previous year. The other calculated member returns the year-to-date amount for the Total Products calculated member. To create these calculated members, we use three new functions: ParallelPeriod, YTD, and SUM.

The ParallelPeriod Function

The ParallelPeriod function returns the member from the time dimension that corresponds to a specified member. For example, if we ask for the time dimension member parallel to Q1 2005 from one year earlier, we would get Q1 2004. If we ask for the time dimension member parallel to August 2004 from one year earlier, we would get August 2003.

The ParallelPeriod function has the following format:

• Parallel Period(TimeHierarchyLevel, NumberOf PeriodsBack, TimeDimensionMember)

TimeHierarchyLevel is the level in the Time Dimension Hierarchy that we are using to move backward. The most common hierarchy level to use here is the year level. NumberOfPeriodsBack is the number of the TimeHierarcy Levels to move backward. TimeDimensionMember is the Time Dimension member that serves as the starting point.

Consider the following example:

 ParallelPeriod([Year], 1, [Time Hierarchy].[Month].[200503] 

This function starts at the March 2005 member and moves backward one year to return the March 2004 member. In this example:

 ParallelPeriod([Quarter], 1, [Time Hierarchy].[Month].[200509] 

the, function starts at the September 2005 member and moves backward one quarter to return the June 2005 member.

The YTD Function

The YTD function returns a set of members from a time hierarchy level that represents the beginning o(' the year up to and including the specified member. If the specified member is Q3 2005, the members Q1 2005, Q2 2005, and Q3 2005 are returned in the set. If the specified member is April 2005, the members January 2005, February 2005, March 2005, and April 2005 are returned in the set.

The YTD function has the following format:

• YTD(TimeDimensionMember)

TimeDimensionMember is a member from the time dimension. For example:

 YTD([Time Hierarchy].[Month].[200503]) 

returns a set of [200501], [200502], and [200503].

The SUM Function

The SUM function adds together the values in a set of measures to create an aggregate value. The SUM function has the following format:

• SUM(SetOfMeasures)

Set OfMeasures is a set of measures. For example:

 SUM({([200504], [Measure].[Total Products]),      ([200505], [Measure].[Total Products])}) 

adds the total products produced in April 2005 to the total products produced in May, 2005.

Learn By Doing—Time-Based Analytics

Features Highlighted

• Creating a calculated member to return a value from the previous year

• Creating a calculated member to return a year-to-date value

Business Need To make analysis easier, the vice president of production would like to have calculated members for the percent rejected in the parallel period of the previous year and for the year-to-date total products produced.

Steps

1. Open the Business Intelligence Development Studio.

2. Open the MaxMinManufacturingDM project.

3. Double-click the Max Min Manufacturing DM.cube in the Solution Explorer. The Cube Design tab appears.

4. Select the Calculations tab.

5. Click the New Calculated Member button in the Calculations tab toolbar. A blank Calculated Members form appears.

6. Enter [Prey Year Percent Rejected] for Name.

7. Enter the following for Expression:

    (ParallelPeriod([Year], 1, [Time Hierarchy].CurrentMember),                                    [Measures].[Percent Rejected]) 

8. Select Percent from the Format String drop-down list.

9. Check the Accepted Products and Rejected Products measures in the Non-empty Behavior selection window and click OK. The Calculations tab should appear as shown in Figure 10-33.

10. Click the New Calculated Member button in the Calculations tab toolbar to add a second calculated member.

11. Enter [Year-To-Date Total Products] for Name.

12. Enter the following for Expression:

     SUM(YTD([Time Hierarchy].CurrentMember),[Measures].[Total Products]) 

13. Select #.# from the Format String drop-down list.

14. Check the Accepted Products and Rejected Products measures in the Non-empty Behavior selection window and click OK. The Calculations tab will appear as shown in Figure 10-34.

15. Click the .Save All button in the toolbar.

16. Select Build | Deploy Solution from the Main menu. The cube definition is deployed to the Analysis Services server.

17. When the deploy is complete, select the Browser tab.

18. If dimensions and measures are still on the browser from the previous Learn By Doing exercise, click the Reconnect button in the Browser tab toolbar.

19. Click the Change User button in the Browser tab toolbar. The Security Context dialog box appears.

20. Select Current User and click OK.

21. Drag the Dim Time dimension and drop it on the Drop Row Fields Here target.

22. Expand Measures. Drag the Percent Rejected calculated member and drop it on the Drop Totals or Detail Fields Here target.

23. Drag the Prev Year Percent Rejected calculated member and drop it next to the Percent Rejected calculated member.

24. Drag the Total Products calculated member and drop it next to the two calculated members already in the browser.

25. Drag the Year-To-Date Total Products calculated member and drop it with the other three.

26. Drill down in the Time dimension hierarchy to confirm that the calculated members are functioning properly. Be patient. Browsing can take a few moments as you drill down to the month and day levels. The Browser tab appears similar to Figure 10-35.

Figure 10-33: The Prey Year Percent Rejected calculated measure

Figure 10-34: The Year-Jo-Date Total Products calculated measure

Figure 10-35: Browsing the calculated members