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!
I assume when using import mode, the data is embedded in the report? Why can't I find the data when i change extension to zip. Is the data in binary form in a certain folder? Is there docs/forum discussion on this investigation process?
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!
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.
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!!!!
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?
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!
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.
@@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!
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!
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 ?
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?
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?
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.
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.
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? 🙏
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)
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 ?
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
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
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?
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.
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!
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?
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
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.
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
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)?
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.
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?
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!
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
If direct query is not caching or importing any data then why is there an option to refresh the semantic model? What is it really refreshing? Is it refreshing the schema/table structure?
In DirectQuery mode, refreshing the semantic model is essentially about updating the schema, revalidating the connection, and ensuring the metadata (such as tables, columns, relationships, etc.) in Power BI reflects the latest structure of the underlying data source. It doesn’t refresh the actual data content - that’s done in real time when the reports are queried. This feature helps ensure that Power BI is working with the correct structure and relationships when querying live data from the data source, and ensures consistency with any changes made to the source system's schema or table relationships.
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.
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!
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.
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.
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
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.
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.
Just want to say this is an amazing channel and has really helped me with my new role at work. Thank you so much.
We love hearing that Josh! 👊
It's good that you are still doing these beginner videos!
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!
Four types then. Import, direct query, live connection and hybrid? Good video!
To who don't know, the PBIX file is a ZIP file with extension renamed to PBIX.
I assume when using import mode, the data is embedded in the report? Why can't I find the data when i change extension to zip.
Is the data in binary form in a certain folder? Is there docs/forum discussion on this investigation process?
@@Han-ve8uh you can use DAX Studio to export the data to an CSV or MSSQL
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!
Thank you for explaining the difference between Import and Direct Query. Now I know what I need to do.
3:12- Direct queries
5:15- Live Connection
not a beginner anymore, but really worth to see this ;)
Great video I have seen this morning. Really worthy to watch this
Thanks, Regards from Cali-Colombia
Very useful! Thanks for taking the time to cover this. And our database is perfect ;-)
What's the reason for using background music? Other than that, the content is good and useful.
Thank you for the high quality videos
Much clearer now, thanks!
Great vid ...thank you! ps- that background music was very distracting
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.
Is it possible to do cross report drill-through between two Reports located in two different workspaces? Is there any way to do it?
This was very nice. Thank you! 🙂
great video Adam, very informative
My man Adam is back ! keep it up :D :D
Great clear introduction! Thanks!
Great video as always.
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!!!!
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?
Thank you for existing
Can you please show a demo about schedule refresh for the follow types
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?
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!
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.
@@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!
Why do you guys assume simplicity when we all know it's security and multi-table aggregation where we spend most time
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!
It's the size of the .pbix
I have a SSAS tabular model. What is de best pratice to get data on power bi? Import or Live connexion?
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 ?
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?
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?
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.
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.
Incremental refresh on import.
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.
How do we connect to Wrokday, if we need to pull data from workday and create a report/ dashboard in Power Bi. Please help
Can you explain also the difference regarding data flows? Please.
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? 🙏
Man 1.5B rows. Would love to know those guys.
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)
@@alishehzadpaul Depends on your license level. If you have a basic level membership no but a standard membership yes
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 ?
Hi there, Is it possible to change back to live connection from Direct Query (composite model) storage Mode ? Thank you.
For timeseries , is there any recommendation or quick start up as it is big data
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
So useful, thank you!
Very helpful. Thanks
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
Does anyone know how he imported 1.5 billion rows? My dataset is around 200 million but the import is timing out. I can’t seem to find the answer
@@zperkk3323 it could be like he used a parameter to restrict rows in pbi desktop and removed that parameter restriction after publishing to service
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?
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.
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!
Thank you so much for this video! 🇧🇷🇧🇷🇧🇷
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.
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?
Cool Shirt! Thanks for the video!
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?
Is there any slack channel to ask questions and have discussions / brainstorming chats with other developers ?
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
Data set size 800gb , it will work in direct query and premium license
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.
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
A very good video, thank you
One thing for the Live connection is that there is no hidden table function when connecting to the Gold Dataset. A bit of disadvantage.
Are you planning to make videos on administering premium power bi platform ?
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)?
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?
i am trying to do this but both file can not installing in my machine
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.
Is there any way to display Power BI Dataset name in the Power BI Report?
Thanks
Amit
how do i change to import mode if i have already done direct quiry
How we can access Jira Historical Data in PowerBi , any help will be appreciated
The T_Shirt is lit...
how do you pass value? I mean table A key to Table B as query param
When you say layering, is it agg layer or model layer (with 3 layers supported currently) ?
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?
Can you have multiple data sources when using live connect
Nice Roci Shirt!
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!
How do I create parameters with a Live connection to Azure Analysis Services?
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
Create view using SQL
Select as ...
If direct query is not caching or importing any data then why is there an option to refresh the semantic model? What is it really refreshing? Is it refreshing the schema/table structure?
In DirectQuery mode, refreshing the semantic model is essentially about updating the schema, revalidating the connection, and ensuring the metadata (such as tables, columns, relationships, etc.) in Power BI reflects the latest structure of the underlying data source. It doesn’t refresh the actual data content - that’s done in real time when the reports are queried.
This feature helps ensure that Power BI is working with the correct structure and relationships when querying live data from the data source, and ensures consistency with any changes made to the source system's schema or table relationships.
Great video, better T shirt
I appreciate that Rhys! 👊
Love the shirt! :D
Informative
how do you connect with Data Vault?
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?
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.
What is dataset limit for Premium per user?
1.5 billion rows? How did you do that?
HOW TO GET DATA TO POWER BI FROM WEB HAVING USER CREDENTIALS
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!
also, we are using premium and do have a star schema
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.
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.
I have about 1 Billion rows, and every time it loads it crashes and restarts my computer. Why is this happening? (PBI Desktop)
Love ❤️ from india..
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.
Did anyone notice the Rocinante T-shirt?
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
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.
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.
Parameter explain sir
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.
I think should be use the live connect
😍
actually direct query has the highest performance