Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
AG_PBI
Frequent Visitor

How to auto refresh the TSQL query in the warehouse

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?

1 ACCEPTED SOLUTION
KNP
Super User
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

4 REPLIES 4
KNP
Super User
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
AG_PBI
Frequent Visitor

Thanks again! I was able to create a stored procedure that is now being called in the pipeline.

AG_PBI
Frequent Visitor

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. 

KNP
Super User
Super User

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.