Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I am new to Power BI. I was used to writing queries. So for my BI report I selected the Warehouse approach. In my workspace I am connecting to the source table via dataflow. The dataflow publishes to the Warehouse where I have written SQL queries to transform the data and build the dataset that I want for the report. The report is built via desktop. The dataflow has been scheduled to refresh daily. But my queries are not refreshing automatically. The sematic Model (default) has a setting 'Keep your Direct Lake data up to date' which is on. But queries are not refreshing due to which the report continues to show stale data. Once I manually run the query in Warehouse and refresh the dataset in the report, then data gets refreshed. How to get the queries run on a daily schedule when the dataflow refreshes?
Solved! Go to Solution.
You need to either turn that query into a stored procedure and then call that stored procedure in a pipeline, or you could copy the query and run it in a copy job writing to your new output table.
If you're more comfortable with Dataflow Gen2 you could connect to the SQL endpoint in a new Dataflow and run your query in there.
Either way, the end result is using your query to write to a new table. This new table, is the one that will be in the semantic model to use for reporting.
My recommendation is to use pipelines. Once you've got your head around them they're pretty straight forward to use.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
You need to either turn that query into a stored procedure and then call that stored procedure in a pipeline, or you could copy the query and run it in a copy job writing to your new output table.
If you're more comfortable with Dataflow Gen2 you could connect to the SQL endpoint in a new Dataflow and run your query in there.
Either way, the end result is using your query to write to a new table. This new table, is the one that will be in the semantic model to use for reporting.
My recommendation is to use pipelines. Once you've got your head around them they're pretty straight forward to use.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Thanks again! I was able to create a stored procedure that is now being called in the pipeline.
Thanks KNP for your reply. I wrote the queries in Warehouse -> Queries -> My Queries. I am not sure what needs to be done to run the query everyday at a specific time so that it will pick latest source data to regenerate the dataset for the report.
When you say, "I have written SQL queries to transform the data and build the dataset...", where did you write them? Are the resulting tables visible in the model? I'm not really understanding the full picture here but it sounds like you may need a pipeline to orchestrate the process.
Maybe provide more info in the form of...
DFGen2 >> Warehouse (table) >> SQL Query stored proc (table)... etc.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |