I’m sure you have come across terms like Facts, Measures, Dimensions, Attribute hierarchies, etc. We will define these terms and see how a dimensional model is built.
A GRAIN is the smallest piece of information that needs to be presented in the warehouse solution.It is the most atomic piece of information in the data warehouse. It is best to give an example: For retail systems, a grain can be the items per transaction per customer. This is the most exact information that the warehouse will be able to provide. In defining the grain we define the fact too.
A FACT or MEASURE is something of interest. Something that we need to measure to give us an indicator to base our business decisions on. It is the most atomic piece of information used for aggregations. For example, for an inventory system this would be the items in stock; so we count (aggregate) the items to give us number of items. The fact here is an individual item, we measure the number of items. Another example can be . The piece of information of interest is customer and thus we would aggregate on this to find he number of customers enrolled or number of customers who shop at a particular store.
A DIMENSION is the information that we want to group the facts by. In the case of the inventory system, we can group the items by the type of item; eg. food, furniture, electronics, clothes, etc. For the fact customers we group them by the type of membership they hold or their city of residence. A dimension holds information related to the facts we measure.
Every dimension has attributes. The attribute usually is a property of the grouping or a sub category. For the dimension of geography, possible attributes are: continent, country, state, city. This information is grouped and described as “geography”.
This should give you an idea of what the terms mean. Once you understand these terms, you are ready to translate the business requirements into a warehouse model. In the next post we will look at requirements gathering and how to get the business users to narrow down their high level objectives.