The following examples explain how to integrate and merge data from different sources into a single ElastiCube structure. This requires properly planning how to merge the data; to avoid creating unnecessary relationships, while avoiding many to many relationships. Examples in this section:

Creating a Common Date Selection

Business Case

When pulling together data from multiple sources, you will have a number of different dates.  Marketing has a Campaign Date, Sales has an Opportunity Date and Finance has a GL Date.

Modeling Challenge

This type of data leaves us with three sets of dates. Modeling it properly will allow you to select from one common date field while still leaving you the option  to choose from one of the three date fields individually.

Solution

Create a custom table that retrieves a unique list of the dates used between the three tables.

GL

Use the following syntax:

Select [GL Date] AS [Common Date] 
FROM [GL Entries] 
Union 
Select [Marketing Campaign Date] AS [Common Date] 
FROM [Marketing Campaigns] 
Union 
Select [Opportunity Date] AS [Common Date] 
FROM [Sales Opportunities]

Link the four tables together:

GL2

Results

This gives us the common date field to use.  This allows, for example, to select a month that will narrow down the selections across all three tables.

 

Financial Reporting

Business Case

Transactional systems are meant for handling transactions and not for reporting and analysis. For example, Financial GL data will include all the transactions but may not include all income statement or balance sheet reporting definitions.

Modeling Challenge

This data usually resides in other data sources or tables.

Solution

Here is what our sample GL entries look like. It is not very useful to analysis and reporting.

FR1

Another data source is needed to help define how the data will be analyzed and reported.
Start by bringing in another Data Source that contains details about the accounts and how they are categorized:

FR2

The two tables are linked on the account number. As there is one record in the GL Categories for multiple rows in the GL Entries, this is known as a One-to-Many Relationship.
The end results show that we can easily view the data in an organized fashion for analysis and reporting.

FR3

 

Looking Up Values

Business Case

Sometimes it is necessary to look up a value from one table and bring it into another table. For example, knowing how much a Marketing Campaign costs versus the Sales Opportunity amount.

Modeling Challenge

These two amounts typically reside in different systems. We need to look up the value from the Marketing system and bring it into the main Sales table.

Solution

In the Sales Opportunities table, create a custom field that will allow to look up a value from the Marketing Campaigns table based on the Marketing Campaign ID. You can do this using the lookup function.

LU1

 

Make the Marketing Campaigns table Invisible as you only need the one field in the Sales Opportunities table. As there is only One Marketing Campaign ID per Opportunity ID, this is known as a 1-to-1 relationship.