General Description
To ensure uniqueness and to facilitate debugging, the MicroStrategy SQL Generation Engine generates Intermediate Table names as the concatenation of different parameters.
For example:Intermediate Table Name = [DBC prefix] + hash of ([Type prefix] + [Server index] + [DBC index] + [Job ID] + [Timestamp]) + [Engine Table Type] + [Counter]For example, this is an actual temporary table name:ZZTG0NLHTG8EA000
PART | VALUE | MEANING |
DBC prefix | ZZ | A custom prefix that can be set at DBC level. The default is 'ZZ'. |
Hash | TG0NLHTG8 | The hash of the report details to ensure uniqueness |
Engine Table Type | EA | A table created when splitting a pass |
Counter | 000 | Indicates that the table is the first intermediate table created for the current report. |
NOTE: For some databases (e.g., DB2 AS400, Tandem), the length of Intermediate Tables is restricted. MicroStrategy SQL Engine uses a shorter version of table names for these databases by shortening the unique hash section of the table name.
Engine Table Types
Table Type (Short Name) | Used for | Description |
AA (A) | Analytical Aggregation | If a metric expression has an aggregation function that is not supported by the database, an AA table can be used. |
DA |
| For databases that do not support Count (Distinct), select all the columns to perform Count (Distinct) on into a DA table. This table has fact columns that can be used to calculate other metrics in the report. Note that the report filter is applied to restrict the data. |
DM | Distinct Metric | A DM table is used when there are more than one Count(Distinct) in a metric expression, e.g., Count(Distinct A) - Count(Distinct B). The engine will create a DM table for each Count(Distinct) and join them together later. |
MR (R) | Ranking Qualification (Metric Ranking) |
|
MQ (Q) | Metric Qualification |
|
RF (F) | Relationship Filtering | An RF table is used for Relationship Filtering. Usually a Relationship Filter is applied through a Sub-Query. If the Very Large Database (VLDB) setting 'Sub-Query Type' is changed to 'Use Temp Table', create an RF table for the sub-query and apply the Relationship Filter by joining to the RF table. |
PO (O) | Partition | Whenever the engine needs to retrieve data from more than one Partition of a Fact Table and then aggregate it to a different level than the original, a PO table is created. The data from the different tables is then inserted into the PO table (which is equivalent to a UNION ALL implementation). |
PU | Partition (Second Aggregation) | This table is used in conjunction with PO table. Once a PO table is created, aggregate it into the PU table with the appropriate keys. |
SQ (S) | Sub-Query | An SQ table is used as an alternate for Sub-Query. For any report SQL that has Sub-Query, if the VLDB setting 'Sub-Query Type' is set to 'Use Temp Table', create an SQ table for this sub-query and replace the sub-query in later passes by this intermediate table. Note that if the sub-query is due to a relationship filter, create an RF table instead of an SQ table. |
OJ (J) | Outer Join | OJ is the first table created in the Outer Join algorithm. All the ID columns for the Analysis Key are selected and inserted into an OJ Table. In other words, it contains a UNION of the keys from all the intermediate tables to be Outer Joined. |
OL (L) | Outer Join to Lookup Table | When 'Preserve Lookup Table Elements' is set to Preserve elements (with or without filter) and the report level has more than one attribute, the engine will create an OL table that is the crossjoin of Lookup tables of all the attributes in the report. This OL table will be used as a Left Outer Join table in the last pass. |
OT |
| OT tables are used to provide a workaround on Count(Distinct) on a database platform that does not support Count(Distinct). Select distinct columns to be counted into an OT table and then Count (Column) or Count (*) from the OT table. |
OO |
| An OO table is similar to an OT table. If the database supports Count (Distinct), issue a 'Select Count (Distinct)…' statement and insert the results into an OO table if a intermediate table is needed. |
OA (A) | Outer Join (Adjust Dimensionality) | When the keys of the different intermediate tables to be Outer Joined are not compatible or, in other words, can not be joined directly, an OA intermediate table that includes a common key for all of the tables is created. It will then be used as if it was a normal OD for the rest of the Outer Join. |
SP (P) | Split Metrics | When a Metric is defined using fact columns from different Fact Tables the engine will typically generate one SP table for each of the source Fact Tables, where the appropriate facts are selected and aggregated to the analysis key level. As soon as all the necessary SP tables are created, the metric is calculated using a Temp Table Join between them. |
NB (B) | Non- Aggregatable metrics (B)* | When calculating a Non-Aggregatable metric (first Day for each Month), the engine will first receive the Day level data and place it into an NB table. |
NC (C) | Non- Aggregatable metrics (C) | After creating an NB table the engine creates an NC intermediate table by applying the MIN or MAX (depending on the Starting or Ending setting) function to the Non-Aggregatable Dimension's attribute. If the Non-Aggregatable metric is the Beginning (End) of the Lookup then an NC table is created from the attribute's lookup table, if it is the Beginning (End) of the Fact then an NC table is created from the NB table. |
MD (D) | Multi- Dimensional metrics | For reports that contain dimensional metrics, the engine divides them into groups of identical dimensionalities and generates one MD intermediate table for each of the groups. After each of the MD tables have been created, a final intermediate table Join query is issued and all the metrics are calculated at that moment. |
EA (A) |
| EA (A) - EA tables are created by the engine when a single pass needs to broken up into 2 passes, where the additional pass is joined only to the lookup table. This could happen if the description attribute form data type is different from that of the database type, for example, a MicroStrategy datatype of Text compared to the database type of Number. This Engine Table Type can also be a result of the VLDB setting - Additional Final Pass Option which forces an additional pass even if it could be done in one pass. |
AB (A) | Analytical Aggregation | Similar to AA table but used in 2nd step. |
AF (F) | Analytical Function | If a metric expression has an non-aggregation function that is not supported by the database, an AF table can be used. |
AG (F) | Analytical Function | Similar to AF table but used in 2nd step. |
AM (A) | Analytical Normal | AM table is used to calculate DBMS OLAP function in metric qualification. |
AN (A) | Analytical Normal | Similar to AM table but used in 2nd step. |
CG (C) | Custom Group | CG table is used to calculate the custom group. |
DN (M) | Distinct Metric | Similar to DM table but used in 2nd step. |
DT (I) | Distinct Metric | DT table is used to calculate Count Distinct metrics. |
DT (S) | Distinct Metric | DT table is used to calculate Count Distinct metrics. |
EB (E) |
| Similar to EA table but used in 2nd step. |
MB (B) | Metric Qualification | Similar to MQ table. Used for metric qualification. |
MB (M) | Non-Aggregation | MB (M) table is used to produce two passes when non-aggregation requests. |
MC (C) | Metric Qualification | Similar to MQ table. Used for metric qualification. |
MC (M) | Non-Aggregation | Similar to MB (M) table but used in 2nd pass. |
ND (D) | Non- Aggregatable metrics (D) | ND table will be created when there are multiple NC tables. |
OD (V) | Outer Join | OD table is used in outer join when VLDB_UNION_MULTIPLE_INSERT is disabled. |
OG (G) | Outer Join | OG table is used in outer join when VLDB_FULL_OUTER_JOIN_SUPPORT is disabled. |
OM (T) | Aggregatable Metrics | OM table is used to calculate aggregatable metrics with distinct property when the VLDB setting for multiple distinct is enabled. |
OP (Z) | One Pass SQL | OP is the default type of the table used in one pass. |
PP (P) | Partition | PP table is used in prequery for MD and WH partition. |
