Refresh sources differently in one dataset

[muggu]

Hej Folks,

I’m looking for a solution for my current project.

My powerbi background:

I‘m using and learning it for 3 years now. I created mainly small reports to help few people to work more effectively. Datasets were sufficient so far. RLS is known and used.

This is my first report with the scope of about 100 users and a bigger need to think about centrally managed data sources (single point of truth) etc.

Environment:

• Powerbi premium
• Enterprise gateway
• SQL database
• SharePoint online

Goal:

A report published in the service with at least 2 sources.

1. Production data on internal SQL DB connected via enterprise gateway. Data (15 views, some million lines on full loaed) can only be to be refreshed on daily basis at night as it’s the productive database of an application. But once a day is enough for the reporting.
2. additional Individual data per user (about 60 users), which should be visible in the report right after or at least 15 minutes after saving. The data will be entered at daytime and we will have peak periods where many changes will happen.

Current state:

The current state is that I started with a report delivering the dataset including:

1. the production data (15 views) via import mode.
2. The additional individual data comes from multiple excel files (1 per user) with a fixed structure stored on SharePoint online document library. Power query combines all excel files. For this source I created a dataflow and integrated it into my dataset for the report. The idea is to trigger the refresh of the dataflow via power automate as soon as a file in SharePoint gets updated. (I also use this for another report and it works fine). Regarding the peak days a scheduled refresh every 15 minutes would also be suitable.

Issue:

The refreshed dataflow data for the individual data doesn‘t show up in the report. I think this is because I need to refresh the dataset of the main report right?

As the production data can’t be loaded at day times, I cannot update the dataset at daytime.

Solution / best approach?

1. Should I move my production data also to a separate dataflow, which get‘s updated a at night?
2. If so, I then would have both sources as dataflows in my report dataset and can refresh the dataset as often as I need without triggering the refresh of the production data, right?

Any other ideas / approaches also highly appreciated.

Greetings Daniel 

[v-henryk-mstf]

Hi @muggu ,

It sounds like you are trying to use dataflows to refresh the data for the individual data in your report, but you are running into issues because the main dataset containing the production data cannot be refreshed during the day.

One solution would be to move the production data to a separate dataflow and schedule the refresh of the dataflow to occur at night. This would allow you to refresh the main dataset containing the individual data as often as needed during the day, without affecting the production data.

To do this, you can create a dataflow that connects to the production data on the internal SQL database, extracts the data, and stores it in the Power BI service. Then, you can set up a schedule to refresh the dataflow at night. In the main report, you can then use the data from the dataflow as a separate data source and combine it with the data from the dataflow containing the individual data. This way, you can refresh the main dataset containing both sources as often as needed, without affecting the production data.

Alternatively, you could consider using DirectQuery mode for the production data, which allows you to connect directly to the production data in the SQL database without storing a copy in the Power BI service. This would allow you to refresh the main dataset containing both the production data and the individual data as often as needed, without having to worry about refreshing the production data. However, keep in mind that DirectQuery mode can have some limitations, such as a reduced ability to use certain features and a potentially lower performance when querying large datasets.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[muggu]

Thanks @v-henryk-mstf @for your answer. As I wrote in my second post, I already tried that. But I'm not happy with it as the powerbi service still loads the whole production data from the dataflow, which is not necessary as the content in the dataflow didn't change during daytime. In addition it takes a lot of time and ressources. Sure the productive SQL database is not affected, but that can't be the solution.

direct query ist not possible as I'm not allowed to load data from that source during business hours.

[muggu]

I tried it and moved the production data to a dataflow. Now it works fine when updating the dataset of the report.

nevertheless it's not ideal, as the data refresh  takes 6 minutes as I have some GB in the production data. How can I avoid that? Why does the powerbi service doesn't consider that there has been no change on the production data dataflow since the last refresh?