Is Your BI Problem Hiding in Your Data Warehouse?

Billions of dollars are spent each year on business intelligence tools. Every year, these tools improve, incorporating better reporting and analysis, visualization, dashboards, forecasting and planning. With all this advancement, shouldn’t the use of BI be a productive, positive experience?

Yet according to the 2008 Forrester study, “Current State of BI Adoption in Enterprises,” only 40 percent of BI users find “most” or “almost all” of the information they need available and accessible from their BI applications. The BI tools do what they can with the data they have. Yet, if the data is incomplete, inconsistent, or inaccurate, even the most sophisticated tools can't provide managers with the right answers to basic business questions. Moreover, when business conditions change, managers have difficulty using their BI tools for answers on how changes will affect their business. Their BI tools can't access the data they need quickly enough to answer these questions.

An examination of the process used by most companies to develop their data warehouses reveals several points where disconnects occur and shows why changes are often long and costly. Fixing these disconnects is key to deriving business benefit from BI.

Lost in Translation

There are many areas of disconnect in the conventional data warehouse design process. BI's true potential can be realized if 10disconnects are avoided.

Disconnect #1: business requirements. Business users in DW and BI projects usually document their requirements with text, lists, diagrams and report descriptions. However, there can still be significant disagreement and conflict. Typical issues include disparities between departments such as different classifications of the same things, unclear business definitions and inconsistent or missing business rules. Too often, project leaders expect to define and agree upon an accurate set of business requirements in a single session. Given that business requirements can be difficult to articulate and frequently shift, such an expectation is not only unreasonable, it can be project-threatening from the start.

Disconnect #2: The logical model. Data architects combine their understanding of business requirements with their understanding of the source data to create a logical data model. Business representatives find it difficult to relate to this technical representation of the data they know, which results in uncertainty about the exact details of their business requirements. Because of this, business representatives tend to make few comments and corrections, then the data architect and the business representative “agree” on the logical model so that the project can progress without a full understanding of what they have defined together.

Disconnect #3: The physical design. The next step is to convert the logical model to a physical database schema design. Data modeler tools convert it to an initial physical design where database designers add indices and foreign keys, declare the primary keys and data types, and denormalize or normalize tables as needed. The architect may apply data warehouse design best practices, resulting in a design that’s different from the logical model agreed with the business representatives. Complexity results in further discussions between the data warehousing professionals. The first version of the design is sealed in the modeling tool, which generates an impressive database creation script. This milestone in the project represents the point where the data architect hands over the responsibility of the design to a separate development team.

Disconnect #4: DBAs fine-tune the design. With the design controlled by the development team to build the data warehouse, the database creation script is run against the DBMS to create the data warehouse physical schema. At this stage, database administrators make additional edits for standardization or performance fine-tuning purposes. Edits may also be required to adjust the schema for the underlying DBMS and hardware platform. The business has no visibility into these changes because the changes are deemed to be just “technical” in nature. However, those changes may unintentionally impact the business requirements.

Disconnect #5: Changes to the design during development. During the development phase, the database design may need additional changes, often for technical reasons. When initial loading starts and tables are loaded with data, we discover issues like duplicate identifiers, nonstandard data values or cardinalities in the data are not as we expected. The development follows an iterative method, so early prototypes or identified issues are resolved with business representatives who may propose changes. Where will all these be applied during development? Should the project manager incorporate them into the data modeling tool and then recreate the development instance? Very few go back to the data modeler because of time or resource restrictions. Nearly all such changes identified during the development phase are directly applied to the development instance. When this occurs, the logical model becomes totally disconnected from the physical database, and we no longer have a logical record that ties to a physical instance.

Disconnect #6: The data modeling tool is offline! Today’s data modeling tools allow changes to the logical or physical model and then creation of a “delta DDL” (database modification script in Data Definition Language). However, there are changes that are not supported. Even if a delta DDL is created, it is unlikely to work on the development DBMS because of changes made directly to the production instance. Furthermore, graphical data modeling tools are available for use by data architects in the enterprise architecture group whereas the development project team may not be using or given access to these tools.

Most data architects are not involved in changes subsequent to their initial design. The best organizations would still capture the as-built design back into the data modeling tool – perhaps using reverse-engineering – but such a best practice still serves only documentation purposes. The business representatives have already been cut off because the IT experts are talking in a different language now, and they are waiting for the user acceptance test anyway. Changes proposed after the UAT, and then any changes implemented during the production life of a data warehouse, face the same treatment; they are applied in the DBMS platform, not in the data modeler tool.