Skip to main content

Joint Child Relationships in MSTR

Some attributes exist at the intersection of other indirectly related attributes. Such attributes are called joint children.

Joint child relationships connect special attributes that are sometimes called cross-dimensional attributes, text facts, or qualities. They do not fit neatly into the modeling schemes you have learned about thus far. These relationships can be modeled and conceptualized like traditional attributes but, like facts, they exist at the intersection of multiple attribute levels.
Many source systems refer to these special attributes as flags. Therefore, if flags are referenced in your source system documentation, these are likely candidates for joint child relationships.
Joint child relationships are really another type of many-to-many relationship where one attribute has a many-to-many relationship to two otherwise unrelated attributes. For example, consider the relationship between three attributes: Promotion, Item, and Quarter. In this case, Promotion has a many-to-many relationship to both Item and Quarter, as shown in the following diagram.
external image AdvDataModel_PIQ.gif
An example of a promotion might be a “Red Sale” where all red items are on sale. A business might run this promotion around Valentine's Day and again at Christmas time.

Supporting joint child relationships

One way to resolve a many-to-many relationship is to have a relationship table for the attributes involved in the many-to-many relationships. In this case, you might create two relationship tables, one to relate Promotion and Item. The second relates Promotion and Quarter as shown in the following diagram.
external image AdvDataModel_PIQ2.gif
These two tables are sufficient to answer questions such as:

What items have been in what promotions?


What quarters have had what promotions?
However, these tables are not sufficient to answer the following more detailed and insightful questions:

What items were in what promotions in a given quarter?


In what quarters was a certain item involved in a certain type of promotion?
To answer these questions, you must combine the two relationship tables, creating one table to relate all three attributes.
The relationship in the distinct relationship table must exist for a joint child relationship to be properly defined. However, it does not necessarily have to be in its own, distinct relationship table. Defining the relationship directly in the lookup table for the parent of the joint child—in this case, Promotion—would be fine. Alternatively, you can build the relationship directly into the fact table.
In these examples, it is important to notice the relationship between the three attributes. The Promotion attribute is related to a particular Item-Quarter pair, as opposed to it being related to Item and Quarter separately. This is the essence of a joint child relationship and is shown in the following diagram.
external image joint_child_relationships.gif
Notice that a joint child relationship can be one-to-many or many-to-many. The issues with many-to-many relationships, including loss of analytical capability and multiple counting, also apply to many-to-many joint child relationships.
If you have a joint child relationship in your data, it is important for you to define it in MicroStrategy so that you get the correct data for reports that use the parent attribute in a joint child attribute. This ensures that when you need to join the fact table to the parent attribute of a joint child relationship (for example, to see sales by promotion) the join will always use both joint children rather than just one or the other.

Supporting many-to-many and joint child relationships

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 Model and 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:

In what colors are certain items available?


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

Comments

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

Error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog

Error. Engine Attribute Role Limit Exceeded! Some times Microstrategy will give the below error when updating the schema after an attribute is created. MicroStrategy Developer --------------------------- Error(s) occurred while loading schema: [DFCSCHEMA] Population Exception: The object shown in the following hierarchy no longer exists in schema: -Table LKUP_TEST_FEE_SHIPMENTS error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog; OR (2) Use Table Alias featureDSSSQLEngine: Schema loading error: Message from GetErrorInfo : Report cache is not found.. Error(s) occurred while loading schema: 63. CAUSE: This error message means that the table shown in the error text needs to be split into a very large number tables. MicroStrategy has a limitation that only allows one table to be split into no more than 100 tables. The error above is shown when this limitation is surpassed....

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

Relative path for images in Microstartegy

Best practices on how to reference an image specified using a relative path for exported documents in MicroStrategy If possible, users should use HTTP paths to reference an image other than relative path or absolute path. If it is necessary to use a relative path to reference image, multiple locations need to store the copy of image as shown in the following table: Important notes: MicroStrategy Web Server is not involved in storing image when exporting. Image should be stored in Intelligence Server or MicroStrategy Developer Client For MicroStrategy Developer export, local MicroStrategy Developer handles the export, image should be placed on every single Developer machine If you don't know your Common Path or MSTR Home Path you can obtain them from the following registry keys if using windows or msireg.reg if using Unix HKEY_LOCAL_MACHINE --> SOFTWARE -->Microstrategy --> DSS Server -> Casetor -->Home Path HKEY_LOCAL_MACHINE --> SO...

Ways to create derived elements

Ways to create derived elements Using the Derived Elements Editor, you can create derived elements using the techniques described in the topics listed below: Grouping attribute elements to create derived elements Filtering attribute elements to create derived elements Using calculations to create derived elements

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

Microstrategy Filters & Prompts explained

Microstrategy Filters & Prompts explained Filters Specify conditions required for data to be included in a report. A filter specifies the conditions that data must meet to be included in a report or a metric. Filters limit the data that is extracted from the data source and focus the data presented to the end user.When building a report, users can create filter expressions that include sophisticated conditional clauses in order to pull specific slices of information. Within reports and documents, end users can further narrow down results by adding view filters that dynamically change the subset of data being displayed. Prompts A prompt allows a user to provide an answer to restrict or filter data during report execution. P rompts allow report designers to create reports that let users change the report content at run time.  Prompts can modify the definition of custom groups, filters, metrics, and templates, as well as reports. P...

Microstrategy Dossier training videos

Microstrategy Dossier training videos Adding data to a Dossier: Creating a visualization filter in Dossier: Sending Dossier to User libraries: How to format Dossiers: Adding a designer filter to a Dossier: Use Bookmarks in Dossiers: Dynamic Links in Dossier: Recreating documents from Dossiers: Exploring and searching in Dossiers: Exploring sample dossiers in MSTR library: Using Page to Page Targets in Dossiers:

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Disable data blending in MicroStrategy

Disable data blending in MicroStrategy Starting in MicroStrategy 9.4 data blending was made available for documents and dashboards. This permits grid, graph and widget objects to source data from multiple different datasets at the same time.  This is available under the analytical engine VLDB properties inside of project configuration. The property is named "document grids from multiple datasets" and defaults to enabled but can be set to disabled.  Below are the steps to enable/disable the settings of data blending: 1. Go to project configuration by right clicking on specific project(You need admin rights to do this). 2. In the Project configuration windows as shown below select Configure under Project level VLDB settings section. 3. Now it will open the VLDB settings window, select + on " Analytical Engine Settings " and then click on " Document Grids from multiple datasets " option. You will be presented with two...