Why connect data sources?

When your source data is a complete data set and contains all of the detail you need to summarise your data by the required collections – you won’t have to connect data sources. But when your data is relational (stored in separate, related tables like a database), you’ll want to bring these separate sources together to provide meaningful insights.

_{Table 1: Sales Table}

For example, if you’re importing a list of products sold from a database, it’s likely that each sale record in the database will only list the item number of the product, the ID number of the salesperson and the specifics of the sale (date, time, amount), but it probably won’t list the product information nor the salesperson information.

_{Table 2: Products Table}

In a separate (but related) table, all product information will be recorded, with each products item number, manufacturer, make, model, product description, category, etc.

_{Table 3: Employees Table}

And in another (separate but related) table, all employees will be listed with their ID number, department, region, and other relevant information.

In order to summarise the sales by category, make, model, department or region, the tables will need to be joined together.

In the old days, prior to the inclusion of PowerPivot data modelling in Excel, this could only have been achieved by manually updating the data source with the additional information; either by adding the extra columns and entering the information, or by creating VLookup functions to look up and return the required information from external workbooks before creating the PivotTable. This would require regular maintenance: updating the source before refreshing the PivotTable, and it would only be possible with data is stored in Excel.

With the recent addition of new data tools, Excel makes it super easy to connect a PivotTable to multiple sources and create relationships between fields in the separate tables – negating the need to update the original data source with additional information. So instead of manually editing the source data, we can simply add multiple sources to the data model. As long as the seperate sources have a common value (e.g. Employee ID or Product ID), we can build our PivotTable from multiple related tables and create relationships to match the records.

Click to expand