Search code examples
databasedata-warehouseolap

Dimensional vs Transactional Database?

Trying to learn more about databases/data warehouses and am getting confused about the different terminology. What is the difference between a dimensional database and a transactional database? Is a transactional database the same as an operational database?

From what I've gathered a dimensional database is in a star schema and is made of fact tables with foreign keys and dimension tables with those foreign keys as primary keys.

Trying to understand how OLAP, RDBMS, T-SQL, etc and all of these other terms fit in.

Please explain as if I'm 5.

Solution

  • Relational Database systems(RDMS) are softwares designed to hold data in relational format(rows, columns). SQL is a language for working with RDMS. TSQL is an extension of SQL language, by Sybase, Microsoft, to add more capabilities to SQL language definition. Read more on T-SQL

    There are two kinds of relational database systems at high level.

    1. Online Transactional Processing(OLTP): Transactional/Operational database
      • These are designed for carrying out business transactions.
      • They are designed in such a way, so that there are no redundancies of the data. For designing these systems, normalization is applied. Read more on Normal Forms.
      • Tables are narrow tables with limited columns, joined using relationships.The modeling is referred as ER modeling.
      • Transactions hold lock on the table rows and should be as short as possible, as writers are blocking other writers.
    2. Online Analytical Processing(OLAP):Reporting Database
      • These are designed for generating reports out of the already happened business processes.
      • They are designed in such a way, the data is readily available in the same table, instead of joining and getting data from another table. So, the tables are wide with more columns. for designing these systems, dimensional modeling is applied. Read more dimensional modeling.
      • Tables are divided into Fact, Dimension tables. These tables are wide tables and Fact is related with Dimension tables using dimension keys. As a single fact is surrounded by many dimension tables, it looks like a star, so it is called Star schema. The modeling is referred as Dimensional modeling.
      • As queries are for reporting purposes, there is no concept of transactions. Readers don't block readers. So, multiple reporting queries can generate report at the same time.