How to Manage Data Sources, Integration, and Redundancy in a BI Project
In the podcast episode below, Blue Margin CEO Brick Thompson and VP of Delivery Operations Caleb Ochs cover the technical details of working with transactional data source connections when building a data warehouse. They also discuss how to combine data from different source systems and manage data duplicates and redundancy. Listen here:
Define the Impact and Start Small
The first step in a successful BI project is often overlooked: define the impact before writing a line of code. What is the return on investment? What outcome are you trying to improve, where does it stand now, where do you want it to be, and by when? Even a rough approximation of ROI helps justify the data preparation and migration effort that comes next and keeps the project tied to business outcomes rather than technical deliverables.
While it is tempting to pull every available data source into one data warehouse from the start, both experienced practitioners and agile BI methodology advise otherwise. Start with one data source, realize some quick wins, and then incorporate additional sources as appropriate. You will learn a great deal from the first one, and that knowledge creates meaningful efficiencies on everything that follows. Starting small should not, however, prevent planning ahead. The warehouse should be built modular and scalable from the beginning, with a clear plan for accommodating future sources as the business grows.
Three Primary Transactional Data Source Scenarios for ETL
For the purposes of building a BI data warehouse, a data source is a transactional software system: an ERP, CRM, or similar platform. There are three primary ways to access data from these systems, listed roughly from easiest to most complex.
Company-Managed Cloud or On-Premises Database
This type is owned and managed by the company itself, either in a physical location such as a local server, or through a cloud hosting service like Microsoft Azure VMs or SQL Servers, or Amazon Web Services VMs or Amazon Redshift. From a data architect’s perspective this is the best-case scenario for access: ETL tools connect easily, and the company has full control of the resource.
Application Programming Interface (API)
An API provides access to a software platform’s back-end data through a standard interface. APIs are common and generally a good option for connecting to a data source. The complication is that some software companies deliver unreliable or immature APIs, which creates delays and troubleshooting overhead. When working with an API connection it is important to validate its maturity and functionality early, which sometimes means contacting the software vendor for detailed documentation or requesting changes to the API before work can proceed.
Third-Party Hosted Databases
When the data source is managed by a third-party software provider rather than the company itself, setting up reliable ETL extracts often requires significant upfront work with the vendor around security and access. This scenario should be planned for explicitly in the project timeline rather than assumed to be straightforward.
Excel Spreadsheets
Beyond the three system-based scenarios, many clients also keep meaningful data in Excel spreadsheets. This requires a thorough quality assurance process to maintain data integrity when loading into a warehouse. There are strategies for handling it, but the sooner that data moves into a more tightly governed storage system, the better for long-term reporting reliability.
Data Redundancy and Master Data Management
Data redundancy happens when the same piece of data is stored in two separate places. It is a common challenge when working with multiple transactional systems, since the same customer, employee, or product may be entered slightly differently across platforms, making it difficult to recognize that two records refer to the same entity. Without good master data management, MDM, to keep systems synchronized, reports may not summarize or roll up correctly across sources.
MDM sometimes requires manual intervention from a company subject matter expert, who uses a matching tool to tie records from different systems together into conformed dimensions. The initial effort can be substantial, but it becomes a much smaller ongoing exceptions management process once the initial reconciliation is complete. With proper dimensional modeling and data visualization, a modeling tool handles the relationships between entries and pulls the correct value for each reporting view. Caleb covers more technical detail around this at timestamp 18:29 of the podcast episode above.
Working Through Technical Complexity
Technical challenges in data source integration, including data quality issues, talent gaps, and the cost and complexity of tooling, are among the most common reasons BI projects stall or fall short of their intended outcomes. The approach that consistently reduces those risks is the same one Brick and Caleb describe throughout this episode: define the business value first, start with one source, build modularly, and treat data redundancy as an expected problem to plan for rather than a surprise.
Blue Margin has helped hundreds of mid-market companies navigate exactly this terrain. Whether you need a partner to build and manage the full data infrastructure or support for a specific integration challenge, contact our team to talk through where you are and what the right next step looks like.
