Watch the Video
Data Vault Business Keys
In the realm of Customer Relationship Management (CRM) and Master Data Management (MDM), integrating data from diverse systems is a common challenge. This is particularly true when dealing with external identifiers, such as social security numbers, VAT IDs, or passport numbers, which can also serve as business keys in other systems. This article explores how to effectively model such scenarios within a Data Vault framework to streamline data integration and analysis.
In this article:
Understanding the Challenge
When a CRM system doubles as an MDM platform, it often houses an “External Identifiers” entity. This entity stores relationships between customers and various external systems, encompassing both external identifiers (like social security numbers) and internal IDs (such as customer IDs in core systems).
The complexity arises when some of these identifiers also function as business keys in other systems, each with its own Data Vault hub. The goal is to combine customer-related data from different domains while maintaining the integrity of these relationships.
Modeling Strategies
- Multi-Active Satellite: One approach involves modeling the “External Identifiers” entity as a multi-active satellite attached to the customer hub. This approach accommodates multiple keys or external identifiers linked to a single customer. By including the ID type (e.g., VAT, SSN) in the satellite’s descriptive data, you can distinguish between different identifier types within the group.
- Joining Data: If a separate hub exists for a specific business key (e.g., VAT), you can directly join the data from the MDM system’s satellite to the corresponding hub. This approach facilitates data integration and enables easier queries.
- Business Vault Links: To optimize join performance, especially with complex business keys or multi-column identifiers, you can create exploration or business links in the business vault. These links implement conditional logic, establishing connections between the customer hub and the relevant business key hubs based on the ID type.
Data-Driven Modeling
A data-driven modeling approach is essential in these scenarios. Start by capturing raw data from the source systems without applying business logic. In the raw Data Vault, treat business keys as descriptive fields within the satellite. Subsequently, in the business vault, you can implement the necessary business logic through links and relationships to integrate data from different domains effectively.
Hub It Out Pattern
The Hub It Out pattern, often used in refactoring, can also be applied here. If a new data set with descriptive data for a specific business key (e.g., corporate car VIN numbers) becomes available, you can extract those values from the existing satellite and create a new hub. Then, establish links between the customer hub and the new hub based on the existing relationships.
Considerations
- Hash Keys: Consider using hash keys for improved join performance, especially when dealing with complex or variable-length business keys.
- Data Virtualization: Where possible, virtualize data downstream from the raw Data Vault satellite to simplify the deletion of personal data.
Conclusion
In conclusion, integrating CRM and MDM data involving external identifiers that double as business keys requires a thoughtful modeling approach within the Data Vault framework. By leveraging multi-active satellites, joining data, creating business vault links, and adhering to a data-driven modeling philosophy, you can efficiently combine customer-related data from disparate domains.
Remember that the goal is to create a flexible and adaptable data model that caters to the evolving needs of your business. By employing these strategies and considering the specific requirements of your environment, you can unlock the full potential of your CRM and MDM systems, enabling seamless data integration and enhanced analytical capabilities.
Meet the Speaker
Michael Olschimke
Michael has more than 15 years of experience in Information Technology. During the last eight years he has specialized in Business Intelligence topics such as OLAP, Dimensional Modelling, and Data Mining. Challenge him with your questions!
Get Updates and Support
Please send inquiries and feature requests to [email protected].
For Data Vault training and on-site training inquiries, please contact [email protected] or register at www.scalefree.com.
To support the creation of Visual Data Vault drawings in Microsoft Visio, a stencil is implemented that can be used to draw Data Vault models. The stencil is available at www.visualdatavault.com.
