By: Ken Adams

 
If you interviewed a handful of database professionals and asked them to define an Enterprise Data Warehouse (EDW, for short), it’s likely you would get a slightly different interpretation from each of them! That’s not to say any of them would be wrong – it’s just that there are different ways to setup and apply an EDW.

In this blog post, I’ll give you my personal definition of an EDW, discuss the alternative, talk about the value one brings, and – finally – provide a real life example of an EDW at work.
 

What is an Enterprise Data Warehouse?

An EDW is a consolidated database that brings together the various functional areas of an organization and marries that data together in a unified manner.
[bctt tweet=”An EDW is a consolidated database that brings together the various functional areas of an organization and marries that data together in a unified manner.
“]
Rather than having segmented data warehouses that line up with individual functional areas such as accounting or manufacturing, the EDW centralizes the data from all of those groups. By doing so, the data is normalized and conformed so that reporting across the organization is possible for planning, forecasting, and other purposes.
 

What is the Alternative?

You may have noticed that I alluded to the alternative in the paragraph above. If you forego the concept of an EDW, then each functional area within an organization would have its own data warehouse with its own specific data extracted from a transactional system. Each data warehouse would be tailored to meet the needs and answer the questions of that specific group.

On a finer level, the subgroups might have their own data marts, which are “subsets of a data warehouse that are usually oriented to a specific business line or team”. Within a group like accounting, the payroll and accounts receivables teams could have separate data marts. The marts would likely be highly specialized and built to answer a specific set of questions. In payroll, the mart might answer: “how much did we pay each employee?” and “how many PTO days did each employee take?”

Maintaining individual data warehouses and marts for specific functional areas is a common practice that works well for many companies, but it begins to fall short when you want to take a holistic view of the company. This is where an EDW steps in.
[bctt tweet=”Maintaining individual data warehouses and marts for specific functional areas is a common practice that works well for many companies, but it begins to fall short when you want to take a holistic view of the company.
“]
 

What can be Gained from an EDW?

Suppose your company has embarked on a heavy marketing campaign that includes advertising spots in both print and digital media, as well as television and radio. How can you measure the effectiveness of the campaign? Having disparate data warehouses or smaller data marts would be painful. Just imagine. First, you would need each group to provide reports or data extracts from their databases. That data would need to be transformed, likely conformed, and possibly loaded into another database. Only then could query and report tools be used to gather the necessary answers.

An EDW would have already handled the steps above – extracting, transforming, conforming – and the data would be available in a single model for querying. Most likely reports would already be in place. And with a few parameter modifications, you could measure important metrics like conversions or website hits since the campaign started.
 

A Real World EDW in Action

Recently, I worked on an EDW for a large hospital and the hospital system had (and has to this day) a mixture of custom software and off-the-shelf software for tracking patient medical information, pharmacy data, claims information, and so on.

The goal of the EDW was to provide a complete picture of a patient across the various areas of the hospital. This was accomplished by identifying and wrangling data from the disparate systems and then loading that data into a consistent and conformed model.

In the end, a single, longitudinal patient record could be created. The record tracked the patient’s demographics, doctor visits, diagnoses, medications, and more. Users no longer needed to access multiple data systems to answer their questions. Instead, the EDW empowered them by providing access to a single location for their patient care information as well as tools to be used to perform their analytical deep dives.

Anyone who then had access to the EDW could now answer simple questions about a patient, such as:

• What facilities have they visited?
• What drugs have been prescribed?
• How much have they been billed?
• Have they called into patient outreach centers?

With their newly consolidated patient care information, research teams began identifying cohorts of patients and focusing their time on these groups. Groups of patients that were prescribed certain medications or were diagnosed with certain diseases were tracked, and specific reports and applications were developed around them.

These researchers began constructing data marts out of the EDW, which was made much easier by the fact that the heavy lifting from an ETL perspective had already been completed. Relationships between entities were established and enforced earlier as part of loading data into the EDW. Microsoft OLAP cubes were created to enable the slicing and dicing of diagnosis codes and medication categories.

The EDW was updated multiple times a day so that the data being analyzed was as close to the transaction as possible. The near real-time nature of this EDW was and is not typical for most, as many are only updated on a weekly, monthly, or even quarterly basis. However, the health-related nature of this data necessitated more frequent updating as the care of the patient was at stake.
 

Do you feel that an EDW might be right for your organization?

Do you feel that an EDW might be right for your organization? As articulated above, the benefits are significant and bountiful! With that said, it is important to keep in mind that this type of data consolidation is not simple and requires extensive planning. In addition, the process of gathering and streamlining this data is no trivial undertaking.

In our example above, setting up the hospital’s EDW required creating a patient identifier and constructing relationships on that patient identifier across ALL the entities in the EDW. And that is just one example of the heavy lifting that goes into the building of a solid EDW.

We hope that the potential value of an EDW is apparent after reading this blog post. If you are interested in exploring how an EDW could benefit your company and data initiatives give us a call anytime at 678-835-8539.We’d be more than happy to help you.

 

Keep your data analytics sharp by subscribing to our mailing list!

Thanks for reading! Keep your knowledge sharp by subscribing to our mailing list to receive fresh Key2 content around data analytics, business intelligence, data warehousing, and more!

Grow your analytics knowledge.

Leave this field empty if you're human:

 
 
 
 
 

---

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.