Skip to main content

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

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:

ExplanationA 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
TN5200-7X0-0123A.gif
ProblemReports that contain or will ignore filters on
RecommendationMicroStrategy recommends to have only one child at the lowest level.
Workaround / SolutionMake B and C IDs to be compound with A


II. In-line M-M Relationships:


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.
TN5200-7X0-0123B.gif
TN5200-7X0-0123B.gif
ProblemDouble counting, ignored filters.
RecommendationMicroStrategy does not recommend this type of schema.
Workaround / SolutionModify table structures, remove M-M relationship.



III. Star Schemas:


ExplanationThis 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
TN5200-7X0-0123H.gif

This type of schemas is fully supported but difficulties may arise when adding aggregate tables:
TN5200-7X0-0123I.gif
TN5200-7X0-0123I.gif
ProblemDouble 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.
RecommendationMicroStrategy 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 / SolutionIf aggregate tables are needed, use one lookup table per attribute to avoid double counting.



IV. Recursive Hierarchies:


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:
TN5200-7X0-0123J.gif
TN5200-7X0-0123J.gif

This information can be stored in a recursive fashion in only one table:
TN5200-7X0-0123K.gif
TN5200-7X0-0123K.gif
ProblemRecursive hierarchies are not natively supported by MicroStrategy 8.x
RecommendationExplode the schema from recursive to dimensional
Workaround / SolutionThe recursive hierarchy table has to be split into several tables, one for each level in the hierarchy. A physical snapshot of the solution is:
external image TN5200-7X0-0123_19.gif
Each attribute has a 1-M relationship with its child.
V. Ragged Hierarchies
ExplanationA 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:
external image TN5200-7X0-0123_14.gif
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 natively supported by
MicroStrategy 8.x
RecommendationCreate entries for the missing attributes.
Workaround / SolutionConsider the case where the data has the following format:
external image TN5200-7X0-0123_15.gif
external image TN5200-7X0-0123_16.gif
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:
external image TN5200-7X0-0123_17.gif
external image TN5200-7X0-0123_18.gif

Comments

  1. Aaron jhonsonApril 19, 2022 at 5:27 AM

    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.

    Data Engineering Services 

    AI & ML Solutions

    Data Analytics Services

    Data Modernization Services

    ReplyDelete

Post a Comment

Popular posts from this blog

Microstrategy "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...
4 comments
Read more

Types of filters in Microstrategy

Types of filters in Microstrategy Below are the types of filters: 1. Attribute qualification filter These types of qualifications restrict data related to attributes on the report. a) Attribute form qualification Filters data related to a business attribute’s form(s), such as ID or description. •  For example, the attribute Customer has the forms ID, First Name, Last Name, Address, and Birth Date. An attribute form qualification might filter on the form Last Name, the operator Begins With, and the letter H. The results show a list of customers whose last names start with the letter H. b) Attribute element list qualification Filters data related to a business attribute’s elements, such as New York, Washington, and San Francisco, which are elements of the attribute City. • For example, the attribute Customer has the elements John Smith, Jane Doe, William Hill, and so on. An attribute element list qualification can filter data to display only those customer...
6 comments
Read more

Microstrategy Caches explained

Microstrategy Caches Improving Response Time: Caching A  cache is a result set that is stored on a system to improve response time in future requests.  With caching, users can retrieve results from Intelligence Server rather than re-executing queries against a database. To delete all object caches for a project 1 In Developer, log into a project. You must log in with a user account that has administrative privileges. 2 From the  Administration  menu, point to  Projects , and then select  Project Configuration . The Project Configuration Editor opens. 3 Expand  Caching , expand  Auxiliary Caches , then select  Objects . To delete all configuration object caches for a server 1 Log in to the project source. 2 From the  Administration  menu in Developer, point to  Server , and then select  Purge Server Object Caches . 4 Click  Purge Now . To purge web cache follow the steps in the link ...
3 comments
Read more

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to  Main.aspx  or  mstrWeb  (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...
2 comments
Read more

Developer not starting with "invalid picture" message

Developer not starting with "invalid picture" message. This error could be due to the fact that t here is insufficient disk space on the drive used for the Windows Temp directory or the Optional Work Drive. https://support.microsoft.com/en-us/help/964421/error-481-invalid-picture Users may see the "Invalid Picture" error thrown when attempting to launch MicroStrategy Developer in Windows: CAUSE: This error is caused by a lack of available space on the user's hard disk to properly launch the platform. ACTION: To remedy the issue, simply clear up some active space on the hard drive to allow MicroStrategy to properly launch.
2 comments
Read more

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...
2 comments
Read more

Types of prompts in Microstrategy

Types of prompts in Microstrategy The different types of prompts allow you to create a  prompt  for nearly every part of a report. Prompts can be used in many objects including reports, filters, metrics, and custom groups, but all prompts require user interaction when the report is executed. The correct prompt type to create depends on what report objects you want users to be able to base a filter on to filter data, as described in the list below. Filter definition prompts   allow users to determine how the report's data is filtered, based on one of the following objects: Attributes in a hierarchy : Users can select prompt answers from one or more attribute elements from one or more attributes. The attribute elements that they select are used to filter data displayed on the report. This prompt lets you give users the largest number of attribute elements to choose from when they answer the prompt to define their filtering criteria. For example, on a repor...
1 comment
Read more

System Manager workflow to execute on a schedule

Creating a System Manager workflow to execute on a schedule System Manager workflow can execute on a schedule or after an event has been triggered. This can be accomplished by creating a simple batch file, and scheduling that batch file to execute with a third-party tool like Microsoft Task Scheduler.   Note : To avoid user permission conflicts, the following steps must be performed with highest privileges.   In the below example, the workflow makes the i-server restarts every day.   1. The user must first have a valid workflow. This particular workflow is a template that is delivered out-of-the-box with System Manager.   2. Save the workflow in  .smw  format.   3. In a text editor (such as Notepad), enter the command line statement that the task scheduler should execute.     MASysMgr.exe -w C:\filename.smw” “UserName=User1 “Password=1234”   4. Save the file in  .bat  ...
1 comment
Read more

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report Apart from using the VLDB properties to create the left outer join,  article describes how to use the Logical View to specify an outer join between two attribute lookup tables when only attributes are on a report. This method exists as attribute only outer joins will not be generated on their own by the MicroStrategy SQL engine. This is because they are only necessary with r agged/unbalanced hierarchies which are not supported as null attribute IDs are not supported (parent elements with no child elements or child elements with no parents).  Brief instructions are provided using the example below. Consider, two attributes: Parent01 and Child01 have a parent-child relationship. Their Lookup tables are defined, as follows Parent01 Child01 Note that although there are 4 ID values for the attribute Parent01, there is no defined relationship ...
Post a Comment
Read more

Microstrategy Removing sections that do not have metric data

Removing sections that do not have metric data This is an interesting feature which might not be explored by many of us and it comes us handy. A  cross join between datasets can result in rows or Group Header/Footer sections that do not have metric data. For example, a document contains two datasets. Dataset 1 contains Year and Revenue, with data for three years (2007-2009). Dataset 2 contains Year and Profit, filtered to return data for only two years (2008 and 2009). If you place Year and Profit in the Details and execute the document, it displays three rows, although no profit data exists for 2007. This is a product of the cross join between the two datasets. You do not want to see the blank line for 2007 since it does not give you any data for profit. You can select the  Trim sections for which no metric value data is available  check box. This removes the row for 2007, since no metric data for Profit is available for 2007. The results are shown below: ...
Post a Comment
Read more