One huge dataset or several datasets ? Raw data or preprocessed data ?

[François]

I have to build a report giving plenty of KPIs based on a specific period & specific customers. Sources are multiples, and if I take the raw data, I have a couple tables with quite some lines (few millions). Until now, we have built mostly some big datasets, and we build reports based on those. But now i'm asked to add more and more data, and I would like to use the right strategy. 

First about the data : 

1/ To use the raw entire tables (quite big already, maybe 30 tables)

2/ I can simplfy by getting rid of the biggest tables : I can pre process them to extract only what is needed by other tables and reduce to some hundred or dozens thousands lines. Less tables, less lines. But of course, it adds this pre-processing tasks to maintain. 

3/ other ?

Then about the report itself :

1/ should I put all the tables in the same report ? 

2/ since there are a lot of tables from various sources (CRM, web, ERP...), should I build one report per source, then use the "virtual tables" (not too sure about the name) taken from another dataset ? I tried that already, working quite fine. 

3/ could I create one report/dashboard grabbing the info from other datasets with the right filters ? I don't think I saw the option to build a master report where you can decide of the filters (dates + customers IDs), then it would query other datasets / reports with those parameters and merge all the results in one report. 

4/ other ?

Any advice, experience, feedback is very welcome 🙂 

[Idrissshatila]

Hello @François ,

1- You could start first by developing a star schema data Model for better performance.
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

2- As long as you can do the data transformation in the data source, don't fo it in the report.

3- you could do the dataset in one power bi file and then open new files that connects to Power bi dataset.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[François]

Thanks for your quick answer !

About 1/, after reading a bit about the star schema, I can see that it's what we were doing in most of our reports already. In this specific case, I think we are more in a "constellation" case than a simple star one : I have multiples stars (one around sales, one around CRM activities, one around quotes), and they share the same dimension tables. To connect the dimension tables together is what we would like to do : you filter on one dimension, it filters on multiple stars. I don't know if that's something easily doable in Power BI, or even if that's the perfect tool for that. What I'm asked for could be more the job of a CDP tool than a job for PowerBI. 

For 2/, noted, we will work on the data first and not let PowerBI do all the job

For 3/, that's what I meant by "virtual tables", it's something we were already doing to avoid importing the same data in multiple datasets.