Natives Queries or ODBC with Incremental Refresh in Power BI
Вставка
- Опубліковано 23 чер 2021
- Are you wanting to use Incremental Refresh in Power BI but need to use ODBC, native queries or something that doesn't fold properly? Adam shows you a solution for Power BI Desktop and Power BI dataflows!
Native Queries
docs.microsoft.com/power-quer...
Using Incremental Refresh with dataflows
docs.microsoft.com/power-quer...
Configure Increment Refresh for Datasets (includes DateKey function)
docs.microsoft.com/power-bi/c...
📢 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 - Наука та технологія
Yes, it blew my mind. I' try it out in 2 weeks.
That's so cool!!!! That fold guy always in the middle. Nice workaround Adam thanks for sharing!
Thank a lot Adam!
I had applied the usual way to configure parameters for my View, and I was having a lot of problems. Whit your solutions, I have reduced 80% the time, and dont have problem in Services BI. I only have a issue whit the format of date, because I am in Latin America, but not more problems. Thank you so much.
I was doing this already for my customer where we have complex logics. Added the range start and end in sql syntax itself and no more additional transformations. This was on Teradata.
If you are concerned about SQL injection, all you need to do is create a stored proc with the SQL query. Then navigate to that in the same way you navigate to a table. Finally, just put your RangeStart and RangeEnd into the function and invoke it.
I've been thinking about how to best approach incremental refresh for sources that use ODBC for a few weeks now, so the timing of this video is great!
Same here!
Do the same work with snowflake ?
This is great! Aswell usefull for Web.Contents! Is there still a way to detect data changes based on a modified column? Maybe possible to pass this within a native query too?
Great video, does date/type field needs to come from the source or we can simply change date type to date/time in query editor and follow the same steps? the reason I'm asking is because if I push date/time to my sorce it adds more Granular row to my data sets just because of the time part.
Would appreciate any insights into the logic. I am using a record timestamp that is updated upon any change. Let's assume I am saving 20 years of records, updating the last year. If I have a record that was last updated in 2012, it will be stored in the 2012 partition. What happens if I update that record. It will get picked up in the refresh query, but will the logic also leave the old record in the 2012 partition?
Great!
Thanks
Hi Adam it amazing. question, after apply all these steps and run the refresh in my dataflow add a new line with Table.SelectRows(Source, each DateTime.From([FechaVisita]) >= RangeStart and DateTime.From([FechaVisita]) < RangeEnd)
Why?
My original query is
let
StartDate = DateKey(RangeStart),
EndDate = DateKey(RangeEnd),
query = "SELECT .... where FechaVisita >=" & Text.From(StartDate) & " and FechaVisita
Great 👍
Great👏
This is great, but does it work with Detect Data Changes turned on? Dataflows automatically insert some query called "Canary" that looks like it takes the max value from a column - do those need to be translated into the native query as well?
Awesome
If my native SQL includes variables, CTEs, or temp tables then it will load the table preview in Power Query Editor, but will have a syntax error when loading the data to the model.
I've tried this and it works great. Except for the data which has future data compared to the Ranges. Any idea on how the query should be modified for this scenario?
I haven’t tried this but I will. Presumably this technique thwarts the dreaded Formula Firewall issue when injecting derived values into native queries.
Is the integer key step necessary if you have a DateTime column?
Doing this in a Dataset Native Query that later is appended to another Native Query. I didn't need to deal with the datetime format, so just inserted RangeStart and RangeEnd in the 'query' section of the Advanced Editor but inside single quotes to make work. Syntax is okay, but the incremental refresh isn't happening in the service the results returning the hardcoded parameter range dates from the desktop. Wonder if it's because of the append query?
Need help! Have several reports that are running stored procedures.... Can this work with sproc??
Your channel is incredibly useful. One question: does this work also for "Premium per user" license?
Sooooo Coooool
Adam could you share the link to the documentation you referenced in the video
Adam, thanks for this video! I am using an ODBC driver to connect to Amazon Athena. Is there any reason you can think of that this approach wouldn't work for that also?
Andrew did you figure it out? I am using Athena and having the same issue as well 😭
Hi, I can't use power bi service connect to sql server DB through ODBC, I must did something wrong, can someone please help? thanks!
My first thoughts on this video was concerning the amount of time it took to load about 550,000 rows of data to a PowerBI dataset. He said it took about 2.5 hours. That seems like a very long time. Well my first thought was 'how come he didn't use a JDBC connector?' Is the JDBC connection available with this feature?
How do you get the hour if you need the hour; I see the year, month and day functions but not something like Date.Hour or DateTime.Hour or any function to get the time part.
Hi Adam, how to use incremental refresh using Amazon Athena using ODBC in the Power BI desktop report.
How do you use this data in a desktop dashboard would you suggest to direct query? I don't know what the best option as my dataset is gigantic
I have a fact table with start_year (2020) and end_year(2023) parameters in it to limit the number of years worth of data. On top of this I have incremental refresh of last 13 months. I publish this file to web service and change the start_year parameter say from 2020 to 2019. Why does the parameter changes in web service doesn't take into effect? If I change from 2020 to 2021, it does make necessary changes but for the first time only after publishing the file. Any thought on this? My report should only hold only last 5 years of data with last 13 months of incremental refresh.
When injecting the RangeStart and RangeEnd in the SQL query, you used the M function Text.From(), won't that convert the number to string and if it converts it to string, how does the filtering work as it is different for strings and numbers?
Thanks!
Using the Text.From() function does convert it to text, for purposes of inserting it into the existing text string of the native query (but without adding single quotes around it). When the text string for the whole native query is ran, it reads it as an integer. If our RangeStart = 01/01/2021 and RangeEnd = 12/31/2021 then it reads the native query like below:
select * from pbi_InternetSales where OrderDateKey > 20210101 AND OrderDateKey
Not Working with Athena can you please help?
Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 1:74: identifiers must not start with a digit; surround the identifier with double quotes
Is there a video of this done on PowerBI Desktop on a dataset?
Hi Adam great video, but did you try to edit the dataflow after you set it up. For me it bring backs the additional step which was removed - DateTime.From([DATETIME]) >= RangeStart and DateTime.From([DATETIME]) < RangeEnd
I got the same problem but, if you convert your date key field into a dattime and select that as the column to filter by, it will work. Because the from/to values are folded into the sql and then used to flter the result. Pretty much a filthy hack but, it works.
I have the same issue. Any help would be appreciated. I am not using the Integer method. Copy and changed a Date column to DateTime which allowed enabling the Incremental Refresh. Then I convert the RangeStart and RangeEnd into test to pass to the Select Where statement. Every time I eliminate the auto generated filter from Power BI, it puts it back in there, so it is filter twice.
@@keithwalz1396 yes, it’s filtered twice. Once folded into the SQL a once in the M code. The later costs nothing. It just looks ugly. But it works.
@@coolblue5929 thanks for the replay
Hello, this this works with a PRO license or only Premium?
I'm going in. If this works, I'll be happy.... ODBC on AS400/iSeries/i DB
Looks like working, but hard to know 100% on a Pro account. If Premium, I could look at the partitions.
@@Milhouse77BS Hey I know this was a year ago, but how did you get this to work with an ODBC connection? I'm unable to create any filters without breaking the Native Query option in Power Query
@@jordannolte8168 if I remember right, the Native Query was greyed out, but incremental refresh worked. You’d want to verify using an sql monitoring tool.
Can we schedule full refresh, let's say every month, when we have already scheduled daily incremental refresh?
No if incremental was set up, first time it will be full refresh n subsequent once will only be incremental and it can’t be rolled back
@@dipsikhadas1895 yes that's the default behavior... Do we have any trick or work around to set up full refresh once in a while where incremental refresh is configured... Let's say quarterly
🤯
will this work with an azure databricks source?
Hi @eljangoolak, did you manage to implement incremental refreshing with a databricks source? Curious to hear your solution!
@@EquinoxOxygen yes and no, ended up doing a hybrid solution. The issue I had was getting the query from a paginated page to my native query inside my datasource. Never figured that out so i changed the whole solution to something else. But it does work with a databricks source, yes!
Hi guys. Would anyone know why countblank wont refresh in a metric table after data has been refreshed?
Only for Premium right?
seems, yes
Pro got Incremental Refresh a year or so ago, so should work.
It is not working in reality:
1. When I change automatically generated step for filtering by Date Integer variables calculated by DateKey function, then saving of PowerQuery fails on error:
Something went wrong
Could not perform this operation due to an internal error
Please try again later or contact support. If you contact support, please provide these detail
Status code 500
Service version13.0.16239.61
Client version2106.1.06599-train
2. If I add additional step for filtering by variables calculated by DateKey, then Data Flow always add additional step with auto-generated incremental refresh
For reference, here is Query for 1st case. When I save this query:
let
DateFromInt = DateKey(RangeStart),
DateTillInt = DateKey(RangeEnd),
Source = OData.Feed(#"Reporting API URL", null, [Implementation = "2.0"]),
Navigation = Source{[Name = "FMonitoringAnswer", Signature = "table"]}[Data],
#"Filter: Active records" = Table.SelectRows(Navigation, each [Sys_IsDeleted] = false),
#"Filter: Incremenatal Refresh" = Table.SelectRows(#"Filter: Active records", each [Date_Int] >= DateFromInt and [Date_Int] < DateTillInt),
#"FMonitoringAnswer-416E737765725F44617465-autogenerated_for_incremental_refresh" = #"Filter: Incremenatal Refresh"
in
#"FMonitoringAnswer-416E737765725F44617465-autogenerated_for_incremental_refresh"
And open same query again, then last step is changed to this:
#"FMonitoringAnswer-416E737765725F44617465-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Filter: Incremenatal Refresh", each DateTime.From([Answer_Date]) >= RangeStart and DateTime.From([Answer_Date]) < RangeEnd)
so that there are 2 times filter by date once by Date Integer filed and second time auto-generated step for incremental refresh.
And most stupid thing is that DataFlow force you to choose Date/Time column for incremental refresh, but when you capture SQL what is generated by DataFlow, then you see that internal parameters are integers and DateTime column is converted to integer (even not using time part, so I really not understand why it should be Date/Time column):
EXEC sp_executesql N'SELECT TOP(@__TypedProperty_5) [f].[MTA_MTDR_PRD_ID], [f].[APP_ID], ...
FROM [HamSFA_DWH].[F_MonitoringAnswer] AS [f]
WHERE (((([f].[Sys_IsDeleted] = @__TypedProperty_0)
AND (CAST([f].[Date_Int] AS float) >= @__TypedProperty_1))
AND (CAST([f].[Date_Int] AS float) < @__TypedProperty_2))
AND ((((DATEPART(year, [f].[Answer_Date]) * 10000) + (DATEPART(month, [f].[Answer_Date]) * 100)) + DATEPART(day, [f].[Answer_Date])) >= @__p_3))
AND ((((DATEPART(year, [f].[Answer_Date]) * 10000) + (DATEPART(month, [f].[Answer_Date]) * 100)) + DATEPART(day, [f].[Answer_Date])) < @__p_4)
ORDER BY [f].[MTA_MTDR_PRD_ID]'
,N'@__TypedProperty_5 int,@__TypedProperty_0 bit,@__TypedProperty_1 float,@__TypedProperty_2 float,@__p_3 int,@__p_4 int'
,@__TypedProperty_5 = 50001
,@__TypedProperty_0 = 0
,@__TypedProperty_1 = 20210401
,@__TypedProperty_2 = 20210501
,@__p_3 = 20210401
,@__p_4 = 20210501
How cool would be to have possibility to use Date Integer field directly in DataFlow
so the question below still stands but part of it is resolved.
- Hey Dzino. Great observation man and thanks for sharing. I encountered the same issue of the auto-generated last step which essentially put two filters and will never get all the data thats required. Also the suggestion to have incremental refresh with other types is great but nothing seems to have moved so far. Did you find a workaround of this issue ? Any method worked for you? Happy to hear back. thanks in advance.
- so it seems that the incremental refresh is still configured and thats on the original column (datetime type) and that brings back the additional filter line everytime. I disabled the incremental refresh which obviously caused two issues.
1. it removed the range start and range end parameters - i recreated them manually.
2. there is some gui issue so incremental refresh will not disable until you refresh the page and come back at it again.
so this resulted in a couple of things
1. the refresh is now great. at least it brings all the query results. I had about 2mil rows in sql and it was decent speed (4 minutes or so)
2. it is unable to partition now the refresh because the incremental refresh is actually disabled now. i think its my understanding (or lack of it) of overall objective but my original requirement is to move large data fast (mine is going to grow with about 2 mil per month with a 12-24 month history requirement) from sql to dataflow to powerbi (which is not happening).
But i will learn further and see if i am on the right path and if this can be improved further. hope this helps others.
🍌🍌🍌🍌🍌