KB6831: Known data modeling restrictions and solutions in MicroStrategy SQL Generation Engine

Data Rows: 6
Data Columns: 1

select a11.Order_Date DATE_ID,
  sum(a11.ORDER_AMT) M1
from   ORDER_FACT a11
where   a11.Order_Date BETWEEN DateAdd(Day, -7, Getdate()) AND Getdate()
group by   a11.Order_Date

Notice how '#0' is replaced with the first parameter outside the quotes ( form for the [Date] attribute), the second parameter ('#1') is replaced by the answer to the value prompt (7) and the SQL is placed after the WHERE clause.ExplanationA split hierarchy is one - that at the lowest level - has more than one child. The schema looks like the following diagram.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemReports that contain B or C will ignore filters on the other.RecommendationStrategy recommends to have only one child at the lowest level.Workaround / SolutionMake B and C parents of a new compound attribute using both columns as its ID with A B and C as its parents.  ExplanationAn 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. These relationships can be direct or indirect.

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting, ignored filters.RecommendationStrategy does not recommend or support this type of schema.Workaround / SolutionModify table structures, remove M-M relationship. Create compound key attributes to use as one to many children of the attributes in questionExplanationThis 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:

ProblemDouble counting. According to the diagram above, a report that contains month 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.RecommendationStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported with restrictions, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following Strategy Knowledgebase document for further information.

ProblemDouble counting. According to the diagram above, a report that contains month 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.RecommendationStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported with restrictions, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following Strategy Knowledgebase document for further information.

ProblemDouble counting. According to the diagram above, a report that contains month 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.RecommendationStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported with restrictions, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following Strategy Knowledgebase document for further information.

ProblemDouble counting. According to the diagram above, a report that contains month 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.RecommendationStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported with restrictions, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following Strategy Knowledgebase document for further information.

KB19194 - Considerations for the use of star schemas with Strategy SQL Generation Engine
Workaround / SolutionIf aggregate tables are needed, use one lookup table per attribute to avoid double counting.ExplanationA 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:

KB19194 - Considerations for the use of star schemas with Strategy SQL Generation Engine
Workaround / SolutionIf aggregate tables are needed, use one lookup table per attribute to avoid double counting.ExplanationA 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:

KB19194 - Considerations for the use of star schemas with Strategy SQL Generation Engine
Workaround / SolutionIf aggregate tables are needed, use one lookup table per attribute to avoid double counting.ExplanationA 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:

KB19194 - Considerations for the use of star schemas with Strategy SQL Generation Engine
Workaround / SolutionIf aggregate tables are needed, use one lookup table per attribute to avoid double counting.ExplanationA 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:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

ProblemRecursive hierarchies are not supported by StrategyRecommendationExplode the schema from recursive to dimensionalWorkaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is:

Each attribute has a 1-M relationship with its child.
ExplanationA ragged hierarchy is the one in which the parent or child attribute element of one or more attribute elements are not present in the level immediately above or below the attribute. In short, some attribute elements don't have a relationship with their parent/child attribute. Information in a ragged hierarchy may look like:

Each attribute has a 1-M relationship with its child.
ExplanationA ragged hierarchy is the one in which the parent or child attribute element of one or more attribute elements are not present in the level immediately above or below the attribute. In short, some attribute elements don't have a relationship with their parent/child attribute. 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.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider the case where the data has the following format:

Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider the case where the data has the following format:

Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider the case where the data has the following format:

Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider the case where the data has the following format:

Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider the case where the data has the following format:

Notice that the Esprit and the Diablo have a missing entry for the Branch, but they have one for the corporation.
ProblemRagged hierarchies are not supported by StrategyRecommendationCreate entries for the missing attributes.Workaround / SolutionConsider 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:

This can be done with case statements if the nulls properly exist in warehouse tables and the warehouse cannot be changed. If the parent/child tables do not have rows for the attributes in question those rows must be added to be valid.