Data Warehouse Design and Dimensional Modeling

Within organizations, information is typically categorized into two different areas. There are operational databases that are used for online transaction processing (OLTP), and there are data warehouses and data marts used for online analytically processing (OLAP). OLTP database users tend to be operational users who enter data or control inventory. OLAP databases are used by analysts and management to support long term decision making.


       
Online Transaction Processing (OLTP) databases support everyday business operations. The operations of an OLTP database include reading, writing and updating. These basic transactions are current and include data entry and changes made to entities such as orders, customers, inventory, etc. When designing an OLTP database we begin with an ER diagram that consists of Entities and Relationships. OLTP data is stored in relational tables.



              

Online Analytical Processing (OLAP) databases contain current and historical business data used to show changes in data over time. OLAP databases periodically update and have read only functions that are used for reporting. When designing an OLAP database we use Star Schema comprised of Facts and Dimensions. OLAP data is stored in multidimensional structures such as data cubes.




Ram, Sudha. (January 2014). MIS 587 – Business Intelligence: Data Warehouse Design Cycle. Lecture Conducted from University of Arizona, Tucson, AZ. Accessed from http://courses.eller.arizona.edu/mis/587/ram/Lecture3_v3/