How do you create a single, reliable source of data?
Efficiency question :
Ideally, an organization wants a single, reliable source from which to use its data. This does not necessarily mean that all data must be stored in one database.
In many situations, the information needed to create a relevant report or view comes from different sources. There is therefore not necessarily any benefit in moving all data to the same location.
A more effective approach is to master the retrieval and merging of different sources to create a complete, reliable dataset. Power Query can bring this information together and prepare it for use.
Key considerations:
When data from several sources is used, it is important to determine which source should be treated as the reference for each piece of information.
Two systems may contain similar information without containing exactly the same data. The organization must then determine which source is more reliable or should take priority when differences arise.
This hierarchy among sources makes it possible to build a consistent dataset without physically moving all information into a single database.
Challenges:
The main challenge is understanding the complete data flow and the organization’s needs.
Data should serve the organization, not the other way around. Understand the production stages, the workstations involved and the processing performed before deciding how the different data sources should be used.
This understanding makes it possible to build a structure suited to actual business needs rather than organizing processes solely around the way data is currently stored.
Technology:
Power Query is particularly well suited to retrieving and merging data from multiple sources. It can query, transform and combine data to create a structured dataset for reporting.
SQL is also a powerful technology for querying and using data stored in databases.
The choice of technology depends on where the data is stored, how it is structured and how it must be combined and used.
Developer's advice:
Begin by developing an overall view of the organization and its needs before moving into the details of individual data sources.
Work from the top down: first understand the major processes, business needs and desired results, then determine which data is required and which sources should be used.
Avoid becoming distracted too quickly by exceptional cases. First build a structure that addresses the majority of situations and provides a coherent overall view.
Exceptions can then be identified and handled individually without losing sight of the overall process.
To explore the concepts in this sheet in practical terms, the on-demand Power Query and Power Pivot for Controllers and Analysts course available through SLV Expert’s training portal is an excellent starting point. It shows how to retrieve, structure and combine data from different sources so it can be used effectively.