A data warehousing project that entails designing the data marts as well as integrated data warehouse from scratch and then performing extensive ETL.

Collaborators.

This project was built in collaboration with

• Hasan Ahmad
• Harris Amjad
• Hadi Amjad

Disclaimer

We do not endorse copy pasting this project and turning in as your own submission in university assignments. That is highly unethical and the person copying would be responsible for any and all consequences.

Built with

• Love
• Microsft SSIS
• Microsoft SQL Server
• Erwin Data Modeller
• Redgate SQL Data generator.

Scope

This project deals with a fictitious car business which sells cars from different retail points, manage car services, and manages car parts. The design of the Car Industry data warehouse will allow the business executives to make swift decisions monitor the business indicators. This data warehouse increases efficiency and accuracy. This can be used to, for example, get answers to questions such as Which car is in demand the most? or Which car features were most preferred by the customers?

Architecture

There are basically three major OLTP systems involved

• Car sales information system
• Car service information system
• Car parts order information system.

So these are primary data sources for our Car Industry Data warehouse and three data marts are formed for these three OLTP systems.

This project utitlizes bottom UP design. We first make individual data marts from the source systems and then integrate them into one large datamart.

One of the data marts, the Car Parts data marts' schema is shown here. The other data marts are too large to be displayed here.

A note on versioning and packaging.

It might be noticed that this project is not versioned properly. The reason is that behind the scenes, SSIS stores everything as XML files which are hardly mergeable. The creators of this project were unaware of this when starting out. However, in hindsight, one of the possible solutions to this problems is to create ETL pipelines in separate packages and never let two developers simulataneously work on the same package. Merge conflicts in XML files generated by SSIS are hardly resolveable.