Business Intelligence Foundations

March 26, 2009
136 Views

When I stopped at Steve’s house, his wife directed me to the backyard where I found him standing knee-deep in a muddy trench he had just dug along his newly-built family room.

Clearly frustrated, Steve climbed out of the pit and gave me the low-down on his outsourced home addition project. He thought the hired builders knew what they were doing and everything seemed fine at the time.

A few weeks later, however, Steve noticed that the windows didn’t open or close smoothly. They stuck slightly when you slid them up or down. Strangely, one of the windows cracked. Coming home from a week-long vacation, Steve discovered another window had broken. With the help of a carpenter’s level, Steve began to figure out the mystery.

To prove a theory, Steve grabbed his shovel and dug down to the room’s concrete footings. He discovered that the main wooden beams for the new room were not even on the concrete foundation; they were off to the side.

Evidently, the concrete person poured the foundation supports in the wrong place. When the next guys on the building project discovered the error, they must not have wanted to wait for a fix. Instead, they just let their wooden beams use the ground for suppor


When I stopped at Steve’s house, his wife directed me to the backyard where I found him standing knee-deep in a muddy trench he had just dug along his newly-built family room.

Clearly frustrated, Steve climbed out of the pit and gave me the low-down on his outsourced home addition project. He thought the hired builders knew what they were doing and everything seemed fine at the time.

A few weeks later, however, Steve noticed that the windows didn’t open or close smoothly. They stuck slightly when you slid them up or down. Strangely, one of the windows cracked. Coming home from a week-long vacation, Steve discovered another window had broken. With the help of a carpenter’s level, Steve began to figure out the mystery.

To prove a theory, Steve grabbed his shovel and dug down to the room’s concrete footings. He discovered that the main wooden beams for the new room were not even on the concrete foundation; they were off to the side.

Evidently, the concrete person poured the foundation supports in the wrong place. When the next guys on the building project discovered the error, they must not have wanted to wait for a fix. Instead, they just let their wooden beams use the ground for support.

The builders’ solution could be considered short-term, at best (just long enough for Steve to pay them).

After a few rains, the weight of the room caused the beams to sink into the dirt. The walls shifted and windows began to break. If allowed to continue, more serious structural damage was sure to happen.

The cost of fixing the problem after the room was built was going to be much higher than had it been addressed properly during construction.

So it is with Business Intelligence applications. If you build on a weak foundation, you will end up paying more later to fix your sure-to-happen future problems.

If you are thinking about implementing BI, what should you establish as the BI Foundation? I see three main components:

  • Business Intelligence Team (People)
  • Business Intelligence Standards (Policies and Procedures)
  • Business Intelligence Infrastructure (Technology and Data)

If you want a successful BI initiative, critical to the success is obviously executive sponsorship for the endeavor. Once you have that, your first step needs to be establishing a team of individuals to support the future BI infrastructure and users.

You could establish your BI team in different fashions. It might be a formally recognized group with its own organizational structure and funding (such as a BI Competency Center or Center of Excellence) or it may just be an informal “community of practice” supporting BI as a spare-time activity. Regardless, you should define these individuals’ responsibilities, accountabilities, and how their activities are funded.

Next, identify potential resources for membership within the BI team. Depending on how robust you would like to make your BI support, you could consider positions such as: BI Director; BI Architect; Subject Matter Experts (data, operational systems); Analysts (Database, Data Quality, BI); and Mentors and Trainers.

Your BI Team’s first responsibility will be to establish formal standards for the future BI environment, including a shared corporate language, formal agreements, and service-level agreements (SLAs).

Your organization must formally define a corporate language of business terms, which will then be implemented as a standard within the BI environment. For example, you would define the term “profit,” and identify where it resides within the operational systems (or how it is calculated if it is not a physical element).

With an enforced shared language, all BI reports created within the organization will have terms of the same meaning – for example, no report should stray from corporate standards and calculate an alternate version for “profit.”

Your BI Team also needs to formal reach certain agreements about the BI environment. For example: Communication protocols between IT, Business, and BI Team; Change control procedures; and Responsibilities and Accountabilities; and Security.

Your BI Team should establish service-level agreements on important BI topics. These SLAs establish expectations for IT, Business, and BI Team as to their performance. For example, SLAs may be created for guarantees on: Data life-cycle (latency, length of storage, historical archiving, purging, etc.); Data quality (validity of data, guaranteed synchronization with operational systems, etc.); and Services (response times, quality of service, etc.).

With the BI Team and BI Standards in place, now it is time to consider technology and data — the BI Infrastructure.

Conceptually, I break out the BI Infrastructure into three layers: 1) the user interaction/presentation component; 2) a centralized BI repository of data and metadata; and 3) an integration mechanism.

Users interact with BI in two main ways: by automatically receiving information (“push”) and by accessing online applications as needed (“pull”).

Automatically, users can receive regularly-scheduled standard reports, exception reports, and alerts sent to destinations such as printers, e-mail, or hand-held devices. Online users have access to interactive BI applications, reports, queries, analytics, and ad-hoc report writing features. This presentation layer will integrate with Microsoft Office for easy use of tools such as Excel spreadsheets.

The next layer is the centralized BI Repository. This is the company’s “single version of the truth” used for all BI needs. Your BI Team will design the repository specifically for reporting to mask the complexity of the underlying operational systems, and fully document it for the users.

Depending on your company’s BI needs, the BI Repository can consist of multiple data structures, such as a main relational database for general reporting, multi-dimensional cube structures for online analysis (OLAP), and star schemas for ad-hoc reporting needs.

In addition to structured data, a BI Repository might contain unstructured data such as scanned documents, Microsoft Office documents, and other files that are free-form in nature. These types of files typically require special BI capabilities such as content indexing, document management, and search engines.

Also in the BI Repository will be the organization’s definition of abstract terms (hierarchies, Master Data Management, virtual calculations, etc.) and a metadata layer.

Lastly is the BI Integration Layer, which performs the functions of accessing the complex operational data, transforming data into understandable information, and loading the BI Repository. Here, your BI Team will define integration rules such as: Mapping of physical data (Source to Repository); Rules for calculating virtual columns; and Rules for Extract, Transform, and Load operations.

Yes, this sounds like a lot of work for just the foundation.

If you are considering skipping this critical step and jumping right into building BI applications, think about Steve standing in a muddy pit. Then picture yourself in a similar situation in the near future.

If you still decide to move forward without first implementing a solid BI foundation, be sure to have your own level and shovel nearby.

http://BI-Software.Blogspot.com