Microsoft Fabric: Load local SQL server data in Lakehouse using on-premise Gateway| Dataflow Gen2

In this video of Microsoft Fabric, I will show to load data from an on-Premise SQL server to Lakehouse using the on-premise gateway with help from Dataflow Gen 2
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 Install On-Premise Gateway
03:00 Create Dataflow Gen 2 with Gateway
06:00 Map Destination to Lakehouse
09:00 Publish Dataflow Gen 2, and Load Challenges
12:30 Create Model and Analyze data in Power BI Service
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...
~-~~-~~~-~~-~

[@azobensadio260]

Thanks so much for this great presentation

[@AmitChandak]

Glad it was helpful!. Thanks 🙏

[@majeedadil777]

Thanks for sharing. How about loading SQL Server data residing in cloud as part of a VM from Data Factory to Fabric Warehouse instead of Data Flow G2?

[@AmitChandak]

Typically, data on a VM will also require an on-premise gateway unless you are able to open the VM's ports and network to make it accessible externally. First, enable the SQL server to start communicating on port 1433 (for example), and then open both inbound and outbound traffic on the VM for port 1433. learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver16 learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access?view=sql-server-ver16

[@user-bu2lu4br5r]

Hi, i installed the on premises data gate way and it shows the gateway is online ready to use while i'm connecting to the sqlserver in dataflow gen 2 it was not establishing the connection for my SqlServer i given all the credentials like ServerName and database name and username and password and it was showing the error like..... and also it was not showing the datagateway in the connect data source... Connection could not be created. This may mean it is not accessible from this network or at this URI, or that a gateway is required to access this data source. (Session ID: c0bbfcd3-7712-4c40-a022-b0f448c24f12, Region: us) and 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)

[@AmitChandak]

Hope you have give name like localhost\ or \ or . And then used on-premise gateway that can access that machine/ip.

[@vishwassee]

Hi Again Amit Thanks for your previous reply i was able to resolve the error. Is there a way i can connect to On-prem SQL server using Fabric Notebook?

[@AmitChandak]

Fabric Notebook, still do not support on-premise gateway. Pipeline start supporting from March-2024.

[@vishaljuneja3880]

Thank you Amit for great explaination, but I would like to know as I want to manage the direct connectivity of data from SQL on premise to lakehouse so how to do as so in process you showed we need to apply schedule refreshes which is again come with limit so I need direct connectivity so any changes made in data it should reflect in our lakehouse and then over power bi reports too. How this can be achived

[@AmitChandak]

Hi Vishal, Once you done with your Lakehouse or Warehouse data loading, you can configure incremental ETL. There are few ways to do it. Microsoft Fabric Part 15: Incremental Data Load(ETL) for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline: kzbin.info/www/bejne/o6Gpg6GQd72mrLM How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse Microsoft Fabric Part 16: kzbin.info/www/bejne/p6SyepmKr892ibM Microsoft Fabric Part 21: How to append only Incremental data using Data Pipeline in Lakehouse: kzbin.info/www/bejne/rqvLnpSHo6dlnK8 Once done you can schedule the pipeline depending on need

[@huongbui4376]

I followed your steps and got a problem that I couldn't choose Destination target. It rotated always when I wanted to open a warehouse (in my case, its name is WH004) to choose an existing table. This problem did't happen when I connected to Github or loaded a csv file.

[@AmitChandak]

Hi @huongbui4376, For last couple of week issue is on-going with on-premise gateway. So this flow might not work. I have reported this issue.

[@saumyakapoor6772]

great ! Looking forward to Purview hub integration with Fabric. When i try to launch it as an administrator, it just shows : Loading Microsoft Purview hub (preview)...

[@AmitChandak]

Check out these steps can help kzbin.info/www/bejne/bpysfnujoLZso5Y or Get started with Microsoft Fabric Training learn.microsoft.com/en-us/training/paths/get-started-fabric/

[@vishwassee]

i am getting this error after i publish error: GatewayCannotAccessSqlError Couldn't refresh the entity because of an internal error any idea how to fix it?

[@AmitChandak]

If you are getting - GatewayCannotAccessSqlError. Seem like using on premise Database. Make sure on premise gateway is up and running. And you have signed in. Make sure you are using latest version of on-premise gateway Also Local DB is running.

[@anmolmalviya2426]

Hello sir, can we set refresh for on premise data

[@AmitChandak]

Yes you can. Call your dataflows/ procedures into on pipeline and schedule it

[@yakkalaakhila]

Could you please tell which name have to give for the connection and database in connection settings.

[@AmitChandak]

For on-Premise SQL server. Once On-Premise gateway is enabled, you need use \\ Where Machine, name is the name or IP or the machine on which SQL server is install in case there is no domain name. \

[@dbsql6238]

Disappointing that it only works with SQL server data sources.

[@AmitChandak]

@dbsql6238, Thank you for your comment! I understand the concern, but I'd like to clarify that Microsoft Fabric is versatile and supports various data sources, not just SQL Server. It can effectively handle on-premise sources too. For instance, we've successfully used Dataflow Gen2 with an On-Premise SAP Hana system, and it works seamlessly.

[@dbsql6238]

@@AmitChandak We tried connecting Fabric in Azure to an Oracle Data Source in OCI. When we selected Oracle Data source, it didn't have the normal host, port, and service name Oracle prompts. It looked like SQL Server database prompts. The Microsoft docs don't provide any help.