Logical Tables » Logical view examples » Business case 1: Distinct attribute lookup table

Business case 1: Distinct attribute lookup table

Many star schemas feature a single lookup table that is shared by all the attributes in one dimension (see the following example). While it is possible to model a schema with such a dimension table, often two problems arise:

The model cannot support fact tables at the level of attributes that are not keys. This restriction applies to summary tables as well as to intermediate results that may be generated by the SQL Engine.

Usually, in one-SQL-pass reports, the MicroStrategy Engine joins the fact table with one lookup table and does the aggregation. If there is no distinct list of attribute elements, you may double count if you have to join to a table where that attribute is part of the key.

Too many rows in the dimension table may slow down the SELECT DISTINCT query, thus affecting element browsing requests that display a list of attribute elements, for example, when populating pick lists for prompts.

The following is an example lookup table for Store, Market, and Region.
















In this table, Market and Region are not the keys. Therefore, if the requested fact table is at the Market or Region level, a direct join between the fact table and the above lookup table may result in double-counting. To avoid that, you can use the Logical View feature to define another logical table Lookup_Market as follows:

Select Market_ID, Market_Name, Region_ID
From Lookup_store
Where level=1

Then use this table as the lookup table for Market. When it is joined with a Market-level fact table (Market_Sales), the following report SQL is generated:

Select a11.Market_ID,a11.Market_Desc,
From (select Market_ID, Market_Name, Region_ID
from Lookup_Store
where level=1) a11,
Market_Sales a12
Where a11.Market_ID = a12.Market_ID
Group by a11.Market_ID,