Make model more efficient

[Aurelio]

Good day everyone

Currently all data is integrated via import into a single report. The data comes from Business Central or a Sharepoint. However, we thought it would be better to split the data and then bring it back together when creating reports, e.g. Sales or General ledger report.

One of the options was to export all the parts of the larger dataset to Power Bi Service and from there extract them in our reports. These will then be retrieved and converted from Import to DirectQuery. So my question is what is the best practice to make the dataset more efficient in terms of model, not table.

1) Put everything separately in Power Bi Service to then convert to a DirectQuery?
2) Convert portions to DirectQuery and leave portions as Imports? If, yes. Best fact. tables in DirectQuery or just not?

I have attached an image of the current data model.

Thanks in advance

[ToddChitt]

Well, try a granular test of ONE table. Create TWO models: one a DIRECT QUERY and one an IMPORT. Creat the same visuals and slicers for testing. I think you will see that the IMPORT is going to out-perform DIRECT QUERY, especially if it is SharePoint. FYI, data sources such as SharePoint are NOT RDBMS, so when they go to filter data, they are not as efficient as SQL Server.

Direct Query is good if you have data that changes constantly and you MUST have up-to-the-minute results.

(I have seen 5000 record datasets take 5 minutes from SharePoint) 

But do your own tests, please.

[Aurelio]

Good morning

I'm not really concerned about the Sharepoint but if it is better to compose a dashboard with mulitple Directqueries that splitted up the original dataset or use 1 big import.