CODEWITHSUNDEEP
databasepowerbidata-warehouse
Ethan_l
Ethan_l

Reputation: 1

Should I create new table in data mart or use Power Query for each new dashboard?

I am working on a PowerBI report that consists of multiple dashboards. The data needed is from a single table with 100K rows of data in DWH . The table stores all the variables and values for different stores, as shown in the picture below.

Currently, we are creating new table in data mart for each separate dashboard, such as total profit in each country, total number of staff in each country etc. However, I realize I can do the same using Power Query without adding new tables for my data mart. So I am curious which approach is better?

And this leads to another question I always have, when we need a tranformed table for dashboard, shoud we create new tables in data mart, or should we do it in the BI tool such as PBI or Tableau? I think performance is a factor to be considered, but not sure about the other factors.

Appreciate if anyone can share your opinion.

1

Appreciate if anyone can share your opinion.

Upvotes: 0

Views: 139

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

Given the amount of transformation that needs to occur, it would be worth doing this in the DWH. Power BI does well with a star schema, so it would be good to break out dimensions like country, store and date into their own tables.

You might also work the measures into a single fact table - or maybe two if some of the facts are transactional and others are semi-additive snapshot facts. i.e. profit vs. number of staff. Designed right, the model could support all of the dashboards, so you would not need a report table for each.

Upvotes: 1

Related Questions