Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Syndicate_Admin
Administrator
Administrator

Ony obtiene pocos registros para múltiples uniones

‎09-07-2023 10:21 AM

Por favor, ayuda. El código es Postgres, pero creo que el principio sigue siendo el mismo (necesito esto correcto para crear el panel de PowerBI).

Estoy recuperando datos de SAP BW mediante SSIS. Los datos se cargan en:

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;

La tabla de destino es:

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")
)

Obtengo los datos en el destino uniendo algunas tablas:

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";
--------------------

Tablas de dimensiones:

 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 ;

El problema es que solo estoy recibiendo 19 filas insertadas en mi tabla de destino. Mi fuente tiene más de 5 millones de filas. ¿Qué estoy haciendo mal?

Por extraño que parezca, tenue". IMETA_Source_Description_Mapping" solo tiene 19 columnas. Todas estas tablas de dimensiones contienen datos para buscar solo la tabla de hechos, lo que significa que los datos de las tablas de dimensiones son datos maestros o datos que no cambian. Intenté buscar datos en cada tabla, incluidas las dimensiones y los hechos, y todos contienen datos. También modifiqué las restricciones sobre la mesa para ver si ese es el problema, no ayudó.

Las claves secundarias de la tabla Dimensión son claves suplentes. Esto es para permitir que las tablas de dimensiones se unan a la tabla de hechos. No puedo crear estas claves como secundarias en la tabla de hechos, si lo hago, obtengo una infracción de clave secundaria al ejecutar SSIS.

El código completo también se adjunta https://drive.google.com/file/d/1eLEVqd06oSWwKHkhGGhFSUtHY40nrudT/view?usp=sharing .

Message 1 of 2
113 Views
0
1 REPLY 1
Syndicate_Admin
Administrator
Administrator

‎09-09-2023 05:48 PM

Proporcione datos de muestra que cubran su problema o pregunta por completo. Omita cualquier cosa que no esté relacionada con el problema.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Muestre el resultado esperado en función de los datos de muestra que proporcionó.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 2 of 2
95 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All