Data Warehousing and Business Intelligence Explained

What is the difference between data warehousing and business intelligence?

Data warehousing is the plumbing and storage: getting data out of operational systems, integrating it, and keeping its history in a shape built for analysis. Business intelligence is the use of that data: the reports, dashboards, ad hoc queries and analysis people read to make decisions. Oracle's documentation ties the two together, describing a data warehouse as designed to enable business intelligence activities and to help users understand and improve their organization's performance.

The Kimball Group writes the pair as DW/BI throughout its methodology, which is the right mental model: one system, designed and delivered together.

Why does business intelligence need a data warehouse?

A BI tool can connect straight to an application database, and small teams often start that way. Problems appear once questions cross systems or span years.

• Integration: Oracle notes that the warehouse works with data collected from multiple sources, from internally developed systems and purchased applications to third-party data. A report that combines CRM, billing and web data needs those sources matched on customer, product and date first. The warehouse does that matching once instead of inside every report.
• History: application databases are built around the current state of each record. A warehouse keeps history, so a report can show a customer's segment at the time of a sale, not only today's value.
• Load: heavy analytical queries against an order system compete with the transactions that system exists to process. The warehouse takes that load away from it.

How does data flow from source systems to a dashboard?

Most DW/BI stacks follow the same five layers, whatever the vendor:

• Sources: ERP, CRM, point of sale, web analytics and files.
• Ingestion and transformation: ETL or ELT jobs extract data and then filter, clean, deduplicate, join and validate it. Microsoft's architecture guide lists those operations as typical transformation work. With ELT, the transformation runs inside the warehouse itself.
• Warehouse model: fact and dimension tables organized by business process, sometimes with data marts for one department. Oracle notes that data marts can be physical tables or implemented purely logically through views.
• Semantic layer: business names, relationships, measures and security rules defined once.
• BI tool: reports, dashboards and self-service exploration on top of the semantic layer.

Each layer should do its own job. When a dashboard carries complex cleansing logic, that work usually belongs one or two layers earlier, where every other report can reuse it.

Why is the star schema the standard shape for BI?

BI questions nearly always have the same form: filter and group by something, then summarize a number. Sales by region by month, tickets by product by week. A star schema matches that form. Microsoft's Power BI guidance puts it in two lines: dimension tables enable filtering and grouping, and fact tables enable summarization. The same article calls the star schema a mature modeling approach widely adopted by relational data warehouses.

The Kimball Group links the design to BI directly through the grain, the statement of exactly what one fact table row represents. Declaring the grain before choosing facts and dimensions enforces a uniformity that Kimball calls critical to BI application performance and ease of use. A model where every fact table has a clear grain and shared dimensions is one a report author can use without reading the loading code.

What is a semantic layer, and where does it live?

A semantic layer sits between warehouse tables and report visuals. It gives columns business names, defines relationships, and stores measures such as net revenue or active customers, so every report uses the same calculation. Microsoft describes Power BI semantic models as a source of data that is ready for reporting and visualization. In Looker the same job belongs to LookML, which Google describes as the language used to create semantic data models; Looker uses the LookML model to construct SQL queries against the database.

The warehouse still has to supply consistent building blocks. The Kimball Group's conformed dimensions, with the same column names and contents across fact tables, let results from separate processes, such as sales and inventory, line up on the same rows of one report. Without them, the semantic layer ends up covering for mismatched keys, and the gaps show up as totals that almost agree.

Should BI tools import data or query the warehouse live?

Most BI tools offer both, and the choice trades speed against freshness. In Power BI, Microsoft's documentation explains that with Import, the data is loaded into the semantic model's in-memory cache, so visuals are fast and fully interactive, but they do not reflect source changes until the model is refreshed. With DirectQuery, the model holds only schema and metadata, and reports query the source.

Warehouses add their own features for the live path:

• BigQuery BI Engine: Google describes it as an in-memory analysis service that speeds up SQL queries by caching the data used most often, including queries sent from BI tools such as Tableau.
• Materialized views: Amazon Redshift describes a materialized view as a precomputed result set based on a SQL query over one or more base tables, which suits dashboards that repeat the same aggregation.
• Composite models: Power BI can keep dimensions imported while querying fact tables live, or the other way around.

In practice, import suits data refreshed on a schedule that fits in memory, and live queries suit data that must be current or is too large to copy.

How do you plan a data warehousing and BI project?

The Kimball Group's DW/BI Lifecycle is a method that treats both halves as one project, and Kimball says thousands of DW/BI project teams have used it. Its core tenets are to focus on adding business value across the enterprise, to structure the delivered data dimensionally, and to develop in manageable increments instead of one big-bang release.

The working tool is the enterprise bus matrix: business processes as rows, dimensions as columns. Kimball recommends using it to set priorities with business management and implementing one row at a time. For each row the core work is the same: agree on the business questions, model the process, load it, and build the semantic layer and reports, with the data and BI application tracks running in parallel. Kimball names business acceptance of the DW/BI deliverables, in support of decision making, as the overall team goal. That is a better test of success than the number of dashboards shipped.

What goes wrong when warehousing and BI are planned separately?

• Conflicting numbers: each dashboard defines revenue its own way, and meetings turn into arguments about whose figure is right.
• Reports on raw tables: BI authors rebuild cleansing and joins inside every report, and small differences multiply.
• Mixed grains: a fact table holding both daily totals and individual transactions double counts as soon as someone sums it. Kimball's rule is that different grains must not be mixed in the same fact table.
• A model built without questions: a warehouse loaded before anyone asked which decisions it supports tends to have plenty of data and few users.
• Silent staleness: imported models without a refresh plan show old data without saying so.

Most of these are design problems, not tool problems, and they cost less to fix in the warehouse model than in the report layer.