Business Intelligence Explained for the DBA

Sun Nov 23, 2008 by Derek Comingore in business-intelligence-nuggets

Business Intelligence (BI) systems exist for one sole purpose: to support improved organizational decision making. BI can be accomplished using a variety of techniques and solutions. There is however pros and cons of the various methods one can implement to support BI. Additionally, you will typically see a life cycle of BI in an organization as it matures:

Maturity	Solution	Pros	Cons
1	Line of Business Reporting	· Easy to implement · Lowest Cost of Entry	· Contention with LOB application server resources · Poor Data Quality · Silo Reports (single business process) · Poor Performance for large datasets
2	Data Marts	· No contention with LOB application server resources · Improved Data Quality · Better Performance for large datasets	· Slightly more difficult to implement · Silo Reports (single business process)
3	[Enterprise] Data Warehouse	· No contention with LOB application server resources · Improved Data Quality · Better Performance for large datasets · Cross Business Processing Reporting (non-silo)	· Most difficult to implement · Highest Cost of Entry

As you can see, a Data Warehouse is usually the best approach for an organization’s BI needs, however like most valuable propositions in life it comes with a higher cost of entry. The hardware, software, and staff skill sets required to implement an enterprise data warehouse are much higher than simply doing basic line of business reporting. Conversely, for that investment organizations reap the most benefits modern day BI systems can provide them with!

BI Solutions

Instead of describing in detail how these solutions work I will use the following diagrams to better illustrate:

Climbing the BI Maturity Levels

Most organizations will follow the path of BI maturity from the basic line of business reports to data marts to an enterprise data warehouse. By following this path an organization can slowly reap additional value from their data assets without a large initial ‘road bump’ (the initial cost and development) to obtain the data warehouse. The bad part about this approach is that once a report is published it is very hard to take away (or replace) them. Change is hard is the reality.

Going from Nothing to BI Maturity Level 3

Rare, but occasionally an organization will go from nothing to an enterprise data warehouse. In reality, this truly never occurs because every organization (even startups) will perform some level of basic line of business reporting. In this context I infer that the organization is just now agreeing that they need this grand pie-in-the-sky concept called BI and they initially commit to the data warehouse solution. The bad side of this approach is obviously the initial cost and development. On the good side however the organization is committing to best practices from the start!

BI Components

Let's now review the common BI components as pictured above.

Extract, Transform, and Load (ETL)

The ETL process is built for the purpose of what the acronym stands for: data extraction, transforming, and loading into destination data mart or data warehouse models. These extracts are commonly scheduled for daily, weekly, and monthly executions.

Data Marts & Data Warehouses

The data marts and warehouse are implemented in two different ‘flavors’: Normalized and Denormalized models. It use to be that most OLAP products (cubes) required their underlying models to be implemented into denormalized models, however as of the past few years this is not always the case. That said, best practices still dictate we implement a dimensional model…

Dimensional Modeling & Denormalization of Schema

The reason we implement our data marts and warehouses using Denormalized schemas is because of a concept called dimensional modeling. Dimensional modeling is a practice of modeling two types of tables for various business processes: Fact Tables & Dimension Tables. By implementing dimensional models we obtain a schema that is highly optimized for the heavy read workload found in data mart and warehouse systems. Additionally, we provide a ‘single version of the truth’ by using what is known as conformed dimensions.

Reports

Need I say more? Reports can be of all sorts and ‘flavors’ but basically they usually provide a pre-defined layout (metadata) and business data to end users. They may be sourced from OLTP databases, data marts and warehouse, or even data mining models.

OLAP

Online Analytical Processing (OLAP) is implemented by creating multidimensional objects most commonly known as cubes. These cubes are formed from two major objects: dimensions and measures. These products provide implementation of what is known as Fast Analysis of Shared Multidimensional Information (FASMI). If you have never viewed the contents of a cube, upon first sight you will quickly see why OLAP is so valuable for BI:

As you can see we can drag-and-drop dimension attributes on rows or columns and place our measures in the center grid (pivot table). What you cannot see via a screenshot is by simply dragging over a new attribute or removing an existing one from the pivot table the results are immediately refreshed! Cubes allow us to provide exploratory functionality to information consumers.

Data Mining

Data Mining is the ‘new kid on the block’ but its promises hold very high. If you think about the maturing of reports we have available today:

1. Basic Reports used for basic trend and fact observation

2. Exploratory Reports (Pivot Tables & Cubes) used for trend and fact exploration

3. Data Mining Reports used for automatic trend and fact observations

As you can see with Data Mining technology we no longer force the end user to manually locate trends and in fact we can somewhat predict what the future may hold too based upon the past. Data Mining can be sourced from both relational and multidimensional (cube) models.

Scorecards & Dashboards

Scorecards & Dashboards are geared for the end user that needs to quickly review business metrics and find out how they are doing. These specific types of reports are usually geared at executives and other higher-level end users and commonly include trending information in the form of what is known as a Key Performance Indicator (KPI).

Enterprise Search for BI

Another recent trend in BI solutions is providing end-users with the ability to search through the hundreds of reports they may have available on a particular keyword. You may want to see only the sales reports or banker scorecards.

There you go…BI explained in 10 Minutes for the DBA!

There is much more information available on each and every one of these topics, however I hope that by providing the ’10,000 foot view’ DBAs who are new to BI now have a little better grasp on its major concepts and practices.

Legacy Comments


Rock 2008-11-26	re: Business Intelligence Explained for the DBA Good Article

Log Buffer 2008-11-28	re: Business Intelligence Explained for the DBA 'On Microsoft BI, Derek Comingore has a much-needed item, Business Intelligence Explained for the DBA—”BI explained in 10 Minutes,” as he puts it.' Log Buffer #125

columbia jackets 2010-10-21	re: Business Intelligence Explained for the DBA The hardware, software, and staff skill sets required to implement an enterprise data warehouse are much higher than simply doing basic line of business reporting. Conversely, for that investment organizations reap the most benefits modern day BI systems can provide them with! snow boots for women \| columbia sportswear outlet \| cheap mac makeup \| the north face jackets womens snow boots \| columbia sportswear \| cheap makeup \| cheap north face jackets

xfzmfvwrcd 2012-02-06	zmlfy AdtCJF kenefwrnqqky, [url=http://rzhdjbidmuxs.com/]rzhdjbidmuxs[/url], [link=http://bcgmzqchhkze.com/]bcgmzqchhkze[/link], http://qtxdabzpteex.com/

Rpblkmym 2012-02-08	knrscsgq pimpmyspace generic - generic

Zzzefruq 2012-02-08	kbwefgkzn bloopers enlargement - enlargement

Pcayypdd 2012-02-09	zepwaunhe topsider payday - payday

Cmwerjuy 2012-02-26	qknot airtours kopa arcoxia - kopa arcoxia

Ryvijpbt 2012-02-26	urplj garlic acheter strattera - acheter strattera

Jgcwpjvb 2012-02-26	aycqm revistas comprar glucovance - comprar glucovance

Rqouoovi 2012-02-27	ftkqqnrle photogrammetry acquisto ayurslim - acquisto ayurslim