Should I create one or two dimensions?

View previous topic View next topic Go down

Should I create one or two dimensions?

Post  arturopw on Tue Feb 26, 2013 2:09 pm

Hello,

I work for a travel company. In the past I built a data mart for our sales department having a Traveler dimension. I now am building some data marts for our operations department and the same dimension is used but from a slightly different perspective. My instinct is to use the same dimension (as suggested when using the bus matrix) but there are some complexities and I'd like to get some feedback.

I think it's the way to go because the Traveler concept is the same in both the Sales and Operations perspectives. The same person we sold to will be the one traveling. It would facilitate traversing the data marts.

There are two reasons to split into two dimensions. The first is that a small minority of the attributes are shared between systems (though isn't a surprise). The second is complexity.

There complexity is that each system tracks its own Traveler object independently which means the Traveler_id (natural key) from the Sales system is a different value than the one from the Operations system. Furthermore, there are multiple Sales systems and one Operations system (even though I only care about 1 sales system). This means that the Traveler can exist in:

- just my Sales system (Right after the sale. Data integration may take months due to business process.)
- just the Operations system (if sale originated in a different Sales system)
- both systems

If I combine them into 1 dimension the unique identifier would be (SalesTraveler_id, OpsTraveler_id), though that will change once the Traveler is integrated from Sales to Ops (st_id: 1, ot_id: NULL -> st_id:1, ot_id:200). This means I probably need a linking table with a surrogate key just to manage them before I even create the dimension table with its own surrogate key.

From experience out there, does the convenience of having one Traveler dimension to use with all the data marts outweigh the complexity of combining them from the multiple systems? Certainly the ETL would be much simpler otherwise.

I appreciate any advice ahead of time.

Thanks,
Arturo

arturopw

Posts : 1
Join date : 2013-02-26

View user profile

Back to top Go down

Re: Should I create one or two dimensions?

Post  BoxesAndLines on Wed Feb 27, 2013 10:11 am

I personally like to consolidate multiple sources of the same information into a single dimension. Likewise, if I can (i.e. I have the tools available and the data supports it), I like to consolidate the data within those dimensions. I view this type of functionality, the value add of data warehousing.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum