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:
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.
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.
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:
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.
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.
This data usually resides in other data sources or tables.
Here is what our 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.
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.
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.
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.
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.
Hey! Was this article helpful?
Questions? Ask the community.