Basics of Fact Table Granularity

Is it imperative that you learn about the basics of fact table granularity? To answer that, you must ask yourself: Are you embarking on a data warehousing project for your company or organization? If you answered yes, then it is imperative that you read about the basics of fact table granularity so that you can make important decisions for the design of your data warehouse. Understanding the basics of fact table granularity will affect the effectiveness of your data warehouse in the long run.

 

Fact Table Granularity Explained

 

One of the first basics of fact table granularity that you must understand is its meaning. Fact table granularity refers to the level of intricacy of detail that a single fact table contains. A higher granularity means that there are a higher number of details in that one table, while a lower granularity means less detail. For someone who is just learning the basics of fact table granularity, the easiest way to see how high or low the granularity of a fact table that your company’s database has is by looking at the number of rows in it. A higher number of rows in a table usually means a higher granularity. Granularity can also mean the lowest level of depth in a set of data for a fact table. For example, the grain for time can be year, quarter, month, day, or hour, with the year being the highest level, in which there is low fact table granularity.

 

 

Granularity in Fact Table Design

 

Understanding granularity is a must for you if you are involved in designing an efficient, functional fact table for a business or organization. Whether you are the designer or the  project supervisor, your decisions based on the basics of fact table granularity will have an impact on how your fact table will work. This is because granularity is one of the most essential steps in designing a fact table, next to identifying analysis points for the business, measures, dimensions, and locations. Knowing how to properly choose granularity for a table will help you identify enough data for your needs without bogging down your system with unnecessary data.

 

Choosing the Level of Granularity

 

Knowing how deep you will go in terms of grain level for your fact table is among the basics of fact table granularity. Choosing to stay in the high levels of granularity will mean that you will have very limited data available to you. For example, choosing to stay in the year, one of the shallowest levels of granularity for time, in your fact table means that there is just one data being added for every year. This brings almost no knowledge to you, and is inefficient in helping you analyze or drill down into the data. Meanwhile, going too deep, in the level of hours, for example, for the fact table might mean that you will have 24 inputs 365 days a year, multiplied by the various other items for which the fact table is programmed. That can mean a lot of useless reports that end up bogging down your system as well as your analysis. There will be too much data to sift through and you will end up not optimizing their use and value. In many instances, a medium granularity is best to be used for a company or organization’s uses. A daily input is manageable in terms of size and analysis capabilities but it also gives enough detail from which you can extrapolate patterns, trends, and conclusions. Deciding on the level of granularity is a skill that must be learned over time and by gaining an understanding of your business processes.