Step-by-Step Data Migration from On-Premises to Cloud: Connecting Your Data with Power BI
This article outlines the process of migrating data from on-premises to the cloud and how to integrate that data with Power BI.
Step :-1 Create a resource on the cloud -ADF(Azure Data Factory), Azure SQL Database.
Step:- 2 To create a pipeline for establishing a linked service to connect MySQL to Data Factory, and Data Factory to SQL Database.
Step 3:- Trigger the pipeline.
Step 4:- Connect the data from the Cloud SQL database to Power BI.
Steps to Create Data Factory and SQL Database
Search for Data Factory and you will find it
After filling out the form you will create your data factory
when we click on the go-to resource and the next page click on Azure Studio we will get the Data Factory window
by clicking on the left pencil button we can see the option to create the pipeline and then give the name of that pipeline and drag the copy data option in the blank window now the main task is to connect with the source is MYSQL and Sink that is our Azure SQL Database .
For that, we will create a sink database where we store data after loading it onto the cloud. for that again we will create a resource for SQL Database.
After filling out the form for the SQL database on the cloud, we will get the SQL database once the deployment is complete. Go to the resources, click on Query Editor, and log in with the username and password provided during the SQL database creation.
and we can see that right now there is no table in the table folder.
Now, We have successfully created a Data Factory and SQL Database on the Cloud.
Step:- 2
Next, to create a pipeline in the Data factory we will create a link service to connect MYSQL to ADF
Now comes the interesting part: we need to establish a connection between on-premises and the cloud. For this, we need to set up integration. If we are integrating within Azure, we will use the Auto-Resolve Integration Runtime. Otherwise, for non-Azure sources, we will use the Self-Hosted Integration Runtime. For that, we have to install Microsoft Integration Runtime from https://www.microsoft.com/en-us/download/details.aspx?id=39717
After installation of Microsoft Integration Runtime, we will find the below window
Once the self-hosted integration is configured, we will test the connection by entering valid credentials.
After establishing the connection we will select the table from MYSQL database
For storing the in Cloud SQL we will create the link service from ADF to Azure SQL Database
After creating the Pipeline and input and output dataset we will publish it all in ADF
Step :-3
To run the pipeline we will click on add trigger.
Once the pipeline runs successfully, our table will be migrated from MySQL to the Azure SQL Database.
Step :- 4
Now, we will connect the data from Azure SQL Database to Power BI
Give the server name, user name, and password that we have given at the time of creation of the Azure SQL Server.
Finally, we have successfully loaded data into Power BI
Conclusion:-
following these steps, you can easily move your data from on-premises to the cloud. This process saves money and makes managing data more efficient. Using Azure Data Factory and Power BI helps connect your data and get real-time insights. Moving to the cloud improves flexibility and supports your business needs as it grows.
Hope this article helps you understand the process. If you found it helpful, please clap and share your feedback.
Happy Learning…!!!
Don’t forget to subscribe to
and join our Power BI community