Home

Blog

Case Studies

Services

Contact

Today’s business decision makers must recognize the critical role this technology and Business Intelligence plays in the information superhighway

At Integrant, we recently engaged with a San-Diego-based life sciences client that focuses on discovering and developing drug treatments for people with serious neurological, endocrine, and psychiatric disorders. This client reached out to our team to explore how data warehousing could help solve certain bottlenecks in its drug discovery and testing workflows. The client also wanted to do a better job of storing and visualizing strategic operations data derived from its robotic handler environment.

To address our client’s need, we devised a data warehousing strategy. This article captures some important takeaways from that engagement. Here we focus on three primary features of the data warehouse: what is it, why it’s important, and the business and technical requirements for setting up such an environment. Since we’ve made this discussion industry agnostic, the principles and points here can be applied to any unique business vertical or sector.

What’s the Big Deal About Data Anyway?

Data is the new currency of the 21st century. With the explosion in digital transformation, many companies and industries are turning to faster and more efficient ways of managing that data to keep ahead of the competition. Whether in hospitality, retail, transportation, or supply chain logistics, customers expect real-time insights and quick business takeaways at the speed of the market.

That urgency is perhaps no more keenly felt than in healthcare, where the demands for fast-paced data are dedicated to saving lives and improving livelihoods. One of the key technologies behind the organization, manipulation, and analysis of data is the data warehouse, and increasing numbers of healthcare institutions are busily implementing “data warehousing” as a more efficient way to keep up with rapid changes in the industry. One source puts it this way:

Data Warehouse in today’s world are helping companies across industries to make data-driven decisions. For the healthcare industry experiencing a massive shift towards consumerism and advanced analytics, a single & comprehensive view of data is becoming imperative. It is one of the many reasons why leading healthcare organizations are beginning to implement enterprise healthcare data warehouses in their structure.

Data Warehouse: A Big ‘Ole’ Storage Unit for Your Enterprise Data

As critical parts of the global supply chain, warehouses come in different sizes and shapes but they have one specific purpose: to be collection and redistribution centers for products, goods, and services. Think about something on the scale of an Amazon fulfillment center, which is dedicated to ensuring your recently ordered package arrives the next day.

A data warehouse (also referred to as a DWH) works on a very similar principle as a regular commercial warehouse – except that it deals in “packages” of data bytes rather than physical goods. But what exactly is a data warehouse? Bill Inmon, the recognized father of the data warehousing concept, describes it as “a subject-orientated, integrated, time variant, non-volatile collection of data in support of management's decision-making process.”

To break the concept down a bit further, we can say the following about a data warehouse:

• It’s a central location where consolidated data from multiple locations (DBs) are stored
• It’s maintained separately from an organization's operational database
• End users access it whenever any information is needed

Because of the unique way that a data warehouse functions, it can do very innovative things with enterprise data that were not possible before. As the illustration above shows, a data warehouse effectively becomes like a “Google for your business” so that end users can ask very strategic questions about their company data and get specific answers back in real-time.

The Key Role of Business Intelligence (BI)

Before going too deep into data warehouses, let’s step back for a moment and make sure we understand an important ally in the quest for digital transformation. Business Intelligence is the act of transforming raw data or operational data into useful information so an organization can become more efficient.

A good example of a popular BI software is Tableau, an interactive data visualization tool which “supports ad hoc analysis for the everyday user, giving everyone in the business the ability to better see and understand their data.” With BI, users can run and publish KPIs and other business metrics in order to understand how data is impacting their organization and how they can respond.

Like our initial discussion on data warehouses above, we can say that BI has three primary functions:

• BI is based on data warehouse technology that extracts information from a company’s operation system
• The data is transformed (cleaned and integrated) and loaded into the data warehouse
• Since this data is now credible, it is useful for generating key business insights

If we use the “Google for business” analogy above, then BI is like the query results in Google – but very specific and focused.

Data Warehouses and Business Intelligence Are Better Together

We’ve already touched upon some of the main benefits of data warehouses. But now that we’ve described a bit about the BI layer, it becomes clearer how the two concepts work powerfully in unison to help decision makers and stakeholders turn their data into competitive advantage in the market place.  

Some of the major benefits of deploying a data warehouse into your business intelligence framework are as follows:

• Combines disparate data into one uniform framework
• Saves time
• Enhances data quality and consistency
• Generates high return on investment (ROI)
• Provides competitive advantage
• Improves the decision-making process
• Enables organizations to forecast with confidence
• Streamlines the flow of information

With a data warehousing and BI infrastructure in place, business analysts and other decision makers can gather important KPIs to measure their company’s market segmentation, sales, risk, and inventory.

Going back to the “Google for business” for a moment, we can now ask our data warehouse any strategic question about our company data and it will return a specific answer in real-time. For example, a data warehouse can help a bank manager discover how many customers are utilizing the ATM by location and if the machine should be relocated or not.

Likewise, an insurance company can discover the number of commercial policies each insurance agent has sold per month. This information will help the insurer provide better performance management for the agents.

Now, you might be asking yourself: “How does all this magic happen under the hood?” What’s the secret formula to turning your disparate, unorganized company data into Google-like searchable queries?

There are four main properties of a data warehouse that make it so useful, which can be well-represented by the outline below.

01

Subject-Oriented:

Data is categorized and stored by business subjects rather than by application.

02

Integrated:

Data on a given subjects is collected from disparate sources and stored in a single place.

03

Time-Variant:

Data is stored as a series of snapshots, each representing a period of time.

04

Non-Volatile:

Typically, this means data in the DWH is not updated or deleted.

Now that we’ve covered the basics of what a data warehouse is and how it relates to business intelligence, let’s cover a few key terms in this universe that are worth knowing. These terms will help orient us more fully to what’s happening “behind the scenes” whenever you send a business query to your data warehouse.

A Brief Glossary of Terms

• Online transactional processing (OLTP): This refers to the real-time execution of large numbers of database transactions by large numbers of people, typically over the internet. OLTP can process a rapid number of relatively simple transactions in a matter of microseconds and concurrently while multiple users are accessing the data. One example is a bank server that records whenever a transaction is made for an individual’s account.

• Online analytical processing (OLAP): A type of analytical data warehouse where the emphasis is on applying complex queries to large amounts of historical data, aggregated from OLTP databases and other sources. OLAP consists of three basic analytical operations that are performed on a “cube” of data: consolidation (roll-up), drill-down, and slicing and dicing (dimensions). These operations gather very precise views of the data. Revisiting the example above, a typical example of an OLAP transaction is a grocery chain that wants to gather sales by region; additional layers may be by state/province, city, and even by a specific store.

• Roll-up: An OLAP operation that refers to the process of viewing data with decreasing detail.

• Drill-down: The reverse operation of roll-up, whereby the user views data with increasing detail.

• Data mart: These are smaller versions of DWHs which deal with a single subject, draw from a limited number of resources, and are less complicated to build. There are three types based on how the data “arrives” to the mart:

               -Dependent: Data travels from the OLTP source to the data mart via the DWH

               -Independent: Data arrives to data mart directly from the OLTP source

               -Hybrid: Data is fed to the data mart both from the OLTP source and from DWH

• Metadata: In a data warehouse environment, “metadata” refers to the data stored in special-purpose repositories that include information about the DWH data contents, location, and structure.

• Extract, transform, load (ETL): This is the process of extracting data from various sources and then transforming this data to meet your requirements. Finally, the last stage involves loading the organized data into your target data warehouse, where it is queried by analytics and business intelligence tools for insight.

Building a Data Warehouse – The Essentials You Need to Know

Now we get to the fun part… actually designing and building a data warehouse. Keep in mind that a data warehouse is a very complicated piece of architecture and is not something you can purchase “off the shelf.”

Each data warehouse is customized for a particular organization with the help of business and IT personnel working together in unison. The process requires careful planning, strategy, and months of teamwork to pull off. Let’s outline briefly here some of the major steps in the development of a DWH.

Planning

Assessment

• Study the business requirements 

               -Define what your organization wants to accomplish

• Outline the technical requirements

Design and Build

Technical architecture

• Describe specific utilities and what’s needed to create and maintain the data warehouse

               -Server requirements

               -Databases that will be created

               -How the data warehouse will be stored

               -How the ETLs will work and where

Data architecture

• Dimensional modeling
• Physical design
• Data staging design & development

Application architecture

• Third-party tools
• End-user requirements
• Visualization software connected to DWH so end users can see the results (BI)
• Additional frameworks supporting features like artificial intelligence (AI) or machine learning (ML)

Deploy

• Test the entire process end-to-end
• Complete automation and scheduling features used to test and check all data values

There are many layers and complexities involved in developing a DWH that cannot be fully captured in one article. However, the following schema gives a nice overview of the process described above, showing the intersection between the various requirements, project management components, and business outcomes.

Questions?

Building a DWH is a very involved and complex process and we’ve thrown a lot of information your way in a relatively short amount of space. There are bound to be questions along the way. To help with that, we’ve pulled together a mini-FAQ based on our client’s feedback that might help clarify your own DWH journey.

Q: Does it make sense to start migrating our raw data to a DWH before we define all the things we want to see?

Sure, we can start the data warehouse as a single container to the data and provide basic measures. For example, time will be one measure of the dimension; the facts we have about reproducing the number of experiments can be another. In other words, we definitely have the right inputs to get started. All we need are multiple dimensions and multiple facts – and we already have that information.

Q: What happens if we need to include more data sources?

The DWH is designed to be continually modified; the layers of the system are changeable. If a new data source is added, then we would make modifications in the logic of the ETL for that new source. The change would then be processed into the staging level and added to the DWH to reflect the new data update(s).

Q: Is this process iterative? What’s the repercussion of changing the data warehouse schema later on?

Think of the warehouse like any other application or software development. Anything can be changed in the design of the data sources. We can always update the code and continue to develop the DWH in the future.

Q: When is the earliest we can start?

Right away! Here are the ideal pieces of information we need to get started:

• What trends are your domain experts looking for?
• What data values are most important for your organization?
• What results are you looking for?
• What results are you missing that you want to see?
• What are the end results or near-to-the-end results you’re looking for?

Work With Us!

There’s never been a more exciting time to be a software developer. The tools and technologies that we interact with daily are enabling us to architect the present and future of digital transformation. As we learned from our local client, organizations today are very interested in extracting enterprise data for deep business insights. Data warehouses are an integral part of that process and are helping business leaders answer questions they never thought to ask – as well as raise questions they never knew existed.

Perhaps you are a small to mid-sized business looking to gain a competitive edge in your industry or vertical. If you’ve been struggling to figure out your data and know you’re leaving “money on the table” by not gleaning more business insight, then let us help! Integrant brings data warehousing experience that can enable you to take your business to the next level. We’re obsessed with making our customers happy; in fact, if you choose to work with us, we’ll stop at nothing to overdeliver on value.

Give us a call today to learn how we can partner together on your data warehousing strategy and help you capture better business insight, increased revenue, and a bigger play in your market. Subscribe below or contact us to see the impact we can make for you and your business. We can’t wait to get started!

Company

Contact

Follow US

About Us

Address: 16870 W Bernardo Dr, Suite 250

San Diego, CA 92127

Email: info@integrant.com
Phone: +1 858.731.8700

Privacy Policy

© 2021 Integrant, Inc. All Rights Reserved