Looking at the Fact and Level Tables

Looking at the Fact and Level Tables

If you are responsible for creating cube definitions, it is useful to understand how the system uses the cube definition to build the tables that the system uses directly: the fact table and level tables. In this section we examine these tables.

1. Access the Management Portal and go to the namespace into which you installed the samples, as described earlier.

2. Click System Explorer > SQL.

3. Click the Execute Query tab.

4. Execute the following SQL query, which runs against the base table used by your cube:

   select top 1 age,gender,homecity->name,primarycarephysician->lastname,
   primarycarephysician->firstname, testscore from BI_Study.patient

   

   Make a note of the details:

   

5. In the left area, navigate to the table Tutorial_Cube.Fact.

6. Click Open Table.

   The system displays something like the following:

   

   This table is generated when you compile a cube and is populated when you build the cube. The fact table has one row (called a fact) for each record that it used from the source table. In this case, each fact corresponds to one patient.

   The first row in this table corresponds to the first row in the base table (who is 13 years old and who has a test score of 88).

7. Note the following points:

   • The %sourceId field indicates the ID of source record on which a fact was based.

   • Each field with a name that starts Dx corresponds to a level that you defined. The fact table stores integers in these fields, which refer to records in the level tables.

   • Each field with a name that starts Mx corresponds to a measure that you defined. The fact table stores numbers (rather than integers) in these fields, because that is the default type for measures.

   • For some facts, the value of the MxTestScore field is null.

8. Click Close window.

9. Navigate to the table Tutorial_Cube.StarGender.

10. Click Open Table. The system displays something like this:

    

    This table contains the names of the members of the Gender level. The DxGender field of the fact table refers to the rows in this table.

    In your case, you might see Male before Female.

    In this case, the Female member is first, because the first patient processed by the system is female.

    When the system populates these tables, it iterates through the records in the base table. For each record, the system looks at the definition of each level, determines a value, adds that value (if needed) to the corresponding level table, and writes a lookup value into the level field of the fact table.

11. Click Close window.

12. Navigate to the table Tutorial_Cube.StarAge. The system displays something like the following:

    

    The Age level is defined by the Age field of the base class; that value is shown in the DxAge column. This level has a level property that is used to define the sort order for the level members; that value is shown in the Dx781900468 column.

    The first record in this level table corresponds to the age of 13 years, the first patient processed by the system in this example.

13. Click Close window.

14. Navigate to the table Tutorial_Cube.StarNameViaHomeCity. The system displays something like the following:

    

    The City level is defined by the HomeCity->Name field in the base class; that value is shown in the DxNameViaHomeCity column. This level has two level properties that are shown in the other columns.

    The first record in this table is Magnolia, the home city of the first patient in the base table.

15. Click Close window.

16. Navigate to the table Tutorial_Cube.StarPrimaryCarePhysician. The system displays something like the following:

    

    The Doctor level is defined by the PrimaryCarePhysician field in the base class, which is a reference (OREF) to an instance of the BI.Study.Doctor class. The system converts the OREF to an integer and writes it into the DxPrimaryCarePhysician column.

    For this level, the member names are defined by a level property that concatenates the last name and first name, with a comma between them. The value of this level property is stored in the Dx582175229 column.

    The first doctor in this table is Quince, Marvin, the primary care physician of the first patient in the base table.

    The name of the null doctor is a comma, but this name is never shown; instead, for this member, the system uses the null replacement string that you specified.