Wednesday, September 2, 2015

DW- Architecture and Multidimensional Model

We know that data warehousing is a collection of methods, techniques and tools which is used to support knowledge workers such as senior managers, directors, managers, and business analysts to conduct data analyses that help with performing decision-making processes and improving information resources.
Fundamentally, data warehouse is the secured place for the historical data where data is never deleted and data updates are normally carried out when data warehouses are offline.

Data Warehouse (with a Staging Area and Data Marts) - Three-Layer Architecture
As per the enterprises/business requirements, you can easily customize your data warehouse architecture according to the different business group. For example, if you want to create a data warehouse for an ERP system (where purchasing, sales, and inventories are separated) then you can do this by adding different data marts, which are systems designed for a particular line (business modules) of business. 

The main advantages of the three-layer architecture is that – 
  1. It creates a common reference data model for a whole enterprise. 
  2. At the same time, it sharply separates the problems of source data extraction and integration from those of data warehouse population. 
  3. Identically, in some cases, the staging layer is also directly used to better accomplish some operational tasks, such as producing daily reports that cannot be satisfactorily prepared using the corporate applications, or generating data flows to feed external processes periodically so as to benefit from cleaning and integration. 
  4. This approach assumes that although a data warehouse is available, it is unable to solve all the queries formulated. 
  5. This means that users may be interested in directly accessing source data from aggregate data (drill-through). 
  6. Data Marts are dependent on the data warehouse and metadata but they are independent to provide information for a particular line of business.
Data Warehouse - Multidimensional Model
Multidimensional databases have generated much research and market interest because they are fundamental for many decision-making support applications, such as data warehouse systems. Before build a data warehouse, you need to have defined the business requirements and also agreed upon the scope of your application, and created a conceptual design where you need to translate your requirements into a system deliverable. To do so, you have to choose the ideal data warehouse architecture to fulfill logical and physical requirement by considering the following things -
  1. The specific data content
  2. Relationships within and between groups of data
  3. The system environment supporting your data warehouse
  4. The data transformations required
  5. The frequency with which data is refreshed

The multidimensional model is the base of any data warehouse and begins with the observation that the factors affecting decision-making processes are enterprise-specific facts, such as sales, shipments, hospital admissions, surgeries, and so on.
Dimensions of the cube are the equivalent of entities in a database. For example, the sales in a store chain can be represented in a three-dimensional space whose dimensions are products, stores, and dates. A data cube allows data to be modeled and viewed in multiple dimensions.


Requirements of Fact Tables
You must define a fact table for each star schema or snowflake schema. From a modeling standpoint, the primary key of the fact table is usually a composite key that is made up of all of its foreign keys.

Conceptual Modeling of Data Warehousing
In relational implementation, the dimensional designs are mapped to a relational set of tables such as dimensions & measures.

Star schema: It supports to de-normalization data concept. A star schema model can be depicted as a simple star: a central table contains fact data and multiple tables radiate out from it, connected by the primary and foreign keys of the database. 


Snowflake schema: It supports to normalization data concept. The snowflake schema represents a dimensional model which is also composed of a central fact table and a set of constituent dimension tables which are further normalized into sub-dimension tables. 


Please visit to know more on -
  1. Collaboration of OLTP and OLAP systems
  2. Major differences between OLTP and OLAP
  3. Data Warehouse
  4. Data Warehouse - Multidimensional Cube


  1. A star schema is used as a basic implementation of an OLAP cube. If your fact table contains a 1 to many relationship to each of your dimensions in your data warehouse schema then it is appropriate to use a star schema. Although if your fact table has a many to many relationship with its dimensions (i.e. many rows in your fact equal many rows in your dimension) then you must resolve this using a snow flake schema where the bridge table contains a unique key to each row in the fact table.

  2. Good article, what could be added is using the Data Vault methodology for the central storage ("Warehouse" layer). Data Vault has many benefits, which are described on my blog:

  3. Excellent Blog, I appreciate your hard work , It is useful
    Tableau Online Training


  5. Be that as it may, the shopping pattern is turned around now. Instead of purchasers, items get drive to their doorsteps science course in pune

  6. Thank you so much for helping me out to find the Data analytics course in Mumbai



  8. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

    Data Analytics Course in Mumbai

  10. Are you looking for different courses that can help you understand different business intelligence terminologies, then we have top Udemy courses that can help.


Popular Posts

Get Sponsored by Big Brands