In this project, I connected an SQL Server database to Power BI to create an interactive dashboard for data visualization. The goal was to visualize real-time data by using Power BI's Direct Query feature, ensuring the dashboard always reflects the latest data.
First, I opened the "Get Data" menu in Power BI and selected "SQL Server". I entered the server details and chose the relevant table. The Direct Query option was selected to enable real-time data refresh, ensuring that any changes in the database would be immediately reflected in the Power BI dashboard.
To optimize the data for visualization, I created views directly in SQL Server. This allowed me to summarize data and streamline the process before importing it into Power BI.
Here, I used the Create View command to simplify the process:
- Created a view using the
CREATE VIEWstatement to organize data efficiently.
In Power BI, I established relationships between tables to ensure that all imported data is properly linked and can be analyzed cohesively. This step is crucial for accurate visualizations and data integrity.
Depending on the analysis needs, I created additional tables in SQL Server and imported them into Power BI using the Direct Query method. This ensured the data was always up-to-date without the need for manual refreshes.
To highlight the most important metrics, I prepared a series of KPIs:
- Amount
- Total Revenue
- Average Recency
- Maximum Day Difference Frequency
- Average Revenue
While "Amount" and "Total Revenue" are static values, the rest are interactive, allowing users to drill down into specific data points as needed.
On the right side of the dashboard, I added slicers to filter the data by years and months. The slicers use a transparent background and corporate colors to match the overall design, providing a smooth and user-friendly experience.
-
Tree Map: Placed on the bottom-right, showing shopping preferences.
-
Donut Chart: Located in the center, it shows the distribution of orders by channel.
-
Customer Table: Displays customers with the highest number of purchases.
-
Line and Stacked Column Chart: In the middle-bottom section, it displays the count of online channels and the sum of online average revenue by channel.
-
Line Chart: On the right-bottom, a line chart shows changes in offline revenue by year, month, and day.
After completing all the visualizations and configurations, the final dashboard was ready, providing a comprehensive overview of key business metrics and trends.
This project demonstrates how to effectively connect SQL Server to Power BI using the Direct Query option and create an interactive dashboard to monitor business performance. With the use of KPIs, charts, and slicers, users can explore data dynamically to gain valuable insights.
- SQL Server
- Power BI