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.
Identify the business process - Business process, not department.
How many jerseys did we sell?
How many parts do we manufacture?
What is the value of our client's portfolios?
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......"
"...a jersey purchased by a customer from a given day."
"...how many products and product attributes (size, color, etc.) are currently in stock at a store/warehouse on a given day."
"...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."
"...a part sold to a retailer on a given day."
"...a part ordered by a retailer on a given day."
"...how many parts are currently in stock on a given day."
"...the current value of one client's portfolio on a given day."
"...the daily value of a specific stock on a given day."
Choose Dimensions - Who, What, Where, and When? The best attributes are descriptive. Use small data types. Here are some examples:
Customers/Clients
Employees
Products
ProductAttributes
Departments
Shifts
Portfolios
Stocks
Retailers
Stores
Warehouses
Date
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:
Product Sales
Parts In Stock
Parts Sold
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