• A data warehouse vaguely combines some of the aspects of the relational model and the object model.
• The relational model breaks data into mathematically precise, detailed structures. These structures are often difficult for the uninitiated to decipher from a business operational perspective. The object model also breaks everything down, not for the purposes of granularity, but to reduce the complexity of the whole. The process of breaking things into their simplest parts is a conceptual thing.
• The result from the point of view of the uninitiated layman is startling, though. Where a relational database model appears cryptic and over-detailed, an object database model starts to look like a real-world picture of data, from the perspective of someone using databases and applications, as opposed to someone building the stuff. This perspective is apparent if you take another brief look at next Figure. The names of structures in the object model (the left side of Figure 6-16) are much clearer than the names of structures in the equivalent relational model (the right side of Figure 6-16). The relational model in Figure 6-16 is abstract and cryptic. The object model, on the other hand, contains a structural break down of data, as an author, publisher, or a book retailer would understand books. The point is that a data warehouse database model combines some aspects of the relational database model in its application of normalization, and the more easily understandable visual aspects of the object database model. There is a big difference with data warehouses, though, in that they are most effective when severely denormalized. The reason why data warehouse metadata (table) structures look more real-world is essentially as a result of severe denormalization, and has nothing to do with the object database model. The comparison is interesting, though, because it also helps to explain one part of why application SDKs are now largely object-oriented in approach. Objects are easier to understand because they mimic the real world much more closely than the set hierarchical structures of the relational model of data.
• So, where does the data warehouse model really fit into the grand scheme of things? The truth is it doesn’t. The data warehouse database model is essentially an animal unto itself, with very little relationship to either the relational or the object database models:
• Data warehouses and the relational model: The relational model is too granular. The relational model introduces granularity by removing duplication. The result is a database model nearly always highly effective for front-end application performance and OLTP databases. OLTP databases involve small amounts of data accessed frequently and concurrently by many users. On the other hand, data warehouses require throughput of huge amounts of data by a small user population. OLTP databases (the relational database model) need lightning-quick response to many people and small chunks of data. Data warehouses perform enormous amounts of I/O activity, over millions (if not billions) of records. It is acceptable for data warehouse reports to take hours to run.
• Data warehouses and the object model: The object model is even more granular than the relational model, just in a different way, even if it does appear more realistic to the naked eye. Highly granular normalized relations (the relational model), or uniquely autonomous objects (the object model), can cause serious inefficiencies in a data warehouse. Data warehouses perform lots of big queries, with lots of data in many records in many tables. The fewer tables there are in a data warehouse, the better! Query joins on large sets of records can become completely unmanageable and even totally useless.

The heart of the data warehouse database model, different to both the relational and object models, vaguely combines aspects of both relations and objects. A data warehouse database is effectively a highly denormalized structure, consisting of preferably only two distinct hierarchical layers. A central table contains highly denormalized transactional data. The second layer contains referential static data. This data warehouse database model is known as the dimensional model or the fact-dimensional model. A data warehouse consists of facts and dimensions. Facts and dimensions are types of tables. Each data warehouse structure consists of a single fact table, surrounded by multiple dimensions. It is possible to have more than a single fact table but essentially different fact tables are likely to be related to the same set of dimension tables. Therefore, different fact tables represent an entire new set of tables, or a new modular structure. That entire new set of tables is essentially another subset data warehouse, also known as a data mart.

The term data mart is used to describe a self-contained, subsection of a data warehouse. Fact and dimension tables contain different types of data. Where dimensions contain static data, and facts contain transactional data. Transactional data is the record of a company’s activities, such as invoices sent to its customers. The dimensions describe the facts, such as the customer’s name and address.

For example, an online retailer selling thousands of items per day could ship 20 items to each customer every year. Over the course of a number of years, each customer might be shipped hundreds of separate items.

The detail of a customer (such as the address) is static information. Static information does not change very often. The customer is a dimension. The customer dimension describes the fact, or the transactions (the invoices or details of every item shipped over many years). The active database (OLTP database) would likely have all records of transactions deleted from its active files on a periodical basis (annually, for example). Annual historical data could be archived into a data warehouse. The data warehouse data can then be used for forecasting (making guesses as to what customers might purchase over the next 10 years). The result of all this mishmash of complicated activities and other wonderful stuff is a table structure looking similar to that shown in Figure 6-17. Figure 6-17 shows a pseudo-table structure, describing graphically what is known as a star schema (a single fact table surrounded by a group of dimensions). Data warehouse database models are ideally made up of data mart, subset star schemas. Effectively, different schemas are also known as data marts. Each data mart is a single fact table, all linked to shared dimension tables (not necessarily all the same dimensions, but it is possible). Each fact-dimensional structure is a star schema (a data mart). Each star schema is likely to contain data for a different department of a company, or a different region (however a company may decide to split its data).

Some data warehouses are built using 3NF table structures, or even combine normalized structures with fact-dimensional structures in the same database.

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/8a6c5a7b-e871-4c23-be80-e304b3a937d7/Untitled

The Origin of Data Warehouses

Data warehouses were originally devised because existing databases were being subjected to conflicting requirements. Conflict arose between operational use and decision-support requirements as follows:

• Operational use: Operational use of a database requires a precise, accurate, and instant picture of data in a database. This includes all day-to-day operations in the functional running of a business. When a customer comes through the door, asks for a specific part, for a specific automobile, for a specific year, the part is searched for in a database. After the customer makes the purchase, they are invoiced. When the customer pays, the transaction is processed through bank accounts, and otherwise. All of this is operational-type activity. Response is instantaneous (or as near to instantaneous as possible) and it is all company to customer-direct trading activity. The operations aspect of a company is the heart of its business.
• Decision-suppor use: Where operational use divides data based on business function, decision support requires division of database data based on subject matter. Operational use requires access to specific items such as a specific part for a specific automobile, for a specific customer. Decision-support use requirements might be a summary of which parts were ordered on which dates, not necessarily by whom. A decision-support database presents reports, such as all parts in stock, plus all parts ordered, over the period of a whole year. The result could be a projection of when new parts should be ordered. The report allows company employees to cater for restocking of popular items.

There is a big difference between requirements for operational and decision-support databases. Operational systems require instant responses on small amounts of information. Decision-support systems need access to large amounts of data (large portions of a database), allowing for good all-round estimates as to future prospects for a company. The invention of data warehouses was inevitable to reduce conflict between small transactional (OLTP databases) and large historical analytical reporting requirements (data warehouses).

The demands of the modern global economy and the Internet dictate that end user operational applications are required to be active 24/7, 365 days a year. There is no window for any type of batch activity because when people are asleep in Europe, others are awake down under in Australia. The global economy requires instant and acceptable servicing of the needs of a global user population.

In reality, the most significant difference between OLTP databases and data warehouses extends all the way down to the hardware layer. OLTP databases need highly efficient sharing of critical resources such as onboard memory (RAM), and have very small I/O requirements. Data warehouses are completely opposite. Data warehouses can consume large portions of RAM by transferring between disk and memory, in detriment to an OLTP database running on the same machine. Where OLTP databases need resource sharing, data warehouses need to hog those resources for extended periods of time. So, a data warehouse hogs machine resources. An OLTP database attempts to share those same resources. It is likely to have unacceptable response times because of a lack of basic I/O resources for both database types. The result, therefore, is a requirement for a complete separation between operational (OLTP) and decision-support (data warehouse) activity. This is why data warehouses exist!

The Relational Database Model and Data Warehouses