Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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 !
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
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 !
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
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 !
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.
Hi,
Will it work for u??