MDM - Data Warehouse Interfaces

MDM - Data Warehouse Interfaces

MDM holds accurate authoritative governed dimension data, the actual operational data cross-reference, and the hierarchy information on all key master data objects.  These represent the foundational interfaces between MDM and the DW. With all the master data in the MDM Hubs, only one pipe is needed into the data warehouse for key dimensions such as Customer, Supplier, Account, Site, and Product. The "Star Schema" illustrated below from the Oracle whitepaper on Data Warehousing Best Practices[1], shows all dimensions except Time are managed by Oracle MDM.

star schema - general.png
 Not only that, Oracle MDM maintains master cross-references for every master business object and every attached system. This cross-reference is available regardless of data warehousing deployment style.  For example, an enterprise data warehouses or data mart can seamlessly combine historic data retrieved from those operational systems and placed into a Fact table, with real-time dimensions form the MDM Hub.  This way the business intelligence derived from the warehouse is based on the same data that is used to run the business on the operational side.



MDM holds quality "governed" dimensions for Customer, Supplier, Product, Location, Distributor, Asset, Account, Employee, Citizen, Parts, etc. Utilizing data standardization, duplicate identification and merge capabilities, a single version of the truth about each dimension is created.  When fed to the DW, these represent "Dimensions on Steroids". They can be moved into the DW, or used to facilitate joins across the MDM and DW data stores. EPM, Dashboards, Reports and ad-hoc queries produce better information when BI utilizes the "trusted" MDM dimensions.




MDM holds the corporate cross-reference for key dimensions such as Customer and Product. MDM maintains the ID of every connected system with its Source System Management capabilities, and it maintains the ID of the object in each connected system. The cross-reference capabilities include understanding multiple duplicates in each system and across systems.  It maintains this cross-reference even as it eliminates duplicate records via merge processes. When the DW uses this master cross-reference data, it correctly combines the trickle fed entries for accurate fact table reconciliation. This is critically important for accurate reporting and analysis. Fragmented data not recognized as the same entity to the BI applications can lead to misleading results and poor decision-making.




Hierarchy information is critical for proper rollup of aggregate information in the BI tools. Operational MDM holds the official hierarchy information used by the operational applications. This hierarchy information is needed for the proper functioning of key business processes such as sales, catalog management, and accounts payable. In addition, Analytical MDM takes these clean governed operational hierarchies and manages multiple alternate hierarchies across multiple dimensions with appropriate cross-domain mappings (product to cost centers, customer to product bundle, supplier to purchasing department, etc.). This is critical for accurate reporting out of the downstream analytical applications. When the data warehouse and the data marts utilizes the hierarchy information provided by Enterprise MDM, profitability analysis, risk assessments, dashboard information, enterprise performance management budgeting and forecasting are all improved.


Analytics Example


To illustrate how Oracle's MDM works to create better information, we will use a simplified real world example. The following events represent activity on the operational side of the business.

1.    Mary Smith buys a blue VN-Sweater for $50 from Old Navy on June 3rd.


2.       The next day, Mary Evans sees the identical sweater (labeled RF-Sweater) at Banana Republic and buys it for $45 for a friend.

3.       Acme, Inc. supplies Old Navy with their VN line of sweaters.

4.       AI Corp supplies Banana Republic with their RF line of sweaters.


We have:









Mary Smith


Old navy

Acme, Inc.

Mary Evans


Banana Republic

AI Corp


Star Schema

A trickle feed into a FACT table in the data warehouse would look like this:

star schema - fact table.png

 Adding the Dimensions, we would have the following Star Schema:


star schema - wrong .png
Query Results












A few ad-hoc queries on this schema would produce the following answers:

What is the average revenue per customer?              $47.50


Who is the most valuable customer?                         Mary Smith

How much did the most valuable customer spend?  $50

Who is the number one retailer?                               Old Navy

What is the maximum revenue for any supplier?      $50.00


Every one of these answers is wrong!


The Data Quality Problem

We have seen that the operational environment is very dynamic and duplicates are often hard to identify.  Reorganizations can change corporate hierarchies over night. Consider the following facts:

·         Mary Smith married Mr. Evans and changed her name to Mary Evans after she bought the sweater from Old Navy. She is, in fact, the same person who bought the similar sweater from Banana Republic the next day.


o    Understanding these dynamics requires fact based knowledge management, duplicate identification, survivorship rules, and cross-referencing.

·         Old Navy and Banana Republic are both subsidiaries of The Gap.

o    Dealing with this kind of information requires hierarchy management.

·         AI Corp is an alias for Acme, Inc.  They are in fact the same supplier.

o    This requires supplier data quality management, duplicate identification, and cross-referencing.

·         VN-Sweater and RF-Sweater are two ids for the same actual item.

o    This requires product data standardization and cross-referencing.

Oracle's MDM solution is designed to understand these facts and accurately reflect this reality. In the next blog, I will itemize the work done in MDM that corrects the dimension, cross-reference, and hierarchy errors that lead to the wrong answers. We will discover the right answers to these queries.

[1] Best practices for a Data Warehouse on Oracle Database 11g, URL


Post a Comment:
  • HTML Syntax: NOT allowed

Get the latest on all things related to Oracle Master Data Management. Join Oracle's MDM Community today.

Follow us on twitter Catch Us on YouTube


« July 2016