KB20144: An attribute’s lookup table is joined to a fact table through the wrong relationship table in MicroStrategy SQL Generation Engine

• Strategy

This article describes a data model problem that can cause the wrong joins to fact table.

SYMPTOM

A user creates a report and finds that it returns unexpected data. When examining the SQL, the user finds an undesired join path in the FROM clause.
For example, in a simple schema derived from Strategy Tutorial data, the following report may be built. The Revenue metric will calculate against the ORDER_FACT table, and the Month attribute in the filter is supposed to refer to the month in which the order was placed.

The report SQL, however, shows that Month is joined to the fact table through the fact table's SHIP_DATE column. Instead of returning revenue for orders placed in January 2006, the report displays revenue for orders shipped in that month.

select a14.REGION_ID REGION_ID,
   max(a15.REGION_NAME) REGION_NAME,
   sum(a11.ORDER_AMT) WJXBFS1
from ORDER_FACT a11
   join LU_SHIP_DAY a12
      on (a11.SHIP_DATE = a12.DAY_DATE)
   join LU_EMPLOYEE a13
      on (a11.EMP_ID = a13.EMP_ID)
   join LU_CALL_CTR a14
      on (a13.CALL_CTR_ID = a14.CALL_CTR_ID)
   join LU_REGION a15
      on (a14.REGION_ID = a15.REGION_ID)
where a12.MONTH_ID in (200601)
group by a14.REGION_ID
CAUSE

This issue is the result of an ambiguity in the logical schema design. Because of this ambiguity, the intended meaning of higher-level attributes cannot be precisely controlled.
The date hierarchy is defined as below. Also shown is the logical model of the fact table.

Google Map in VIGoogle MapApple MapGoogle Map in RSDGoogle MapApple MapESRI Map in VIESRI MapApple MapESRI Map in RSDESRI MapApple Map in 10.1 (showing Grid in 10 and lower)TimeEventDetails12amA scheduled data load occurs at 12am as desired.The 'Last data load start time' is set to 12am.5amAn administrative user wakes up and decides to trigger a manual or custom time window data load at 5am, 1 hour before the next scheduled data load.Because this is a manual or custom time window data load, the 'Last data load start time' is not affected.6amAt 6am, the scheduled data load kicks off, not affected by the manual or custom time window data load.The 'Last data load start time' is 12am, the frequency is 6 hours, so the data load is triggered as desired.TimeEventDetails12amA scheduled data load occurs at 12am as desired.The 'Last data load start time' is set to 12am.5amAn administrative user shuts down the intelligence server for maintenance.The data load schedule is not stopped.6amThe Intelligence Server is still shut down. At 6am, the scheduled data load kicks off, but fails.Because the data load kicked off, the 'Last data load start time' is set to 6am, the frequency is 6 hours, so the next data load will occur at noon. If the Intelligence Server is up and running at noon, the data load will migrate data over the entire time period since the last successful data load finish time.TimeEventDetails12amA scheduled data load occurs at 12am as desired.The 'Last data load start time' is set to 12am.5amThe data load schedule is stopped by an Administrator for system maintenance. 6amAt 6am, nothing happens.The data load schedule is still stopped.9amMaintenance finishes and the data load schedule is started again. 9am and 1 secondThe data load schedule process checks, and finds that the 'Last data load start time' plus 'frequency' is less than the current time, so the data load kicks off immediately.The data load kicks off immediately, setting the 'last data load start time' to 9am.Prompt Answer MethodRequirementsPrompts in the Target Are Answered By...Answer with the same promptSame prompt in the target and sourceThe prompt answer from the sourcePrompt userNoneThe user (prompts are displayed when the target is executed)Default answerNoneThe default prompt answer for the target promptDynamicallyAttribute element prompt in targetThe object selected in the source (for example, the attribute element that the user clicked on)Empty answerTarget prompt must not be requiredNothing (no prompt answer is provided from the source and the user is not prompted)Static element listAttribute element prompt in targetThis prompt overrides the prompt ansnwers from the source with a list of elements. Selecting this option makes the Element List area available to select the elements to be passed as answers to the element list prompt in the target.Current unitHierarchy prompt in targetSimilar to the Dynamic prompt answer method, where the prompt is answered using the object selected in the source.All valid unitsHierarchy prompt in targetAny object to the left of or above the user selection in the source is used as the prompt answer for the target. In other words, this method passes all the selections made on the source, rather than just the selection made for the link.ORDER_FACTOrder_IDOrder_DateShip_DateBUSINESS_DAYSBusiness_DayDAYS_TO_SHIPOrder_IDDays_to_shipSupported Platforms Latest macOS (macOS 10.14: Mojave (Liberty)) and Windows OS (Windows 10) preferred. Supported Browsers Google Chrome and Microsoft Edge.

Note: Use the latest version of each browser for the best experience. Strategy Authoring Tool for Cards Strategy Workstation 2019 and above.Authentication Modes SupportedGuest, Standard, LDAP, Trusted, SAML, Kerberos, and Badge.Supported Cubes OLAP Intelligent cube, in-memory MTDI cube, or an in-memory MDX/SAP cube CORS Settings CORS Settings must be enabled. Please see How to enable CORS Settings? Supported PlatformsMac and WindowsProcessors (CPUs)64-bit (minimum), 64-bit multicore (recommended) Memory RAM 4 GB or higher Disk Space 8GB or higher Date hierarchyORDER_FACT logical model

Because Month has both Day and Date Shipped as children, and both child attributes exist on the fact table, there are two equally valid join paths between Month and the fact table. Neither can be preferred definitively over the other. When the Strategy SQL Generation Engine searches for a way to connect Month to the fact table, it searches through the children of Month and stops when it finds a valid path. In this case, the search checks the Date Shipped attribute first, causing the join to be made through this attribute.

This type of hierarchy design has a serious, fundamental flaw. The business concept represented by the Month attribute -- the attribute's meaning -- is ambiguous. It literally can mean two different things which cannot be distinguished by the schema alone. When this attribute is used on a report, there is no way to be certain which type of Month (order or shipping) the results reflect. Consequently, users cannot be sure what information the report conveys, and the report's value as a decision-making tool is compromised. This limitation is an inherent consequence of the ambiguity in the schema. Therefore, such schemas are not supported as data integrity cannot be maintained.

ACTION

If the difference between month of ordering and the month of shipping is significant to end-users, then these concepts should be modeled as separate attributes, each with only one day-level attribute as its child. Users can control the meaning of a report by choosing the appropriate attribute. Note that these attributes are, by definition, not redundant because they mean different things in terms of the business data.

Note: Care should be taken when mapping these parallel hierarchies to lookup tables. Review Strategy's recommendation for modeling role attributes, found in the Strategy Project Design PDF manual, Chapter 6, "The Context of Your Business Data: Attributes" > Attributes that use the same lookup table: Attribute roles.

Note: It is not a hard and fast rule that an attribute should never have more than one child with similar data. The problem occurs when an attribute has multiple such children and both children exist on the same fact table. There would be no problem in the schema if Month had both day-level children, but Day were on ORDER_FACT (without Date Shipped) and Date Shipped were on a separate SHIP_FACT table (without Day). In that case, there would be no ambiguity because there would be only one join path between Month and either fact table.

Comment

0 comments

Details

Knowledge Article

Published:

May 5, 2017

Last Updated:

May 5, 2017