MicroStrategy dimension tables left outer join fact tables scenario

Question

I am trying to mimic following SQL in MicroStrategy using given tables

• master_dimension - Itemkey, color, weight, season, target_age_group... 1000 rows with 1000 unique items

• sub_dimension - item_key, need_special_handling - 400 rows with only 400 of 1000 items having rows in this table

• prices - item_key, price, start_date, end_date - 600 rows with 200 unique products having price/price changes

• stock - item_key, available_stock_qty - 155 rows with 155 unique products having stock quantity

select            
m.itemkey, m.color, m.weight,...
sub.need_special_handling
, count(p.price)
, sum(st.available_stock_qty)
from master_dimension  m
left outer join sub_dimension  sub
on m.itemkey = sub.item_key
left outer join prices  p
on p.itemkey = m.itemkey
left outer join stock st
on st.itemkey = m.itemkey
group by
m.itemkey, m.color, m.weight,...
sub.need_special_handling

I created the attribute itemkey with all the tables involved as lookups with primary lookup table as master_dimension. I modified VLDB properties of itemkey attribute to joins->preserve all final pass result elements -> 3rd option (preserve all elements of final pass... but not relation table). I modified report VLDB properties to joins-> preserve all lookup table elements -> 4th option (preserve lookup elements... with filter). I played with multiple options of VLDB properties, but not able to achieve left outer join between attributes and between attribute and fact tables.

Answer 1

MicroStrategy wants to have a Dimensional model (forget about left join) and you need to explode slowly changing dimensions like your price table.

Things you can do to use MicroStrategy with your tables:

• Create a logical table in MicroStrategy (or a view on your database) like:

           select Itemkey,
                  ...
             from master_dimension  m
  left outer join sub_dimension  sub
               on m.itemkey = sub.item_key
  

  This will be your lookup table for your Item attribute.

• Create another logical table (or view) for your prices:

           select Itemkey,
                  date,
                  price
             from prices p
       inner join calendar d
               on d.date between p.start_date and p.end_date
  

• Use the VLBD setting to preserve the lookup elements

Couple of additional things:

• Not sure why you count prices, but I guess you have your reasons.
• From your table looks like you have no date for the stock metric, in case you have it don't forget that you don't want to aggregate today with yesterday stock, in that case you need to add the level Date (Standard, Fact ending).