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.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access