FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Data is at the Heart of Enterprise-Wide BI and DW

BI Solutions

My online series of columns has been focused on the need for businesses to "get serious" about their approach to developing an enterprise business intelligence (BI) and data warehouse (DW) capability. When pursuing this capability it is important to adopt a holistic view, followed by disciplined investment and execution. To develop the future vision for this capability, you should consider seven interrelated areas:

  1. Strategy
  2. People
  3. Process
  4. Metrics
  5. Applications
  6. Data
  7. Architecture

My next few columns will explore the key considerations of the "data" focus area.

At the core of an enterprise BI/DW program is the data that is being made available to the business users who are looking to make better decisions using this information. Properly managing this information is the most critical activity of the enterprise BI team. It is also the area that is fraught with the greatest challenges.

If you've thought through the reporting and analysis views that your users need in their BI applications and the metrics and dimensions that they need in these views - you've taken the right first step. This will help you figure out what data you need and what the priorities are for getting this data.

Sourcing

Once you know what types of data you need, you'll need to figure out where to get it.

If the data exists in a source system, you'll want to analyze the data to see if it really will be able to support the reporting needs. This is often done by getting sample extracts of the data and running queries against it to determine the contents. Data profiling tools can help speed up this process and help you do a better job at figuring out what is good and bad about the data contents.

If the data exists in multiple source systems, the data analysis task is more difficult - because you'll need to determine which source is the authoritative source or which data elements from which source are the authoritative elements. Final decisions on what should be considered "authoritative" will need to involve input from the business data owners and the IT source system owners.

If the data does not exist in any source system, you may need to implement a new data collection application in order to gather the info that is needed. Additionally, some data needs are often met by incorporating external data about markets or customers or suppliers and their activities.

Another consideration when sourcing data is how much data to get from each system. A best practice here is an approach I call "touch it/take it." While you are working on getting data from a source, it makes sense to look around and see what else is there that could be relevant to future reporting needs - and work to include this data in the BI databases and in the extract, transform and load (ETL) routines. This helps to avoid some of the inevitable trickle of change requests to the BI databases and ETL processes that will occur over time as additional data elements are needed. This approach is not meant to say that you need to get all data elements from all tables in a given source system - rather it's just an encouragement to keep your eyes open to future uses of data that may not be immediately requested by business users.

Integration

When seriously tackling enterprise-wide integration of data, a best practice is to create an enterprise data warehouse (EDW) layer in your data architecture. The EDW is used as the point of integration of the data from the multiple source systems and subject areas. The data from the multiple systems is brought into the EDW database into a common enterprise data model, and the source data values are mapped to enterprise standards.

In the EDW, the data is usually stored with historical versions of the data elements that you need to keep track of over time. For example, analytic needs may require you to know that the customers in a given state used to be considered part of a certain geographic region but now that state rolls up to a different region. You'll find many examples like this and will need to design the data model and ETL processes to be able to store the multiple versions of the data elements that change over time.

A rule of thumb when developing the EDW is to store the data at the lowest level of granularity that you can anticipate the users needing for reporting/analysis. If you only store data in the EDW at an aggregated level of detail, when the levels at which you aggregate change over time you'll never be able to compare apples to apples. However, if you store the data at a low level of granularity you will most likely be able to reroll up the data to the right levels to meet the changing reporting needs. When designed in this fashion you can ensure that the EDW will be more resilient and able to support the various levels of reporting detail that will be required over time.

Just as importantly, you should design it to the lowest level of granularity that will allow data from one source to be properly integrated with data from another source. For example, when integrating data from two systems that both track data at an employee level - you'll want to store the data in the EDW at the employee level so you can properly merge the data between the two systems and develop more interesting and meaningful metrics.

More to Come

I'll continue this discussion on the "data" focus area in my next column.


Robert Farris, Hitachi Consulting Vice President and Business Intelligence Capability Practice Leader, has more than 19 years of information technology and consulting experience. He has served both in consulting organizations with Andersen Consulting, Navigator Systems and Hitachi Consulting, and in industry organizations with Bankers Trust and American Power Conversion. Farris specializes in developing the strategy for a BI Program, specifically defining and implementing the team organization, architectures, technologies, methodologies and internal processes. Farris is a graduate from Purdue University with a Bachelor of Science in Industrial Management with a minor in Computer Science. He may be contacted at rfarris@hitachiconsulting.com.

For more information on related topics, visit the following channels:



Industry Vendors