6 Replies Latest reply: Sep 18, 2016 12:53 AM by Digvijay Singh RSS

    Can I create my composite key like this?

    Jim Chan

      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

        • Re: Can I create my composite key like this?
          Anton Wibowo

          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

          • Re: Can I create my composite key like this?
            Digvijay Singh

            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.

            • Re: Can I create my composite key like this?
              Anil Babu Samineni

              Hope This helpful

               

              Try With out Link table. And see how synthetic keys are there. After using composite key try to check Data Model