Please assist. The code is Postgres, but the principle remains the same i believe(need this correct to build PowerBI dashboard).
I am retrieving data from SAP BW using SSIS. The data is loaded into:
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "ZTBR_TA_BW_PrimaryKey" PRIMARY KEY ("Master_BRACS_Secondary_Key")
)
TABLESPACE pg_default;Destination table is:
CREATE TABLE IF NOT EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ZTBR_Query_Destination_pkey" PRIMARY KEY ("Master_BRACS_Secondary_Key")
)
I get the data into the destination by joining a few tables:
ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to
-------------------------------
INSERT INTO model."IMETA_ZTBR_BRACS_Model_TA_BW" (
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Cost_Center",
"Internal_Order",
"Trading_Partner",
"Amount_in_company_code_currency",
"Company_code_currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Master_BRACS_Secondary_Key",
"Source_Description_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Entity_Secondary_Key",
"Region_Secondary_Key",
"RowInsertedTimestamp",
"RowUpdatedTimestamp"
)
SELECT
ZTBR."Company_Code",
CAST(ZTBR."Posting_Period" AS character varying(7)) AS "Posting_Period",
ZTBR."Fiscal_Year",
ZTBR."Profit_Center",
ZTBR."Account_Number",
ZTBR."Business_Process",
ZTBR."Cost_Center",
ZTBR."Internal_Order",
ZTBR."Trading_Partner",
ZTBR."Amount_in_company_code_currency",
ZTBR."Company_code_currency",
ZTBR."BRACS_FA",
ZTBR."Expense_Type",
ZTBR."BRACS_ACCT_Key",
ZTBR."CC_Direct",
ZTBR."Segment_PC",
ZTBR."CC_Master_FA",
ZTBR."Master_BRACS_Secondary_Key",
ZTBR."Source_Description_Secondary_Key",
ZTBR."Direct_Indirect_Secondary_Key",
ZTBR."Entity_Secondary_Key",
ZTBR."Region_Secondary_Key",
CURRENT_DATE AS "RowInsertedTimestamp",
CURRENT_TIMESTAMP AS "RowUpdatedTimestamp"
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS ZTBR
JOIN
dim."IMETA_Direct_Indirect_Mapping" AS DIM_DIR_IND ON ZTBR."Direct_Indirect_Secondary_Key" = DIM_DIR_IND."Direct_Secondary_Key"
JOIN
dim."IMETA_Entity_Mapping" AS DIM_ENT_MAP ON ZTBR."Entity_Secondary_Key" = DIM_ENT_MAP."Entity_Secondary_Key"
JOIN
dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" AS DIM_MASTER_BRACS ON ZTBR."Master_BRACS_Secondary_Key" = DIM_MASTER_BRACS."ZTBR_TransactionCode"
JOIN
dim."IMETA_Source_Description_Mapping" AS DIM_SRC_DESC ON ZTBR."Source_Description_Secondary_Key" = DIM_SRC_DESC."BRACS_Key"
JOIN
dim."IMETA_Region_Mapping" AS DIM_REGION_MAP ON ZTBR."Region_Secondary_Key" = DIM_REGION_MAP."Region_Primary_Key";
--------------------Dimension tables:
Table: dim.IMETA_Source_Description_Mapping
-- DROP TABLE IF EXISTS dim."IMETA_Source_Description_Mapping";
CREATE TABLE IF NOT EXISTS dim."IMETA_Source_Description_Mapping"
(
"BRACS_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Source_Description_Mapping_BRACS_Key_seq"'::regclass),
"BRACSFA" character varying(255) COLLATE pg_catalog."default" NOT NULL,
"Function" character varying(255) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT "IMETA_Source_Description_Mapping_pkey" PRIMARY KEY ("BRACS_Key")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Source_Description_Mapping"
OWNER to ;
-----------
-- Table: dim.IMETA_Region_Mapping
-- DROP TABLE IF EXISTS dim."IMETA_Region_Mapping";
CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
"CoCd" character varying(255) COLLATE pg_catalog."default",
"Sub Region" character varying(255) COLLATE pg_catalog."default",
"Region" character varying(255) COLLATE pg_catalog."default",
"BRACS Entity" character varying(255) COLLATE pg_catalog."default",
"Consul" character varying(255) COLLATE pg_catalog."default",
"Report" character varying(255) COLLATE pg_catalog."default",
"Region BRACS" character varying(255) COLLATE pg_catalog."default",
"Group" character varying(255) COLLATE pg_catalog."default",
"Group BRACS" character varying(255) COLLATE pg_catalog."default",
"J" character varying(255) COLLATE pg_catalog."default",
"K" character varying(255) COLLATE pg_catalog."default",
"L" character varying(255) COLLATE pg_catalog."default",
"M" character varying(255) COLLATE pg_catalog."default",
"N" character varying(255) COLLATE pg_catalog."default",
"Region_Primary_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY ("Region_Primary_Key")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Region_Mapping"
OWNER to ;--------
-- Table: dim.IMETA_Master_BRACS_to_SAP_Data_TA_BR_
-- DROP TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_";
CREATE TABLE IF NOT EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR__ZTBR_TransactionCode_seq"'::regclass),
"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" double precision,
"Account Desc" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" double precision,
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date,
CONSTRAINT "ZTBR_TransactionCode_key" UNIQUE ("ZTBR_TransactionCode")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
OWNER to ;
--------
-- Table: dim.IMETA_Entity_Mapping
-- DROP TABLE IF EXISTS dim."IMETA_Entity_Mapping";
CREATE TABLE IF NOT EXISTS dim."IMETA_Entity_Mapping"
(
"Entity_ID" integer NOT NULL DEFAULT nextval('dim."IMETA_Entity_Mapping_Entity_ID_seq"'::regclass),
"Entity_Secondary_Key" integer,
"Entity" character varying(255) COLLATE pg_catalog."default",
"Entity Name" character varying(255) COLLATE pg_catalog."default",
"Entity Level" integer,
"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",
"Level 4" character varying(255) COLLATE pg_catalog."default",
"Level 5" character varying(255) COLLATE pg_catalog."default",
"Level 6" character varying(255) COLLATE pg_catalog."default",
CONSTRAINT "IMETA_Entity_Mapping_pkey" PRIMARY KEY ("Entity_ID"),
CONSTRAINT "IMETA_Entity_Mapping_Entity_Secondary_Key_key" UNIQUE ("Entity_Secondary_Key")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Entity_Mapping"
OWNER to ;
-- Trigger: trigger_increment_secondary_key
-- DROP TRIGGER IF EXISTS trigger_increment_secondary_key ON dim."IMETA_Entity_Mapping";
CREATE TRIGGER trigger_increment_secondary_key
BEFORE INSERT
ON dim."IMETA_Entity_Mapping"
FOR EACH ROW
EXECUTE FUNCTION public.increment_secondary_key();
----------
-- Table: dim.IMETA_Direct_Indirect_Mapping_New
-- DROP TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New";
CREATE TABLE IF NOT EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
(
"BRACS Account Description" character varying(255) COLLATE pg_catalog."default",
"CLASSIFICATION" character varying(255) COLLATE pg_catalog."default",
"Direct_Secondary_Key" integer NOT NULL,
"Direct_Primary_Key" integer NOT NULL,
CONSTRAINT "IMETA_Direct_Indirect_Mapping_New_pkey_new" PRIMARY KEY ("Direct_Primary_Key")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
OWNER to ;The problem is that I am only getting 19 rows inserted into my destination table. My source has over 5 million rows. What am I doing wrong?
Strangely enough, dim."IMETA_Source_Description_Mapping" only has 19 columns. All of these dimension table contains data to only look up to the Fact table, meaning data in the dimension tables are Master Data or Data that don't change. I tried looking up data in every table including the dimensions and fact, and they all contain data. I also amended the constraints on the table to see if that is the problem, it did not help.
The Secondary Keys in the Dimension table are surrogate keys. This is to allow the dimension tables to join to fact table. I can not create these Keys as Secondary in the Fact table, if I do I get an Secondary key Violation when running SSIS.
The complete code is also attached https://drive.google.com/file/d/1eLEVqd06oSWwKHkhGGhFSUtHY40nrudT/view?usp=sharing .
