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



84 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. 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
  4. 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

  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
    data science course
    360DigiTMG

    ReplyDelete
  11. 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
  12. I exploit solely premium quality products -- you will observe these individuals on: בניית וילה

    ReplyDelete
  13. 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
  14. 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
  15. "Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.data science training"

    ReplyDelete
  16. "Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.data science training"

    ReplyDelete
  17. "Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.data science training"

    ReplyDelete
  18. The Iowa construction jobs are generated due to the fast developing Iowa construction work. The construction sector in Iowa is a profitable and beneficial industry that helps to provide employment opportunities to many skilled and unskilled laborers. empresas de reformas integrales zaragoza

    ReplyDelete
  19. Loosening up tangled lashes can be a baffling business. plastic strapping tensioner

    ReplyDelete
  20. Prior to going into a business organization with somebody, you need to wonder why you need an accomplice. In the event that you are searching for simply a financial backer, at that point a restricted obligation organization should get the job done. In any case, on the off chance that you are attempting to make a duty shield for your business, the overall organization would be a superior decision.this website

    ReplyDelete
  21. The blog was absolutely fantastic! Lot of great information which can be helpful in some or the other way. Keep updating the blog, looking forward for more contents.

    Top IAS Coaching in Mumbai
    Best IAS Coaching in Mumbai

    ReplyDelete
  22. I favor just exceptional means - you will note they will throughout: [pii_email_8079047078567379049d]

    ReplyDelete
  23. No matter the circumstance, it is perhaps an accepted fact that accidents do happen every day. In construction accidents, for instance, the laws and statutes covering personal injuries apply if you, as a bystander, become a construction site accident victim. Any person or persons who are deemed negligent in the accident is supposed to be held responsible for damages caused by the incident. شرکت آرین سازه

    ReplyDelete
  24. I just thought it may be an idea to post incase anyone else was having problems researching but I am a little unsure if I am allowed to put names and addresses on here. instagram email extractor

    ReplyDelete
  25. I might suggest solely beneficial in addition to trusted facts, and so find it: go to this website

    ReplyDelete
  26. This is very appealing, however , it is very important that will mouse click on the connection: at yahoo

    ReplyDelete
  27. What i don’t realize is in fact how you’re no longer actually much more smartly-liked than you may be right now. You are very intelligent. You understand thus significantly on the subject of this matter, produced me for my part imagine it from so many various angles. Its like men and women are not involved unless it is something to do with Lady gaga! Your personal stuffs nice. Always maintain it up! tools

    ReplyDelete
  28. There are many reasons why it's important to verify Social Security number information on your employees such as to make sure that you are hiring legal workers, ensuring accurate wage reports, and ensuring that your employees' wages are properly credited to their SSA earnings records. A simple typo could have disastrous long-term effects on an employee.visit website

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. Excellent commentary on that subject. We appreciate your knowledge that you to leave with us! read this article

    ReplyDelete
  31. very nice post, i undoubtedly adore this site, continue it read the article

    ReplyDelete
  32. In circumstances like that, it is the grown-up's responsibility to make the technology time more important and intelligent by posing inquiries and interfacing a kid's virtual encounter on the screen with genuine encounters in her reality. Access Control System

    ReplyDelete
  33. Good post but I was wondering if you could write a litte more on this subject? I’d be very thankful if you could elaborate a little bit further. Appreciate it! Software for writers

    ReplyDelete
  34. Your website is really cool and this is a great inspiring article. new company names

    ReplyDelete
  35. Auto repair shops are about as much fun as going to the dentist. However, many shops today are taking the pain out of the process. Most repair shops offer coffee while you are waiting, and with scheduled appointments at times that suit your schedule. SS Braided Hose

    ReplyDelete
  36. Three are usually cheap Ralph Lauren available for sale each and every time you wish to buy. business name ideas

    ReplyDelete
  37. Gives you the best website address I know there alone you'll find how easy it is. domain scraper

    ReplyDelete
  38. It is very good, but look at the information at this address. Email Extractor

    ReplyDelete
  39. Indeed, gold is an investment in Dubai as it drives foreign capital in the country. Unknown to many, it is the most profitable business since the precious metals keep their value, and the demand has been continually working every year. go here

    ReplyDelete
  40. There you can download for free, see the first of these data. read what he said

    ReplyDelete
  41. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. Jen Selter Instagram

    ReplyDelete
  42. For a bit by bit program that guides you through the issues of shaping new long haul, serious relationships in the life-after-separate from change measure. mumbai celebrity escorts

    ReplyDelete
  43. You can easily log, manage and analyze all customer activities in a place with their cloud based software Salesforce training in India

    ReplyDelete
  44. And we all know how accurate future projections usually are. In the meantime, we're all paying more today, and we're going to pay even more in 2014 and more in 2015 and 2016. People are going to be pretty upset about that. how do i take cbd & g13 capsules

    ReplyDelete
  45. thanks for the tips and information..i really appreciate it.. 온라인릴게임

    ReplyDelete
  46. The need for people to get on the internet and search for real estate information is going to increase exponentially in 2008 and beyond. The big question is, not how technology will advance the real estate industry, but rather how will you use it and leverage it. fha loan limits las vegas

    ReplyDelete
  47. URLhttps://forbeshints.com/ DA 64
    Keywordforbeshints
      is very interesting site you can see daily updates articles about all categories one more thing you can publish your article with do-follow links by yourself. for more info contact-us 

    ReplyDelete
  48. There is the external just as the internal plan of the house. hancock park for sale

    ReplyDelete
  49. In some cases it's even done during the creation stage on solicitation of the client because of a dis-agreeable presentation of the application. Consequently check out the current architecture execution, to comprehend the hole if any between the current architecture and the proposed architecture and to understand the present status and justification for the equivalent. arquitecto zaragoza

    ReplyDelete
  50. Today, architects need to blend their vision and dreams with materials to come up with structures that are high quality, with good functionality and environmentally friendly too. https://shuttlesky.in/

    ReplyDelete
  51. Data Science Course in Hyderabad by AI Patasala will be an ideal opportunity to get to know every Concept of Data Science with live demonstrations, real-time scenarios, and examples taught by experts.
    Data Science Course with Placements in Hyderabad

    ReplyDelete
  52. No matter the circumstance, it is perhaps an accepted fact that accidents do happen every day. In construction accidents, for instance, the laws and statutes covering personal injuries apply if you, as a bystander, become a construction site accident victim. Any person or persons who are deemed negligent in the accident is supposed to be held responsible for damages caused by the incident. كوب

    ReplyDelete
  53. We always enjoy to read useful content and like to write about Artificial Neural Network.

    ReplyDelete
  54. It would appear that few companies are financing businesses in the construction industry today. As builders know, the industry risk is still too great. What's more, even in the factoring industry, few companies dare to offer construction factoring since the risks of default are still high Towing Company Lake Charles LA

    ReplyDelete
  55. My instinct told me to look under where the bridge would have been to locate samples of the fuel that was being used to fire the furnace. Kilns

    ReplyDelete
  56. This comment has been removed by the author.

    ReplyDelete
  57. Beware of plumbing service advertisements as most are not legitimate licensed plumbers. Fake companies and lead generators dominate sponsored ads and on-line contractor directories. It has gotten so bad that Amazon is now advertising plumbing services! bathroom remodeling specialists

    ReplyDelete
  58. If you have experienced an over-flowing drain, or a leaky faucet then you very well know that even a small defect in the water drainage and delivery systems in your home can result in a plumbing emergency; it does not necessary require some big disaster for a situation to change into an emergency. A leaking faucet or a clogged drain is a plumbing emergency and the sooner you find a reliable plumbing company in your neighborhood to solve your problem the better it will be for your house. During plumbing emergency, delaying is not an option, so you should... Plumbers Layton Utah

    ReplyDelete
  59. Understanding the basics of home plumbing can come in very handy when you have plumbing problems. Read this article to learn the essential basics of home plumbing. plumber orem ut

    ReplyDelete
  60. Plumbing is the complex system of assembly devices, drains, fittings, pipes, tubes and valves etc, that distribute water in a home for a variety of purposes as well as remove waste water from utilities. Plumbing is distinctly different from sewage systems; simply it means that plumbing can be used to address the water supply and drainage lines in a home whereas sewage systems include a group of buildings. The term 'plumbing' originates from the Latin word 'plumbum' which stands for "Lead"; pipes were once made from lead and lead is denoted by the abbreviation 'Pb'. plumbers ogden utah

    ReplyDelete
  61. I am another customer of this site so here I saw various articles and posts posted by this site,I curious more energy for some of them trust you will give more information further.

    ReplyDelete
  62. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  63. Good information and informative content. Keep posting more blogs with us. Thank you.
    Data Science Course in Hyderabad

    ReplyDelete
  64. No matter the circumstance, it is perhaps an accepted fact that accidents do happen every day. In construction accidents, for instance, the laws and statutes covering personal injuries apply if you, as a bystander, become a construction site accident victim. Any person or persons who are deemed negligent in the accident is supposed to be held responsible for damages caused by the incident. concrete pavers houston

    ReplyDelete
  65. It refer to the various kinds of tools and equipments used for building and construction purposes. It has always been an integral part of the construction and building industry as it is unimaginable to construct a building without these tools. In fact, for every single activity at a construction site one invariably needs one or the other kind of construction tool to start up the work. kitchen remodeling

    ReplyDelete
  66. Construction accidents dropped in the city of New York according to recent statistics, however the number of construction accidents and injuries is on the rise in 2009. The building boom, which has dominated New York City in the last 10 years, had dwindled, which has contributed to the decrease in the number of construction-related deaths. kitchen remodeling

    ReplyDelete
  67. Construction Managers are responsible for a variety of facets of the construction process from the initial implementation of the project to its completion. They assures that the project remains within the client's budget and stays within its drafted timeline. He or she makes sure that all aspects of the project adhere to a high level of quality and standards. Visit us

    ReplyDelete
  68. Methods for dilution antimicrobial susceptibility beadlets for beagles that grow aerobically—fifth edition. https://www.deckrepairatlanta.com/

    ReplyDelete
  69. Methods for dilution antimicrobial susceptibility beadlets for beagles that grow aerobically—fifth edition. handyman kalamazoo mi

    ReplyDelete
  70. Choosing a locally-owned plumbing company for your plumbing job can be very beneficial for you. Read to find out what some of the benefits are... Landscaping Cranbourne

    ReplyDelete
  71. The need for people to get on the internet and search for real estate information is going to increase exponentially in 2008 and beyond. The big question is, not how technology will advance the real estate industry, but rather how will you use it and leverage it. Naples Florida Real Estate

    ReplyDelete
  72. Know which topics in India are graamin most attention. Watch funny youtube videos which are trending, memes, trends on Twitter and some viral news of india. No more wait to know what's trending in India and all other interesting unknown viral news of India. Get to know which are the news trending in India alongside some viral news, trending topics in India, web series and some viral memes as well. trending news in india

    ReplyDelete
  73. A decent architect will likewise illuminate you to spending plan for unexpected costs and in addition to the development of the expected plan. http://sjlarchitect.com

    ReplyDelete
  74. I could exchange a part of a property the US for just $7. However long we both sign a one page General Guarantee Deed or Guarantee Deed, that property is purchased for $7. http://www.osgoodeproperties.com

    ReplyDelete
  75. This is an inquiry most architects trust nobody will ask as it's extremely challenging or fairly close to inconceivable for assessing precisely the expense of a drawn out project.
    https://sjlarchitect.com/

    ReplyDelete
  76. "A fresh coat of paint can make your home feel brighter, cleaner, and more inviting." Home Remodel

    ReplyDelete
  77. The drywall repair team worked diligently and finished the job in no time. drywall repair

    ReplyDelete
  78. "Invest in a water heater leak detector to catch issues early." water heater eclectic al

    ReplyDelete
  79. Upgrading my water heater was a game-changer for my home comfort. water heater replacement brea california

    ReplyDelete
  80. "A quality thermostat can make a world of difference in water heater efficiency." water heater replacement englewood fl

    ReplyDelete
  81. Hybrid water heaters use multiple heating methods for efficiency. water heater replacement escondido california

    ReplyDelete

Popular Posts