Zansa › Answers › Integration and BI tools
What does 'grain' mean in a data model and why does it cause double counting?
Grain is what a single row in a table represents, such as one order, one order line or one payment. Double counting happens when you join tables of different grain, like orders to lines, and then sum an order-level value that has been repeated on every line.
Example: an order has a shipping fee of 10 and three lines. Join orders to lines and sum shipping, and you get 30 instead of 10.
How to avoid it:
- Write down the grain of every table.
- Aggregate to a common grain before joining.
- Keep order-level amounts in the order table and line amounts in the line table.
- Test that totals after joining equal totals before joining.
A simple test comparing row counts and sums before and after a join catches many of these errors.
Want this applied to your business? Zansa builds dashboards and forecasts for small and mid-sized companies worldwide. Start with a free intro call.
Book a call