Skip to main content

many-to-many and joint child relationships

Supporting many-to-many and joint child relationships in MSTR


Two forms of attribute relationships, many-to-many relationships and joint child relationships, can introduce additional complexity to the schema and warehouse design process. The following sections discuss the considerations you must make to ensure an effective warehouse design in light of the unique nature of these relationships.
While the topics are largely related to logical model design, a working knowledge of physical schemas is helpful when dealing with the challenges involved with these topics.
Before reading this section, you should know what logical data models and physical warehouse schemas are, and how to read and interpret them. Logical data models and physical warehouse schemas are discussed in The Logical Data Modeland Warehouse Structure for Your Logical Data Model respectively. These chapters discuss how to plan and create a conceptual framework for your business intelligence data.

Many-to-many relationships

The presence of many-to-many relationships introduces complexity during the warehouse design process. With the presence of many-to-many relationships, you must make additional considerations to effectively plan your design.
Below are some real-life examples of many-to-many relationships which must be carefully handled in the data model and schema:

In a certain organization, each salesperson can work in more than one calling center. Likewise, each calling center has many salespeople.


In a car manufacturing plant, many models of cars are produced, and each comes in several colors. That is, there are many colors for a single type of car, and many types of cars can be associated with the same color.
The following sections use the example of items and colors to demonstrate a many-to-many relationship and the options you have for dealing with them. One item can come in many colors, such as red hats, blue hats, and green hats, and one color can be associated with many items, such as red dress, red hat, red shoes, and red socks.
Potential problems with many-to-many relationships usually come in the following forms, both of which can be avoided by correctly modeling the relationship:

Loss of analytical capability


Multiple counting

Loss of analytical capability

With the color and item many-to-many relationship, there are usually two business questions for which users want answers:

1In what colors are certain items available?


2How much of a particular item/color combination was sold?
Answering the first question requires a table that contains a list of all possible item/color combinations. Recall that one-to-many relationships are usually in the child’s lookup table.
In many-to-many relationships this is not feasible. Rather, a distinct relationship table needs to be present in your warehouse. The following diagram shows the lookup and relationship tables for item and color:
external image color_item_relate_table.gif
The Rel_Color_Item table provides a row for every possible item/color combination.
Answering the second question requires a fact table that has sales information as well as color and item information. The following diagram shows the same scenario as before, but in addition it shows a simple fact table containing sales data keyed by item, color, and date.
external image color_item_relate_fact.gif
The fact table in the above diagram alone is not sufficient to answer the first question. Only item and color combinations that were actually sold, and therefore have sales recorded, can be retrieved from this table. If you have item and color combinations that are available but that have never been sold, this fact table cannot provide a complete list of item and color combinations to answer question one.
In summary, to prevent any loss of analytical flexibility when dealing with a many-to-many attribute relationship, the following requirements must be met:

A distinct relationship table to identify all the possible combinations of attribute elements between attributes


Both the attribute ID columns in the fact table
You can implement the above points in several different ways, which are discussed in Working with many-to-many relationships.

Multiple counting

When dealing with many-to-many relationships, loss of analytical capability is only one challenge. Another equally significant issue is multiple counting. Multiple counting occurs when all of the following takes place:

You attempt to aggregate data to the level of one of the attributes in the many-to-many relationship, or a higher level than one of the attributes in the many-to-many relationship.


The relationship exists in a distinct relationship table.


All of the attributes in the many-to-many relationship are not in the fact table.
Recall the example from above, but make the following change: remove color from the fact table.
external image multiple_counting.gif
Assume that there are three items, including hats, dresses, and socks. These items come in three colors, including red, blue, and green, with the exception of socks, which come in only green and blue. The following diagram shows this data in the lookup tables as well as some simple sales data:
external image multiple_counting2.gif
The risk of multiple counting occurs when you run a query requesting the sales by color, effectively aggregating to the item attribute level in the many-to-many relationship. This query would require both the fact table, which has the sales information by item, and the relationship table, since color is not recorded in the fact table.
The difficulty lies in the fact that color is not in the fact table. There is no way to directly relate the sales of an item in the fact table to the color of that particular item. For example, instead of calculating the sales of red items, the query aggregates sales for all items that come in red according to the relationship table. The sum includes all hats and all dresses, including blue ones and green ones. This obviously leads to numbers that are higher than the true sales for red items.
For example, using the given data, the following questions cannot all be answered accurately:

What are the total sales for hats?
The answer is $35, which can be calculated directly from the fact table.

What are the total sales for red items?
You cannot determine an accurate answer. The answer you get is $85, which is the total for all hats and dresses, since socks do not come in red. It is entirely possible that all the dresses sold are green; however, you cannot confirm this since color is not recorded in the fact table.

What are the total sales for red dresses?
Again, you cannot determine an accurate answer. If all the dresses sold are indeed green, the correct answer is $0, but the answer you will get based on the data in the fact table is $50.
The following section describes several ways to prevent multiple counting when dealing with many-to-many relationships.

Working with many-to-many relationships

As you can see, seemingly simple questions can require you to take a number of steps to answer them when many-to-many relationships are involved.
You can use one of three techniques to provide physical support to answer the types of questions that cannot be answered accurately when using many-to-many relationships. The three techniques all have differing levels of flexibility, and flexibility is always a trade-off with complexity. In all cases, the two fundamental components remain in place in one form or another:

A relationship table to define the attribute relationship


Both the attribute’s ID columns in the fact table
MicroStrategy builds the rules that MicroStrategy SQL Engine uses to generate SQL when a report request is made. If you make both of the above physical implementations, the SQL Engine uses the related table when no metric is included on the report. When a metric is included, the fact table is used to answer the query.
All of the following methods require additional data in the fact table. This means that you must capture the additional data in the source system. For example, you need to have data in the source system as to what the color is of each item sold. If this additional data was never captured in the source system, you cannot fully resolve the many-to-many relationship to calculate the amount of sales for items of a certain color.
Method 1
This method is the most straightforward way to effectively manage many-to-many relationships.
Method 1 requires you to create a separate relationship table (in this case, Rel_Color_Item) and add both attribute IDs to the fact table as shown in the following diagram.
external image MM_Method1.gif
Method 2
Method 2 eliminates the many-to-many relationship and the need for a distinct relationship table.
Here the many-to-many relationship is converted into a compound attribute relationship. You treat one attribute as a child of the other and have a compound key for the lower level attribute. Also, you add both attribute IDs, in this case Item_ID and Color_ID, to the fact table as shown in the following diagram.
external image MM_Method2.gif
While this method eliminates the need for a separate relationship table, you lose the ability to view items independent of color, or vice versa.
Method 3
Method 3 is the most versatile solution and has the following characteristics:

Further simplifies the compound attribute relationship from Method 2 into a simple attribute relationship


Provides the ability to view item and color together or independently


Requires only one attribute column in the fact table for complete flexibility, rather than two
Here you must create a new attribute, lower in level than either Color or Item. This attribute is essentially a concatenation of Color and Item, which gives it a one-to-many relationship between itself and each of its parent attributes. This is the SKU attribute, particularly common in retail data models or situations.
Finally, rather than including Color and Item in the fact table, you only need to include this new child attribute SKU, as shown in the following diagram.
external image MM_Method3.gif
This method is actually quite similar to Method 1. The major difference is that the distinct relationship table from Method 1 has an additional column, SKU, which extends the relationship of each item and color combination into a single value. Consequently, you can use this single value in the fact table.
The major disadvantage of Method 3 lies in creating the new attribute if your business model does not already use a similar structure, as well as possibly adding complexity to the ETL process.

Excerpt from https://www2.microstrategy.com/producthelp/10.7/ProjectDesignGuide/WebHelp/Lang_1033/Content/ProjectDesign/Supporting_many_to_many_and_joint_child_relationsh.htm#pd-attributes_overview_3631995489_1086149

Comments

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 . ...

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:...

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. {...

Relationship with Report Filter options for Levels metrics

Relationship with Report Filter options for Levels metrics You can define how the report filter affects the metric calculation. From the  Relationship with Report Filter  drop-down list, select one of the following: • To include only data that meets the conditions in the report filter in the metric calculation, select  Standard filtering . • To raise the level of the report filter to the level of the target, if possible, then apply the report filter to the metric calculation, select  Absolute filtering . For example, the report filter contains the Washington, DC, Boston, and New York call centers, but the Revenue metric is calculated at the Region level. Because Call Center is a child attribute of Region, the report filter's level is raised to the Region level, and the report filter is treated as if it includes the regions that contain Washington, DC, Boston, and New York (in this case, Mid-Atlantic and Northeast). Data from all call centers in the Mid-At...

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...

Microstrategy removing rows with Zero metric values

Microstrategy removing rows with Zero metric values If there are more than one metric and want to remove the rows with Zero metric values. There are several ways to do this but I guess the easiest one will be creating a Metric which is the sum of all the metrics to be used in the report. Such as Sum Metric = Metric 1 + Metric 2 + Metric 3 +........... The Sum Metric should be included in the report and it can be controlled by any of the ways below: 1) Adding a view filter to the report where  Sum Metric != 0 which will bring only non zero values 2) Creating a filter definition prompt with the  Sum Metric , so that when the rpeort is prompted user cans elect  Sum Metric value is >0 . Or we can jsut had code the filter  Sum Metric >0 if we want to pre-filter the report with non zero rows for all the metrics in the report. We can also use the report limits with metric values >0 with an and condition between each metric c...

Retrieve a list of user groups and the associated users in MicroStrategy

    Retrieve a list of user groups and the associated users in MicroStrategy Developer 9.x Although MicroStrategy Command Manager 9.x can be used to generate lists of all user groups and all users within a particular group, there is currently no way for it to retrieve a list of all groups and all of the users in each group. If such a list is desired, it can be created using the Project Documentation Wizard in MicroStrategy Desktop Developer 9.x. Follow the steps below to create a list of all groups and the users in each group: In MicroStrategy Developer 9.x, select 'Project Documentation' from the Tools menu to start the wizard. Select any project that is in the project source that contains the users and groups and click Next. Select only Configuration Objects for documentation. Uncheck the 'Basic Properties' object category from the next screen, as shown below: Then select only 'User Group' under the Configuratio...

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...

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...

Tag Users in Microstrategy Dossier

Tag Users in Microstrategy Dossier You can tag multiple users or user groups in a comment. You want to tag the team leader responsible for creating the dossier. Navigate to the first page of the Office Royale Sales dossier. On the  Overview  page, click the  Comments    icon. In  Comments , enter  @ . A list of suggested users appears as you type. The owner and/or certifier of the dossier appears at the top of the list with  Owner    and  Certifier    icons. Select a user from the list. If you want to include the filtered state of your dossier, enter  @  in  Comments  and click  filter . Enter your message. Click  Post .