Unrelated Filters

written by Slavik Taubkin on January 16, 2013 in All Items and Modeling with no comments

If an attribute does not (ggf. indirect) Connection to one of the attributes of the SQL engine selected fact table ( = „fact entry level“) has, we call it unrelated attribute ("Unrelated" is in German MicroStrategy surface as "unrelated" or. translated in some places as "not belonging").

As we here have seen, MicroStrategy generates each a CROSS JOIN to lookup tables of unrelated attributes, unless those attributes on the template or in the OLAP area (and their fields are thus included in the SELECT clause of the generated query).

But how does MicroStrategy, when an "unrelated attributes" in the filter area (also in der WHERE-Klausel) used?

For our test, we create the report "Units Received for an Employee", der Metrik Units Received is for a specific employee.

The key of the fact table INVENTORY_ORDERS (the entry level of the Faktes Units Received) consists of the attributes Item and Month. However, the table does not contain the attribute Employee nor any of its "parent attributes" (Attribute, which are higher in the hierarchy), is therefore Employee In this report an "unrelated attribute". A filter on the attribute Employee is in accordance with the unrelated filter referred.

In SQL view, we note, that MicroStrategy has generated no CROSS JOIN. Instead, the filter is completely ignored:

select
	sum(a11.[UNITS_RECEIVED]) AS UNITSRECEIVEDPERMONTH
from	[INVENTORY_ORDERS]	a11

This compact is the result of a query optimization of SQL engines. Without optimization, the query would look like this:

select
	sum(a11.[UNITS_RECEIVED]) AS UNITSRECEIVEDPERMONTH
from	[INVENTORY_ORDERS]	a11
	cross join	[LU_EMPLOYEE]	a12)
where	a12.[EMP_ID] in (1)

This query would be identical in content to the more compact version. Therefore, it is actually useful, that MicroStrategy not considered in our example, the "unrelated filter".

You can force a query in MicroStrategy, which contains both the CROSS JOIN and the WHERE condition, indem man das Attributes Employee also draws on the template.

So we have seen, that the filter "optimized away" is, if it has no connection to the entry level of the Facts. Now we want to investigate, which compound is needed for the inclusion of the filter.

To explore this question, we expand the Tutorial Project to the attribute Week:

  1. Download the compressed file week.zip down. The database contains the table contained therein week.mdb LU_WEEK and the updated table LU_DAY with the assignment to the weeks.
  2. Open the database in MS Access TUTORIAL_DATA_7200.mdb. Typically, this file is located in the "C:Program FilesMicroStrategyTutorial Reporting“.
  3. Delete the table from the database TUTORIAL_DATA_7200.mdb LU_DAY.
  4. Select via "Get External Data / Import" week.mdb the downloaded file, and import the two tables LU_DAY and LU_WEEK.
  5. Now open the Tutorial Project. In the Warehouse Catalog LU_DAY the structure of the table needs to be updated; also adds to the new table added LU_WEEK.
  6. The new attribute must be created and the relationship Week Week -> Day be defined.

In addition, we duplicate the fact Units Received and delete the extension to Day. Because of this fact no extension is present only on a monthly basis, we call the copy Units Receided (per Month). Then we update the schema and create new fact based on the same metric.

Die Attribute Day and Week are in a "many-to-one" relationship. The same applies to the relationship of the attribute Day to attribute Monat: also it is "many-to-one". It follows, dass Attribute Week and Month implicitly are connected by a "many-to-many" relationship.

It would be interesting to know, how MicroStrategy behaves, Wenn man Metrik Units Receided (per Month) with a restriction of the attribute Week calculated.

Although the relationship between the attributes Month and Week about Day given, is on the filter Week von MicroStrategy ignoriert, Thus, as unrelated considered.

select
	sum(a11.[UNITS_RECEIVED]) AS UNITSRECEIVEDPERMONTH
from	[INVENTORY_ORDERS]	a11

To understand, why no join between the fact table and the lookup for the attribute weeks Day was generated, we consider, for example, the filter "Week = 200901".

Since the week after assignment DIN 1355/ISO 8601 was generated, this is the first day of the week 29.12.2008 and the last of 04.01.2009. Thus, this week contains days from two months (and even two years): December 2008 and January 2009. MicroStrategy is the view, that it would not be intuitive, simply display the sum of these two months in this situation. For this reason, the filter is simply ignored.

But if you pursuing exactly that goal, must be the link between the attributes Month and Week explicitly are produced. There are two approaches. The question is critical to the selection of the approach, whether this connection global, therefore may be potentially relevant to all reports or only for a specific report locally is to be applied.

Global approach:

The data model is a new table (or. View) REL_WEEK_TO_MONTH extended. Die Attribute Week and Month obtain a new explicit Parent-child relationship with cardinality "many-to-many".

Rooms Ansatz:

Using a association filter is specified, that the limitation "week = 200901" at the level Month is to be applied (offer similar functionality, the filter reports).

In both approaches, the same SQL query is generated by MicroStrategy:

select
	sum(a11.[UNITS_RECEIVED]) AS UNITSRECEIVEDPERMONTH
from	([INVENTORY_ORDERS]	a11
	inner join	(select	distinct r12.[MONTH_ID] AS MONTH_ID
	from	[LU_DAY]	r12
	where	r12.[WEEK_ID] in (200901)
	)	pa12
 	(a11.[MONTH_ID] = PA12.[MONTH_ID]))

The query produces the following result:

As expected, for all days of December 2008 or. for all days from January 2009 the same value is shown.

Incidentally, the VLDB setting “Query Optimizations/Unrelated Filter Options = 1 (Keep unrelated filter)” In our example, no effect on the SQL generation: the filter on Woche is still ignored. The setting can be used only useful, if the report contains at least two filter. The precise statement is in TechNote 11121.