Unique Primary Key Linked to Multiple Accounts

Question

Post reply

Unique Primary Key Linked to Multiple Accounts

yrstruly

Hall of Fame

Points: 3475

November 13, 2023 at 6:58 am

#4326542

Please advice. I brought in data from SAP and assigned unique primary key to the table:

unique

CREATE TABLE IF NOT EXISTS fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
    "ZTBR_TransactionCode" integer NOT NULL,
    "Company_Code" character varying COLLATE pg_catalog."default",
    "Posting_Period" integer,
    "Fiscal_Year" integer,
    "Profit_Center" text COLLATE pg_catalog."default",
    "Account_Number" integer,
    "Business_Process" character varying COLLATE pg_catalog."default",
    "Internal_Order" text COLLATE pg_catalog."default",
    "Amount_in_Company_Code_Currency" numeric,
    "Company_Code_Currency" text COLLATE pg_catalog."default",
    "BRACS_FA" character varying COLLATE pg_catalog."default",
    "Expense_Type" text COLLATE pg_catalog."default",
    "BRACS_ACCT_Key" character varying COLLATE pg_catalog."default",
    "CC_Direct" text COLLATE pg_catalog."default",
    "Segment_PC" text COLLATE pg_catalog."default",
    "CC_Master_FA" text COLLATE pg_catalog."default",
    "Region_Secondary_Key" integer,
    "Direct_Indirect_Secondary_Key" integer,
    "Source_Description_Secondary_Key" integer,
    "Entity_Secondary_Key" integer,
    "Master_BRACS_Secondary_Key" integer,
    "Loaddate" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY ("ZTBR_TransactionCode")
)

TABLESPACE pg_default;

I joined it with dimension table.

Joining code

 fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
    LEFT JOIN dim."IMETA_BRACS_Mapping" AS bracs_map
        ON fact."Account_Number" = bracs_map."GCoA" AND fact."Expense_Type" = bracs_map."EXPENSE FLAG"

It is joined on the Account numbers, which appears in the table multiple times. Problem is the Unique Primary Key is then mapped to these Account numbers multiple times.

unique2

CREATE TABLE IF NOT EXISTS dim."IMETA_BRACS_Mapping_"
(
    "Acct Type" character varying(255) COLLATE pg_catalog."default",
    "Level 1" character varying(255) COLLATE pg_catalog."default",
    "Level 2" character varying(255) COLLATE pg_catalog."default",
    "Level 3" character varying(255) COLLATE pg_catalog."default",
    "GCoA" integer,
    "Account Desc" character varying(255) COLLATE pg_catalog."default",
    "EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
    "BRACS" integer,
    "BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
    "Source data.Company Code" character varying(255) COLLATE pg_catalog."default",
    "Source data.Currency" character varying(255) COLLATE pg_catalog."default",
    "Source data.Account" integer,
    "Source data.Account Description" character varying(255) COLLATE pg_catalog."default",
    "Source data.BRACS Account" integer,
    "Source data.BRACS Account Description" character varying(255) COLLATE pg_catalog."default",
    "Source data.IS/BS" character varying(255) COLLATE pg_catalog."default",
    "Source data.Classification" character varying(255) COLLATE pg_catalog."default",
    "Source data.Function" character varying(255) COLLATE pg_catalog."default",
    "Source data.Region" character varying(255) COLLATE pg_catalog."default",
    "Source data.Roll - Up" character varying(255) COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

Result:

unique3

Please advice.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

yrstruly wrote:

It is joined on the Account numbers, which appears in the table multiple times. Problem is the Unique Primary Key is then mapped to these Account numbers multiple times.

So you have a join of 1 to many. This is what happens when you do such a thing. It's simply saying that the 1 has many matches to the other and so the one will be repeated for each occurrence of the other. What would you expect to see, in such a case?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

@jeff Moden Thank you for the feedback.

Table has a column Account Numbers that i could make unique, but Table B, the same account Number is duplicated because it might be linked to one or more Company code as in this:

I can not simple remove the duplicates in Table B, because each duplicate is linked to more than one Company code. How Can i solve this issue?

Ed B Hall of Fame Points: 3665 More actions · Answer 3

Are the dimension columns you need unique without Company Code and is Company Code irrelevant? If so, can you join to a distinct query of the columns you need?

It appears you're not using SQL Server, so the syntax may vary and some people prefer to use CTE's instead of inline derived tables

SELECT fact.colum1......,
       dim."Account", dim."AccountDesc"
FROM   fact.["IMETA_ZTRB_MP$F_ZTBR_TA_BW"] AS fact
LEFT JOIN 
  (  SELECT DISTINCT bracs_map."GCoA", bracs_map."EXPENSE",  
                     bracs_map."Source data.Account" AS "Account", 
                     bracs_map."Source data.Account Description" AS "AccountDesc"
     FROM dim.["IMETA_BRACS_Mapping"] AS bracs_map
  ) AS dim ON fact."Account_Number" = dim."GCoA" AND fact."Expense_Type" = dim."EXPENSE FLAG"

yrstruly Hall of Fame Points: 3475 More actions · Answer 4

Thank you @Ed B and @jeff Moden.

I have attached sample data in Excel. My data sits in Postgres, but I have converted it to T-SQL for better understanding.

(1) The fact table is from SAP.

(2) Dimension Tables are named "B Masked". Please note that none of the tables had primary keys, I had to bring them in myself. In the Bracs Mapping tab of this file, there is a column GCoA which is the Account Number. Then there is BRACS which is the Bracs(source) Account number. these numbers match the same columns in the Mapping tab of the Cash Flow Pivoted file.

(3) The Source Data is a combination and shorter version of the Mapping tab. Column Account match GCoA and BRACS Account match

matches BRACS in the Bracs mapping tab above of the B Masked file.

I noticed some similarities in the different tabs/tables so I denormalised some tabs/tables. e.g. Region Mapping and Entity Mapping in the same file I made as 1 table.

Joining on not unique keys is of concern.

(4) Cash Flow file, tab Sap Source is a pivoted table of the Source Data.

(5) The Cash Flow tab is the final reporting structure that must be replicated in power. Column "G" in this tab is the "Function" column in the Pivoted tab.

I do left joins to the Fact table and some inner join. Please advise.

The Function column that makes up the bulk of giving meaning to the figures, most of the detail of this column is left out when I do a join as seen in Imeta_Bracs_Roll_Up joined to Fact file with SQL code.

I brought in the Mapping table to connect the Roll Up table to it as seen in Imeta_Bracs_Roll_Up joined to Bridge, this query takes. very long to run. Also, the joins take a lot of memory and some ETL/SSIS task may fail. What would you be bettering in this instance?

Attachments:

You must be logged in to view attached files.