Make your own free website on Tripod.com
Venky's World
Grain is the Grail
Home
About Me
Favorite Links
Contact Me
Why I Started This?
Welcome to the DW World
OLTP & OLAP
How to Start Isolating data
Grain is the Grail
Schemas
Normalization
Facts & Dimensions
Oh Yeah, Its ETL Time
SCD
SCD, is this Table/Column dependent
Handling Error Loads

Grain is the Grail

 

Every warehouse depends on the granularity. What is granularity? Granularity is the most fundamental part of DW implementation. The entire scope/functioning of a DW depends on the granularity. Granularity means the lowest level of information the DW will represent. Sounds confusing? Well, this would get clearer as we move on. Granularity determines the data volume, level of information in a warehouse. Lets consider an example. Consider an organization consisting of 15 departments in each spread across multiple locations having many numbers of employees. Also assume that an employee can work in only one department. Lets chart out the relationship chart for this.

Entity1

Entity2

Relationship

Organization

Location

One to Many

Location

Department

One to Many

Department

Employee

One to Many

 

For this case the E-R diagram would be like the following

erdia.jpg

 In the above diagram, moving left to right depicts the relationship between organization, location, department & employees in that order respectively. From this diagram we can say that Organization is at a higher grain than the location, department & employees. The location is at a higher grain than that of the department & employees. The department is at a higher grain than that of the employees. Hence, if want a DW for tracking employee related data comprising of all the organization, location & department then our DW is said to be at the lowest grain. The more lower the grain is, more is the data volume. But more is the information. There will always be a trade off between data volume & granularity. As we move towards higher grain the data gets to be more summarized. How is that? Consider we are storing expenses incurred by each employee in a database. Now while considering the expenses incurred by each department it would be the summation of expenses of each employee. The same can be said with respect to other higher grains.
            Identifying the grain of a warehouse is very important. If a proper grain is not defined then the DW is bound to fail sometime down the line.

Attitude,Not Aptitude, Determines Altitude