Synthetic key

[#1]

Hi,

I am trying to remove this circular reference created with this synthetic key. Here i have connected with ODBC connector(Data Load editor). Can you help me with the changes required for removing the synthetic key.

Thanks.

LIB CONNECT TO 'Microsoft_SQL_Server_KWTPRAPCSQL01';

LOAD ClientNumber;

[Member]:

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

LOAD TransactionSlip,

  StoreID,

  BrandName;

[TransactionContent]:

SELECT TransactionSlip,

  StoreID,

  BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

LOAD TransactionSlip,

  StoreID,

  ClientNumber;

[TransactionFeed]:

SELECT TransactionSlip,

  StoreID,

  ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

[migueldelval]

Hi Deepika,

Try it:

[Member]:

LOAD ClientNumber as Client_Key;

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

LOAD TransactionSlip,

  StoreID,

  BrandName;

SELECT TransactionSlip,

  StoreID,

  BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

  [TransactionFeed]:

LOAD TransactionSlip,

  StoreID,

  ClientNumber as Client_Key;

SELECT TransactionSlip,

  StoreID,

  ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Regards

Miguel del Valle

[sunny_talwar]

Just try this (I don't think you need a preceding load here)

LIB CONNECT TO 'Microsoft_SQL_Server_KWTPRAPCSQL01';

[Member]:

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

SELECT TransactionSlip,

  StoreID,

  BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

[TransactionFeed]:

SELECT TransactionSlip,

  StoreID,

  ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Or you might be able to create a link table or join TransactionContent and TransactionFeed in order to avoid synthetic key, but synthetic keys are not always bad so unless there is a one to one relation or one to many, I wouldn't join them.

[#4]

Hi Miguel,

Than k you for your response. The transaction slip and storeID still shows as synthetic keys.

Deepika

[sankartalluru]

Hi,

We need to design the modeling based on requirement.

as per my understanding below will work

[Member]:

SELECT

ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionFeed]:

SELECT

  TransactionSlip,

  StoreID,

  ClientNumber,

  StoreID&''&TransactionSlip as KEY,

FROM "APC_PROD_APC".dbo.TransactionFeed;

[TransactionContent]:

SELECT

  StoreID&''&TransactionSlip as KEY,

  StoreID as StoreID_TC,

  TransactionSlip as TransactionSlip_TC

  BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

[migueldelval]

Hi Deepika:

Try it:

[Member]:

LOAD ClientNumber as Client_Key;

SELECT ClientNumber

FROM "APC_PROD_APC".dbo.Member;

[TransactionContent]:

LOAD TransactionSlip,

  StoreID,

  BrandName;

SELECT TransactionSlip as TransactionSlipContent,

  StoreID,

  BrandName

FROM "APC_PROD_APC".dbo.TransactionContent;

  [TransactionFeed]:

LOAD TransactionSlip,

  StoreID,

  ClientNumber as Client_Key;

SELECT TransactionSlip,

  StoreID,

  ClientNumber

FROM "APC_PROD_APC".dbo.TransactionFeed;

Regards

Miguel del Valle