Attribute Mapping vs.. Attribute Relationships

written by Slavik Taubkin on February 8, 2013 in All Items and Modeling with no comments

Consider the time attributes of the system hierarchy.

Even in projects with highly normalized Snowflake-Date model is the dimension of time usually is an exception: alle Eltern-Attribute des attribute Day (also Month, Quarter, Year and possibly. Week) are stored in the same table. This is primarily due to the simple derivation of these values ​​from the current date.

Also in the MicroStrategy Tutorial Project is no different: table LU_DAY includes not only the foreign key MONTH_ID, but also QUARTER_ID and YEAR_ID:

Also the lookup table LU_MONTH includes not only the necessary technical QUARTER_ID, but also the YEAR_ID.

In the modeling of the scheme, the question arises, as the parent-child relationships between the attributes Day, Month, Quarter and Year must be defined. If one of each existing logical connection creates a parent-child relationship in MicroStrategy, the system hierarchy of the time attributes look like this:

To test the SQL generation, We create a review: we drag the attribute Day on the template and restrict the attribute in the filter area Year one.

For this report, MicroStrategy generates the following SQL query:

select	a11.[DAY_DATE] AS DAY_DATE
from	[LU_DAY]	a11
where	a11.[YEAR_ID] in (2008)

As you can see, remained the dimension tables for Month and Quarter disregarded. Instead, MicroStrategy has the value of Year directly from the table LU_DAY taken. This optimization does, however, no direct connection between Day and Year!

For the attribute relationships in MicroStrategy is namely the transitive law: da das Attribut Day than one-to-many with Month connected, and Month again with Quarter (with the same cardinality), weiss MicroStrategy, that the attributes Day and Quarter as well indirectly via a one-to-many relationship.

By omitting derivable relations we get a lot about prejudice ubere system hierarchy, which is semantically equivalent to the first version but:

This modeling is the original definition of the system hierarchy in the MicroStrategy Tutorial Project.

As expected, generates the test still the same query:

select	a11.[DAY_DATE] AS DAY_DATE
from	[LU_DAY]	a11
where	a11.[YEAR_ID] in (2008)

Thus, the SQL generation still working properly, the attribute Year still on the table LU_DAY be mapped! It is this condition while necessary, but not alone adequately: if between the connecting chain Year and Day somewhere interrupts (So one of the relationships Month -> Day, Quarter -> Month or Year -> Quarter distant), MicroStrategy will each filter to Year than unrelated relative to attribute Day consider - even if the attribute Year still on the table LU_DAY is mapped!

The parent-child relationships are so crucial to the question, whether der Join bzw. the WHERE clause is generated at all. For the decision, as exactly the generation is to be, However, the reduction of table accesses plays the most important role.

This dual principle is even more apparent in the following example.

First, we remove the relationship between Year and Quarter. After the schema update we have again the SQL query to generate Review:

select	a11.[DAY_DATE] AS DAY_DATE
from	[LU_DAY]	a11

As expected, is on the filter Year is no longer taken into account (he was from the SQL engine marked as "unrelated" and removed in the optimization step).

Now we want the relationship between Day and Year Restore "artificially". For this we declare Year as a parent of the attribute Month. As a relationship table but we do not choose LU_MONTH, but F_TUTORIAL_TARGETS, both MONTH_ID and YEAR_ID contains.

We update the schema and call the Review again in SQL mode:

select	a11.[DAY_DATE] AS DAY_DATE
from	[LU_DAY]	a11
where	a11.[YEAR_ID] in (2008)

The "artificial" relationship between Year and Day has caused, that the filter is again generated. The table F_TUTORIAL_TARGETS however, is not used; instead comes YEAR_ID from the table LU_DAY, although we have not given as a relationship table!

Even if we have a metric (z.B. Units Sold) Add to Template, still the same principle applies:

select	a11.[DAY_DATE] AS DAY_DATE,
	sum(a11.[TOT_UNIT_SALES]) AS UNITSSOLD
	[DAY_CTR_SLS]	a11, 
	[LU_DAY]	a12
where	a11.[DAY_DATE] = a12.[DAY_DATE]
 and	a12.[YEAR_ID] in (2008)
group by
	a11.[DAY_DATE]

The artificial relationship leads us to the monthly level. Then the attribute Month the reporting level Day get to, is a join with LU_DAY needed. So the join between the fact table and must LU_DAY are generated in each case,. However, since the attribute Year directly on LU_DAY is mapped, will join with another F_TUTORIAL_TARGETS no longer needed!

The relationship between Year and Month about table F_TUTORIAL_TARGETS was created only to illustrate the join logic. In real projects, no fact tables to be used for the definition of the attribute relationships! Click here to read, why.