Recommended Approach for Large Project

[szelenko]

Hello friends,

im currently working in a large project for an insurance company.
Until now i got approx 10 dimensions and 8 fact tables.

Dimensions (some of them)
- Date
- Customers
- Locations
- Coverages
- Vehicles
- Agents

Facts (some of them)
- Sinister
- Sales
- Payments
- Policy accrual
- Required documents status

Source database: Firebird, but for performance purposes i have a Data warehouse in a Centos VM, where daily the data is updated, hosting a copy of the necessary tables in a Postgres DB.
To develop the dimensions and fact i use Dremio in the VM.
Power bi connects to Dremio in import mode. (Currently PBI service doesnt support Dremio in direct query mode).

Considering the large volume of data (for example, in the sales facts table there are approximately 7 million records) I had to use incremental refresh, making only the latest records be updated.

In the report I have very few calculated columns, I try to do everything in sql, in dremio.
I have many measures, more than 150.

Currently i have 60 pages.

As more dimension tables and fact tables are being included, opening the project and creating measures becomes extremely slow, almost impossible to work with.

Having parallel projects (forks) with fewer tables is not an option as the questions to be solved involve almost all dimensions and facts.
I am using star modeling best practices and I am very careful about the need to include a table.

I have tried using this project as the source for a blank project and it greatly improves speed.

My question is:
Which path/option is appropriate for large and complex projects?
Should I have a project with the data model but without pages or graphics, which serves as the source for several independent reports?

Unfortunately, due to company policy, I cannot share screenshots or more information.

I appreciate your comments.
Regards.

[v-stephen-msft]

Hi @szelenko ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @szelenko ,

Firstly, Power BI Premium supports uploads of Power BI Desktop (.pbix) files that are up to 10 GB in size. For more details, please kindly refer to: What is Power BI Premium?

Secondly, instead of import all data to Power BI, try using DirectQuery mode or a live connection.

Please kindly refer to: Use DirectQuery in Power BI Desktop

DirectQuery, Live Connection or Import Data? Tough Decision!

 If you still choose import mode, you can follow the guide in this article to optimize your data model.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StefanoGrimaldi]

I would also recommend you making a idea post: https://community.powerbi.com/t5/Custom-Visuals-Ideas/idb-p/CustomVisualsIdeas about Dremio not being able in direct query, see if Power BI team can work on a conector for it if good amount of people need this, this can help you optimize your model further and send statement to your SQL in it for the source to process part of the data before getting it to BI,

and yes migrating to a cloud wherehouse does have their pros and could be a good idea for your needs, 

if any of this post helped you out, please let us know marking them as solution, and best of lucks with your proyect. 

[StefanoGrimaldi]

hey, 

you in the rigth path actually, pulling as basic stuff, tranformation, filters etc on the data source to get a cleaner data its a good idea, 

- having a main model as base for reports make big model mor managable:

this way you have a central model and them live conection to reports, (also in december release there a new capabilities that increase this topic features ( https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s... ) this add a whole new level to composite models), 

- give all tables a adecuate and detailed description of what information its in it

- Give all columns a detail description of what means it have the data on the column (or at least the keys columns being used for modeling and visuals)

- Group Columns in folder in the data model relationship power bi desktop interface to have the information type in each table in order, this will help you during visuals creation, modeling and any collaborator working on Excel Conected to BI. 

[szelenko]

Gracias Stefano (de acuerdo a tu captura al parecer hablas español al igual que yo 😀 ).

Voy a probar el enfoque planteado y además voy a consultar con algún partner de Microsoft si puedo armar el data warehouse en la nube para obtener los mejores resultados.

[littlemojopuppy · Accepted solution]

I had a similar project...about 50 tables, 800 measures, 50M records.  I developed the data model in Azure Analysis Services and used that as the source for dozens of individual reports.  Simplified maintenance since measures were all centrally located, performance was adequate (used S0 AAS so it was kind of a weakling, but later upgraded to S1).  That may/may not be an option for you, but if possible it's worth looking at.