I have developed a simple Azure Data Warehouse where I want to periodically track the stock of my products. In addition, I want to be able to see that the data is grouped by months, weeks, days and hours with the ability to expand from top to bottom. I defined 3 dimensions:
DimDate
DimTime
DimProduct
I also defined a fact table to track product stocks:
FactStocks
- DateKey (20160510, 20160511, etc)
- TimeKey (0..23)
- ProductKey (Product1, Product2)
- StockValue (number, 1..9999)
My facts example data below:
20160510 20 Product1 100
20160510 20 Product2 30
20160510 21 Product1 110
20160510 21 Product2 35
20160510 22 Product1 112
20160510 22 Product2 28
20160510 23 Product1 120
20160510 23 Product2 31
20160511 00 Product1 150
20160511 00 Product2 29
20160511 01 Product1 95
20160511 01 Product2 40
I need a product availability chart over time with the ability to check the total quantity (where the x axis represents the clock), as well as the ability to filter by specific product:
Total - 130, 145, 140, 151, 179, 135
Product1 - 100, 110, 112, 120, 150, 95;
Product2 - 30, 35, 28, 31, 29, 40;
x-> 20,21,22,23,00,01
In addition, I need the ability to expand and view the average availability by days and products (where the x axis represents days, weeks, month, years available through the table DimDate):
Total - 141.5, 157
Product1 - 110.5, 122.5
Product2 - 31, 34.5
x-> 20160510, 20160511
, Power BI , , , ( ). , Power BI (, 20160511):
150+29+95+40 / 4 = 78.5
:
(150+29) + (95+40) / 2 = 157
. , , .