Data Modeling and Integration Quiz

• Peter Bellis works for 2150 date World Builder and demonstrates how to solve a use case prepared by the German speaking data volt user group in a video.
• The video is not a demo with perfect data, but rather a challenge that can be solved by different tool providers.
• The use case involves creating a data model, loading data, creating interfaces, and creating reports using Data Vault Builder.
• Data Vault Builder is a model-driven data warehouse automation tool.
• The process begins with creating the data model, which includes adding concepts and relations.
• The data model is linked to another system via credit card payment.
• The next step is staging data and connecting it to the data model.
• Data is connected to the data model by creating interfaces.
• The use case involves a company selling seeds, which has online sales and on-site sales.
• Online sales are captured in a webshop and involve different partners who get discounts.
• On-site sales involve small systems capturing what was sold and the clients are not fully captured.
• The data is brought together to see overall sales and analyze it by product group and other things.
• The data model includes orders, order positions, clients, gardening groups, delivery addresses, deliveries, and product categories.
• The data is loaded from SQL Servers and connected to the data model.
• The data is staged and transformed as needed before being loaded into the data model.
• The data model is tested and the output is analyzed to ensure the data is correct.
• The use case involves dealing with data from three different deliveries and handling special cases like duplicates and missing business keys.
• The data is profiled to check for data problems and null values.
• The data is then connected to the objects in the data model.
• Unique business keys are defined, and if needed, prefixes are added to keep distinct data sets.
• Satellites are created to keep data distinct, and the ETL process is updated to pre-calculate business keys and hash keys in the next loading.
• The data is then loaded into the raw data vault, which is not directly accessible to anyone.
• The next step is creating the dimension model, which converts the data into a more usable format for reporting and analysis.
• The data is transformed into a denormalized form, and the grain of the output is selected.
• The STD type for the output is selected, which determines how the historical data is treated.
• The data is then loaded into the dimension model, and the process is repeated for each table in the data model.- The text describes a process of creating interfaces and linking data in a data model using the Data Vault Builder tool.
• Two hubs, Core Business Concept and Gray hub, have been loaded with their corresponding satellites.
• The process involves defining links between tables and creating interfaces.
• The data is being loaded from a source system and staged in the Data Vault Builder.
• The order table and related tables have been added and linked.
• The problem of spaces in business keys has been addressed.
• The process of creating a link from product to product category has encountered an issue, due to a missing semicolon in the SQL code.
• The data is being checked for errors and loaded into the interface.
• The process involves creating a load for each table and adding a relation between tables.
• The data model includes a parent-child relationship between product categories.
• The data is being checked for uniqueness of keys.
• The manual creation of a satellite for the delivery table is demonstrated.
• The delivery service table is added and linked to the delivery table.
• The process is repetitive as more tables are added and linked.
• The goal is to create a functional data model and output interface for the data.
• The data is being checked for relationships and uniqueness of keys.
• The Data Vault Builder provides a visual interface for data modeling and linking.
• The process involves creating a data model, staging the data, and creating interfaces.
• The tool allows for tracking changes in the data and provides full history.
• The data is being checked for errors and loaded into the interface.
• The process involves creating a hub load, tracking objects, and creating a satellite manually.
• The goal is to model the data in a way that accurately reflects the business and allows for efficient querying.
• The tool provides features for data conversion and type checking.
• The process involves adding relations and checking for uniqueness of keys.
• The data is being checked for data types and converted as necessary.
• The process involves loading the data and creating interfaces for each table.
• The tool provides a visual interface for creating and managing relationships between tables.
• The goal is to create an output interface that can be used by consumers to access the data.
• The process involves defining the grain and function for each interface.
• The tool allows for the creation of multiple interfaces for different consumers or purposes.
• The data is being loaded and the interfaces are being created and previewed.
• The process involves checking for errors and making adjustments as necessary.
• The tool provides features for automating the loading and creation of interfaces.
• The goal is to create a functional data model and output interface that accurately reflects the data and can be efficiently queried and used by consumers.- The text is about working with a data modeling tool to integrate and analyze data from two different systems: a webshop and a Roadshow system.
• The goal is to create a fact table that integrates both systems' data, create interfaces for reporting, and test the results.
• The text describes the steps taken to prepare the data for integration, including modifying order line data for the Roadshow system and adding business rule calculations.
• The text also mentions creating a sales order line total rule for both systems and testing the results in Power BI.
• The text highlights the importance of harmonizing data and handling missing values or nulls in the data.
• The text discusses the use of a dummy customer in the Roadshow system for cases where there is no real customer ID.
• The text also mentions the use of deployment scripts and the comparison of environments to deploy changes.

Key facts with context:

• Two systems: webshop and Roadshow.
• Goal: create fact table, interfaces, test results.
• Prepare data: modify order line data, add business rule calculations.
• Sales order line total rule for both systems.
• Test results in Power BI.
• Harmonize data, handle missing values or nulls.
• Use dummy customer in Roadshow system.
• Deployment scripts, compare environments.