This link has been bookmarked by 25 people . It was first bookmarked on 23 Jul 2006, by Jeff Tucker.
-
30 Jul 11
-
25 Jun 08
-
18 Mar 08
-
Dimension tables answer the “why” portion of our question: how do we want to slice the data
-
For example, we almost always want to view data by time.
-
-
12 Dec 07
Andrew GilmartinCreating a Star Schema Database is one of the most important, and sometimes the final, step in creating a data warehouse. Given how important this process is to our data warehouse, it is important to understand how me move from a standard, on-line transac
-
10 Oct 07
-
OLTP allows us to minimize data entry
-
records take up much less space when we have a normalized table structure. This means that the table is smaller, which helps speed inserts, updates, and deletes
-
Since one of our design goals to speed transactions is to minimize the number of indexes, we are limiting ourselves when it comes to doing data retrieval. That is why we look at creating two separate database structures: an OLTP system for transactions, and an OLAP system for data retrieval
-
the data in an OLTP system is not user friendly
-
we usually want to see some sort of aggregated data. These data are called measures. These measures are numeric values that are measurable and additive
-
Therefore, in designing a star schema, our first order of business is usually to determine what we want to see (our measures) and how we want to see it (our dimensions).
-
all dimension tables should have a single-field primary key. This key is often just an identity column, consisting of an automatically incrementing number. The value of the primary key is meaningless; our information is stored in the other fields. These other fields contain the full descriptions of what we are after.
-
Notice that both Category and Subcategory are stored in the table and not linked in through joined tables that store the hierarchy information. This hierarchy allows us to perform “drill-down” functions on the data.
-
the hierarchies are contained in the individual dimension tables. No additional tables are needed to hold hierarchical information.
-
the dimension tables have the hierarchies broken out into separate tables. This is a more normalized structure, but leads to more difficult queries and slower response times.
-
The lower the granularity, the more records you will have in the fact table. The granularity also determines how far you can drill down without returning to the base, transaction-level data.
-
One of the things we do not want to do is store zero values. So, if a product did not sell at a particular store for a particular day, we would not store a zero value. We only store the records that have a value. Therefore, our fact table is often sparsely populated.
-
However, if we simply go in and update the table, and then try to look at historical sales for a region, the numbers will not be accurate. By simply updating the region for a store, our total sales for that region will not be historically accurate
-
In some cases, we do not care. In fact, we want to see what the sales would have been had this store been in that other region in prior years. More often, however, we do not want to change the historical data.
-
Some people store higher-level aggregations in the database by pre-calculating them and storing them in the database.
-
-
22 Aug 07
-
14 Aug 07
-
14 Jun 07
-
02 Apr 07
-
20 Feb 07
-
Designing the Star Schema Database
-
-
18 Sep 06
-
29 Jun 06
-
17 May 06
-
25 Mar 05
-
09 May 04
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.