Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Oct 26, 2010

The Business Intelligence Framework - Built to Last

By Denise Rogers

The success of a business intelligence program depends on the approach or methodology used to implement the framework and the related components. Incorporating key activities in the Software Development Life Cycle and the use of an architectural framework can implement business intelligence programs that are scalable, maintainable and aligned with the enterprise strategic direction.

Over the past few years, business intelligence has risen in prominence and popularity. One of the primary reasons for this surge in the need for business intelligence services is the recognition of the tremendous value it provides. An organization with business intelligence services can now see their data from various perspectives. The benefits of seeing data from different viewpoints enables an organization to gain insights into new markets, identify new products to existing markets or gauge the impact of their products in various marketing regions.

The working definition of business intelligence for this article is that Business Intelligence (BI) is a set decision support applications that deliver information used in operations such as forecasting, analytics, dashboards, querying, reporting and data mining.

However, a major part of the success of a business intelligence program is the approach or methodology used to implement the framework and the related components. For example, if the approach used is one that simply constructs a data mart to support a specific set of business functions with no consideration for the organization strategic direction, scalability or maintainability, this business intelligence solution will certainly become a prime candidate for an expensive re-engineering project once those same business functions outgrow the application.

Now having said all of this, let's take a look at how incorporating key activities in the Software Development Life Cycle and the use of an architectural framework can implement business intelligence programs that are scalable, maintainable and is aligned with the enterprise strategic direction--and of course, with documentation playing an integral part of the approach. Each phase should produce documentation that is used as a method to obtain signed approval to end one phase and begin the next.

Initial Assessment

One of the most important activities is conducting an initial assessment. This assessment has two components to it; business and IT. The purpose of the assessment is to identify the business needs and the impact that a business intelligence solution would have on the organization. It is an effective way to identify the amount of time, cost and risk to the organization. It also provides the justification for business intelligence. This is a huge plus as it enables continued executive sponsorship.

The IT assessment is a complete evaluation of the current infrastructure. This answers the question "Does our organization have the tools and infrastructure to facilitate a BI solution?" The answer is in the results of a readiness assessment of the organization's ability to support a business intelligence framework and the related applications and solutions. The readiness assessment should include a detailed review of the hardware, BI and database software and operating system.

With this information, the team has everything it needs to produce a high level conceptual design of the BI architectural framework and an executive summary of the assessment.

These artifacts are the important business and IT drivers for the remaining activities in the SDLC.


Planning is the next set of activities that puts the BI framework in place that really and truly, puts everything in motion.

It is within this phase that the scope is clearly defined into sufficient detail that sets the expectations of senior management and in essence gets all members of the team to a common understanding of the goals of the project.

Once the scope has been defined and accepted by all members of the team complete with signed approvals, the next step is to define roles and responsibilities needed to complete the BI project. These roles and responsibilities should include both business and IT. It is very important that these roles are defined such that all is taken into account from a budget and time perspective. This task also facilitates the communication protocols for everything from project status reporting to incident management. These roles should include but are not limited to business stakeholders and sponsors, business SMEs, business analysts, architects, DBAs, infrastructure engineers, ETL developers, BI developers (should be specific to the software solution such as COGNOS, SQL Server SSAS, Business Objects, etc) and project managers.

Also in the planning stage, depending on the level of BI maturity in the organization, a training assessment should be conducted to gain an understanding of whether training is required for the BI suite of tools being deployed into the organization. This assessment has direct impact on cost and time lines as well.

The end results of the planning phase should be a fully fleshed out project plan with assigned responsibilities and time lines with alignments to the detailed budget for the project.

Requirements Gathering

With the project plan and budget approved, the functional and non-functional requirements are defined. The functional requirements are the articulated business needs that must be met with this BI solution. It is within these work sessions that requirements related to various types of reporting (ad-hoc and canned), the need for dashboards, key performance indexes (KPIs) and presentation displays are gathered and documented. Metadata management activities are a part of the requirements gathering process as it relates to the business context definitions of data. Also included in the requirements gathering from a business perspective is identifying the data sources that will be used to populate the data mart that will be the supporting data layer for the BI solution (in most cases). Ideally, the identification of these data sources should mainly be contained in the Data Warehouse. This is an important component. Why? Because if the data for the BI solution is sourced from the Data Warehouse, there is a high level of confidence in the quality of the data and minimal use of an ETL solution. If, however the data sources are disparate then the opposite holds true.

Non-functional requirements includes gathering information related to security, an essential and integral part of the BI solution. It is extremely important that the information being delivered is being consumed by authorized users only. Remember, each and every solution/application within the organization must survive an internal or external audit. Think Sarbanes-Oxley and HIPAA.

Other non-functional requirements include the definition of infrastructure needs as it relates to hardware, software and network components. These requirements should address the needs of a BI solution in terms of server memory configuration, use of SAN/NAS for storage, number of CPUs per server and parallel processing capabilities.

Once all this information and data has been compiled, a business requirements document is produced that frames the context of the business and IT requirements related to the BI solution that are in scope of the project.

Design Guidelines

The purpose of the design phase is to translate the business requirements document into design specifications. These specifications will be used as the blueprint for constructing the solution.

However, in designing a BI solution, there is a further refinement of the requirements as it relates to the type of data models that will be designed to deploy the data mart. This means that based on the business needs for reporting and the BI solution proposed, design decisions are made on whether a star schema or snowflake schema or hypercube be used as the supporting data layer for the application. Factors that weigh heavily in this decision are primarily related to the type of BI reporting. For trending, drill through, slice and dice, and analytics, then use of the hypercube works best. However, caution here, loading of the hypercube is resource intensive and complex and should be factored into the availability SLAs. So each design decision does have its tradeoffs; choose wisely!

Other design components within this phase include major enhancements to the security framework. This is often sensitive data. Only the users meant to view specific sets of information should have the required permission. Also included are infrastructure enhancements related to storage, server and network configurations.

The document created that captures the output and results of this phase are the Technical Design Specification.

Construction and Deployment Guidelines

The construction phase is just that. It is constructing, creating or installing the components that have been presented for the BI solution. However, before the actual construction can begin, the details of the Technical Design Specification should be reviewed with all members of the business and IT teams to ensure that there is a common understanding of all components of the BI solution. All work efforts should be focused on building the components of the BI solution. This includes the creation of the data models, the database or hypercubes, development of the application and presentation layers, installation and upgrade of the hardware and software.

The message here is that completion of the previous phases ensures that the BI solution is constructed and deployed with minimal refinements and modifications of the design specifications.

The Last Word

Following the software development life cycle with a specific focus on the build out of the BI solution with awareness of the organization needs for information will enable the BI program that scales to meet the growing needs of the organization.

» See All Articles by Columnist Denise Rogers

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM