Fact Table with Mixed Granularity

[kishoreravi1983]

Hi All

I have Set of 3 Tables

 Orders Tables:  Granularity at Date Level

Order Details: Granularity at Item Level for each Category

Sales Target Table: Month and Category: Target for Each Category is Set at Month level for multiple Years.

Need to Build a Data Model to measure the performance of Each Category Month wise.

Can any one help me how to build a Data Model with Mixed Granularity

Attached Data file

Thanks

Ravi

[zzyjordan]

Hi, Kishoreravi

Please find the load scrtipt below

OrderList:
LOAD
"Order ID",
"Order Date",
"Customer Name",
City,
Country,
Region,
Segment,
"Ship Date",
"Ship Mode",
State,
MonthStart("Order Date") as "Month of Order Date"
FROM [lib://AttachedFiles/AmazingMartEU2.xlsx]
(ooxml, embedded labels, table is ListOfOrders);

Left Join (OrderList)

LOAD
"Order ID",
"Product Name",
Discount,
Sales,
Profit,
Quantity,
Category,
"Sub-Category"
FROM [lib://AttachedFiles/AmazingMartEU2.xlsx]
(ooxml, embedded labels, table is OrderBreakdown);

Left Join (OrderList)
LOAD
"Month of Order Date",
Category,
Target
FROM [lib://AttachedFiles/AmazingMartEU2.xlsx]
(ooxml, embedded labels, table is SalesTargets);

sample of result

Hope this helps

ZZ