Data Modelling issues with Split, Ragged and Recursive Hierarchies in Microstrategy

 Edit 0 1…

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/167845

I. 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. TN5200-7X0-0123A.gif
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. TN5200-7X0-0123B.gif
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: TN5200-7X0-0123H.gif This type of schemas is fully supported but difficulties may arise when adding aggregate tables: TN5200-7X0-0123I.gif
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: TN5200-7X0-0123J.gif This information can be stored in a recursive fashion in only one table: TN5200-7X0-0123K.gif
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.

V. Ragged Hierarchies

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: