C H A P T E R  3

Combining Data Sources

Good judgment is the result of experience. Experience is the result of bad judgment.

—Fred Brooks

The principal reason for utilizing PowerPivot for Excel as an ad hoc reporting and analytics solution is its unique capability to combine large volumes of related data from disparate sources. The goal of this chapter is to give you the skills to connect to different data sources, relate the information from those sources, and reuse the solution over time by refreshing the data.

The taxonomy of corporate data can be organized a number of ways—for example, structured data, organized into strictly defined fields of rigid data types, vs. unstructured data in the form of Microsoft Word documents. Another way of classifying the data used in everyday decision-making is the idea of governed data in corporate transactional databases, data marts, or data warehouse structures. These governed data sources are generally managed by a corporate information technology resource and have established access and change control policies. At the opposite end of this spectrum would be ad hoc or ungoverned data. This is data required by an information worker for the decision-making process, but it has not yet met the threshold for inclusion as an element of a governed data source. From information workers’ perspective, this ungoverned last mile of data represents the majority of their efforts to generate required information or insight. In my consulting career, I have seen incalculable numbers of ad hoc solutions put together by information workers to relate the governed to the ungoverned, from Microsoft Access databases containing exports of data (long since out of sync) with their governed sources to Microsoft Excel spreadsheets pressing the very limits of the software and hardware with data volumes. Fortunately, relating governed to ungoverned data in large volumes to analyze and report is PowerPivot for Excel’s primary function.

In this chapter, I will present an illustration of combining ungoverned and governed data and techniques for combining data stored in a Microsoft Excel spreadsheet with data from a SQL Server database.