MDM 360 for Retail > MDM 360 for Retail and external data integration > MDM SaaS and Google BigQuery integration

MDM SaaS and Google BigQuery integration

Use MDM 360 for Retail to integrate MDM SaaS with Google BigQuery to drive analytics and generative AI applications for customer, product, supplier, emission, household, and location data and relationships.

You can publish master data and relationships from MDM SaaS to the Google BigQuery staging store. You can then publish data from the staging store into the Google BigQuery target dimension tables.

Data flow to publish master data to Google BigQuery

Use egress jobs and Cloud Data Integration assets to publish master data and relationships from MDM SaaS to the Google BigQuery staging store. The extension then uses data transformation and taskflows to populate data into the Google BigQuery dimension and fact tables.

The following image shows the flow of data from MDM SaaS to Google BigQuery:

The extension uses an egress job and Cloud Data Integration taskflows to write data into Google BigQuery staging store. Cloud Data Integration taskflows transform and populate data into the Google BigQuery dimension tables.

When you run MDM SaaS egress jobs, Cloud Data Integration assets export MDM SaaS master data and relationships, such as product relationships and business entity to business entity relationships, into the Google BigQuery staging store. You can export MDM SaaS master data, such as customer, product, location, and supplier master data, into the Google BigQuery staging store.

When you run Cloud Data Integration taskflows, Cloud Data Integration transforms and publishes master data and relationships from the Google BigQuery staging store into the Google BigQuery dimension and fact tables.

Based on the type of records that you manage in MDM SaaS, data is stored in the Google BigQuery dimension and fact tables.

You can use the sample views to view analytical reports from the Google BigQuery analytical schema.

MDM SaaS master data

Based on the type of records that you manage in MDM SaaS, MDM 360 for Retail uses its corresponding data model.

The following table lists the different record types and their corresponding data model details:

Record Type	Data Model Reference
Customer	- Person business entity model - Organization business entity data model
Product	- Product business entity - Item business entity - Category business entity model
Supplier	Supplier business entity model
Location	Location business entity model
Emission	Emission business entity model
Household	Household business entity data model

Staging schema

The extension writes master data from MDM SaaS to the Google BigQuery staging store.

Based on the type of records that you manage in MDM SaaS, the master data is generated in the tables of datasets in the staging store.

The following table displays the datasets in Google BigQuery for MDM SaaS record types:

Record Type	Datasets
Customer	InfaRetail_Extension_customer_master_stg
Emission	InfaRetail_Extension_esg_emission_master_stg
Household	InfaRetail_Extension_household_master_stg
Location	InfaRetail_Extension_location_master_stg
Multidomain	- InfaRetail_Extension_Supplier_category_relationship_stg - InfaRetail_Extension_Supplier_emission_relationship_stg - InfaRetail_Extension_Supplier_item_relationship_stg - InfaRetail_Extension_Supplier_location_relationship_stg - InfaRetail_Extension_Supplier_relationship_stg - InfaRetail_Extension_category_location_relationship_stg - InfaRetail_Extension_customer_org_person_relationship_stg - InfaRetail_Extension_customer_person_relationship_stg - InfaRetail_Extension_item_category_realationship_stg
Product Relationship	InfaRetail_Extension_item_category_realationship_stg
Product	InfaRetail_Extension_product_master_stg
Supplier	InfaRetail_Extension_supplier_master_stg

Note: When you create datasets, ensure that you use the same dataset names to generate master data in the tables of the staging schema. For more information about creating datasets, see Creating datasets.

Analytical schema

The analytical schema is a repository for master and transactional data in Google BigQuery. Use the analytical schema to gain insights into the data.

When you run Cloud Data Integration taskflows, the Cloud Data Integration assets transform and publish master data and relationships from the Google BigQuery staging store into the Google BigQuery dimension and fact tables. Data in the analytical schema is stored in the InfaRetail_Extension_BigQueryAnalytics.tbl dataset.

The analytical schema includes the following dimension and fact tables:

•InfaRetail_Extension_BigQueryAnalytics.tbl_sales_txn_fact
•InfaRetail_Extension_BigQueryAnalytics.tbl_item_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_customer_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_email_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_calendar_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_address_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_location_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_phone_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_supplier_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_esg_score_dnb_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_esg_score_dnb_social_reason_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_esg_score_dnb_governance_reason_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_esg_score_dnb_environmental_reason_dim
•InfaRetail_Extension_BigQueryAnalytics.tbl_supplier_order_txn_fact
•InfaRetail_Extension_BigQueryAnalytics.vw_retail_analytics
•InfaRetail_Extension_BigQueryAnalytics.vw_supplier_analytics
•InfaRetail_Extension_BigQueryAnalytics.order_transaction
•InfaRetail_Extension_BigQueryAnalytics.supplier_transaction

Based on your use case, you can create or edit the target dimension and fact tables.

Note: When you create datasets to generate master data in the tables of the analytical schema, ensure that you use the same dataset names. For more information about creating datasets, see Creating datasets.

The following image shows the tables of the Google BigQuery analytical schema and their relationships:

The image shows the tables of the Google BigQuery analytical schema and their relationships.