Attribut-Drill trotz „Fact does not exist at a level“

written by Slavik Taubkin on January 9, 2013 in Modeling and Performance with no comments

MicroStrategy offers two different drilling techniques: to drill to an attribute (further in the text referred to as Attribute-Drill) and the drilling template to a (Template-Drill).

The attribute-Drill provides the user with greater flexibility available: through several processes executed one after the drill, you can view the metrics in theory at any level. The disadvantage of this technique, however,, that the amount of metrics related to the template at a drill attribute remains unchanged. Consequently, the situation may occur, that one of the metrics does not exist on the new drill level, while another could be quite metric calculated and presented. Unfortunately, in this case suggests the complete report execution fails: in the current version of MicroStrategy no way is intended, simply hide the non-existent metrics.

In the article Fact extensions We have seen one such example: when drilling in the "Sold report vs.. Received Units“ auf das Attribut Day Copy war nur Metrik Units Sold represented; because of the inventory metric Units Received However, there was a crash with the error message Fact does not exist at a level that can support the requested analysis. Fact: „Units Received“. Level: „Day Copy, Item“.

We now pursue the following objective: the report "Sold vs.. Received Units“ bzw. customize the schema to, that the attribute drill is also possible, when one of the metrics does not exist on the resulting level.

Of course you could just the fact Units Received to the plane Day Copy expand - Narrow jump, how it is done in the existing fact extension "degradation to Day" (see here). In this case, MicroStrategy would when drilling to the level Day Copy show the average value (Month value divided by the number of days).

Since the average value is often technically irrelevant and thus only misleading, you could even completely hide the metric values ​​at a given level (only the values, not the column itself!): z.B. by adjusting the Allocation Expression, so that it always returns NULL values.

Since the constant NULL can not be entered directly in the Expression Editor, one might instead the equivalent MicroStrategy expression ZeroToNull(0) use.

The disadvantage of the solution by means of extension fact is, however,, that an unnecessary join is performed, quite a lot of time can take, although in the end anyway just an empty column is represented. This workaround is therefore quite ineffective.

I'd like to introduce a different approach, although it also represents only a workaround, However, much better performance.

The idea is, one empty Establish facts on the table required for the drill level:

Create Table INVENTORY_ORDERS_DAY (
   DAY_DATE DATETIME(0),
   Item_id SMALLINT,
   UNITS_RECEIVED REAL,
   MONTH_DURATION SMALLINT
);

Now we can attribute Day Copy mapped to the new fact table. Thereafter, for the metric Units Received The following query generates:

select
	a11.[ITEM_ID] AS ITEM_ID,
	a11.[DAY_DATE] AS DAY_DATE,
	sum(a11.[UNITS_RECEIVED]) AS UNITSRECEIVED
from	([INVENTORY_ORDERS_DAY]	a11
	inner join	[LU_DAY]	a12
	  on 	(a11.[DAY_DATE] = a12.[DAY_DATE]))
where	(a11.[ITEM_ID] in (1)
 and a11.[MONTH_ID] in (200912))
group by
	a11.[ITEM_ID],
	a12.[DAY_DATE]

Da Tables INVENTORY_ORDERS_DAY contains not a single line, is for Units Received at the level of Day Copy actually displayed an empty column. However, the query is run immediately, yes because no calculation at all cost arises.

After running the drill action the result is as follows: