Identify the data model grain

Question

I'm currently working on a project of designing and implementing a banking data warehouse. I want to define the data model for the accounting data mart, define the grain and use the star schema to model it. I have been told that we are interested in the transactions of a customer that's registered in a branch for an account .... ( some other dimensions) ..... at a certain date. But they're asking for the DAILY transactions ! My opinion is that it's pointless to have daily transactions in the data warehouse because it would be the exact replica of the transactional database ! This data warehouse will be used to make dashboards my guess is that decision makers aren't intereted in such detailed data. What do you think ?

Thank you.

hard drives are cheap. duplication is good in data warehouses. Always go with the finest grain — Neil McGuigan, Jul 13 '16 at 20:59
@NeilMcGuigan but are we interested in daily data ? I don't think so — yasmine92, Jul 13 '16 at 21:12
You will never think of all questions beforehand in my experience. Always go with the lowest grain possible. — tobi6, Jul 14 '16 at 08:03

score 1 · Accepted Answer · answered Jul 15 '16 at 12:49

Use the day grain for your time dimension and consider the following:

The warehouse not a replica of the transactional database, even though the same information may be available in both. The warehouse is optimized for analysis, it contains all history, it's non-volatile, and it aggregates data along the dimensions.

In your example, the warehouse may have a single row representing many transactions that occurred within a single day, so it doesn't duplicate the grain. It may contain information from five years ago that's been purged from the transactional system. It will be lightning fast to aggregate amounts in a query. It's use will not put a load on your transaction system. Some day it may contain information from another transactional database when your company merges with another company. Or the customer information may be enhanced with data imported from one or more social networks.

The point is, don't balk at having fine-grained data in the warehouse that seems to be redundant to the transactional system. It's useful, and common.

Thank you for your detailed answer it makes things clearer for me — yasmine92, Jul 15 '16 at 13:07

score 0 · Answer 2 · answered Jul 14 '16 at 22:15

0

A principle of dimensional modelling is to always model at the finest grain possible. I'd never think of modelling transactions at anything less than day, and I'd even try for time (although that may be a separate dimension).

answered Jul 14 '16 at 22:15

Ron Dunn

2,971
20
27

I've thought more about and i see what you mean here. Thank you! – yasmine92 Jul 15 '16 at 13:05

Identify the data model grain

2 Answers2