Data modeling in star

[#1]

Hello,

I am writing to you because I want to model my app like a star model.

Actually, my structure is like :

I think it will be good to transform it with the method of hollow table to obtain :

But what I don't understand is how can I do that, considering that I already have relations between tables, like the field "Section".

Do I have to rename them ?

Thanks for your help

[crusader_]

Hi, Olivier

Firstly unite Invoice tlb and InvoiceLine tlb. You can use join or ApplyMap().

What's the semantic difference between OrderLine and OrderLine2 tlb?

As I understood Order tlb - should be Dimension in your datamodel.

Also Orders, Invoices and Budget are different semantical entities, but  technically you can concatenate them using additional field Flag to recognize definite entity.

Hope this helps.

Kind regards,

Andrei

[fkeuroglian]

Hi Andrei

First of all try to understando what you want and what info does your tables have

you have to determinate what would be your fact table and then determinate the dimension you have

invoice and invoice line could be join in the same table but ORDER LINE, ORDER LINE 2 AND ORDER? WHAT IS THE DIFFERENCE between this table?

please try to explain that to be easier for us to help you

Thanks

Fernando

[#4]

Hello,

Thanks for your responses.

I will join invoice and invoice line with applymap.

In fact, Order, Order Line and Order Line 2 come from different tables. If I concatenate all the three, the result isn't correct.

Order

Contains the order number, supplier and designation (it is a concatenate of two tables with supplier and designation).

Order Line

Contains the order number, section (like department), order line reference (line of order) and the total of this line.

Order Line 2

Contains the number and the ligne too, but what I need is the comment 1 and 2.

Thanks again !

[crusader_]

Hm...

Why don't you join all Order tables? Is order line a grain in your datamodel?

Could it be multipled by other fields, I mean could one order line belong to different orders or could it have 2 values for Comment 1 field etc ?

If not, you can join all three these  tables I suppose.

Andrei

[#6]

Hello Andrei,

Thanks for your reponse.

I've made the apply map for the two invoices tables.

It will be very good if I can join all the three Order tables yes.

To respond to your question, I don't know what it means "order line a grain in your datamodel", but one order line only belong to one order or one comment field.

Can you please explain me how can I join the three table ?

Is it by concatenate or other thing ?

Thank you !

[crusader_]

Hello,

Firstly, "grain" means the least unique part of your datamodel, for example:

================================

| OrdNo.| Product | Type | Qty | Price |  Sum |

================================

|   1       | Pencil A  | usual | 2    |  0.1    |   0.2   |

|   1       | Pencil A1 | usual | 2    |  0.12 |   0.24 |

|   1       | Pencil B  | auto   | 5    |  0.2    |   1      |

In this table grain is the string with unique combination  OrdNo&Product&Type.

In your case I asked about next case:

======================================================

| OrdNo.| Section | OrderLine Reference  | OrderLine Amount  |   Comment 1

======================================================

|     1     |   A         |    Smth                          |    200.00                  |  ABC

|     1     |   A         |    Smth                          |    200.00                  |  CBA

======================================================

To join tables you just write "left join" before your LOAD statement in script.

Order:

LOAD

     [Order Number]

     ,  [Order Supplier]

     ,  [Order Designation]

from ...xls or SQL;

OrderLine1:

left join

LOAD

     [Order Number]

     , [Section]

     , [Order Line Reference]

     , [Order Line Amount]

from ....;

OrderLine2:

left join

LOAD

     ....

FROM...;

The joining will be made by the same fields name, so be sure about combination of field names when you make joining.

Hope this helps.

Andrei

[#8]

Thanks Andrei for your response.

I did the join, and I also found and other field in invoice (order number) that permit to do the link between the order and the invoice.

This is the schema I have. But to avoid a loop, in the OrderLine table I changed the name "Section" for "Section Line". So, I don't have the link with the Budget table anymore...

Do you have any idea to correct this ?

Thanks !

[Yousef_Amarneh · Accepted solution]

It would be better if you use Link table,

LinkTable:

Load Distinct [Order Number Line] & '-' & Section as Key1,

        [Order Number Line],

        Section

Resident OrderLine;

Concatenate(LinkTable)

Load Distinct [Order Number Line] & '-' & [Order Number] as Key2,

        [Order Number Line],

        [Order Number]

Resident Order;

Concatenate(LinkTable)

Load Distinct [Order Numbe] & '-' & Section as Key3,

         [Order Number],

        Section

Resident Invoice;

and Do the concatenated Keys in all tables also.

Good Luck.

[mahesh_agrawal]

Hi,

Will it work for u??