Large dataset with many derivations

[DaGuggi]

Hi Community,

I have an issue with PowerBI (PowerQuery).
I have several SAP tables which I can import with ‚Denodo‘ (some kind of middleware) via Dataflows. One Table is for example ‚EKPO‘ and it has around 300.000 rows with around 500 columns in our configuration.

My task is to create a data quality report and therefore I have to create several checks. Each check requires certain steps (filters, joins with other tables, group by‘s and so on).

I did create a main EKPO table which I prepared properly and I did try the reference approach.

But now data update is kind of impossible and PowerBI crashes. After some research, I’ve leaned that each reference causes the root table to update itself. Thus, the reference approach is kind of not available. 

DAX and/or calculated columns might be another approach, but I fear that PowerBI might collapse (DAX tables create a duplicate of the data and the PBIX might become to big).

It is no option to modify the source as there are no capacities available.

Now my question: what is your suggestion to handle my issue/ What is the best approach?

Thank you in advance for any hint!

[Anonymous · Accepted solution]

Hi @DaGuggi 

Here are a few suggestions to help you manage your data quality report more efficiently:

1. DirectQuery Mode

Consider using DirectQuery mode instead of importing the data. DirectQuery allows you to work with large datasets without loading all the data into Power BI, which can significantly improve performance . This way, queries are sent directly to the data source, and only the results are returned to Power BI.
DirectQuery for SAP HANA in Power BI - Power BI | Microsoft Learn

2. Incremental Refresh

Implementing incremental refresh can help manage large datasets by only refreshing the data that has changed . This reduces the load on Power BI and can prevent crashes. You can set up incremental refresh policies in Power BI to handle large tables more efficiently.
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,

Jayleny

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

[anupamj]

Hello @Anonymous  , you will find these useful:

1. PowerBI/ Fabric refresh from SAP: https://youtu.be/NglcXn9EAMU
2. Guide for high volume data replication from SAP : https://www.youtube.com/playlist?list=PLTum8dvrbVA2nqRs4pUN29XzJ8Qd-OUym
3. Tables delta capture from SAP : https://www.youtube.com/playlist?list=PLTum8dvrbVA1lguJcC3aX1w4NswhHDn_0

Your scenario seems to be suitable for scheduled refresh from SAP.

Microsoft recommended architecture for PowerBI / Fabric integration with SAP (See the cached method): 

https://techcommunity.microsoft.com/t5/azure-data-factory-blog/integrate-microsoft-fabric-with-sap-d...

Questions? please reach out.

Anupam