Skip to main content

Fact Table

Describes the fact table that is generated when you compile a Business Intelligence cube definition class.

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):

Item and Scenario Field Name in Fact Table (If Not Overridden by factName attribute) Example
Measure based on a source property Mxprop_name where prop_name is the name of the property. MxAge
Measure based on a source property in another table, via dot syntax (rare) Mxother_prop_nameViaprop_name where other_prop_name is the name of the property in the other class. However, if the resulting field name would be too long, the system generates a unique number. MxAgeViaOtherTable
Measure based on a source expression MxnnnnnnnnnT where nnnnnnnnn is an integer and T indicates the measure type. (For example, I represents an integer measure.) Mx1968652733I
Data level based on a source property (with no range expression) Dxprop_name DxGender
Data level based on a source property with a range expression Dxprop_nameRgnnnnnnnnnn where nnnnnnnnn is an integer. DxAgeRg855025875
Data level based on a source property in another table, via dot syntax Dxother_prop_nameViaprop_name

However, if the resulting field name would be too long, the system generates a unique number.

DxPostalCodeViaHomeCity
Data level based on a source expression Dxnnnnnnnnnn Dx2163088627
Time- or age-type dimension Dxdim_name where dim_name is the name of the dimension. (This field is for use by levels in this dimension.) DxBirthDate
Time- or age-type level Dxdim_nameFxfunc_name, where func_name is the name specified in the timeFunction attribute for this level. DxBirthDateFxYear
Relationship Rxgenerated_name, where generated_name is the name of the source property or a generated name based on the source expression. RxMainCity

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.

FeedbackOpens in a new tab