Dimensions that you want to analyze the data used when a volume, such as you to analyze product sales, you can choose by category to be analyzed, or analyzed by region, so that the press constitutes a dimension of analysis .. . The preceding example can have two dimensions: type and region. In addition, each dimension can also have sub-dimensions (called attributes), such categories can have sub-type, product names and other attributes. Here are two common dimension table structure:
Product dimension table: Prod_id, Product_Name, Category, Color, Size, Price
Time dimension table: TimeKey, Season, Year, Month, Date
The fact sheet is based on data aggregated to generate the results of a dimension table. Example of its structure is as follows:
Sales fact table: Prod_id (reference product dimension table), TimeKey (reference time dimension table), SalesAmount (total sales, in monetary terms), Unit (sales)
Above the table is present in the data warehouse. From here you can see it has several characteristics:
1 large dimension table redundant, mainly because of the dimension is not in general (as opposed to is the fact table), while the redundant dimension table fact table can save a lot of space.
2 fact tables are generally large, if the ordinary methods of inquiry, then, the general made the time to get the results we can not accept. So it is generally for some special treatment. Such as SQL Server 2005 will be on the fact table, such as pre-generation process and so on.
3 dimension table's primary key value is generally a sign to take integer column types, so is the fact table in order to save storage space.