A combo of 1:1 and 1:n relation

Hi guys,

I've started modeling what should be my first DV driven data warehouse. After reading both books, super charge.. and dv 2.0.., I took a swing at our use case.

The source system involved in this use case is a service management system used for registering customer cases (Ticket) including related information like, Contract, CI, Service and Customer.

In the source system I've identified the following relations:

Ticket > CI (1:n) NOTE: 1 CI is always marked as primary CI
Ticket > Service (1:1)
Ticket > Customer (1:1)
Ticket > Contract (1:1)

I started by linking all the hubs (Ticket, CI, Service, Customer, Contract) together with one single link (Service Request). But this will not do the trick since a Ticket can have multiple CI's (1:n) and I'll end up by storing duplicate information in case a Tickets belogns to multiple CI's.

So I'm struggling to find the best DV approach which will not compromise the DV standard. So far I came up with two alternatives but I'm unable to identify the consequences...

1. Seperate links between hubs

Model seperate links between the hubs, but this doesn't fit the grain of the source system. Becasue when a new ticket is stored in the system the relations listed above are stored.

2. Seperate link between Ticket and CI

Model a Service Request link to link Ticket, Service, Customer and Contract, and a seperate link between Ticket and CI

Another question is, how can I solve this problem. What kind of question should I ask myself or the business? What information can be used from the source model to solve this?

Thank you!

Regards,

Bas

asked Oct 22, 2015 in Modelling by bas_vdl (140 points)

2 Answers

Dear Bas,

are all these relations in one source table? e.g. "ticket" ?

Can you share a E/R model?

Thank you kindly,

Mike

answered Oct 22, 2015 by molschimke (1,890 points)

Hi Mike,

Thank you for taking a moment to look into my case. There is no ERD available, the source database only consist of PK's all other constraints like FK's are not defined...

As we go we try to puzzle this together.

The following 'FKs' are present in the Ticket table:
ticket.account_id < 1:1 > (Customer)
ticket.serv_cont_id < 1:1 > (Contract)
ticket.it_service < 1:1 > (Service)

For CI's, a 1:n relation is applied:
ticket.id < 1:n > (TicketCI) < n:1 > (CI)

I hope this is the required information you were looking for.

Again, thank you.

Regards,

Bas

commented Oct 23, 2015 by bas_vdl (140 points)

edit: created account and placed comment again.

commented Oct 23, 2015 by Bas

I would model this using your first sugestion of seperate links. 5 Hubs of Ticket, Cusotmer, Service, Contract and CI and 4 links connecting these together. You could then use effecitvity satellites to show which are the active records and track the history, as I assume that all these fields may be updated on each ticket.

answered Nov 21, 2015 by Liam Whales

Hi Liam, I went with separate links. This will give us the most flexibility.