Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2| End to End Flow
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2 and analyze using SQL endpoint, and Power BI Dataset. Load and Transform the data using Dataflow Gen2 into Microsoft Fabric Lakehouse
Microsoft has Microsoft Fabric, the next-generation Analytic Platform. Check out how to enable it on Power BI Service. How to start a Fabric (Preview) trial
The Microsoft Fabric Platform provides customers with a SaaS-ified, open, lake-centric(One Lake), full-featured data, analytics, and AI platform that meets all their data estate needs. Power BI, Synapse Data Warehouse, Data Factory, Spark, and Notebook all under one platform
00:00 GIT file to be loaded in Lakehouse
02:30 Load Data in Lakehouse using Dataflow Gen2
24:00 Checking data using SQL End Point
28:00 Create Model
29:10 Test Data in Power BI online, Create Measure
38:00 Test Data in Power BI Desktop
42:00 Analyze data in Excel
GIT File link - github.com/amitchandakpbi/pow...
All files: github.com/amitchandakpbi/
Power BI - Azure Free Tier, Create Azure Data Lake Gen 2 and connect that in Power BI: • Power BI: Connect with...
Get Azure SQL DB and Configure it on Power BI: • Abstract Thesis 93: Po...
Official Document: learn.microsoft.com/en-us/fab...
Video from GuyinaCube: • Microsoft Fabric Launc...
Buy a Microsoft Fabric subscription: learn.microsoft.com/en-us/fab...
SKU: learn.microsoft.com/en-us/fab...
Licenses: learn.microsoft.com/en-us/fab...
Enable Fabric: learn.microsoft.com/en-us/fab...
🔵 What is Microsoft Fabric
Microsoft Fabric is an all-in-one analytics solution for enterprises that covers everything from data movement to data science, Real-Time Analytics, and business intelligence. It offers a comprehensive suite of services, including data lake, data engineering, and data integration, all in one place.
With Fabric, you don't need to piece together different services from multiple vendors. Instead, you can enjoy a highly integrated, end-to-end, and easy-to-use product that is designed to simplify your analytics needs.
How to enable Fabric, find details - learn.microsoft.com/en-us/fab...
Microsoft Fabric licenses details -learn.microsoft.com/en-us/fab...
Don't forget to like, comment, and subscribe for more great content!
▶️Please Subscribe: / @amitchandak
▶️Data:
• Covid 19: covid19.who.int/WHO-COVID-19-...
• Get The data and Pbix files on Github: github.com/amitchandakpbi/pow...
• DropBox files: www.dropbox.com/sh/90ycq7hpmn...
---------
▶️Follow me on:
• Medium: / amitchandak
• Twitter: / amitchandak78
• LinkedIn: / amitchandak78
---------
▶️My Other Videos:
• Mastering Power BI: • Power BI for Beginners...
• Expertise Power BI: • Expertise Power BI- Ho...
~-~~-~~~-~~-~
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
• Microsoft Power BI Tut...
~-~~-~~~-~~-~ - วิทยาศาสตร์และเทคโนโลยี
![fellow fellow - Proud [OFFICIAL MV]](http://i.ytimg.com/vi/9uZF7BlUQPE/mqdefault.jpg)
I learned so many new things on the Microsoft Febric Platform today.
Thank you Sir for your detailed videos on this.
Glad it was helpful!
It was clear and concise explanation about fabric sir
Amit ,The way you are teaching, really easy to understand. will love to explore all 40 videos in this fabric Playlist 👍
Thank you so much for your kind words! 🙏 I'm happy to hear that you find the videos easy to understand. Enjoy exploring the rest of the series, and feel free to share any questions or feedback you might have. Happy learning! 😊📊👨🏫
Mastering Microsoft Fabric 40+ Videos: th-cam.com/video/p-v0I5S-ybs/w-d-xo.html
@@AmitChandak Thank you Amit 😊. I might need your support and guidance for DP-600🙏
Thanks for sharing. By loading the data through DFG2, the table names are appearing in proper case but if we do the same process through Apache Spark, the table names will always be in lower case? Any reference point ....
Refer to this
community.fabric.microsoft.com/t5/Data-Science/Notebooks-writing-tables-with-names-lowercased-how-to-fix/m-p/3753909
Nice video. Is it possible to write data PARTITIONED through Dataflow gen2 into a Lakehouse? I know I can do it with a notebook after data is ingested from source. But what if I want to read data from source through DF Gen2 and write it into the Lakehouse partitioned at the same time?
Nice video Amit.👍Loved it.
Thanks. 🙏
Hope you will like the full series
th-cam.com/video/p-v0I5S-ybs/w-d-xo.html
@@AmitChandak will watch the series..👍
Sir fabulous video, if possible create video on importing data from excel or migrating data from on premise data source into fabric.
Thanks. Check this video for excel - th-cam.com/video/Pi5IvkHxJBs/w-d-xo.html
Sir I used create a folder for all measures, as they will be in one folder, whether I can create it fabric or I should create it in desktop
You have create tables in fabric and use those as folders. Like we created a table with name measure with one column and no data and used that as measure folder.
I have no data on cloud,what ever i am using that is on sql server ,and have created gateway connection for this,so when i am creating dataflow gen 2 through sql server ,using that on premise gateway getting this error " The selected connection has an on-premises data gateway that is different than the project's gateway. Committing will apply to all queries in this mashup" what to do next?
Please check this video in the series.
th-cam.com/video/oEF-jHVmvdo/w-d-xo.html
Always use latest on-premise gateway.
If Dataflow uses already existing connection and not working, Then use new connection and update the details again.
Thanks for the video
I used dataflow to add data to a data lake from web api, which worked fine, but then I wanted to switch from "New table" to "Existing table" to add data every day by refreshing the dataflow, but when I go to edit or recrate destination - it doesnt see the table in the data lake that was created by initial refresh :(
Awesome explanation Amit bhai👏, I am little confused between the data lake and warehouse.. Can you do a detailed video about it would be awesome.. 😊
Please refer
Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse- th-cam.com/video/pVvvb-MzxKw/w-d-xo.html
Microsoft Fabric-What it is, Why Use, Who Should, When and How to use
th-cam.com/video/sbWNJMaspeA/w-d-xo.html
@@AmitChandak thanks
Is it possible to Query the SQL Exten about calculated Measure as well like the actual columns ?
I doubt you can query measure from SQL as of now
Amit, really appreciate your way of teaching...
I am not able to find perfect video on Git integration in power bi, version control and multiple users working on same power bi report, how to merge and manage conflicts can you please make video on it.
I need to create one yet. But please watch this from guyinacube - th-cam.com/video/zvyr2qYCQNo/w-d-xo.html
I was following this video till 23:03. Created a lake house but accidentally clicked on "Publish Later". Its been over 12 hours and I do not see the tables I added to the Lakehouse. These are the tables from my On Prem SQL server and are fairly large in size. Now I have a couple of questions:
1. What advantage I will have of keep these on the Lakehouse v Keeping them on SQL Server VM?
2. Can I also add Views from SQL server to Lake house and they will perform process data from their respective tables in the Lakehouse?
3. To me its seems like Fabric provides an alternative to everything I used to do with SSIS, SQL server, SQL job agents etc. So if my current system is working why do I need to shift to fabric?
On the three dots near to Dataflow gen2 name click and check Refresh history , if refresh is not running of failed. manually run it
1. What advantage I will have of keeping these on the Lakehouse v Keeping them on the SQL Server VM?
Lakehouse is on the cloud and saves data in Delta parquet format, also it Read only SQL end point, you can use to query data in SQL. Power BI dataset do not copy data, so only one copy
2. Can I also add Views from SQL server to Lake house and they will perform process data from their respective tables in the Lakehouse?
If you want to import yes. If you want a shortcut, no as of now
3. To me its seems like Fabric provides an alternative to everything I used to do with SSIS, SQL server, SQL job agents etc. So if my current system is working why do I need to shift to fabric
It is a complete analytics system. It can replace many existing systems like SSIS, SQL server, ADLS gen2 etc
Hi Amit, please, will be dataflow gen 2 available also in regular Power BI or it will be exclusive to Fabric? Thank you!
I checked as of now it is not available. I have asked the question on forum. I will update once I see any replay.
@@AmitChandakthank you very much!
Thanks, nice video.
How to create a scheduled refresh? When I create a scheduled refresh on Dataflow gen 2, it creates new table every time it refreshs.
Basically what I want is, import data via Dataflow Gen2 into Lakehouse, transform it (on SQL endpoint, notebook python or Power Query as you done) and then use this data set on PBI.
Everything is okay but can't manage the refresh data. When I refresh dataflow gen2 it creates new table which power bi and transform process is not connected to this new table.
Refer if these two incremental approaches, if these can help
Microsoft Fabric Part 15: Incremental Data Load(ETL) for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline: th-cam.com/video/mpFRnZBXsvQ/w-d-xo.html
How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse
Microsoft Fabric Part 16: th-cam.com/video/qsOIfTzjCSQ/w-d-xo.html
@@AmitChandak Thanks for super quick replay,
But I'm a bit confused.
What I want to do is refresh table. Why should I go and deal with SQL? Is it not possible just refresh table in Data Flow Gen 2? When I run Dataflow Gen 2, it creates new table because Dataflow Gen 2 that we created is getting data from a file. I think we need to update data via Dataflow Gen2, not get data. Can we not do this without SQL?
Instead of data lake house can we create all in ware house ?
Yes, We can do an end-to-end flow on the Warehouse too.
Dataflow Gen2, Data Pipeline sqn SQL can write to Warehouse.
As of now, notebooks do not write directly to Warehouse.
I have covered the flow in the series. Initially, the Warehouse destination was not available to I used an alternative. But now you can use warehouse destination
Mastering Microsoft Fabric 35+ Videos: th-cam.com/video/p-v0I5S-ybs/w-d-xo.html
If we are using sql server to get data and if it have space then what to do?
You can rename those columns in Dataflow gen 2(remove spaces), if destination is Lakehouse.
@@AmitChandak even we can use ware house also right
Thank you Sir for making this video.
However, I am getting error while at video 32:50 when I add implicit and explicit measure like Gross, Discount in visual in Power BI Web.
It is showing error like "Can't display the visual - couldn't load the data for this visual. Please try again later or contact support"
Could you please advise on this?
Please log an issue - community.powerbi.com/t5/Issues/idb-p/Issues. I faced that in past with couple of measures.
@@AmitChandak Thank you once again for your advise.
Hi Amit, i am getting an error message "THE GIVEN DATA SOURCE KIND IS NOT SUPPORTED" can you help me out please?
Please share more details with me. You can ping me LinkedIn - www.linkedin.com/in/amitchandak78/
Sir can I create a new table for measures in power bi fabric lakehouse, if yes can you explain the steps
Create an empty table. And move you measures there and hide the column of the table.
For Lakehouse use Spark SQL for Warehouse you can Use SQL endpoint(Warehouse)
Create table Measure (column1 varchar(5))
How to create empty table sir
In power Bi fabric I am able to create a measures, but not able to create new column as we create in powerbi desktop. Please explain how to create a column in power bi fabric
Hi Amit please make a video on how to create power bi data warehouse and difference between lakehouse and datawarehouse
Warehouse I will load tomorrow. For difference refer
Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse- th-cam.com/video/pVvvb-MzxKw/w-d-xo.html
Microsoft Fabric-What it is, Why Use, Who Should, When and How to use
th-cam.com/video/sbWNJMaspeA/w-d-xo.html
Hello Amit, I am a new member to your channel as I was following the same steps of loading csv file through WEB API options but its fails stating "Files contains corrupted data" can you please update the file. Thanks
I tried this file again, and it worked for me. However, some users in the past have also complained that the Git file is not accessible, so giving file at Drobox too:
github.com/amitchandakpbi/powerbi/raw/main/Sales%20Data%20for%20Fabric.xlsx
Please download this file from Dropbox, and you can upload it to Fabric Lakehouse or OneDrive to use it.
www.dropbox.com/s/z9d6qfah6451ct3/retailData_fabric.csv?dl=0
www.dropbox.com/scl/fi/ru5jusnq4c89bsbhganm0/Sales-Data-for-Fabric.xlsx?rlkey=e62al8qyleagpfm4ryggtwkvx&dl=0
Hi Amit. Great Job. I have one doubt
I am getting below error, can you help me with this?
Error Details: Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Details: DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server);ErrorCode = -2146232060;Number = 53;Class = 20 GatewayObjectId: 6309202d-8f81-4235-9651-68924de52acf. Request ID: 490256f4-86ec-4c9b-a71f-83515aa6f59f. (Request ID: c5b3731b-2a50-4ee7-a227-03045b44d390).
Is it Azure SQL or SQL server? If this on-premise SQL server
Microsoft Fabric Part 17: Load local SQL server data in Lakehouse using on-premise Gateway and Dataflow Gen2:
th-cam.com/video/oEF-jHVmvdo/w-d-xo.html
For last few days this one was not working (Using on-premise gateways)
@@AmitChandak Thank you so much, I am using an on premise gateway, and as you said its not working for last few days. I ll go through the video, Please do make more videos.