cancel
Showing results for 
Search instead for 
Did you mean: 
LukeG
Employee
Employee

Introduction

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.

What you need to know before reading this article

Let's Go

Tables to Support Unions

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.

Example

Two views will be created in the MultiSource1 business schema.

  • EBS_AP
  • SAP_AP

Screen Shot 2022-07-27 at 5.18.59 PM.png

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.

Screen Shot 2022-06-23 at 11.22.46 AM.png

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.

Screen Shot 2022-07-27 at 5.22.56 PM.png

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:

  • Primary key(s)
  • Date
  • 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.

Example

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.

 

 

if(
	MultiSource.EBS_SAP_BRIDGE.Source = 'EBS', 
	EBS_AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT, 
	SAPECC_SD.VBRP.NETWR
)

 

 

In plain english, the formula says: If the source of the transaction is 'EBS', query EBS_AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT, if not, query SAPECC_SD.VBRP.NETWR

This can be done for all other attributes required for insights (Business_Unit, Supplier_Name, Item_Description, etc.).

Related Material

Best Practices Index
Best Practices

Just here to browse knowledge? This might help!

Contributors
Version history
Last update:
‎08-03-2022 07:30 PM
Updated by: