modelling

[surajap123]

Hi All,

For a booking there are many invoice line items.

The booking details are in table1 and invoice details are in Table2.

Both the tables can be linked with keyfield bookingID.

-Since there are many common fields between both tables, i see lot of synthetic keys created.

-I wanted to put both TableA and TableB in one table.

What is the best i can do to achieve this?

Should i go with concatenate, left join or anything else?

I have one more query, Is booking table and invoice table both are fact tables. I think no. because both tables have same transaction details.

Regards,

Suraj

[vijetas42]

can you share sample of your data model by using Ctrl+T?

[Roop]

You can put both into the same table by concatenation but if you are going to do this you need to ensure that you mark each source. So the following may work for you:

Load

     Book1,

     Book2,

     CommonRef1,

     CommonRef2,

     UniqueRef1,

     'Booking' as Source,

     1 as Booking_Count

from Booking.CSV;

Concatenate

Load

     Invoice1,

     Invoice2,

     Invoice3,

     CommonRef1,

     CommonRef2,

     UniqueRef3,

     UniqueRef4,

     'Invoice' as Source,

     1 as Invoice_Count

from Invoice.CSV

They are both Fact Tables and the above may work for you but without the schema it is difficult to tell.

[surajap123]

Hi Rupert,

Thanks a lot for the script.

I am here attaching the same data structure. Please help me with the script.

In your script I didn't understand the functionality of fields-

1 as Invoice_Count

1 as Booking_count

Please explain

[Roop]

Aha ....

Much easier than expected

Invoice:

Load

     InvoiceID,

     BookingID,

     ItemID,

     ItemName,

     CurrencyID,

     InvoiceType,

     FromDate as Inv_FromDate,

     ToDate as Inv_ToDate,

      Price as Inv_Price,

     Discount as Inv_Discount

etc etc,

     1 as Invoice_Lines

from Invoice.CSV;

Booking:

Load

     BookingID,

     StartDate as Book_StartDate,

     EndDate as Book_EndDate,

     Qty as Book_Qty,

     StudentID,

     BookingOrigin,

etc etc,

     1 as Bookings

from Booking.CSV;

The above will join on the common field BookingID (which appears in both Booking and Invoice). There are no other common fields so all should be well.

If there are other fields that have the same name, these are either common to both tables or unique. If they are unique in both tables, one of the can be renamed using "AS". This is a useful method to fully define fields for the end user or developer.

Hope this helps

[Roop]

Sorry, the 1 as Invoice_Lines and 1 as Bookings in the above code is used so that you can count the number of bookings and/or invoices using SUM rather than COUNT which is more efficient

[#7]

Hi Suraj,

If you left join the booking table with invoice table you will get the following output which is in below attached QVW file.

[surajap123]

Hi Rupert/Ishwar,

Thanks a lot for your input.

-As Rupert mentioned, I need to Keep the link between both tables and rename the common field names. This is resolving the issue.

-As Khutal mentioned, doing left join the booking table and renaming common field names, also resolving the issue.

-The 2nd approach finally brings a single table, which i think looks good.

I am still confused about which is the best approach to go with.

kindly suggest..

Thanks a lot..

[Roop · Accepted solution]

I think this will depend on how many times you use each of the underlying tables independently. If the tables are very large and sparse, there can be space considerations.

Generally it is a personal issue, and I would always tend towards having 2 separate tables as the fields in each can then be readily identified for both developers and end users.

At the end of the day there is not much in it.

[surajap123]

Thanks a lot for all the support !!