Multi-company Consolidation

From DataSelf Knowledge Base
Jump to navigation Jump to search

A multi-company setup is used to consolidate data from two or more ERP or CRM systems into a single analytics solution. Once the data is consolidated, security settings can control what data set each user will have access to. With data set level security the head office of a multi-company system can have access to all data from all business units, while users from a given unit will only be able to access their own data.

Strategies for Consolidation

Consolidation of some multi-company systems can be a fairly simple process, just merge all the required tables. See Import Table Wizard ... - Table Union window. However, quite often a simple merge will not work.

Merging Sales Tables from Two ERP Systems

A Simple Case

Consider the problem of integrating the sales tables from two ERP systems. A straight forward merge of the two tables would work if we can assume that both ERP systems handle the same type of sales processes (ex.: selling off-the-shelf products to clients), and there are no conflicts with product or customer key values between the two ERPs. In this case, a simple merging of each ERPs’ tables into a target table in the data warehouse will do the job.

A More Typical, Real World Example

Now, consider that the two ERPs share some customers in common but identify them with different Customer IDs. Or that some customers have the same key value but actually represent different entities. How can the data conflicts be resolved? There are many possible solutions but choosing a good stratgy requires analysis and discussions with the business users to define exactly how to perform the table and data merging.

Levels and Dimensions of a Multi-Company Consolidation Setup

The setup of a multi-company solution often relies on design strategies in one or more dimensions. Three dimensions often found are:

  • DataSelf ETL: Used to perform table unions of all desired source tables into the data warehouse via the ETL. See Import Table Wizard ... - Table Union window.
  • DataSelf data warehouse: In most instances, the canned data warehouse templates already come with a [[revise|multi-company setup.|Meaning some canned solution to resolving the data conflicts? What does "setup" mean?}} Consult if the default settings don’t work as is.
  • Template:Revision In most instances, the data connectors and report templates already come with a multi-company setup by default. Consult if the default settings don’t work as is.