In a previous article, we discussed three different approaches to Multi-Source Table Design at the Schema level. After designing tables for effective multi-source reporting, we have the opportunity to create business schemas for dashboard developers to report on without navigating the complexity of the source system.
In this article, we will discuss how to approach business schema design for multi-source reporting use cases.
To make the process of merging tables from disparate sources easier, we will create a view in a business schema for each of our two disparate sources. It is a best practice to use consistent naming conventions for your selected columns even though the original names may differ between sources. This allows us to create a more readable SQL script.
Two views will be created in the MultiSource1 business schema.
In the SQL script below, you will see that each view contains the following columns: Business_Unit, Supplier_Name, Item_Description, Period, Year, AMOUNT. The script will perform a UNION on our business schema views to create a single table as output in our schema.
The resulting table can be included in the business schema to be shared with dashboard developers. The developers can reference a single column within the table (e.g. AMOUNT) to see all data across both ERPs.
To remove inconsistencies in the data points (e.g. Business_Unit, Supplier_Name, etc.) between sources, consider using a method of Data Harmonization.
Conditional Logic to Query Appropriate Sources
When using a Materialized View to create a bridge table between two sources, we typically include:
Source of record
To include other attributes, we will create a view in a business schema. The business view will include a formula field for each attribute that determines which source to fetch the data from.
The formula below is defined in a business view and retrieves the AMOUNT field from each invoice. The source system from which the transaction originated will determine which table to query for the AMOUNT field.