December 22, 2021 at 5:15 am
Except I'm not talking about normal SQL Server/T-SQL, except to get data out of SQL Server and into the data model. I'm talking about DAX. =)
Here are the measures for my report:
Total Store Sales = SUM('Table'[StoreSales])
Area Total = CALCULATE(SUM('Table'[StoreSales]), ALL('Table'[store]) )
Area Share = DIVIDE ( [Total Store Sales], [Area Total])
And most/all of the stuff in PowerBI dashboards is visuals. You wouldn't drill all the way down to the raw data except in rare circumstances, but it's possible. =)
December 22, 2021 at 7:18 pm
I was pointing that out to the OP... Maybe read a little of The Data Warehouse Toolkit to learn how to build star schemas might be in order. I didn't find it immediately obvious, for sure.
the problem I have with importing already aggregated data is that you lose the ability to drill all the way down into the data. And that's what PowerBI is for. (either that or I've been missing something for a long time!)
but it's entirely possible to create views to create dimension tables in the PowerBI data model. Might actually be easier that way. The book I would absolutely recommend is Rob Collie's book, though... it's about the easiest introduction to star schemas I've seen... and he's really good at explaining DAX so even I could understand it. You quite literally build along with him as you read the chapters.
I have read the Data Warehouse Toolkit. When a report request is cemented at a certain grain, I just write the query to pull in the results. Not saying it is ideal for later changes.
----------------------------------------------------
December 22, 2021 at 8:36 pm
That's the weird thing about DAX / PowerBI... the reports are fully interactive. You can get data from a stored procedure or view that has no aggregates at all, and do it all in DAX. It uses something similar to column store to compress the data so you don't really have to preaggregate it. Most of the time you wouldn't drill all the way down, but you can. And then export that to somewhere.
Silly me. I think a big part of the OP's problem is that he has an OLTP structure, and he needs a Data Warehouse/Star Schema structure. I should have mentioned that a long time ago. Then he has a Forecast fact table, a Sales fact table, and then dimension tables, like Product, Store, etc.
Little wonder he's confused. I would be too.
How soon does all this have to be done? Creating a data warehouse / star schema for this may take a minute. Some CREATE TABLE scripts for the data you already have would help a lot. With that we can figure out what your star schema could/should look like, and queries to populate all the tables. And then you can start writing DAX for the analysis...
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply