Business Intelligence Tutorial: Creating Subject Areas

Introduction

In this tutorial, we create two subject areas that divide the patient data by ZIP code. In the Patients sample, ZIP codes contain small cities as follows:

We will create the following subject areas:

Creating the Subject Areas

To create the subject areas, do the following:

1. In the Architect, click New.

2. Click Subject Area.

3. For Subject Area Name, type Patient Set A

4. For Base Cube, click Browse and select Tutorial

5. For Class name for the Subject Area, type Tutorial.SubjectA

6. Click OK.

   The system creates the subject area and saves the class.

   You should see the following:

   

   In the Architect, there is no user interface for defining a filter. Instead it is necessary to type a suitable filter expression or to copy and paste one from the Analyzer.

7. In a separate browser tab or window, access the Analyzer and then do the following:

   1. Expand HomeD.

   2. Drop ZIP Code to the Filters box. This adds a filter box directly above the pivot table.

   3. In that filter box, click the search button and then select 32006, 32007, and 36711.

      Click the check mark.

      This action filters the pivot table.

      Important:

      Do not drag and drop 32006, 32007, and 36711 separately to the Filters box. Instead drag the level as described and then select the members.

   4. Click the Query Text button .

      The system then displays a dialog box that shows the MDX query that the Analyzer is using:

      SELECT  FROM [Patients] 
      %FILTER %OR({[HOMED].[H1].[ZIP Code].&[32006],[HOMED].[H1].[ZIP Code].&[32007],[HOMED].[H1].[ZIP Code].&[36711]})
      

      

   5. Copy the text after %FILTER to the system clipboard.

   6. Click OK.

8. In the Architect, click the line labeled Patient Set A.

9. In the Detail Pane, paste the copied text into Filter.

   %OR({[HOMED].[H1].[ZIP Code].&[32006],[HOMED].[H1].[ZIP Code].&[32007],[HOMED].[H1].[ZIP Code].&[36711]})
   

   

10. Click Save and then click OK.

11. Compile the subject area.

12. For the second subject area, repeat the preceding steps, with the following changes:

    • For Subject Area Name, type Patient Set B

    • For Class name for the Subject Area, type Tutorial.SubjectB

    • Repeat the preceding steps with the other two ZIP codes. So, for Filter, use the following:

      %OR({[HOMED].[H1].[ZIP Code].&[34577],[HOMED].[H1].[ZIP Code].&[38928]})
      

      

Examining the Subject Areas

Now we examine the subject areas that we have created. The numbers you see will be different from those shown here.

1. In the Analyzer, click the Change button .

2. Click Patient Set A.

3. Click OK.

   The Analyzer then displays the contents of the selected subject area.

   Notice that the total record count is not as high as it is for your base cube:

   

4. In the Model Contents area, expand the HomeD dimension, ZIP Code level, and City level. You should see the following:

   

5. Repeat the preceding steps for Patient Set B.

   When you expand the HomeD dimension, ZIP Code level, and City level. You should see the following:

   

Common Filter Expressions

In this section, we experiment with common filters in the Analyzer and see their effect on the generated queries.

1. In the Analyzer, open the Tutorial cube.

   The Analyzer refers to both cubes and subject areas as subject areas. The formal distinction between them is relevant only when you are creating them.

2. Click New.

   The Analyzer displays Count (a count of the records):

   

   Before we add a filter, let us see how the query is currently defined, so that we have a basis of comparison.

3. Click the Query Text button .

   The system then displays a dialog box that shows the MDX query that the Analyzer is using:

   SELECT FROM [TUTORIAL]
   

   

4. Click OK.

5. Expand ColorD and Favorite Color.

6. Drag and drop Orange to Filters.

   The Analyzer now uses only patients whose favorite color is orange. It looks something like this:

   

7. Click the Query Text button .

   The system then displays the following query:

   SELECT FROM [TUTORIAL] %FILTER [COLORD].[H1].[FAVORITE COLOR].&[ORANGE]
   

   

   The %FILTER keyword restricts the query. The fragment after %FILTER is a filter expression:

   [COLORD].[H1].[FAVORITE COLOR].&[ORANGE]
   

   

   This filter expression is a member expression, because it refers to a member (the Orange member of the Favorite Color level). A member is a set of records, and a member expression refers to that set of records.

   Notice that this expression uses the dimension, hierarchy, and level names. The &[ORANGE] fragment refers to the key of the Orange member. The Analyzer uses keys rather than names, but you can use either if the member names are unique.

8. Click OK.

9. Add another color to the filter. To do so:

   1. Click the X next to Orange in Filters.

      This removes that filter.

   2. Drag and drop Favorite Color to Filters. This adds a filter box directly above the pivot table.

   3. In that filter box, click the search button and then select Orange and Purple.

   4. Click the check mark.

      This action filters the pivot table.

      Important:

      Do not drag and drop Orange and Purple separately to the Filters box. Instead drag the level as described and then select the members.

      The Analyzer now looks something like this:

      

      The system now uses only patients whose favorite color is orange or whose favorite color is purple. (Notice that the count is higher than it was for orange alone.)

10. Display the query text again. Now you should see the following:

    SELECT FROM [TUTORIAL] 
    %FILTER %OR({[COLORD].[H1].[FAVORITE COLOR].&[ORANGE],[COLORD].[H1].[FAVORITE COLOR].&[PURPLE]})
    

    

    In this case, the filter expression is as follows:

    %OR({[COLORD].[H1].[FAVORITE COLOR].&[ORANGE],[COLORD].[H1].[FAVORITE COLOR].&[PURPLE]})
    

    

    The %OR function is an InterSystems optimization; the argument to this function is a set.

    The set is enclosed by curly braces {} and consists of a comma-separated list of elements. In this case, the set contains two member expressions. A set expression refers to all the records indicated by the elements of the set. In this case, the set refers to all patients whose favorite color is orange and all patients whose favorite color is purple.

11. Click OK.

12. Use the filter drop-down list and clear the check box next to Purple.

    Now the Analyzer uses only patients whose favorite color is orange.

13. Expand AllerD and Allergies.

14. Drag and drop mold to Filters, beneath Orange.

    The Analyzer now looks something like this:

    

    Notice that the count is lower than we saw using just Orange alone. This pivot table displays only patients whose favorite color is orange and who are allergic to mold.

15. Display the query text again. Now you should see the following:

    SELECT FROM [TUTORIAL] 
    %FILTER NONEMPTYCROSSJOIN([ALLERD].[H1].[ALLERGIES].&[MOLD],[COLORD].[H1].[FAVORITE COLOR].&[ORANGE])
    

    

    In this case, the filter expression is as follows:

    NONEMPTYCROSSJOIN([ALLERD].[H1].[ALLERGIES].&[MOLD],[COLORD].[H1].[FAVORITE COLOR].&[ORANGE])
    

    

    The MDX function NONEMPTYCROSSJOIN combines two members and returns the resulting tuple. The tuple accesses only the records that belong to both of the given members.

Now you have seen the three most common filter expressions:

[dimension name].[hierarchy name].[level name].&[member key]

[dimension name].[hierarchy name].[level name].[member name]

{member_expression,member_expression,member_expression...}

NONEMPTYCROSSJOIN(member_expression,member_expression)

(member_expression,member_expression)

For additional variations, see Using InterSystems MDX and InterSystems MDX Reference.