How to Remove Syn Key - Data Model

[vikasmahajan]

Dear all,

I have sales group master  ( data indoco/spade/wareen)  and   head  quarter master with sg id field when i link this with sales group master I am getting synthetic key

DIM_SalesGroupMaster:

LOAD SG_ID AS [Sales Group Code],

     SG_NAME AS [Sales Group],

   FROM

[..\Qvds\DIM_SalesGroupMaster.qvd]

(qvd);

DIM_GeoHQMaster:

HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')

LOAD Hqid AS [HQ Code],

     Hqname AS [HQ],

     GeoLevelName AS [Level Name],

     CHILD_ID,

     PARENT_ID,

     Sgid as [Sales Group Code],

     [EmpName],

    [Email]

FROM

[..\Qvds\DIM_GeoHQMaster.qvd]

(qvd);

Please guide me how to remove syn key ?

Vikas

[Anonymous]

I must admit that from you script I cannot see how it could have created a synthetic key.  Do you have more script that creates other tables ?

Notwithstanding that I would suggest using a Mapping Load & Applymap as per the below, which will result in just one table.  With just one table it is impossible to get a synthetic key generated.

SalesGroupMasterMAP:

mapping LOAD

SG_ID AS [Sales Group Code],

    SG_NAME AS [Sales Group],

  FROM

[..\Qvds\DIM_SalesGroupMaster.qvd]

(qvd);

DIM_GeoHQMaster:

HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')

LOAD Hqid AS [HQ Code],

    Hqname AS [HQ],

    GeoLevelName AS [Level Name],

    CHILD_ID,

    PARENT_ID,

    Sgid as [Sales Group Code],

    applymap ( 'SalesGroupMasterMAP', Sgid )     as [Sales Group],

    [EmpName],

    [Email]

FROM

[..\Qvds\DIM_GeoHQMaster.qvd]

(qvd);

[avinashelite]

Hi Vikas,

If you want to combine tables more than one key Please use a link table it will avoid the formation of Synthetic keys.

Regards,

@vi

[vikasmahajan]

Thanks for reply I have other tables also along with sales group field how solve with  to generate link table  ?

Vikas

[Anonymous]

I have just noticed the you attached an image of some of your data model to your original post, and you sure do have a lot of tables.

Rather than worry about a synthetic key, I'd suggest you need to massively flatten your data model.  In your script use joins / concatenations and try and get down to just one Fact Table.

the repeated use of ApplyMap() to include your dimensions into the flattened Fact Table.

I reckon that once you have sorted your data model then the synthetic key will have been sorted as per of that.