Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
i want to demonstrate how to remove synthetic key by using composite key. I have created a composite key.
But i am confused which table's common field should i comment? and how should i determine which table's common fields to be commented out?
I have commented common fields that available in my Sales Target table.
Orders:
LOAD
EmployeeID &'|' & Month(OrderDate) &'|' & Year as order_key,
OrderID,
Month(OrderDate) as Month,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
APPLYMAP('Product_Details',ProductID,null()) as Product_Made,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\Order.xlsx]
(ooxml, embedded labels, table is Order);
Sales_Target:
LOAD
EmployeeID &'|' & Month &'|' & Year as order_key,
//EmployeeID,
//Year,
// Month,
Target
FROM
[Data Source\Sales_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
Rgds,
Jim
Hi Jim,
I believe it doesn't matter much which fields that you comment it out, but in the above case, I'm leaning towards to comment out the Year, Month, and Employee ID in Orders table instead.The reasons are:
1. More likely than not, your Sales_Target already contains unique records based on EmpID, Year, Month combination, and I'd like to see the fields made up these unique combination from table viewer.
2. I prefer to treat the "Sales_Target" as my "Dimension" table, If you were to keep the 3 fields in Sales_Target (and commented them from the Orders instead), when you are displaying the EmpID, Year, and Month fields, it is as-if these fields come from Dimension table.
3. Orders table potentially have more records as it stores transaction records, thus keeping the table smaller by removing unnecessary fields will help speed up the reload process (even though in relatively few records, the speed improvement will be insignificant).
I'm sure others may have different opinions, and I'm totally welcome you to share yours. That's how we grow this community.
Cheers,
Anton
Hi Jim,
I believe it doesn't matter much which fields that you comment it out, but in the above case, I'm leaning towards to comment out the Year, Month, and Employee ID in Orders table instead.The reasons are:
1. More likely than not, your Sales_Target already contains unique records based on EmpID, Year, Month combination, and I'd like to see the fields made up these unique combination from table viewer.
2. I prefer to treat the "Sales_Target" as my "Dimension" table, If you were to keep the 3 fields in Sales_Target (and commented them from the Orders instead), when you are displaying the EmpID, Year, and Month fields, it is as-if these fields come from Dimension table.
3. Orders table potentially have more records as it stores transaction records, thus keeping the table smaller by removing unnecessary fields will help speed up the reload process (even though in relatively few records, the speed improvement will be insignificant).
I'm sure others may have different opinions, and I'm totally welcome you to share yours. That's how we grow this community.
Cheers,
Anton
I think all the points mentioned by awqvuserneo are very critical, along with that you may think of connecting both the tables to a master calendar( using date key with all dates from order and target) and remove month and year from order and target tables, so this way your order and target tables are connected using EMP ID and both order and target table connected to master calendar using date field. I think going by your way may have confusion by treating target year/month same as order year/month and you may find difficult to answer questions like for which target year there is no sales for particular EMP ID if you comment out fields by treating them having same.
Hope This helpful
Try With out Link table. And see how synthetic keys are there. After using composite key try to check Data Model
do you mean.. try with or without linktable?
I try not to use link table as i wanted to demonstrate removing synthetic key using composite key.
In this case the simplest and fastest solution is to concatenate target table to order table.