Web hosting faq - Types of Dimension Tables The data warehouse database
Types of Dimension Tables The data warehouse database models for the REVIEW and SALE tables, shown previously in this chapter, are actually inadequate. Many data warehouse databases have standard requirements based on how end-users need to analyze data. Typical additions to data warehouse databases are dimensions such as dates, locations, and products. These extra dimensions can be built from most types of transactional data. Any types of transactions involving products or services are usually dated, such as orders, invoices, or payments. Therefore, every transaction can be placed (or dated) into specific periods. Locations can usually be assessed either generally or in detail from customers, suppliers, shippers, and other company s that contact is had with. The REVIEW and SALE fact tables shown in Figure 7-2 and Figure 7-4 both contain date fields (REVIEW.REVIEW_DATE and SALE.SALE_DATE). Adate or timestamp dimension might look something similar to that shown in Figure 7-8. Date fields in the tables would be replaced with identifiers as shown in Figure 7-9. Note how the date values in the TIME table, as shown in Figure 7-8, are reconstituted in a specific time window period, such as MONTH, QUARTER, and YEAR. Figure 7-8: A time dimension entity. Figure 7-9 shows fact tables for both book reviews and sales, where the date fields have been replaced with foreign key links to the TIME table. The result of this is that facts can be analyzed by month, quarter, and year. In this case, removing the date fields from the fact tables has actually decreased the detail level. Now data can only be analyzed by month. Months are the lowest level of detail. To retain the ability to analyze on a daily basis, or with further detail such as hours and minutes, either the TIME table should be expanded to accommodate more detail periods, or the date fields should be retained in the fact tables. Another commonly used dimension involves locations, which can be states, cities, countries, continents, regions, and others. Location details for review and sale facts can be gleaned from customer and shipper address details. The resulting LOCATION table would look something like that shown in Figure 7-10. Some silly fields have been added just to give this conversation a little color. The equivalent star schema changes are shown in Figure 7-11. Time time_id month quarter year 184 Chapter 7
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.