A Cartesian product is a worse-case scenario. .
A Cartesian product is a worse-case scenario. . Now look at the next query. SELECT * FROM SALE SAL JOIN AUTHOR AUT JOIN CUSTOMER CUS JOIN SHIPPER SHP JOIN SUBJECT SUB JOIN BOOK BOO WHERE … GROUP BY … ORDER BY … ; . Using the star schema from Figure 7-7, assuming the same number of records, a join occurs between one fact table and six dimensional tables. That is a Cartesian product of 106 multiple by 106, resulting in 1012 records returned. The difference between 1012 and 1015 is three decimals. Three decimals is not just three zeroes and thus 1,000 records. The difference is actually 1,000,000,000,000,000 1,000,000,000,000 = 999,000,000,000,000. That is effectively just a little less than 1015. The difference between six dimensions and nine dimensions is more or less infinite, from the perspective of counting all those zeros. Fewer dimensions make for faster queries. That s why it is so essential to denormalize snowflake schemas into star schemas. . Take another quick glance at the snowflake schema in Figure 7-4 and Figure 7-5. Then examine the equivalent denormalized star schema in Figure 7-6 and Figure 7-7. Now put yourself into the shoes of a hustled, harried and very busy executive trying to get a quick report. Think as an end-user, one only interested in results. Which diagram is easier to decipher as to content and meaning? The diagram in Figure 7-7 is more complex than the diagram in Figure 7-5? After all, being an end-user, you are probably not too interested in understanding the complexities of how to build SQL join queries. You have bigger fish to fry. The point is this: The less complex the table structure, the easier it will be to use. This is because a star schema is more representative of the real world than a snowflake schema. Look at it this way. A snowflake schema is more deeply normalized than a star schema, and, therefore, by definition more mathematical. Something more mathematical is generally of more use to a mathematician than it is to an executive manager. The executive is trying to get a quick overall impression of whether his company will sell more cans of lima beans, or more cans of string beans, over the course of the next ten years. If you are a computer programmer, you will quite probably not agree with this analogy. That tells us the very basics of data warehouse database modeling. How can a data warehouse database model be constructed? How to Build a Data Warehouse Database Model Now you know how to build star schemas for data warehouse database models. As you can see, a star schema is quite different from a standard relational database model (Figure 7-1). The next step is to examine the process, or the steps, by which a data warehouse database model can be built. 182 Chapter 7
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.