Integrating Data

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.

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:

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 the sample GL entries look like. It is not very useful to analysis and reporting.

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:

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.

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. For details, see LOOKUP(remote_table,remote_result_column,current_match_column, remote_match_column) The Lookup function imports a field from a remote table into the current table, by matching the values of current_match_column and remote_match_column, and importing the value from remote_result_column. When there is no match between the fields, the returned value is 'N\A'. .