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 Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
4 comments
Read more

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...
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

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

Bursting file subscriptions Microstartegy

Bursting file subscriptions: Delivering  parts of reports across multiple files: Large MicroStrategy reports and documents are often broken up into separate pages by attributes. In a similar way, with Distribution Services, you can split up, or burst, a report or document into multiple files. When the subscription is executed, a separate file is created for each element of each attribute selected for bursting. Each file has a portion of data according to the attributes used to group data in the report (page-by axis) or document (group-by axis). Ex:, you may have a report with information for all regions. You could place Region in the page-by axis and burst the file subscription into the separate regions. This creates one report file for each region. As a second ex:, if you choose to burst your report using the Region and Category attributes, a separate file is created for each combination of Region and Category, such as Central and Books as a report, Central and Ele...
1 comment
Read more

Email Subscription in Microstrategy

Create Email Subscription in Microstrategy Creates an email subscription. Do not include any leading or trailing spaces in the ANSWER parameters. This causes a SQL error and prevents the command from executing. Ex: CREATE EMAILSUBSCRIPTION "New Multi Users" FOR OWNER "administrator" SCHEDULE "Books Closed" CONTACTGROUP "TEST"  CONTENT "Electronics Revenue by Region" IN FOLDER "\Public Objects\REPORTS\SUBJECT Areas\Sales and Profitability Analysis" IN PROJECT "MicroStrategy Tutorial" DELIVERYFORMAT HTML  EXPIRATIONDATE NEVER EXPRIED FILENAME "file_name"   SUBJECT  "Test REPORT" MESSAGE "Please Test"; CREATE EMAILSUBSCRIPTION [ subscription_name ] [FOR OWNER login_name ] SCHEDULE schedule_name  (ADDRESS address_name | USER user_name  | CONTACT contact_name [ADDRESS contact_ address_name ] | CONTACTGROUP contact_group_name ) CONTENT ( report_or_document_name IN FOLDER   loc...
1 comment
Read more

Export a Report Services document to Excel with formatting using URL API

Export a Report Services document to Excel with formatting using URL API in MSTR Web In order to export a document in excel format using the URL API, the executionMode must be set to 4.  If excutionMode is not provided in the URL, by default PDF will be used as executionMode.   Below are the list of parameters that the URL must contain in order to execute correctly.   evt= 3069 src= Main.aspx.3069 executionMode= 4 documentID= 7E1644CA424F482DA811569FCE8127FF( Replace the document Id with your document ID)   Sample URL for .NET environment: http://WebServerName/MicroStrategy/asp/Main.aspx?evt=3069&src=Main.aspx. 3069 &executionMode= 4 &documentID= 7E1644CA424F482DA811569FCE8127FF    
1 comment
Read more

Microstrategy Report Services documents and dashboards

Microstrategy Report Services documents vs Dashboards A MicroStrategy Report Services document displays data coming from multiple reports, with the data laid out and designed in presentation-quality format. Most data on a document is from one or more underlying datasets. A dataset is a standard MicroStrategy report. Other document components that do not originate from the dataset, such as static text used for a title or heading, page numbers, and images, are added by the document's designer and are stored in the document's definition. A Report Services (RS) dashboard is a special type of document. An RS dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data, as well as what data they are viewing. A broad selection of widgets and a wide variety of formatting options allow you to design a customized, interactive dashboard. Both documents and RS dashb...
1 comment
Read more

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps.  Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI.  Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for  where 2 is Category ID for "Books"  Report will display result as below.  Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as  Max(Category) {~ }  and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this  Case((Books = 1), Revenue, 0).  Report result would now display as expected as shown below
2 comments
Read more
Microstrategy Release Types Platform release Interval:  Annually every twelve (12) months in December Who:  Entire customer base What:  Focus on production level security, stability, and performance defect fixes for all customers. Expectation:  Customer has chosen platform path and wants product stability without new enhancements. Support:  Three (3) years, patches for approved P1 defects, and regular hotfix cadence addresses critical defects. Feature Release Interval:  Quarterly every three (3) months Who:  Customers with specific feature requirements. What:  New functionality developed in close collaboration with customers and customer council. Expectation:  Customer has chosen feature path, will consume further feature releases. Support:  Six (6) months patch support for approved P1 defects and (eighteen) 18 months troubleshooting. Customers upgrade to next feature release for roll-up f...
1 comment
Read more