Combining refresh and real time with Hybrid Tables in Power BI

[@micahdail]

Even better idea: A. Really cold data for more than 5 years ago in DQ mode. B. 5 years archive imported. C. 5 days of incremental refresh imported. D. 1 day of real time in direct query. This is the first thing I'm going to do with hybrid tables. THIS IS BANANAS!

[@GuyInACube]

Oh yeah. Stay tuned for more videos.

[@kamilsomka3193]

Hi Micah! Really great idea, but as I tested it is possible to have only one DQ partition in a table at this moment. So, you would have to combine cold and real time data in one DQ partition.

[@srinivaspappu]

Hi Micah, How do we handle if 'C' Data is updated today and we receive in 'D', which leads to duplicate rows one with old data and one with latest data.

[@andyparkerson]

This is really impressive. We are definitely going to check this out.

[@CepaCode]

This is indeed BANANAS! For those of us who have struggled with big data sets in import mode this really changes the game!

[@wadecbyt]

WHAAAATTTT!!! This is the definition of INSANEAMAZING. Nuff said.

[@Nazeerul_Hazard]

Great piece of engineering by powerbi team and great presentation from Patrick! 👍

[@guustvandijck9390]

Yoooo Patrick! Thanks as usual for an amazing video! There´s been some hints in previous videos/ documentation that it´s possible to do a sort of "reverse" hybrid table. So the latest data gets refreshed on import and older data is accessible through direct query. Any chance on a video on that? Or is there already documentation out there that I can read into? Either way, happy new year and keep on rocking!

[@snehil_gupta]

Hey Guust! Have you found any workaround or a probable solution for this. I would really appreciate your help if you could point me in the right direction. Cheers!

[@guustvandijck9390]

@@snehil_gupta no, not really to be fair... I've tried to use a combination of direct query (current day) and imported the last 30 days of data. This works ok. The thing is, that the lowest granularity to set this up seems to be "day level". For the current day, I would like to be able to split that up further (hour level). I haven't found a way to do so...

[@snehil_gupta]

@@guustvandijck9390 thanks a lot for your response. I'll also try and find a solution. Will keep you posted if I stumble across something.

[@tosinopeniyi1407]

This is INSANE AMAZING 🥰

[@diphetu]

Hi Patrick... as usual i love your this Hybrid Table video and it is amazing the way you explain very quickly......

[@saurabhmundada7422]

Amazing Patrick 👍

[@rahulmaurya9444]

Superb 😍

[@udayburidi633]

Hey Patrick, Can we avoid direct query when we are using Imported data in the report ?

[@rezatavakol2531]

That was super! Thanks a lot Patrick! I was in need of such a this feature!

[@spilledgraphics]

🔥 Power BI kicking ass 🔥

[@nazimscr]

Brilliant👍👍👍

[@atisheyamaharana9288]

Insane Amazing 👌👌👌

[@richardnguyenduong]

This is amazing! Thanks Patrick!

[@kovaisampath63]

Awesome Patrick, thanks for this video.

[@aaron6516]

Dude love it!!! But PBI license navigation... need an app for that...lol... gonna share with the teams and admin... thanks!!

[@dusbarbosa]

Wow Patrick, A-M-A-Z-I-N-G!!

[@evnpresson7258]

Awesome sauce! I can't seem to get it to work on Azure Postgresql DB for some reason. DQ works fine but the box to enable real time refresh is grayed out. Am I missing something?

[@swinderjo]

I am having the same problem but with postgresdb on premise. hybrid tables seems to have a bug

[@krystiansakowski443]

we had a bug impacting non-microsoft sources. could you reinstall power bi desktop w/ the fix and try again?

[@Jack2of3]

When a year has passed does it maintain the first year partition or does it drop it and now you have partitions 2018-2022?

[@jerypaz9812]

Patrick es fantástico muy buen video! el Direct query para la actualización más reciente es espectacular!

[@jaromgleed869]

Hey Patrick. Love the videos and I just tried my first hybrid table on a slow dataset that was entirely direct query. I get an error though saying I can not create a hybrid table if the table has calculated columns. So are calculated column not allowed on hybrid tables or do I just need to make a change? I really prefer to use calculated column in my case rather than measures. Any insight would be a great help!

[@srinivaspappu]

Hi Patrick, This works for a fact table with data appending.. However, if my Fact table's data is updated today (Not APPEND rows, but updated values of few rows in the realtime and refresh range) , then it leads to duplicate rows. To overcome that, I tried to remove the duplicates in the transformation by using Table.Buffer() and Duplicate clearing.. But the moment I add any such duplicate deletion, It is turning off the Incremental refresh settings. So it is a blocker for me..with this either I have to live with duplicate rows or forget about Hybrid approach. Please suggest how shall I handle this ?

[@manishdharmadhikari5506]

Same Problem. Any Solution to handle this?

[@udayburidi633]

any solution u got for this issue ? Please update here

about the performance: does it work for bigdata?

[@DevereauBR]

Niceeeeeeee !! Greatings from a Guy In the cub from BRAZIL !

[@saifali4107]

Great video! I Always benefit from your tutorials and Tips. Hey Patrick I am looking for a solution to share Power Reports with people outside the Organisation. I am hoping you can make a video on that.

[@novak2033]

Amazing!

[@thomothomo9802]

Hi Patrick This is impressive. My question is can I use direct query when I use native query to load the table

[@roseventura1711]

Ok, this is great stuff, but I have to officially slow down the playback speed for #GuyInACube because you both talk waaaayyyy too fast!

[@ИльяСмирнов-ж8я]

what will we see on January 7th? 7th - direct query, 2-6 import incremental, what about archive?

[@czowiekktoryzaduzowidzia5612]

Is it possible to do it the opposite way. Last two years in import and data older than two years in DQ? This would be great for data sets that are too large for the model

[@krystiansakowski443]

yes you can do that, but you need to manage paritions via XMLA EP, incremental refresh UI doesn't support that configuration. remember to upgrade compatibility level of your model to 1565 before you attempt to create hybrid tables.

[@__HumanBeing]

Does it work only in the Power Bi Desktop? I mean the Page Refresh. I want to "publish it to the web". Would it work there too?

[@jeanguerrapty]

Awesome!

[@qaz56q]

How does the measuring instruments work on the year-to-year basis at the interface between import and directquery? also Running-Count What limitations will we meet in this regard?

[@marounsader318]

hi patrick, just a question, when the incremental refresh data will be part of the archived and not re-imported again with every refresh? is it at the end of the year ?

[@AHMEDALDAFAAE1]

Hey Pat! Thank you so much for those amazing videos!! BTW, you don't need to start with DQ. I saw other building their Hybrid table right away from the incremental refresh on an imported data. Please correct me or update your video! Regards!!

[@leoafurlongiv]

Can one use the XMLA endpoint to "customize" their settings, or will that mess up the Power BI Service?

[@krystiansakowski443]

yes you can set up incremental refresh via XMLA endpoint but it is advanced workflow, you can also manage partitions yourself via xmla ep, that is you can add dq partition to your import table or import partition to your dq table after upgrading compatibility level to 1565.

[@pedropradocarvalho]

so cool! please, more on this

[@dhruvinshah4817]

does it remove 5 days from january 2017 as we progress into further days /months? does that 5 year archive keep the newest year post the refresh date - 5 day..? how does that work?

[@rachelbrockschmidt2632]

This is amazing! I am wondering if this is supported in combination with the Direct Query to Power BI datasets preview feature. We use SAP HANA in relational mode as a data source frequently. We expose Direct Query HANA data through a Power BI dataset for our analysts. But our users like to combine the HANA data with their own data sources. We were told that while this may work sometimes, SAP HANA DQ is not supported for this feature. So we plan to make import mode versions of these datasets. If I were to set this up against an SAP HANA data source, would users be able to combine the dataset with their own data?

[@sreeharsha1172]

Great video Patrick !! We are trying to implement this feature using Google Cloud data source . The real time isn’t enabled at all when using GCP and we see no problems with MAFT SQL server. Can you throw some light if this feature is enabled for few data source types?

[@krystiansakowski443]

We had a bug impacting non-microsoft datasources, we are going to ship a fix to powerbi desktop within 1 week or so.

[@krystiansakowski443]

we released fix to the public. could you reinstall powerbi desktop and try again?

[@juanmanueldaher2428]

Hey! Can I define in Power BI an hybrid table whose data comes from an api?

[@tashagrihma6052]

What if my date table is not from SQL, but created in Power Query? How would I change it to DQ?

[@FrequentC88]

What if I’m querying a database view and not importing fact and dimension tables. Can it still be done?

[@Irimae]

THIS IS INSANE!!!

[@VishnuPriya-ng6wk]

Cant I have direct query for all the 5 days instead of just the latest date?

[@mauricemeijers7956]

Wow👍😀

[@josbar80]

Even though I have Premium, the option Get the latest data in real time with Direct Query(Premium only) is greyed out. Any idea's?

[@srinivaspappu]

It greys out if you apply any transformations like duplicate clearing , sort or buffer etc.. It seems will be available only on the plain Table.

[@snehil_gupta]

Hey Patrick! Thank you for all your amazing videos. Could you also please make a video about creating hybrid tables using dataflows. We currently import 6 years data using dataflows which refresh on a daily basis, however, we would want the performance to be better and retain only 2 years worth of data as import and the rest as direct query. Appreciate your help. Cheers!

[@wexwexexort]

Holy Sh.t. I'm speechless.

[@kim1217]

Absolutely 🍌🍌🍌!

[@aamoody81]

Nice

[@anderpaim10]

Really unfortunate it doesn't exist for PRO 🤨🤨🤨

[@staziks]

WHATTTTTT!! :)

[@1yyymmmddd]

Can't you just make an import table1 for 5 years, set it to No Refresh and No Load, then create table2 for everything that is after year 5 and just append table1 to table2?

[@sanjuktasamom3923]

Too fast difficult to follow

[@kurtnelle]

Therefore PowerBI is now trying to be an ETL? Sigh.

[@FedericoLov]

Disappointing. Limiting DAX to DQ only functions essentially renders it useless in any real world scenario. A better implementation would be true partitions management like we had in SSAS with the ability to refresh only the most recent one.

[@krystiansakowski443]

"A better implementation would be true partitions management like we had in SSAS with the ability to refresh only the most recent one." but we already have that feature, either use incremental refresh (import only) or manage your partitioning with your own custom code runing against XMLA EP. This feature is targeting existing DirectQuery datasets, using DirectQuery for data freshness reasons.. now we can offer nearly import performance and DirectQuery like data freshness. This feature also targets people using DirectQuery due to data volume concerns, now they can import recent chunk of the data into the dataset and reference archival data living in the datasource via DirectQuery partition.. I hope that helps to understand scenarios we target with that feature.

[@pawewrona9749]

Amazing!