how to remove syn table

[Anonymous]

How can I remove syn table from the following schema

Any help would be the most appreciated .

[Gysbert_Wassenaar]

If you don't experience performance problems then you don't need to remove it. See this blog post: Synthetic Keys

[Anonymous]

define a field with combination from both fields in each table

'Code Structure' & 'Code Rayon' as %CodeStructureRayon

rename both fields at least in one table

or

build a table with both fields and the new created field %CodeStructureRayon

[Colin-Albert]

Have a look at the links in this post

Data Model

[vijetas42]

Hi,

please check whether you need to join these two tables on Code Structure and Code Rayon. If you want to join on any one of the column then just rename the second column with different name.so that ,Your synthetic keys will resolved and from your data model syn table will get removed.

Thanks

[Anonymous]

I need both Code Structure and Code Rayon

[sasiparupudi1]

use qualify statement

qualify Code Structure,Code Rayon;

[sasiparupudi1]

sorry, forgot to mention , you need the following key field in your tables

'Code Structure' & 'Code Rayon' as %CodeStructureRayon

[Karim_Khan]

You can remove the same by renaming the fields or applying the Link table concept.concept. Pls share ur script for better understanding.

[Karim_Khan]

You can implement the same throigh below code

DIRECTORY;

Budget:

LOAD// [Customer Number],

//     Year,

//     Month,

     [Budget Amount],

     [Customer Number] &'|'& Year &'|'& Month as LinkKey //Key Field Genrated

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Budget);

Sales:

LOAD //[Customer Number], //Common Field has Dropped

//     Year,

//     Month,

     [Sales Amount],

     [Customer Number] &'|'& Year &'|'& Month as LinkKey //Key Field Genrated

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Sales);

Customer:

LOAD [Customer Number],

     [Customer Name]

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Customer);

//Step – 2 Create the Link Table by loading the distinct values of the fact tables

Link:

LOAD Distinct

     [Customer Number] &'|'& Year &'|'& Month as LinkKey ,

     [Customer Number] &'|'& Year &'|'& Month as Duplicate_LinkKey, //For Checking Duplicate combination while Concatenating

     [Customer Number],

     Year,

     Month

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Budget);

Concatenate (Link)

Load Distinct

     [Customer Number] &'|'& Year &'|'& Month as LinkKey, //Concatenate Distinct Combination of Key

     [Customer Number],

     Year,

     Month

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Sales)

WHERE NOT EXISTS(Duplicate_LinkKey, [Customer Number] &'|'& Year &'|'& Month ); //Filter Duplication

DROP Field Duplicate_LinkKey FROM Link;

//Step-3 Load other dimension tables.