Figure 7-7: The SALE fact-dimensional structure denormalized into (Web site layout)
Figure 7-7: The SALE fact-dimensional structure denormalized into a star schema. What does all this prove? Not much, you might say. On the contrary, two things are achieved by using fact-dimensional structures and star schemas: . Figure 7-1 shows a highly normalized table structure, useful for high-concurrency, precision record-searching databases (an OLTP database). Replacing this structure with a fact-dimensional structure (as shown in Figure 7-2, Figure 7-4, and Figure 7-6) reduces the number of tables. As you already know, reducing the number tables is critical to SQL query performance. Data warehouses consist of large quantities of data, batch updates, and incredibly complex queries. The fewer tables, the better. It just makes things so much easier with fewer tables, especially because there is so much data. The following code is a SQL join query for the snowflake schema, joining all nine tables in the snowflake schema shown in Figure 7-5. SELECT * FROM SALE SAL JOIN AUTHOR AUT JOIN CUSTOMER CUS JOIN SHIPPER SHP JOIN SUBJECT SUB JOIN CATEGORY CAT JOIN BOOK BOO JOIN PUBLISHER PBS JOIN PUBLICATION PBL WHERE … GROUP BY … ORDER BY … ; . If the SALE fact table has 1 million records, and all dimensions contain 10 records each, a Cartesian product would return 106 multiplied by 109 records. That makes for 1015 records. That is a lot of records for any CPU to process. Book Customer Shipper Author Subject One-To-Many Relationship Sale 181 Understanding Data Warehouse Database Modeling
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.