Data Mart - how to handle one to many relation?

Question

I have a following situation that I am not sure how to handle: There is a table Inovice_Item, Service and ServiceLang. Invoice_item table has FK_Service key (one to one). Service table has FK_Service_Lang key. ServiceLang table has FK_Service key so it makes it many to many relation.

In other words, Invoice_Item can have multiple ServiceLang records, which means that when I make a join query, invoice_item records get duplicated. What are the options to handle such situations?

I would like to have ServiceLang dimension in the cube, but I am not sure how to handle duplicates caused by join.

EDIT

I've made an example:

Schema

enter image description here

The queries are as following:

-- One lang for service A, two langs for service B
select * from ServiceLang

-- Two records: A and B
select * from Service

-- Total amount is 20
select * from InvoiceItem

-- Query to populate Fact table
-- Total amount is 30
select * 
from InvoiceItem II
inner join Service S on II.FK_Service = S.PK_Service
inner join ServiceLang SL on S.PK_Service = SL.FK_Service

So, if there are two Service_Lang records related to one service than there is a duplicate row meaning that total services amount would be 30 but it should be 20. So, my question is how to handle these situations?

Ciarán · Answer 1 · 2014-08-20T13:35:10.667

From the description you are mistaken. Each Invoice_Item has one and only one Service and each Service has one and only one Service_Lang. However each Service_Lang has many Service records and each Service has many Invoice_Item records

The relationships are

    Invoice (n) <- (1) Service (n) <- (1) Service_Item

Thus the JOIN would be

    Select Invoice_Item.*, Service_Lang.WhateverColumnYouWant
      From Invoice_Item
           Inner Join Service On Service.Key = Invoice_Item.FK_Service_Key
           Inner Join Service_Lang On Service_Lang.Key = Service.FK_Service_Lang_Key

Edit: So the Service table does not have a FK_Service_Lang key on it, in which case you can only select one of the possible values for languages associated with the service. You could select the Min, the Max or some derivation based upon your preferred language, some examples...

Select InvoiceItem.*,
       Case When Exists (Select 1 From ServiceLang
                          Where ServiceLang.FK_Service = InvoiceItem.FK_Service
                            And ServiceLang.Name = 'English')
            Then 'English'
            Else (Select Max(Name) From ServiceLang
                   Where ServiceLang.FK_Service = InvoiceItem.FK_Service)
            End As ServiceLanguage,
       (Select Max(Name) From ServiceLang
         Where ServiceLang.FK_Service = InvoiceItem.FK_Service) As MaxLanguage,
       (Select Min(Name) From ServiceLang
         Where ServiceLang.FK_Service = InvoiceItem.FK_Service) As MinLanguage
  From InvoiceItem

I've no idea how big your ServiceLang table is but good practice would be ensure there is a key on the FK_Service column

I've edited my question, added examples, maybe it will be more clear now — ilija veselica, Aug 20 '14 at 11:22
I just found this post which gave me some guidance, will update once I find the final solution: http://stackoverflow.com/questions/10385513/ssas-one-to-many-dimensional-relationship — ilija veselica, Aug 21 '14 at 13:32

Data Mart - how to handle one to many relation?

1 Answers1