Time-based drilldowns in Power BI powered by Azure Data Warehouse

Question

I have designed a simple Azure Data Warehouse where I want to track stock of my products on periodic basis. Moreover I want to have an ability to see that data grouped by month, weeks, days and hours with ability to drill down from top to bottom. I have defined 3 dimensions:

DimDate
DimTime 
DimProduct

I have 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 fact sample data is 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

What I need is a chart of product availability over time with ability to check total (where x axis represents hours), as well as 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

Moreover I need an ability to drill up and browse average availability by days and products (where x axis represents days, weeks, month, years available via the DimDate table):

Total - 141.5, 157
Product1 - 110.5, 122.5
Product2 - 31, 34.5

x-> 20160510, 20160511

It seems that Power BI is unable to make that group by date thing, because it's trying to use an aggregate function to get the daily value and there is no ability to specify the average function which will take into account products (with grouping by product). The aggregating function just doesn't work here, Power BI is trying to sum all values across all products for a given day and get the average (e.g. for 20160511):

150+29+95+40 / 4 = 78.5

When what I need is the following:

(150+29) + (95+40) / 2 = 157

I just want to make it interactive with an ability to chose a product and easily drill up and down. Please advise how I should modify my Warehouse structure to support my scenario.

Ferland

Answer 1

I don't think you need to change your structure. I would create a New Measure on your FactStocks table (using Power BI Desktop), to calculate Availability as you require it using DAX functions.

I'm guessing a bit as you havent really spelled out your requirement besides 1 example, but it would probably look something like this:

Availability = SUM([StockValue])/DISTINCTCOUNT([ProductKey])

Here's a full tutorial on Measures in PBI Desktop:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-create-measures/

Frierson

thanks for your vision. My requirement is as simple as display changes of stock by product over the time (hour, day, week, month, quarter, year). – Ferland

Answer 2

I think the problem here is that your measure is actually extremely complex - what you're trying to do is sum within each day, then average across multiple days. To get this, you'll need to get your hands dirty with DAX.

I'm writing this without an editor or data, so please forgive me if I get some syntax errors or mix up my parameter order:

DailyAverage:=CALCULATE(SUMMARIZE('FactStocks', [DateKey], 'DailySum', Sum([StockValue])), AVG([DailySum]))

The principle is to SUMMARIZE (basically GROUP BY in SQL) your table, grouping by DateKey, summing up the values within each group, then average the result (now averaging one row per day instead of per row in the source table).

Ferreby

Answer 3

Could you perhaps use database views to do your calculations and aggregations?

Oriente