Building Sales Data Mart using SSIS and SQL Server Designing a data mart utilize the power of SSIS (SQL Server Integration Services) and SQL Server, using the AdventureWorks2022 database, an OLTP (Online Transaction Processing) system, as the primary data source, a widely recognized database in the realm of data analysis.
A data mart is a focused and purpose-built subset of a data warehouse, designed to cater to the specific analytical needs of a particular department, team, or business unit within an organization. Data marts are typically structured around a dimensional model, employing star schemas, which consist of dimension tables describing various attributes and a central fact table containing numerical measures.
-
Data Extraction from AdventureWorks2022 (OLTP)
-
Cleanse and preprocess extracted data to ensure data quality
-
Design the star schema for data mart.
-
Develop ETL (Extract, Transform, Load) processes using SSIS or other ETL tools.
Data Modeling (Star Schema)
Then, I used SSIS to create Fact table and the dimention packages separately for:
-
Integrating the data from data source.
-
Transforming and processing data.
-
And then incremental load data after applying the transformation, and apply full and slow-changing dimension (SCD) loading.
