top of page
  • Writer's pictureJeff Taylor

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.

  2. How many jerseys did we sell?

  3. How many parts do we manufacture?

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

  5. 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......"

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

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

  8. "...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."

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

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

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

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

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

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

  15. Customers/Clients

  16. Employees

  17. Products

  18. ProductAttributes

  19. Departments

  20. Shifts

  21. Portfolios

  22. Stocks

  23. Retailers

  24. Stores

  25. Warehouses

  26. Date

  27. 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:

  28. Product Sales

  29. Parts In Stock

  30. Parts Sold

  31. 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

bottom of page