Tuesday, July 8, 2014

Data Warehouse versus Business Intelligence - By Daniel Rubiolo

The traditional definition of “data warehouse, or DW” from late ‘70s/early ‘80s involved the end-to-end solution for business users… from extracting data and aggregating it into special data models, the queries, all thru the applications/reports the users interacted with to consume that data.

In the ‘90s, the concept of “Business Intelligence, or BI” came to describe the technologies to take this data and present it to users as dashboards, reports, scorecards, and graphical analysis. This happened because early adopters of data warehousing only aggregated data in repositories to keep history, but they did not build the last mile of user presentation and consumption, leaving the DW concept just for the backend databases.

However, it became clear over time that BI could not exist without DW, where the data warehouse is the foundation for business intelligence. Today, because the tools evolved to cover more and more of this end-to-end scenario, these two terms are widely used interchangeably. I use BI from now on.

New meaning for BI: Business Insight

I propose we forget about the traditional concept of “BI”: those terms refer mostly to database design practices and tools, instead of what they intend to solve for the business.

First, we need to make clear what BI means when faced with building a BI solution. When considered as “Business Intelligence”, we have two dynamic terms. We model the business with these BI systems, where the business evolves all the time responding to strategies and market conditions. These BI models provide information – intelligence – to business’ decision-making, which in turn trigger new questions and business scenarios that will require new information. We learn something about the business, and new questions arise. This means that when we start a BI project, we already know it will evolve dynamically all the time because its two components (business and intelligence) will evolve all the time. Therefore, a BI project CANNOT follow a traditional software lifecycle, because in doing so, the solution will be obsolete as soon as released.

Second, a BI solution does not mean having some reports, dashboards, and scorecards from our operational systems or applications. To consider it BI, it must apply business logic to enrich the data with analytical insights for business users. Without this additional business logic, our systems may only tell the users what they already know. We have categorizations that can add value to our data, such as organizational taxonomies, business categories (i.e. critical list; Compliance versions), linkages to other data sources, and history… the interconnection between business processes that provide an end-to-end view of the business and its evolution.

This describes why I recommend we start considering BI as “Business Insight”, so we focus on the business value it provides, instead of the technologies that implement it.

Key components of BI

Successful BI depends on three fundamental concepts:

  1. Focus on the business.
  2. A special structure for the data delivered to the business via several mechanisms.
  3. Iteratively develop the overall BI environment in manageable lifecycle increments rather than attempting a galactic Big Bang.
A true BI system should address these business concerns:
  • “We have mountains of data in our apps, but we can’t access it.”
  • “We need to slice and dice the data in many ways.”
  • “You’ve got to make it easy for business people to access the data directly.”
  • “Just show me what’s important.”
  • “It drives me crazy to have two people present the same business metrics at a meeting, but with different numbers.”
  • “We want people to use information to support more fact-based decision making.”
  • “We spend way too much time in producing data for reviews.”
  • “In spite that we have an official scorecard, during review sessions some offline spreadsheets come out questioning the data. From there, the review is spent second guessing numbers instead of driving business discussion.”
These hint to a key element on driving a BI solution: the key skills needed to manage a BI project have to have a mix of DBA and MBA. A pure IT technical DBA (database administrator) can design the databases, reports, and applications, but will fail delivering the right information users need to drive their business decisions because won’t understand the business dynamics. Other projects may have a pure MBA collecting requirements, but will fail balancing technology limitations/constraints and setting the right expectations back to users. Clearly, BI projects need a mix of DBA/MBA very close to the business.

ETL Systems

A little known fact about building BI systems: 70% of the resources go into designing the data model, creating the supporting databases, and implementing the value added processes mentioned earlier (called “ETL: Extract, Transform, Load”); just 30% go to presentation UIs and reports. This because the ETL:

  • Removes mistakes and corrects missing data.
  • Provides documented measures of confidence in data.
  • Captures the flow of transactional data for safekeeping (history).
  • Adjusts data from multiple sources to use it together.
  • Correlates data across disparate systems to get views into the business that you could not get otherwise.
  • Structures data for use by end-user tools.

Data Marts and Data Warehouses

Another little secret of BI systems resides in that we can apply the BI or DW concepts to many business levels, and concatenate them. Many times, big applications generate mountains of operational data as we use them to execute business processes, but it becomes difficult to use all that data to monitor and measure those processes, and evaluate its impact to the overall business.

Usually we need an Operational BI system on top of these big apps, called “Data Mart”, or departmental DW, and thus reserving the Data Warehouse term for the overarching business BI system.

Again, I will use just “BI”, clarifying when we mean an operational one or business one.


In brief, we can define a BI system as the solution for gathering data from multiple sources, transforming that data so that it is consistent and stored in a single location, and presenting its information to business users for analysis and decision-making.

Original Source

Daniel Rubiolo - Via Blog MSDN

No comments:

Post a Comment

Share Your Inspiration...