Business Intelligence System
BI solution with Snowflake and Tableau
In this project, I developed a Business Intelligence (BI) system using Snowflake and Tableau to analyze and visualize sales data across six retail stores over two years. This platform allows stakeholders to effectively compare sales targets against actual outcomes, providing a clear view of each store's performance. It facilitates a deeper understanding of sales trends, product performance, and consumer preferences, crucial for informed decision-making.
A key feature of this system is its multifaceted approach to data analysis. It includes a detailed examination of product types and sales trends, alongside a bonus calculation module that aligns employee incentives with company goals. This module not only encourages a performance-driven culture but also enhances transparency and accountability within the organization.
Finally, the system includes a locational analysis tool, assessing each store's performance in relation to its geographic context. This insight is vital for strategic planning, such as marketing initiatives and potential expansion, making it an invaluable asset for driving business growth and operational efficiency in a data-driven manner.
In the following section, I showcase the dimensional model schema that I organized to effectively manage and manipulate such large datasets.
Dimensional Model Schema
My dimensional model schema represents a robust framework for analyzing sales performance within a retail business context. At the heart of this schema are two fact tables: Fact_SalesActual, which captures the actual sales data, and Fact_ProductSalesTarget, which contains target metrics for product sales. These tables are rich with quantitative data, such as the amounts, quantities, and profits of sales, and are linked to various dimensions that add valuable context to these numbers. The fact tables are designed to enable the aggregation of sales data across multiple axes such as product, store, reseller, customer, and time, providing a comprehensive picture of the business's sales operations.
Surrounding the fact tables are multiple dimension tables that store descriptive attributes related to the business's different aspects. For example, Dim_Product holds information about the products, including names, types, and categories, while Dim_Store and Dim_Reseller detail information about the stores and resellers through which the products are sold. Additional dimensions such as Dim_Customer, Dim_Channel, and Dim_Location provide insights into customer demographics, sales channels, and geographical distribution, respectively. These dimensions are meticulously designed to slice and dice the numerical data, allowing for multifaceted analysis and enabling businesses to drill down into specific areas of interest.
The overall architecture of this schema is modeled after a star schema, which is optimal for query performance in analytical scenarios. It simplifies the process of data retrieval for reporting purposes, making it easier for business analysts to extract insights without navigating a complex database structure. Moreover, the use of conformed dimensions, like Dim_Location, ensures that the data remains consistent and reliable across different analyses. This schema is not just a technical blueprint but a strategic asset that underpins data-driven decision-making, providing a clear path to uncover trends, measure performance against targets, and ultimately steer the business toward its strategic goals.
