Problem Statement
The Client is looking to integrate all the Data from Traditional On-premises SQL Server Database into Cloud Database for efficient data management and analysis using the advanced SQL features in Cloud, such as security, scalability, and data management. The Client also wants to see the Data visualization in Power BI and the data should be taken from Cloud SQL Database.
Methodology
Analysis of existing on-premises SQL Server: The project begins with a comprehensive analysis of the existing on-premises SQL Server architecture. This involves understanding the data schema, formats, dependencies, and any specific requirements for the migration process. Leveraging Azure Data Factory and Power BI: Azure Data Factory provides a scalable and secure framework for seamless data migration. Once the data is migrated, Power BI is employed as a business intelligence and data visualization tool to create interactive dashboards and reports.
Model Deployment and Integration: This ensures that the model seamlessly integrates with other components, allowing stakeholders to utilize its predictions and insights in real-time decision-making processes.
Data Preparation and Exploration: This includes gathering the relevant data for the global stores, ensuring data quality, and addressing any missing values or outliers. Exploratory data analysis techniques are employed to understand the distribution, relationships, and patterns within the data. This step helps in identifying potential features that can contribute to the model’s predictive power.
At a Glance
Challenges
- Data Quality & Availability
- Model Selection & Performance
- Deployment & Scalability
Benefits
- Improved Decision Making
- Enhanced OPerational Efficiency
System Requirements
- Azure Data Factory (ADF)
- MS SQL Server
- Azure SQL Cloud Database
- Power BI Desktop
Project Architecture
Our Approach
- Created The Azure SQL Database Account in Azure Platform.
- Connect the Azure SQL data Base with credentials in the Azure Portal.
- No Tables are Present In Azure Database or the Azure SQL Database is Empty.
- Iterating the 3 tables and creating one variable for storing the three tables’ names should be of type “Array”.
- Source: Create the data set for source data and create one linked service i.e. self-hosted integration and take one parameter and pass it into data set properties and by using Foreach iterator passes it to the target destination.
- Destination: Create the dataset for destination data and create one linked service for connection the linked service should be self-hosted after that take one parameter to pass into data set properties and by using the auto-create table option to migrate the data automatically.
- Now we can see the transformed tables are reflected in the Azure SQL database.
- Get the data from the azure cloud SQL database by selecting the Get Data button followed by Azure SQL database.
- The dashboard provides a single point of view to analyze the retail business and build business strategy and future decisions.
- Built the Data visualization Report in Power BI by using the transformed data.
–> In the Power BI report, used Slicers to take a year as a field to show sales by each year.
–> Used cards to show total product Sales, total Quantity Sold, and Average delivery day taken by the order to reach the customer.
–> Donut chart to show Sales by segment and used Pie-chart to show sales by market.
–> Used column chart to show the top 10 customers who are generating the most profit.
–> Bar-graph to show the top 5 profiting products and bottom 5 products by Profit.
–> Utilized map to show the sales by region.
