Power BI Get Data: Import vs. DirectQuery vs. Live (2021)

Import vs. DirectQuery vs. Live connection. Which do you choose and why in Power BI? Adam looks at the three options when creating reports in Power BI Desktop including composite models.
Power BI Data Sources:
docs.microsoft.com/power-bi/c...
Star Schema:
docs.microsoft.com/power-bi/g...
DirectQuery model guidance:
docs.microsoft.com/power-bi/g...
Composite Model guidance:
docs.microsoft.com/power-bi/g...
DirectQuery for Power BI Datasets video:
• Blend Power BI dataset...
📢 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 #GetData #GuyInACube

[@late_arvie]

It's good that you are still doing these beginner videos!

[@Josh-iw3md]

Just want to say this is an amazing channel and has really helped me with my new role at work. Thank you so much.

[@GuyInACube]

We love hearing that Josh! 👊

[@DPFierce]

Although you have similar video, it’s good to revisit some topics as updates are made. This is very timely for me as I am having to reconfigure a report due to the 1GB threshold w/o Premium capacity. Thanks for the updated info!! BTW...one of tables I’m working with has 33Million rows and Power BI loads it without an issue!

[@challanagendra4642]

Great video I have seen this morning. Really worthy to watch this

[@knuckleheadmcspazatron4939]

Very useful! Thanks for taking the time to cover this. And our database is perfect ;-)

[@jwh001]

Great clear introduction! Thanks!

[@thomasivarsson2468]

Four types then. Import, direct query, live connection and hybrid? Good video!

[@thomasnicholas9314]

Thank you for explaining the difference between Import and Direct Query. Now I know what I need to do.

[@carolinnerabbi965]

Much clearer now, thanks!

[@johnheck1551]

Another great video. I have a question, you mentioned about a date issue with Direct Query in SQL. Can you explain it further? If you want to use Dates, Months, Quarters, Week Ending, etc. would you suggest using Import over Direct Query? Do you have any videos that show working with Dates?

[@bero5541]

Thank you for the high quality videos

[@TumiSelepe]

great video Adam, very informative

[@frikharamy1649]

My man Adam is back ! keep it up :D :D

[@test-jr8bx]

With DirectQuery we have a nice separation between the data and data model, which is handy to be able to upload/publish changes to the model only. Is the same possible with imported data (splitting data & the data model into 2 separate files)?

[@NOZOEMI]

Hi Adam! If I’m pulling noSQL source into power BI, do you mean I’d I create a star schema within power BI, breaking one giant table into several tables to for star schema, the performance will be better than pulling that one table directly? If I have several messy tables from noSQL database, would you rather create a unit giant table with only needed columns, or just every tables and join them in Power BI data model? Or have to consolidate them into one and then break into a star schema? Thank you so much!

[@geoidconsulting1834]

This was very nice. Thank you! 🙂

[@bandishgupta2046]

Hi Adam, what should be the approach to convert source model to star schema in Direct query mode. Sources are different ERP and CRM systems.

[@vanishedxu]

Thanks for the video! So what would be the best connection mode for near real-time reports? I got different messages from the docs. Sometimes it says DirectQuery can do a few seconds, and sometimes it says every 15 minutes.

[@alvarorodriguezlasso]

Thanks, Regards from Cali-Colombia

[@sarar941]

So useful, thank you!

[@tomaskavicky]

not a beginner anymore, but really worth to see this ;)

[@garymcbride340]

Great Video Adam, I’m trying to use the composure method with a live connection with analytical services to a n OLAP cube but it won’t let me add an additional external file by direst query or any other means. I assume OLAP Cube are just not accomodating in this way are they? Is there a way? Thanks

[@rapatel1642]

Great video as always.

[@dsilva001]

Thank you so much for this video! 🇧🇷🇧🇷🇧🇷

[@omarcastillo122]

Hi guys, first of all you are great! you explain everythong very clear. I have a question: do Power BI data flows are restricted to import, and are we able to do an incremental refresh with data flows? thx!

[@rushikamangalarapu471]

Is it possible to do cross report drill-through between two Reports located in two different workspaces? Is there any way to do it?

[@Bharath_PBI]

When you say layering, is it agg layer or model layer (with 3 layers supported currently) ?

[@regulardev]

What is the advantage of Live Connection over Direct Query? Both are pulling data from remote. More importantly, do we need scheduled refresh on live connection ?

[@npaler01]

Great video, as always. I have a question on the 1GB limit. Is that AFTER the transformation steps are done? Is it basically the .pbix file size, or the size of the dataset in the workspace? Thanks!

[@christaulbee6402]

It's the size of the .pbix

[@felipebarrera3060]

Can you explain also the difference regarding data flows? Please.

[@mohitchhabra41]

Hi there, Is it possible to change back to live connection from Direct Query (composite model) storage Mode ? Thank you.

[@shaggz20022002]

Great videos. Do you offer a .bak or script to create your Star Wars demo db?

[@KayakKiwi_Peter]

A very good video, thank you

[@alishehzadpaul]

Can we connect two different data sources using Direct Query? For example, I want to connect a table from a Teradata server, and another table from an Oracle database (assuming both tables have a common key)

[@pratibhamishra869]

Thank you for existing

[@priyankasaxena802]

For timeseries , is there any recommendation or quick start up as it is big data

[@harshindublin]

Can you please show a demo about schedule refresh for the follow types

[@powerbi-amitsrivstava4451]

Is there any way to display Power BI Dataset name in the Power BI Report? Thanks Amit

[@gregdvorkin]

Can I ask questions? I want to better understand how import works. I use Power BI Desktop and data imported from Excel. Let's say I need to send saved Power BI File to another user who also has Power BI desktop. Is it good enough just to send Power BI File or I also need to send Excel files as well?

[@lunedfrancis2768]

If I were to delete some columns when transforming data in the power query editor. Column that include sensitive data, would these be published the the service when it came time to publishing? Or would it just be the comlums that are loaded

[@ponnadavenkataraviprakashr3033]

What is your view on creating a view on the database before importing it instead of the table? Will that increase the speed or refresh for the flow of data?

[@daltonhale4436]

If I wanted a report to be refreshed with live data upon end user clicking refresh from Power BI server - must the source be Directquery?

[@damn68]

Can you have multiple data sources when using live connect

[@claudiaouellet8499]

I don't know why I bother trying to find information on PowerBI from other channels. I always end up back on yours for the real stuff. High-quality content as usual. Thank you!

[@harshindublin]

I have connected DB viaimport mode . My query was regarding schedule refresh. Would this db pull realtime data or do I need set refresh timeslots for maintaining data updates

[@aurelbk8214]

I have a SSAS tabular model. What is de best pratice to get data on power bi? Import or Live connexion?

[@vitto_af]

Hi, I have a live connection to SSAS and a measure that return text , but it seems disenable when I try to use it as field value in order to do Dynamic the text button.

[@aruncser]

Are you planning to make videos on administering premium power bi platform ?

[@IQBooksPUBIQBooksPUB]

Is imported data indexed? say you had an index on several columns, would your model be equally efficient, and leverage indexes that were in your source?

[@jonathanFN04]

Hi. One probably dumb question. Why when you load the DQ file suddenly the table options appeared? is it not only available for Import mode not DQ?

[@aruncser]

Is there any slack channel to ask questions and have discussions / brainstorming chats with other developers ?

[@devinsolkov8645]

Cool Shirt! Thanks for the video!

[@SK-ck3qb]

How do I create parameters with a Live connection to Azure Analysis Services?

[@eagillum]

3:12- Direct queries 5:15- Live Connection

[@ishti8211]

What's the reason for using background music? Other than that, the content is good and useful.

[@hadyshaaban1221]

Hi dear, How can i mix between live data source and imported tables. my dashboard contains imported tables and i need to access a powerbi dataset live. How can i do that please

[@anshulroxx6791]

1.5Billion rows in import? Woah!! What is the Gen2 capacity they have for such scenario and how much time does it take for initial load in import?

[@DanielWeikert]

Hi, what would be the best way to change the underlying data souce in a report from excel files to tables from a database when the column names are not the same? Any best practices here? Thanks

[@simonmasters3295]

Create view using SQL Select as ...

[@SanchezOKelly]

What is performance like at 1.5 billion rows however? I know in other similar software, like tableau. You really hit a wall if you're trying to use large data sets and they crawl.

[@Marcel-f1]

To who don't know, the PBIX file is a ZIP file with extension renamed to PBIX.

[@NirdeshM]

how do you pass value? I mean table A key to Table B as query param

[@AMGunawardana]

Hi I have a small issue with direct query. In a report if we built a direct query model and if we want to see the real time change on the chart how can we do that? What we are doing currently is hitting refresh tiles button or refresh the browser please help!!!!

[@visheshjjain]

What is dataset limit for Premium per user?

[@gopalb7954]

Hi guys you make understand easy.. With every topic I watch to refer and can you guys show me how to connect to service now tool to get data.. From long time i been waiting for it.

[@RKTECH1021]

I am using latest Power BI Desktop connecting to Power BI datasets or Live connection to SSAS 2016 on prem model. I just don't see any option to import other dataset for ex: excel or SQL Server. everything gets grayed out after connecting one Power BI datasets. we are on Power BI premium capacity and Desktop Pro license. I also enabled DirectQuery for Power BI datasets and Analysis Services checkbox from preview features. any clue what am I missing it?

[@lancewilliams3279]

Nice Roci Shirt!

[@sanketkhunte537]

Informative

[@thealchemist6133]

how do you connect with Data Vault?

[@vmckameyjr]

Great vid ...thank you! ps- that background music was very distracting

[@rebekahhenken5805]

Love the shirt! :D

[@dugrecco10]

How about security implications when importing the data? Once imported the data is now available to anyone with access to the report/dashboard. Table and/or row level security is no longer applicable or the responsibility of end user/developer which most are not familiar with. Am I missing something? Thanks!

[@GuyInACube]

Wasn’t really the topic of this video. And that’s a bit more advanced than what I was going for. You are right that those things should be considered and Row Level Security is an option to help with that.

[@dugrecco10]

@@GuyInACube Understood. Maybe a video to discuss the data security aspects when working with Power BI would be great. Just a suggestion if you guys haven't done that already. Thanks for the reply and keep up the great content!

[@simonmasters3295]

Why do you guys assume simplicity when we all know it's security and multi-table aggregation where we spend most time

[@mohankiran3230]

how do i change to import mode if i have already done direct quiry

[@francismumbi49]

The T_Shirt is lit...

[@connorbarrett8583]

You mentioned a customer got 1.5 billion rows imported in Power BI, do you know what the data source was / how they bypassed the hardware memory limitations with PBI Desktop? Would love to understand more about how to push the limits of import mode. Thanks!

[@connorbarrett8583]

also, we are using premium and do have a star schema

[@GuyInACube]

Data source doesn’t really matter. It was Premium using incremental refresh. At that size, you won’t be able to load it in Power BI Desktop more than likely with the whole set.

[@micahdail]

You will need to use incremental refresh to limit the data in Power BI desktop, but then when published to the service the whole dataset is loaded.

[@vkkpp]

Hi, When I have a SSAS cube which when I connect from Power BI it do not allow me to import any other excel source which you are stating to be converting live connection to direct query mode (last part of the video). Could you please help me how can we do this ?

[@prashanthlv]

I have the same question. I am connecting to SQL Server Analysis services from Power BI desktop. Once connected, i cannot do "Get Data" from other sources. Any answer?

[@RobertoTempo-uh2db]

Great video tutorial, thanks! I've had a problem for a few days. I regularly use DirectQuery (import) to download Oracle data, until last week no problem, weekly downloads of over 50 million records (2 years of data) since this week I can't download even 1 year, the Powerbi Desktop counter reports l download increases until the last record but then it doesn't close and doesn't save the data in the table, it continues in a loop. I'm going crazy 😤, I can't understand why, can you help me, please? 🙏

[@ottisc]

I have taken a new role as systems administrator with a new company. A previous administrator used power bi to create the company website and it has stopped updating as we would expect. I am having difficulties tracking down the reason for the failure of updating the dataset . I am beginning to think we may have possibly reached the max storage rows for our service but am not familiar enough with power bi services to confirm the true issue. I have watched a few of your videos that have been very helpful which I appreciate very much. I wanted to reach out to see if you might provide consulting services and if so what your hourly rate might be to help assist me with familiarizing myself with the environment. Looking forward to your reply. Thank you.

[@ShubhAyush]

How do we connect to Wrokday, if we need to pull data from workday and create a report/ dashboard in Power Bi. Please help

[@MrRhysybaby123]

Great video, better T shirt

[@GuyInACube]

I appreciate that Rhys! 👊

[@richardcroft3676]

I haven’t tried it yet as I’m just too busy but is it possible to create a composite model with multiple AAS models?

[@richardcroft3676]

The initial test is in. As expected although you can pull two AAS models into the same PBI model, it’s not really working. Obvs some new joins are needed between the tables, which is a bit of a problem... Might work if I avoid using fields from both connections in the same visuals... Further analysis required.

[@tr7948]

Love ❤️ from india..

[@bilalhasan3828]

How we can access Jira Historical Data in PowerBi , any help will be appreciated

[@MinhTamNguyen-jd3uz]

One thing for the Live connection is that there is no hidden table function when connecting to the Gold Dataset. A bit of disadvantage.

[@bhargavmehta9894]

i am trying to do this but both file can not installing in my machine

[@masonwhitehouse7587]

Man 1.5B rows. Would love to know those guys.

[@picssuxxx]

Guys! You'r f*ck awesome !! Thanx to teach us with this all patience ^^ ... Luv =*

[@B4H4MuTT]

1.5 billion rows? How did you do that?

[@aliciarobinson2531]

In another video Patrick talks about using views instead of tables for data modeling best practices. When using import is this still a good idea? I have a lot of queries importing and refreshing daily and am trying to find the most efficient way to model them.

[@hahaying9280]

I think should be use the live connect

[@manideep8566]

Hey, In IMPORT mode once we have a copy of data(CACHED DATA) do we have any connection between Power BI and data source(ORIGINAL DATA)? Because in the video you were showing the link that exists between Power BI and data source.

[@donovan6094]

when u choose import mode, u should add "scheduled refresh" for pro user max 8 per days. and dont forget to add gateway connection first.

[@praveenmullamuri2835]

Data set size 800gb , it will work in direct query and premium license

[@tob-robt]

Hello, thank you very much for your videos, they are very interesting. I've been trying to search your channel for something that helps me calculate what I'm trying to calculate for days. In my case I have something like this, in the same column: Row A1 = (POWER(1+Forward_Rate,-1)) Row A2 = A1 * (POWER(1+Forward_Rate,-1)) Row A3 = A2 * (POWER(1+Forward_Rate,-1)) Row A4 = A3 * (POWER(1+Forward_Rate,-1)) …and so on in the same column that has 930 records. I've been trying to replicate this in Power BI for days, creating several variables, making conditionals with them, etc., but I'm not able to reach the same result that I calculated by doing this in Excel. Do you have a video that can explain more or less what I'm trying to do? Or could you make one that shows how to do it? Thank you so much

[@codfalcondelex5781]

HOW TO GET DATA TO POWER BI FROM WEB HAVING USER CREDENTIALS

[@kochooloo]

I have about 1 Billion rows, and every time it loads it crashes and restarts my computer. Why is this happening? (PBI Desktop)

[@maramsubbu1596]

Parameter explain sir

[@jeanpierre4370]

Personally speaking - Import is good for temporary reports. Or to view past data only. Direct Query is good for a live report. Up to date sales, etc.

[@jeanpierre4370]

I don't agree with you, you can create 1 SQL statement that pools sales/Quantity information from the past 3 years and that can give you 5 different graphs based on Top performance items, gross income, worse performance, etc. Where if you import it, you might have to import 10 different tables and based on the amount of data will take longer and your performance will be slower.

[@rhys87]

Incremental refresh on import.

[@MarijanSivric]

Did anyone notice the Rocinante T-shirt?

[@allmhuran]

Coming in 2 years late, and *of course* in that time microsoft has made things more confusing. The "power BI data sources" table shown in this video no longer exists at the given link, and the information is far more obfuscated.

[@mohammedabdulraheemkhizer5037]

Rocinate! ;) #Expanse

[@user-xz8te5dq2d]

😍

[@dominikreim7723]

actually direct query has the highest performance