Connecting Data Sources in BI: How to Bring Internal and External Data Together Meaningfully

Website, social media, revenue, costs, email open rates, peak times: most companies have a large amount of useful data available. Platforms and software like social media, CRM, ERP, etc. make this data readily available. In addition, data is often captured manually in spreadsheets or automatically through internal solutions.

But the data is frequently scattered across departments and systems. A directory of that data, a centralized data basis, and solutions for visualizing and analyzing it are often missing. This leads to no one knowing which data exists where. Manual preparation work, contradictory interpretations, and decisions based on incomplete information result.

Data integration solves exactly this problem: it connects the relevant sources into a central analytics base from which reliable dashboards and reports can be derived.

---

This article walks step by step through how to identify, evaluate, and meaningfully connect your data sources. It is aimed at teams that want to make their data landscape accessible, usable, and scalable – without their own data engineering team.

The article's focus is not on the technical details of the connection itself. It is about the preparation and the overall picture: creating clarity about your own data structures, setting priorities, and running the data infrastructure reliably with minimal operational overhead.

---

Integration methods at a glance

These are the typical ways data is transferred between systems:

Native connectors are the most straightforward. They offer a prebuilt connection to common platforms: Google Analytics, Google Ads, CRM systems, shop systems, and virtually all major platforms or modern software solutions.

Setup usually requires only authentication and possibly a selection of the desired data. Updating, cleaning, and schema mapping run automatically. Which connectors a BI solution includes is an important selection criterion (see Choosing BI Software).

API connections are used when no native connector is available, but the source system offers a documented interface. Setup is more technically demanding and requires developer work. The clear advantage: even internal systems can be connected automatically this way.

File-based imports (XLSX, CSV) remain for sources without an API or connector. These often include accounting exports, budget plans, or historical data. Import is easy but not automated: the file has to be updated and re-uploaded regularly.

Typical data sources and how they are connected

To create a unified data infrastructure, different types of data sources usually need to be connected. Automated integrations are very convenient, but even manual imports and exports take just a few clicks.

External platforms and software

This covers all cloud-based services a company uses: web analytics (e.g. Google Analytics), advertising platforms (e.g. Google Ads), CRM systems (e.g. HubSpot), e-commerce platforms (e.g. Shopify), and newsletter tools.

These systems typically offer APIs through which data can be read automatically. Many BI applications have prebuilt connectors for this, so the connection and automated data transfer can be set up in just a few clicks.

Internal systems

ERP software, accounting solutions, inventory management systems, or industry-specific applications often fall into this category. Integration capability varies greatly: some offer open APIs, others allow only file export (CSV or XLSX). In some cases a direct database connection is possible, though that requires technical know-how.

Manual and spreadsheet-based sources

Excel spreadsheets, Google Sheets, CSV files, or manually maintained lists exist in almost every company. They are flexible but error-prone: no automatic updates, no version control, no consistent definitions. Yet for many data points they remain the only source initially – for example for budget planning, staff allocation, or target values.

Public and external data

Market data, industry benchmarks, competitor information, or public statistics can supplement BI analyses. They are usually researched manually and imported as a file, less often connected via an interface.

The problem behind data silos

A data silo forms when a system stores information that other systems or departments cannot use. This rarely happens deliberately – usually planning, coordination, and documentation are simply missing.

It happens because marketing has its own analytics tool, sales maintains its CRM, accounting works in a separate application, and individual teams organize their figures in spreadsheets.

Fakt

Over 87% of organizations struggle with disconnected data sources that lead to inefficiencies in decision-making and operations.

Quelle

The consequences are concrete:

• Reports contradict each other because departments use different definitions.
• Metrics are gathered manually, which costs time and invites errors.
• Decisions are based on partial information because no one has the complete picture.

The problem of data silos can emerge even with just a handful of software products that share no common data basis.

Without a strategy, the problem grows quickly. Companies of all sizes today use numerous applications, of which only a fraction are connected to each other.

The first step to dissolving silos is a stocktake: what data exists, where does it sit, and who uses it?

The advantages of a central data infrastructure

One system in which all relevant data flows together, is stored, and is accessible for analysis. Modern Business Intelligence solutions offer exactly that.

Once set up, data is transferred automatically into the BI solution. Through templates for professional dashboards, it is prepared interactively, made accessible, and shareable in just a few clicks.

This way management, department heads, and employees can all gain insight into the data that matters to them. Role management ensures only the desired people have access.

This brings potential advantages:

• A shared data basis creates a shared understanding.

• Data no longer needs to be manually exported, copied, and merged. The time saved is available for analysis and decision-making.

• Those with access to current, complete data make better decisions. Decisions become more reliable and faster.

• New data sources can be added to an existing infrastructure far more easily than to a web of individual spreadsheets.

How a central data basis is built technically and organizationally, from the architecture decision to the role model, is its own topic and is covered separately.

Creating a data inventory: stocktake in four steps

Before connecting data sources, you need clarity about which data exists, where it sits, and what priority it has.

A data inventory creates this overview. It makes it possible to derive a structured integration strategy from what might otherwise be an opaque data landscape.

Step 1: List data sources

Go through all departments and processes and capture which software systems and manual sources are used. The goal is a complete list.

Alongside department heads, also involve employees who work with the data daily.

Sources often exist that are not known centrally: a spreadsheet managed within the team, a reporting dashboard in the newsletter tool, or manually maintained tracking in sales.

Step 2: Map data to business goals

Not every data source is equally important. Map each source to a concrete business goal or decision. Data that serves no regular decision has low priority.

The questions are:

1. Which decisions are currently being made on the basis of incomplete information?
2. Which data sources exist and should be embedded in processes?

Step 3: Bring business value, priority, and availability together in an overview

The results are brought together in an overview. The following table shows an example:

The data inventory should be maintained as a living document. It grows with the BI process and is regularly updated as new data sources are added or existing ones fall away.

It is also a central component of the BI strategy (see Developing a BI Strategy).

Data structure and schema mapping

A typical data structure consists of dimensions and metrics. Metrics (e.g. revenue and costs) are assigned to dimensions (e.g. time or cost center).

Each data source delivers its information in its own schema. Social media platforms provide entirely different data than a CRM system. Modern BI solutions are able to receive data in its original format, store it, and make it productively usable.

Automated mapping makes it possible to visualize individual datasets immediately, without needing to adapt your own data and schema to the BI solution.

Modern BI solutions can receive and store data in its original format – including datasets that do not fit neatly into a typical schema, such as qualitative interview data or other text-based datasets.

Conclusion

The order matters. Those who build dashboards immediately without first inventorying sources and clarifying structures build on sand. Those who plan for months without connecting first data lose momentum.

The pragmatic path lies in between: set up a data inventory, identify the three to five most important sources, establish the first connections, and build a first functioning dashboard from them.

Related articles

Getting started with Business Intelligence: BI fundamentals for beginnersWhat is business intelligence, how does the BI cycle work, and how do you get started as a team without specialist knowledge—in a few steps.

Continue in the BI Guide

Previous articleData and KPIs in Business Intelligence: What Matters and How It Fits TogetherBack to categoryData & KPIs