Search code examples
sql-serverssaspowerpivotdaxtabular

modeling correct star schema for ssas tabular

I'm using ssas tabular (powerpivot) and need to design a data-model and write some DAX. I have 4 tables in my relational database-model:

Orders(order_id, order_name, order_type)
Spots (spot_id,order_id, spot_name, spot_time, spot_price)
SpotDiscount (spot_id, discount_id, discount_value)
Discounts (discount_id, discount_name)

One order can include multiple spots but one spot (spot_id 1) can only belong to one order.
One spot can include different discounts and every discount have one discount_value.
Ex:
Order_1 has spot_1 (spot_price 10), spot_2 (spot_price 20)
  Spot_1 has discount_name_1(discount_value 10) and discount_name_2 (discount_value 20)
  Spot_2 has discount_name_1(discount_value 15) and discount_name_3 (discount_value 30)

I need to write two measures: price(sum) and discount_value(average)

How do I correctly design a star schema with fact table (or maybe two fact tables) so that I in my powerpivot cube can get:

If i choose discount_name_1 I should get order_1 with spot_1 and spot_2 and price on order_1 level will have value 50 and discount_value = 12,5
If I choose discount_name_3 I should get order_1 with only spot_2 and price on order level = 20 and discount_value = 30

Solution

  • Fact(OrderKey, SpotKey, DiscountKey, DateKey, TimeKey Spot_Price, Discount_Value,...)

    DimOrder, DimSpot, DimDiscount, etc....

    TotalPrice:=
SUMX(
    SUMMARIZE(
        Fact
        ,Fact[OrderKey]
        ,Fact[SpotKey]
        ,Fact[Spot_Price]
    )
    ,Fact[Spot_Price]
)

AverageDiscount:=
AVERAGE(Fact[Discount_Value])

    Fact table is denormalized and you end up with the simplest star schema you can have.

    First measure deserves some explanation. [Spot_Price] is duplicated for any spot with multiple discounts, and we would get wrong results with a simple SUM(). SUMMARIZE() does a group by on all the columns passed to it, following relationships (if necessary, we're looking at a single table here so nothing to follow).

    SUMX() iterates over this table and accumulates the value of the expression in its second argument. The SUMMARIZE() has removed our duplicate [Spot_Price]s so we accumulate the unique ones (per unique combination of [OrderKey] and [SpotKey]) in a sum.