From a focused data mart to a shared analytics foundation

IN BRIEF  We recommend a data warehouse when several systems and teams need historical data and common definitions. A smaller first step is often enough for simpler needs.

A data warehouse can be a useful foundation. We recommend it when decisions depend on combining data from several systems, keeping a reliable history and sharing stable definitions. If one source and a handful of indicators cover your needs, a simpler first step will often work better.

Start with a problem you can observe. Do your reports combine several systems? Do you need to compare today's figures with those from two years ago? Do multiple teams need to use the same definitions? The more often you answer yes, the more useful a central analytics layer becomes.

“Data warehouse” can sound like something reserved for large organisations. The need appears in fairly ordinary situations: recovering sales as they stood at close, reconciling ERP stock with orders, or sharing a single definition of margin.

A data warehouse provides a shared layer between operational systems and analytics.

What a data warehouse is

Think of it as an organised memory for analysis. Operational systems continue to handle orders, invoices or production. The warehouse brings together controlled copies, retains history and organises the data so people can query it more easily. Your business applications keep doing their day-to-day work.

A data warehouse is a store designed for analysis. It combines data from multiple sources, harmonises it, retains its history and structures it to answer business questions efficiently. Ingestion and transformation processes load the data, which is then available to semantic models, reports or analytics tools.

Look for these characteristics:

  • Centralised. Multiple sources feed a common foundation.
  • Integrated. Customers, products, dates and units follow consistent rules.
  • Historical. The warehouse retains changes needed for analysis.
  • Stable. Validated data is protected from everyday operational edits.
  • Decision-oriented. The structure supports analysis, aggregation and comparison.

Operational databases, data warehouses and data lakes

An operational database manages an application’s transactions and prioritises consistency in day-to-day updates. A data warehouse combines prepared data for reporting. A data lake often stores more varied data, structured or unstructured, in its original format. Modern platforms can bring these approaches together, and it is still useful to distinguish what each one does.

The five architecture layers

1. Source systems

ERPs, CRMs, manufacturing execution systems, accounting packages, files, APIs and operational databases remain the origin of the data. Identify the systems of record and understand their update cycles.

2. Ingestion

Connectors, ETL or ELT flows extract and load data. Log each run and make it possible to run it again. Set alerts for missing data.

3. Storage and history

The storage layer retains detailed data and the required versions. Define retention, backup and hosting location rules based on how sensitive the data is and what the business needs.

4. The analytical model

Fact and dimension tables translate technical sources into business concepts such as sales, movements, dates, customers, products and sites. A consistent level of detail makes calculations easier and helps performance.

5. Delivery

Power BI, Excel or other tools use a shared semantic model. Users can access indicators without having to understand every table in the source system.

An SME can start with a focused data mart and expand the foundation when the use cases justify it.

Signs your SME needs a data warehouse

The clearest sign is several teams performing the same reconciliation. If finance, sales and operations each extract data from the ERP, recreate their own rules and arrive at different totals, a shared layer can save work. First check that the definitions are ready to be shared.

SignWithout a warehousePotential benefit
Several sourcesReconciliation is repeated in every reportIntegration and common reference data
Unstable historyPast figures change when the ERP changesSnapshots and rules for retaining history
Conflicting KPIsEach team recalculates its own figuresShared definitions and model
Slow refreshHeavy transformations run on each workstationCentralised, reusable processing
Multiple reportsDuplicate connections and permissionsGoverned foundation for several uses

When a data warehouse is premature

A more robust architecture cannot fix a poorly defined need. If you are still deciding what to measure, the main source changes every week or no users are available to test, stabilise one use case before investing in a warehouse.

A well-built Power BI model can suffice if the use case relies on one stable source, a few users and already reliable history. A full platform would add administration without an immediate benefit. Let the use cases demonstrate when a warehouse is needed.

A data mart is a practical first step

Many SMEs can learn what they need from a data mart focused on sales, inventory or finance. Establish your first rules for history and quality within a manageable scope. If it proves useful, bring the mart into a wider architecture and keep the work you have already done.

A data mart covers a limited area, such as sales, stock or production. It applies quality rules, retains history and provides a reusable model at a manageable scale. Serve it with clear standards so you can integrate it into a larger warehouse later.

What to decide before choosing a tool

Which use cases and periods are in scope? Which source is authoritative for each entity? How much detail and history should you keep? What checks show that the load is complete? Who owns the data and validates definitions? Where is data hosted and how is it backed up? What level of service do you genuinely need?

Cloud, on-premises or hybrid

The cloud often shortens setup time and lets you adjust capacity. An on-premises installation can meet specific integration or data sovereignty requirements. Hybrid setups are common when sources remain on site and users access reports in the cloud. Compare the total cost, operational skills, security and how easily you could move to another platform.

Datavizin offers BI infrastructure and hosting plans and a Data Starter Pack to build a first model for a defined use case.

NEXT STEP  List three reports that repeat the same reconciliation. If they use several sources and the same definitions, a shared data mart may already give you a measurable benefit.