Refining the Cube

Refining the Cube

In this part of the tutorial, we will make the following changes to the cube:

• Change how the members of Age are sorted.

• Ensure that the Doctor level does not combine doctors who have the same name

• Ensure that the Doctor level has a member named None (the cube default replacement string) rather than ,

1. Access the Architect, which shows the cube definition you last looked at.

2. First, redefine the Age level so that its members are sorted numerically. To do so:

   1. Click the Age level.

   2. Click Add Element.

   3. For Enter New Element Name, type AgeSort

   4. Click Property.

   5. Click OK.

      The system adds the property and selects it in the Architect.

   6. In the Details Pane, select Expression and enter the following:

      $CASE($LENGTH(%source.Age),2:%source.Age,:"0"_%source.Age)
      

      

      This expression adds leading zeros to the age, so that string sorting causes the ages to be sorted correctly. The first age is 01, the second is 02, and so on. (The highest age in this sample is 99 years, so no age has more than two characters.)

   7. For Sort members by property value, select asc.

      This option causes the system to use the values of this property to control how the members are sorted.

   8. Save the cube.

   Note:

   The Patients sample uses a different approach, and both approaches are valid.

3. Redefine the Doctor level again so that it cannot combine doctors who have the same name. To do so:

   1. Click the Doctor level.

   2. Select the value in the Expression field and copy it to Notepad or other temporary location.

   3. Select Property and enter PrimaryCarePhysician

      Now the Doctor level is based on the bare PrimaryCarePhysician property, which is an OREF and is unique for each doctor.

      This ensures that the level does not combine different doctors who happen to have the same name.

      This step also ensures that the value is null for patients with no doctor; this means that the cube default null replacement string is used for that member of this level.

   4. While the Doctor level is selected, click Add Element.

   5. For Enter New Element Name, type DoctorName

   6. Click Property.

   7. Click OK.

      The system adds the property and selects it in the Architect.

   8. In the Details Pane, select Expression and paste in the expression you previously copied.

   9. Select Use as member names.

      This option causes the system to use the value of this property as the name for each member.

   10. For Sort members by property value, select asc.

       This option causes the system to sort the members in ascending order by the value of this property.

4. Compile the cube.

   When you do so, the Architect saves the cube.

5. Build the cube.

6. Go to the Analyzer and click the Analytics > Analyzer link to refresh with the most current model.

7. Double-check the changes. You should see the following:

   • When you drag and drop Age to Rows, you see the members sorted in numeric order:

     

   • When you drag and drop Doctor to Rows, you see the None member:

     

     Depending on the generated data, you might also see duplicate doctor names. For example: