Advice on Reusing a Big Table in Many Datasets

[meehael]

Hi everyone,

what do you advise, how to reuse the same big table in many datasets, please?

More specifically, I have a table with 20 million rows (4 columns), which I'm using for our custom row-level security purposes.

I have about 50 different reports and most of them have their own dataset (only some of them are using shared datasets), and almost each report and dataset is using this RLS table.

Currently, each report is set to refresh once a day and this big table is reloaded almost 50 times per day.

I would like to reduce the load times, generated traffic and costs as much as possible.

My idea was to create a dataflow with that table and only refresh the dataflow once a day. All the datasets should use and point to the dataflow in regards to the RLS table, but unfortunately, if I understand correctly, this wouldn't simply be a "pointer" or a reference to the data in the dataflow, but I would still need to refresh the datasets and it would still require 50 refreshes and data loads in the end with the same amount of data, and I didn't reduce neither traffic or costs. Or am I wrong?

Is there a way to achieve something like this, please?

Thank you and best regards,

Mike

[GilbertQ · Accepted solution]

Hi there

Just so that I understand you want to incorporate this one table into all the other datasets?

If so I am not sure how else it can be done unless you import it all into a single dataset that can then be shared across all the different reports?

[meehael]

Hi Gilbert,

thanks for the reply and, yes, you understood correctly.

Do you know perhaps if there is much difference in performance and costs/traffic generated, for datasets to refresh daily from a Power BI dataflow (which is in an Azure Data Lake if I understand correctly) or directly from an Azure SQL Server, please?

Thank you and best regards,

Mike

[GilbertQ]

Hi there

As long as the Azure SQL Server database is in the same region there would be no costs or traffic generated. And the performance should be really fast if you have good database design and have a good query.

Dataflows do allow reuse of the same data by multiple datasets and if need be can do some of the ETL if needed on your data.