Defining DeepSee Models
Details for the Fact and Dimension Tables
[Back] 
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Search:    

Internally, DeepSee uses a star schema, which consists of the fact table and dimension tables, which the fact table refers. Note that dimension tables are also called star tables. You can directly query these tables, which DeepSee populates when you build a cube, and which DeepSee updates when changes occur. This chapter discusses the following:

Important:
Do not redefine these tables or attempt to write data to them except via the authorized interfaces; see the chapter Keeping the Cubes Current in the DeepSee Implementation Guide.
When you compile a cube definition class, DeepSee also generates a table name Listing, in the same package as the fact table. Do not use the Listing table, which is for internal use only.
Fact Table
When you compile a cube definition class, DeepSee 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.
DeepSee 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:
The class does not include properties for any iKnow levels or iKnow measures; those are handled differently.
If you specified the dependsOn attribute for any levels, the fact table contains additional indices 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 indices. For example:
Index %UserIndexName On (MxAge, DxGender) [ Type = bitmap ];
These custom indices are for your own use; DeepSee does not use them.
Field Names
The following table summarizes how DeepSee 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, DeepSee 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, DeepSee 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. For efficiency, the fact table would include the field AvgAgeFact, but not the field AgeFact.
The same logic applies when a cube contains multiple levels that use the same property. The logic also applies 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, DeepSee always generates a separate field for that value.
Dimension Tables
When you compile a cube definition class, DeepSee also generates a table for each level, other than the age- and time-type levels. These tables are in the same package as the fact table.
DeepSee updates the dimension tables (also called star tables) whenever you build the cube or update it incrementally.
The dimension table for a level contains one row for each member of that level. The dimension tables are created dynamically as DeepSee processes records in the base table. For a given level, each time a new unique value is discovered, that value is added as a new row to the appropriate dimension table. This means that DeepSee automatically adds rows to the dimension tables when needed; no intervention is required.
Name of Dimension Table
If the cube definition specifies the factName attribute for the corresponding level, the applicable dimension table uses that value as its name.
Otherwise, the name of the dimension table has the following form:
Stargenerated_name
Where generated_name is the corresponding field name in the fact table, without the Dx at the start. For example, suppose that in the fact table, the field name for the Home City level is DxPostalCodeViaHomeCity. In this case, the corresponding dimension table is named StarPostalCodeViaHomeCity.
Columns in a Dimension Table
The columns in this row are as follows:
Depending on how the level is defined, you can find the member names as follows: