Calling a Stored Procedure using Incremental Refresh in Power BI? We think so!
HTML-код
- Опубликовано: 31 янв 2023
- Have you wanted to use a Stored Procedure with Incremental Refresh? Patrick has a way that you can do just that!
Sandeep's blog:
pawarbi.github.io/blog/power%...
Stored Procedures: learn.microsoft.com/sql/relat...
Sample:
github.com/guyinacube/demo-fi...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #IncrementalRefresh #GuyInACube 
Наука
Sandeep Pawar is a great community contributor! Love this video!
Thanks Patrick and Alex. I made it big, finally 😀
Great video, short, clever and useful, thanks Patrick! 👍🏻🙂
This video is amazing, I was stuck where I had a monthly agggregated file in Synapse where daily dates were not present, only first and last day of the month was present, using normal import with incremental refresh was not working, but these method works like a charm. Thanks buddy.
Can't wait to try it
Awesome concept!
Appreciate that! Thank you!
Great explanation as always. I'm actually trying to test if incremental refresh car work on a view (defined as SELECT clause on the Facts table with some joins in there as well). So far no improvement in the dataset refresh time, so I guess not... Although I couldn't find any definitive answer on that point online.
You seem to drop in the video that it should be possible? Is that so?
oh that is some of the dopest coding presentation i've ever seen. don't know what tools that is but super nice use of Highlighting and zooming which I've only seen used at in person presentations, not online. So so much easier to follow when you can see what the presenter is drawing attention to. Only thing I could add is use a bigger red mouse pointer for visability and your video of yourself seems out of focus (maybe that was intentional to stop the bots...). thanks!!
Hi, as always great job there! I am little bit concerned about partitions and reload for last year data. I am aware how new set of records is being created in this approach and limited in this example to 1 year of data. But what logic is being used underneath to delete old records that are assigned to last 1 year in case they changed in mean time.
a brilliant solution
Thank you for this tutorial, is that possible to do this on Sept 2022 PBI version
Can you show how you manage your datasets in the database for example your tables or views. Things such as names for tables, sql code, say you have your customers table how would you bring the table in for funded sales or pipeline sales would you filter the customers in power query or create two seperste customer tables/views
Can we do this with dataflows too ?
Do you know if this functionality are going to added for direct query models? I need it!!!!
Hi Patrick, this is awesome.
I have a unique requirement on top of this. We have multiple client databases, all the same structure and without a DW in place. We need to connect to these one-by-one using dynamic connection strings etc.
Since the example you explained speaks to "Import Mode", do you have any advise how we can use direct query or ... point me in the direction of doing all this without duplicating e few 100 reports to share with customers, since they will all have their own datasets.
Oh yes, on top of that, the are being embedded in an application 😝
Any help will be appreciated! 🤗
I'm new to Power BI. Does this work with Embedded reports and does it require a Pro license?
Holy shit. That's genius
Hi Patrick & GIAC Team, Greatly appreciate for this video, particularly the sprocs for pbi incremental refresh. Is it possible to pass other parameters other than the datetime through native query if sprocs have them? Unfortunately, I am seeing an error message while loading the data into pbi desktop. Microsoft SQL: Incorrect syntax near the keyword 'Exec'. Must declare the scalar variable "@StartDate".Thanks.
Yes, I have the same error.
@@ifernandoubbi I have the same error too. It works in Power BI Desktop, but not when published... ?
You have to update the Power BI desktop to the latest version.
I am currently facing one more issue with how to call Oracle stored proc with parameters in Direct Query mode? Appreciate your inputs or any workaround
Thanks
Hi Patrick, ned your help on an odbc database created by a SCADA software called Clear scada. We are struggling with trying to implement incremental refresh with this database
does this help get around the limitation of being unable to download PBIX from web service because of an incremental refresh set up?
post video for slicer with store procedure
For some reason my incremental refresh says that I have to set up parameters, even though I set them up the same as in the example. Do I have to select DirectQuery mode when initially setting up the connection?
Found the answer...the RangeStart and RangeEnd have to be datetime, not just date
Hi Patrick great video. Unfortunately when l set the Incremental refresh i get a warning message saying "Unable to confirm M query can be folded" It all seems to be working. Refresh time doesnt seem to have reduced to much though.
Did you verify you added the EnableFolding Parameter as shown in the video?
@@GuyInACube sure did
Great video. This is exactly what we are looking for. Unfortunately, I keep getting error: Microsoft SQL: Incorrect syntax near the keyword 'exec'. Must declare the scalar variable "@StartDate". It loads in Power Query editor and data loads, but once closing and applying it pops up! Please help! :D
SOLVED: Make sure that you are getting data in Import Mode (not Direct Query!)
I didn't get this Store procedure thing, can anyone helpme understand it....
It's a T-SQL construct. There is a link in the description for the documentation on it if that helps.
Can Snowflake Procedure be called with Native Query feature in Power BI? I tried but it seems doesn't support, any work around Please suggest.
Hi,
I have created the stored procedure working fine, then created all the steps in Power BI and when I close Power Query there is an error message "Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Must declare the scalar variable "@StartDate"".
Do you know what could this be?
Sorry, my mistake I was using a PBI version from a couple of months ago.
After downloading the last version, this works great! thanks
@@ifernandoubbi which version did you install ? On my end the issue keeps appearing
@@michalwroblewski3089 December 2022.
Confirm, You need to use a newer version of Power Bi desktop, I had the same issue and solved it downloading the last version
Use table value functions.
I cant replicate this in dataflow in the service, keeps adding an extar step for incremental
Q: How to call a stored procedure from Amazon redshift into power bi ?
Why didn't you have to define RangeStart and RangeEnd?
I do not believe query folding is actually working. Yes, it shows View Native Query and you can click on it, however, if you do any additional steps, you lose the option to select View Native Query, which leaves me to believe that enablingfolding=true is misleading and is just visually happening vs. actually happening.
Patrick showed the sproc being called once in trace
Please, do not shout at beginning of videos , otherwise you are awesome :D
You do not feel professional or serious person during your video