Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

The CRM-Ready Data Warehouse:
Customizing Packaged Business Intelligence

  Column published in DM Review Magazine
May 2002 Issue
  By William McKnight

This continues my recent emphasis on packaged business intelligence (PBI) by discussing some of the major means of customizing a PBI solution. The major areas of customization are the data model, the transformation rules, the architecture and the ad hoc data access.

Data Model Customization

The data model is the area of a package that will require the most customization. Any generic model will have numerous deficiencies.

Hopefully, the model is customized to the business relationships and the business needs -- not just to the sources of the data. You may not be able to populate parts of the PBI data model with the source data you have available, and this may cause the applications to encounter problems.

Certain models I've worked with have expected one row for each item purchased (i.e., purchase three widgets in a transaction = three rows) when the source data generates one row for each product purchase regardless of quantity (i.e., purchase three widgets = one row). Another example is returns managed separately in the operational environment but needing to be simply negative sales for the data model. Still another example is the operational environment separating customers into active, inactive and prospects when the PBI model needs all of these customer views together. A final example is that the number of levels in the dimension hierarchies is usually insufficient.

Of course, some data discrepancies can be modified in the ETL process. Your choice often is to change the model or change the ETL process. You can put a lot or a little (relatively speaking) work into building or customizing any major subject area of your business such as sales, customers or products. The depth of effort appropriate for you will depend on your application needs and the time and budget you have to spend on modeling; but eventually, if not initially, these subject areas become very expansive and inclusive. As discussed in last month's column, understanding how you will make these changes and mix vendor upgrades over time into your changed model is a key purchase consideration.

Transformation Rules

We've talked about some of the structural transformations that may be needed in order for the data to fit the model, but the other half of the transformations that should be considered is in the area of data values for data cleansing.

I've learned of PBI implementations that have loaded data straight into the data warehouse, only to have the users reject the warehouse due to the miserable state of the data -- missing elements, fields containing multiple sets of data and data with inconsistent representations. This was despite the fact that they had been using the same dirty data in the operational environment for years! Use of a data warehouse tends to uncover new uses for data. These uses require data cleanliness which is easily overlooked when you have to populate a model for applications that the business needs quickly.

Data cleansing issues are often unique to a shop, and PBIs do not tend to focus on this. How could they? But you should! Take care of this high-risk factor to data warehouse success with a data quality program as part of your PBI environment customization.

Architecture Layering

A single-layer architecture is often present in an uncustomized PBI. However, depending on your plans for data, user and usage scaling and your chosen data warehouse machine(s), it is often necessary to break the architecture into "n tiers" allowing for separation of load/transformation from access cycles, breaking access cycles into more manageable pieces and customizing data into multiple representations appropriate for multiple usages.

If needed, the work required could be significant. The key is understanding your scale and your architecture capabilities and "if and when" creating a staging layer and/or data mart layers is necessary. An alternative is to increase machine capabilities. Be sure to avoid putting an architecture in place that works for the first few months, only to begin yielding poor performance and then requiring abatement of usage.

Ad Hoc Data Access

Because most PBIs are built for data access through PBI applications and because ad hoc data access needs are custom, it's not surprising that the ad hoc layer is usually scant or missing in PBIs. As the data model is relational and open for access with your favorite data access tool, here is definitely an opportunity to extend the data access capabilities.

It will not be long after a PBI is in production until usage is needed that is appropriate for ad hoc access. The applications are something you'll probably customize the least because often this interface is the sizzle that sells the steak (the architecture), and you need to make sure the applications work.

From within any data warehouse, custom or packaged, learn to accommodate a variety of analytical applications - those applications with data needs and perhaps their own data mart -- in the environment. Get these applications the data they need from the architected data warehouse environment so they do not create additional extract strains on the operational environment and propagate data quality problems.


For more information on related topics visit the following related portals...
Business Intelligence (BI).

William McKnight has architected and directed the development of several of the largest and most successful business intelligence programs in the world and has experience with more than 50 business intelligence programs. He is senior vice president, Data Warehousing for Conversion Services International, Inc. (CSI), a leading provider of a new category of professional services focusing on strategic consulting, data warehousing, business intelligence and information technology management solutions. McKnight is a Southwest Entrepreneur of the Year Finalist, keynote speaker, an international speaker, a best practices judge, widely quoted on BI issues in the press, an expert witness, master's level instructor, author of the Reviewnet competency exams for data warehousing and has authored more than 80 articles and white papers. He is the business intelligence expert at www.searchcrm.com. McKnight is a former Information Technology Vice President of a Best Practices Business Intelligence Program and holds an MBA from Santa Clara University. He may be reached at (214) 514-1444 or wmcknight@csiwhq.com.

View Full Issue View Full Magazine Issue
E-mail This Column E-Mail This Column
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.