Only getting few records into destination table

Question

Post reply

Only getting few records into destination table

yrstruly

Hall of Fame

Points: 3475

September 7, 2023 at 5:08 pm

#4281773

Please assist. The code is Postgres, but the principle remains the same i believe.

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.

Attachments:

You must be logged in to view attached files.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

Run the package in Visual Studio and add some data viewers in the data flow to find out what's going on and where the missing data is going.

I suggest changing your source query to SELECT top (50) ... for the purposes of debugging, which should make the process more manageable.

Providing loads of CREATE TABLE scripts but zero information about your SSIS package makes helping you quite a challenge.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

My first step when debugging things like that is to look at the data insert steps (anything that has an "INSERT" statement) and run the code below the that to see how many rows I get back. My GUESS (without being able to see any actual data) is that the joins are resulting in most of that 5 million rows being filtered out.

And what I usually do is change the SELECT <column list> to a SELECT * or SELECT COUNT(*). The reason I do this is so that I can remove some of the joins to try to figure out why my data is being filtered out when I don't think it should be.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

The SSIS package is simple. I am getting the data loaded from SAP using XTRACT IS add on in SSIS.

ssis

ssis2

ssis3

ssis5

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

I did this "And what I usually do is change the SELECT <column list> to a SELECT * or SELECT COUNT(*). The reason I do this is so that I can remove some of the joins to try to figure out why my data is being filtered out when I don't think it should be".

I get data for the different joins, including the joined table that gives me 19 rows. This amount of rows is available onlywhen i do the complete join insert.

Sample row data is attached.

Attachments:

You must be logged in to view attached files.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

So, based on what you have said, your JOIN syntax is filtering out the data.

My next steps then would be to do the SELECT COUNT(*) and have all the joins except the last one. If you get 19-ish rows, then put that one back and remove the one before it. Repeat until you get the number of rows you are expecting. Then you know which JOIN is misbehaving and you can correct that JOIN.

Now, with the above being said, MAYBE 19 rows is the expected value based on your data. If you are expecting to get 5 million rows with those joins and are getting 19, it means someone messed up the join logic.

If you REALLY want to do analytics on the data to determine what's up, you COULD try exporting the data from all of the tables into a tool like Excel (1 table per worksheet) and then have another to calculate out the data for what you'd expect in your final table. Use excel for the data modelling to get what you are expecting. Start with a small data set (50-100 rows of data out of each table with a carefully crafted WHERE clause so your data will match up how you want), then once you are happy with that, bump it up to 1000-10,000 and make sure it still appears right. Once you are happy with that, translate things over to SQL and see if you get what you expect. Once it is running happily in SQL as a SELECT statement, then put that into your INSERT inside SSIS.

When I am building up INSERT statements for SSIS, I ALWAYS start by building up a SELECT statement and getting my ducks in a row. If things aren't working how I expect, I inspect the data to see where things went sideways.

Looking at your table design, I see no foreign keys being created, so are you certain that your join mapping is correct? Not saying it is wrong, but just asking if you are certain that it is correct. If the join mapping is 100% correct, then that means you only have 19 rows that meet the join requirements.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

yrstruly

Hall of Fame

Points: 3475

September 7, 2023 at 7:13 pm

#4281854

ssis6