Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Ony obtiene pocos registros para múltiples uniones

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 .

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

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...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.