Building a Robust Data Warehouse with SQL Server and SSIS

This portfolio demonstrates the creation of a data warehouse using SQL Server and SQL Server Integration Services (SSIS). It includes the data warehouse design, ETL processes, and multidimensional cubes. The data warehouse was built by extracting data from an OLTP source database, transforming the data, and loading it into a star schema data warehouse hosted on SQL Server. SSIS was used to create packages for each of the ETL processes, including loading dimension tables and fact tables. Multidimensional cubes were then designed on top of the data warehouse to enable fast querying and data analysis. The portfolio showcases database and ETL skills as well as knowledge of SQL Server Business Intelligence components.

Overview Case:

Designed and built a data warehouse for Bitter Pill Pharmacy to analyze operational data for decision making

Requirements Gathering:

Interviewed CEO to understand reporting needs like medicine and equipment sales, consultations, analyzing by customer, employee, etc.
Identified dimensions like customer, employee, doctor, supplier, medicine, equipment, branch
Noted requirements for each dimension attribute to enable analysis like gender, age, location, etc.

Design:

Created a star schema with fact and dimension tables
Fact tables for medicine purchase, medicine sales, equipment sales, consultations
Dimensions for customer, employee, doctor, supplier, medicine, equipment, branch
Identified measures and aggregate functions for facts

ETL Process:

Used SQL Server Integration Services (SSIS) to extract data from OLTP database
Transformed and cleaned data
Loaded data into dimension tables using slowly changing dimensions
Loaded data into fact tables

Analysis:

Built OLAP cubes on top of data warehouse
Created pivot tables in Excel to generate reports for CEO analysis
Enabled slicing and dicing data by different dimensions like customer, product, etc.

This data warehouse enabled powerful analysis of pharmacy operations to support data-driven decisions. The portfolio demonstrates proficiency in requirements gathering, dimensional data warehousing design, ETL, and reporting.

1. Database transaction

Step by step :

Requirements Analysis

Identify facts, dimensions, attributes needed for reporting
Facts: medicine purchase, medicine sales, equipment sales, consultations
Dimensions: customer, employee, doctor, supplier, medicine, equipment, branch
Attributes: gender, age, location, salary, etc.

Data Warehouse Design

Create star schema with fact and dimension tables
Define grains for fact tables
Identify measures and aggregate functions
Design slowly changing dimensions where applicable

ETL Process

Extract data from OLTP database using SSIS
Transform data by cleaning, joining, deriving
Load data into dimension tables
Load data into fact tables

For More info please click button below