Fact Table

Basics

When you compile a cube definition class, the system generates the corresponding fact table with the name Package_CubeClass.Fact where Package_CubeClass corresponds to the package and class of the cube definition, following the usual rules for translating package and class name to table names. The system also generates foreign key definitions in the fact table that declare the links to the star table IDs.

The system updates the fact table whenever you build the cube or update it incrementally.

It is useful to examine this table, as well as the class definition that uses it.

The fact table contains the following fields:

• ID — ID of this row, assigned when the row is created.

• %dspartition — For future use. Ignore this field.

• %sourceID — The ID of the record in the base class on which this record is based. This field is a pointer to the source table.

• One field for each measure, to contain this measure value for this record. (There is one exception; see Reuse of a Source Property in the Fact Table, later in this page.)

  This field stores the actual measure values. The fact table class defines bitslice indexes on these values. For example:

  /// Index for measure M1.
  Index MxAge On MxAge [ Type = bitslice ];
  
  
  /// Measure: MxAge <br/>
  /// Source: Age
  Property MxAge As %Integer;
  

  

• One field for each level, to indicate the level member to which this record belongs. (There is one exception; see Reuse of a Source Property in the Fact Table, later in this page.)

  • For levels in data dimensions, the fact table stores the ID of the level member, which is a pointer to a table that defines the members.

    The fact table class defines bitmap indexes on these values. For example:

    /// Index for fact 1. Index DxGender On DxGender [ Type = bitmap ];
    
    /// Dimension: DxGender <br/>
    /// Source: Gender
    Property DxGender As Test.TestCube.DxGender;
    
    

    

    If this dimension is shared from another cube, then this pointer refers to a record in the applicable dimension table for that other cube. For information on shared dimensions, see Advanced Modeling for InterSystems Business Intelligence.

  • For a time- or age-type level, the fact table stores an integer.

    The fact table class defines the corresponding property as a computed value and defines a bitmap index for it. For example:

    /// Index for fact 4. Index DxBirthDateFxYear On DxBirthDateFxYear [ Type = bitmap ]; 
    
    /// Dimension: DxBirthDateFxMonthYear<br/> 
    /// Source: BirthDate Property DxBirthDateFxMonthYear As %Integer [ details omitted ]; 
    Property DxBirthDateFxYear As %Integer [ Calculated, SqlComputeCode = ... , SqlComputed ];
    

    

  • For a cube-to-cube <relationship>, if this cube specifies the sourceProperty or sourceExpression for the relationship, the fact table stores the ID of the corresponding row in the other fact table. That ID is a pointer to the other fact table.

• An additional field for each time-type or age-type dimension, to contain the complete value of the time or age dimension for this record. (There is one exception; see Reuse of a Source Property in the Fact Table, later in this page.)

  This value is in the form %DeepSee.Datatype.dateTimeOpens in a new tab and is not indexed.

The class does not include properties for any NLP levels or NLP measures; those are handled differently.

If you specified the dependsOn attribute for any levels, the fact table contains additional indexes on the level combinations:

Index DxPostalCodeViaHomeCityANDDx2642257510 On (DxPostalCodeViaHomeCity, Dx2642257510) [ Type = bitmap ];

If you defined any <index> elements in the <cube>, the fact table contains additional, custom indexes. For example:

Index %UserIndexName On (MxAge, DxGender) [ Type = bitmap ];

These custom indexes are for your own use; the system does not use them.

Field Names

The following table summarizes how the system determines the names for the measure, level, and dimension fields (in the case where you do not specify the factName attribute):

Reuse of a Source Property in the Fact Table

If a cube contains multiple measures that use the same property (via sourceProperty), the fact table contains a field for only one of those measures (the last of those measures found in the cube definition). For example, suppose that the cube contained the following measure definitions:

<measure name="Age" sourceProperty="Age" aggregate="SUM" factName="AgeFact"/>
<measure name="Avg Age" sourceProperty="Age" aggregate="AVG" factName="AvgAgeFact"/>

These two measures are different only by how they aggregate across multiple records; the fact table would contain the same value for these measures, for any given record. In this scenario, a compile error will be thrown to alert you to the alias conflict. To prevent an alias conflict, either all factNames of related measures must be left blank, or they must be given identical factName values.

The same logic applies when a cube contains multiple levels that use the same property or when a cube contains multiple age or time dimensions that use the same property.

For a given measure, level, or dimension, if you use sourceExpression and access the property via %source.propertyname, the system always generates a separate field for that value.