Why Build a Data Warehouse?

For decades, companies have been spending millions of $$$’s on systems to streamline and automate the execution of the business plan.  Over time, these systems have become more and more integrated within a companies operations and between business partners.  However, due to the granularity of the data stored in these systems, management has difficulty accurately monitoring how well the business plan is being executed. 

The alternative to making decisions based on feelings or assumptions is often spending hours each month “summarizing reports” to get a macro view of their environment.  Often the job of monitoring the success of business practices is so tedious that managers often fore-go the process altogether and try to rely on their gut feelings or “biggest daily headaches” to drive their decision making process.  When a manager does need hard data to back up their assumptions, they have to wait for weeks for the backlogged IT department to give them a report (hopefully the right one).

Dimensional Model based data warehousing is an enabling technology that can put real answers in the hands of your management team in an efficient and timely manner.

Data Orientation- Dimensional modeling provides meaningful data to end users by reorganizing transactional data to a dimensional structure, suited for reporting instead of operating efficiency.

Complexity- By applying consistent and intelligent business rules to raw data, information in the dimensional model can be presented in a simple and straightforward manner, instead of the complex structure found in the transaction systems.

Integrity - By putting transactional data through rigorous tests and applying business logic specific to the companies operations, data warehouse users can be certain that they are viewing complete, accurate, and consistently defined information.

Sources and Accessibility – Instead of having to go to the various information brokers in your organization to get information from various subsystems, dimensional modeling allows disparate data from any enterprise application to be brought together and presented in a “one-stop-shop” approach.  Many OLAP (On Line Analytical Processing) tools are deployable over an intranet (or extranet), and can be configured to provide an experience that is unique to each user – giving them access to what they need – and cutting out the noise that they don’t.

Focus – With data organized for reporting, analysis tools can be used to efficiently ask questions about the data, providing insight into how the business operations are really doing.  By utilizing one of the many powerful OLAP (On Line Analytical Processing) tools, managers can view the information they specifically want to see – and if necessary adjust the level of detail or type of information that is included in the report.

The Data Warehousing Process

At Integration Management, we believe the key to successful decision support application development and maintenance is an effective and efficient data warehousing process.  To make the end product useful, specific attention must be given to the configuration of back-end processes.  To make these processes more effective over time, it is important to have back-end processes that can evolve as the business model becomes more understood or changes over time.  That is why nearly all IHDS utilities were designed to operate from easily configurable metadata. 

Figure 1 - Data Warehousing Process

Readying Source Data

The process of transforming transactional data from multiple sources into an integrated, analysis oriented dimensional structure begins with the replication of transactional data in a SQL Server data store.   A key element of this step includes determining the frequency in which the SQL server data store is updated with live data.

Business Rule Application

The heart of a good data warehouse is the process of applying business rules to the raw transactional data to better model the business processes.   It is in this area that organizational knowledge and business procedures that are kept outside of the transaction systems are applied to the transaction systems data, creating a more complete picture of what is happening.

Once the required data is “local” to the warehouse in the replicated data store, “scrub” business rules can be applied to the data to search and respond to incomplete or invalid data.  When the data set is acceptable, business rules are used to categorize and classify objects and events in the transactional data. 

An example of a valuable business rule is the ability to enforce referential integrity on the transaction data.  Since some managed care applications do not utilize a true relational database structure, the link between important records is often lost.  For example, the provider identified on the claim may not even exist in the provider file.  Depending on the safeguards used during the data entry or claim adjudication process, this data condition may not be caught.  When standard reports are developed for this particular claim, provider information will not be included, and in some situations, claim may not even be displayed.

After the scrubbing and classification process are complete, the process of mapping the data to the dimensional structure and calculating required business measures is executed.  The dimensional structure transforms the data from a complex structure keyed and organized according to the processing of transactions to one that is more intuitive to business analysts, representing how they view the business critical data in your organization.

Multi-Dimensional Modeling

Once the data has been organized in the “Star Schema” dimensional model, it is ready to be used for reporting and analysis.  The dimensional data can be used as the data source for various reporting applications such as Crystal Reports and Cognos Impromptu.  Multi-dimensional cubes can also be generated to take advantage of OLAP tools such as Cognos Powerplay or Proclarity.

The key to dimensional structure is the organization of data into dimensions and facts.  Dimensions hold the “qualitative” characteristics of your data, such as

• Members age, group, plan for specific claim,
• Dates like service, claim, post, and check date
• Claim diagnosis or procedure codes or categories
• Relationships such as IPA, PCP

Facts store detailed or summarized qualitative data that is either detail or summarized values of specific numeric or time data associated with your transactional data.  Examples include:

• Cost values on transactions (claim paid amount, claim billed amount, capitation paid amount, etc)
• # of occurrences of specific events (admits per 1000, deliveries per 1000, member months)
• time between events (inpatient days, claim receive date to check date)

After the data is organized into facts and dimensions, it is ready for business analysis driven query development and OLAP tool integration.  Records are efficiently linked providing short query time even for the largest of data sets.

Lance Dowling, Principal
Integration Management, Inc.