TN6831: Known data modeling restrictions and solutions in MicroStrategy SQL Generation Engine 8.1.x and 9.x
https://success.microstrategy.com/t5/Architect/TN6831-Known-data-modeling-restrictions-and-solutions-in/ta-p/167845I. Split Hierarchy with M-M relationships:
| Explanation | A split hierarchy is the one - that at the lowest level - has more than one child. The schema looks like the following diagram.
| ||
| Problem | Reports that contain or will ignore filters on | ||
| Recommendation | MicroStrategy recommends to have only one child at the lowest level. | ||
| Workaround / Solution | Make B and C IDs to be compound with A |
II. In-line M-M Relationships:
| Explanation | An in-line many-to-many relationship involves an attribute with at least one parent and one child . Its relationships with them are many-to-many.
| ||
| Problem | Double counting, ignored filters. | ||
| Recommendation | MicroStrategy does not recommend this type of schema. | ||
| Workaround / Solution | Modify table structures, remove M-M relationship. |
III. Star Schemas:
| Explanation | This schema is characterized by one lookup table per dimension, with base tables at the lowest level. This is the fastest way to set up a data warehouse:
This type of schemas is fully supported but difficulties may arise when adding aggregate tables:
| ||||
| Problem | Double counting. According to the diagram above, a report that contains and the a metric SUM(SALES_AMT) will go to the aggregate table and join to the column to retrieve the description from the table. Since the column is not unique in its lookup table, the results will appear duplicated. | ||||
| Recommendation | MicroStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following MicroStrategy Knowledgebase document for further information. TN19194 - Considerations for the use of star schemas with MicroStrategy SQL Generation Engine 8.1.x and 9.x | ||||
| Workaround / Solution | If aggregate tables are needed, use one lookup table per attribute to avoid double counting. |
IV. Recursive Hierarchies:
| Explanation | A recursive hierarchy or recursive dimension, usually consists of elements that point to other elements pertaining to the same attribute with a parent-child relationship. A classic example is an organization chart:
This information can be stored in a recursive fashion in only one table:
| ||||
| Problem | Recursive hierarchies are not natively supported by MicroStrategy 8.x | ||||
| Recommendation | Explode the schema from recursive to dimensional | ||||
| Workaround / Solution | The recursive hierarchy table has to be split into several tables, one for each level in the hierarchy. A physical snapshot of the solution is: Each attribute has a 1-M relationship with its child. |
| Explanation | A ragged hierarchy is the one in which the parent attribute element of one or more attribute elements are not in the level immediately above the attribute. In short, some attribute elements don't have a relationship with their parent attribute, but instead have a direct relationship with a grand-parent. Information in a ragged hierarchy may look like:  Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation. |
| Problem | Ragged hierarchies are not natively supported by MicroStrategy 8.x |
| Recommendation | Create entries for the missing attributes. |
| Workaround / Solution | Consider the case where the data has the following format:  New entries for the missing attributes should be added to the Branch attribute lookup table. These entries have to keep the one-to-many relationship of the attributes, so they can not share the same ID. As shown below for the above example, the entries should be added to the LU_BRANCH table. Additionally the BRANCH_ID column of the LU_MODEL table must also be updated:   |
I truly appreciate the time and work you put into sharing your knowledge. I found this topic to be quite effective and beneficial to me. Thank you very much for sharing. Continue to blog.
ReplyDeleteData Engineering Services
AI & ML Solutions
Data Analytics Services
Data Modernization Services