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.
The concept of dimension gave life to the broadly used metaphor of cubes to represent multidimensional data. According to this metaphor, events are associated with cube cells and cube edges stand for analysis dimensions. If more than three dimensions exist, the cube is called a hypercube.
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.

Dimension tables - A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

Fact tables -A fact table typically has two types of columns: those that contain numeric facts (often called measurements), and those that are foreign keys to dimension tables. A fact table contains either detail-level facts or facts that have been aggregated. Fact tables that contain aggregated facts are often called summary tables. A fact table usually contains facts with the same level of aggregation.
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. 

In a star schema implementation, Warehouse Builder stores the dimension data in a single table or view for all the dimension levels.

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. 

In a snowflake schema implementation, Warehouse Builder uses more than one table or view to store the dimension data. Separate database tables or views store data pertaining to each level in the dimension.

Fact constellations: Multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation.
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



53 comments:

  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.

    ReplyDelete
  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: http://www.hansmichiels.com/2016/04/02/data-vault-and-dimensional-modelling-a-happy-marriage-data-vault-series/

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

    ReplyDelete
  4. This article gives the light in which we can observe the reality. This is very nice one and gives indepth information. Thanks for this nice article. architect

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

    ReplyDelete
  6. Thank you so much for helping me out to find the Data analytics course in Mumbai
    Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.

    ReplyDelete

  7. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. I would like to state about something which creates curiosity in knowing more about it. It is a part of our daily routine life which we usually don`t notice in all the things which turns the dreams in to real experiences. Back from the ages, we have been growing and world is evolving at a pace lying on the shoulder of technology. data science course will be a great piece added to the term technology. Cheer for more ideas & innovation which are part of evolution.

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

    ReplyDelete
  9. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.
    Data Analytics Course in Mumbai

    ReplyDelete
  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.

    ReplyDelete
  11. Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about

    ReplyDelete
  12. Very awesome!!! When I seek for this I found this website at the top of all blogs in search engine.
    ExcelR Data Analytics courses

    ReplyDelete
  13. Very nice blog here and thanks for post it.. Keep blogging...
    ExcelR data science training

    ReplyDelete
  14. Attend The Data Analytics Courses Online From ExcelR. Practical Data Analytics Courses Online Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses Online.
    ExcelR Data Analytics Courses Online

    ReplyDelete
  15. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    ExcelR data analytics courses

    ReplyDelete
  16. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it. excelr data science

    ReplyDelete
  17. You completed certain reliable points there. I did a search on the subject and found nearly all persons will agree with your blog.
    ExcelR Courses in Business Analytics

    ReplyDelete
  18. This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

    ReplyDelete
  19. Impressive! I finally found great post here. Nice article on data science . It's really a nice experience to read your post. Thanks for sharing your innovative ideas to our vision.
    Data Science Course
    Data Science Course in Marathahalli
    Data Science Course Training in Bangalore

    ReplyDelete
  20. What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.data science course
    360DigiTMG

    ReplyDelete
  21. Attend The Data Science Courses From ExcelR. Practical Data Science Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses.
    Data Science Courses
    Data Science Interview Questions

    ReplyDelete
  22. The information provided on the site is informative. Looking forward more such blogs. Thanks for sharing .
    Artificial Inteligence course in Aurangabad
    AI Course in Aurangabad

    ReplyDelete
  23. Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
    data science course
    360DigiTMG

    ReplyDelete
  24. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data science Interview Questions

    ReplyDelete
  25. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. ExcelR Data Analytics Course Any way I’ll be subscribing to your feed and I hope you post again soon. Big thanks for the use

    ReplyDelete
  26. Study ExcelR Business analytics course where you get a great experience and better knowledge.

    Business analytics course.

    We are located at :

    Location 1:
    ExcelR - Data Science, Data Analytics Course Training in Bangalore
    49, 1st Cross, 27th Main BTM Layout stage 1 Behind Tata Motors Bengaluru, Karnataka 560068
    Phone: 096321 56744
    Hours: Sunday - Saturday 7AM - 11PM
    Google Map link : Data Science Courses

    ReplyDelete
  27. Attend The Artificial Intelligence course From ExcelR. Practical Artificial Intelligence course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Artificial Intelligence course.
    Artificial Intelligence Course

    ReplyDelete
  28. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  29. Know more about Data Analytics
    Cool stuff you have, and you keep overhaul every one of us.

    ReplyDelete
  30. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  31. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspried me to read more. keep it up.
    Correlation vs Covariance

    ReplyDelete
  32. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now I am a bit clear. I’ve bookmarked your site. keep us updated. <a href="https://www.excelr.com/business-analytics-training-in-pune/”> ExcelR Courses </a>

    ReplyDelete
  33. I exploit solely premium quality products -- you will observe these individuals on: בניית וילה

    ReplyDelete
  34. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  35. Data science training allows you to get a job with one of top companies. Today, many companies hire data science pros, such as Google, eBay, PayPal, Facebook, Apple, Microsoft and Amazon, just to name a few. 360DigiTMG data science course in hyderabad

    ReplyDelete
  36. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.

    data science interview questions

    ReplyDelete
  37. This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data science certification

    ReplyDelete
  38. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

    Data Science In Banglore With Placements
    Data Science Course In Bangalore
    Data Science Training In Bangalore
    Best Data Science Courses In Bangalore
    Data Science Institute In Bangalore

    Thank you..

    ReplyDelete
  39. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  40. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  41. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  42. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful.

    business analytics certification

    ReplyDelete
  43. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.
    Data Analyst Course

    ReplyDelete
  44. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  45. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  46. Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.
    Data Science Course Training in Hyderabad

    ReplyDelete
  47. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  48. Attend The Data Science Courses Bangalore From ExcelR. Practical Data Science Courses Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses Bangalore.
    Data Science Courses Bangalore

    ReplyDelete
  49. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete

Popular Posts