How To Identify The Grain

Creating a data warehouse can be challenging. Identifying the grain is one of the most important parts of having a quality data warehouse. Before identifying the grain, you must identify the business process or processes. Below are four steps with examples to walk you through creating a data warehouse.

1. Identify the business process - Business process, not department.

   1. How many jerseys did we sell?

   2. How many parts do we manufacture?

   3. What is the value of our client's portfolios?
      

2. Identify the grain - What does one data row represent? Choose the most detailed level of data. Detailed data can be rolled up and grouped later based on the lowest level. Examples: "One row represents......"
   

   1. "...a jersey purchased by a customer from a given day."

   2. "...how many products and product attributes (size, color, etc.) are currently in stock at a store/warehouse on a given day."

   3. "...a part built by one or more parts on a given day in a given shift by one or more employees, in their department, in their warehouse."

   4. "...a part sold to a retailer on a given day."

   5. "...a part ordered by a retailer on a given day."

   6. "...how many parts are currently in stock on a given day."

   7. "...the current value of one client's portfolio on a given day."

   8. "...the daily value of a specific stock on a given day."
      

3. Choose Dimensions - Who, What, Where, and When? The best attributes are descriptive. Use small data types. Here are some examples:
   

   1. Customers/Clients

   2. Employees

   3. Products

   4. ProductAttributes

   5. Departments

   6. Shifts

   7. Portfolios

   8. Stocks

   9. Retailers

   10. Stores

   11. Warehouses

   12. Date
       

4. Choose Measures - How the business measures success. Measures that can be aggregated across all dimensions are best. They are called Additive Measures. Measures that can be aggregated across some dimensions are called Semi-Additive Measures. Measures that cannot be aggregated across any dimension are called Non-Additive Measures. These are usually ratios or other mathematical calculations. Here are some examples:
   

   1. Product Sales

   2. Parts In Stock

   3. Parts Sold

   4. Portfolio

Once you have completed these four steps, you should be well on your way to creating a quality data warehouse and having one source of truth for your company's reporting purposes