0

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.

androboy
  • 817
  • 1
  • 12
  • 24

1 Answers1

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).
mucio
  • 7,014
  • 1
  • 21
  • 33
  • Thanks mucio@. The tables here are representative model for the problem I am trying to solve. Counting prices is equivalent to counting a different activity for me (like price changes). I went in this route of creating a logical table in MSTR, but decided to create views in my database and use the views instead of tables as logical databases. That was one of the alternative I tried, but was looking for MicroStrategy provided options other than we manipulating the model. – androboy Feb 17 '16 at 18:40
  • 1
    MicroStrategy requires a dimensional model, there are multiple way to trick the engine, but at the end you will have a pile of hacks not easily maintainable. Fix the model and you can enjoy the other MicroStrategy features, otherwise in the future you will have much more problems – mucio Feb 18 '16 at 11:11