Losing control of data is a common dilemma. Much of it boils down to one thing — enterprises have so much data from so many sources, they simply cannot keep track of where it all lives. Even the basic sources of data expand by multitudes as an enterprise grows. Building a data model within a cloud data warehouse (CDW) is a big step toward taming the data beast.

A data model describes an entire enterprise from a data point of view. It’s a representation of the data objects in a company’s CDW, the associations between these objects, and the rules in place. A data warehouse is in fact nothing more than the sum of its parts. A business can segment the parts and pieces of a warehouse into various persistence layers to generate the outputs it needs. But in order to understand what is available from the CDW, a business needs a data model. When a business skips the step of data modeling, it limits its ability to leverage its data assets efficiently, because its team can’t find them quickly – or at all.

Data models are a team effort. In order to create a solid data model, early collaboration and consideration is the key to the best possible end result. The cloud has made the jobs of data engineers and modelers more efficient and much faster. They have all the tools needed typically within one integrated development environment (IDE) and they can generate messages, set queues, link hundreds of existing flows to another all without even considering the end result. However, a clear understanding of data modeling can start any CDW project on the right path. Here is an overview of the different types of data models:

  • Conceptual data model: Used to describe concepts, rules, and processes that are needed to support the business. In addition to identifying the data needed, the conceptual model also tracks business events and their related performance. It doesn’t concern process flow or the characterization of data types.
  • Logical data model: An attempt to move from a conceptual model into the logical steps of a process and the data attributes it generates. The logical model describes the process in more detail as it relates to other business processes within the organization. Additionally, the logical data model will typically include any relationships between concepts and are split by subject area.
  • Physical data model: The actual representation of the process data elements grouped by entity for storage in a database management system (DBMS). The physical data model contains all relationships and entities, data types, keys, foreign keys, indexes – any DBMS feature that would be included in the schema build would be included in this model.  This model is derived from the conceptual and logical models and should provide a third pass at reviewing the build requirements. In most cases the physical model will not look much like the logical model. Rather it contains entities and relationships that support performant data storage \ retrieval of enterprise data.

A CDW is not just a collection of data – it’s a live and growing model of the business processes that generate data that the organization values. Using data models greatly simplifies the complexity that lies in the various levels of data ingestion both on premises and off. There are several benefits to creating a data model, both for a data team and for the business in general:

  • Speeds time to market for long-term data assets
  • Highlights business rules
  • Improved understanding of how both relational and non-relational data flow through the environment
  • Promotes data democratization
  • Creates visual documentation to show how actual data elements and business processes relate to one another
  • Identifies ways to improve scalability and performance

In order to optimize a company’s CDW investment, it must make the data meaningful to the end user by turning its data into relevant data objects –  groups of data points that are ready for consumption by another system. One of the biggest benefits of creating a data model is being able to identify and track all of the data objects required by the database. The data model creates reusable data objects. These objects or compilations of data are used in analytics reporting and data vending. An example of a reusable data object can be as simple as defining a product down to its finest grain of detail — shape, size, color, usage, point of origin, materials, etc. Any attribute that is related should be included in the data object.

A data model simplifies a company’s ability to identify where data is coming from and how best to use it to create and refine data objects. These objects can then be sorted into datasets for analysis that deliver the insight to drive strategic decisions. The data objects an enterprise creates will depend on its business goals and the specific actions its data model will drive. For example, a company might use a third party HR management system and that system may need to be integrated into the data model. Using the same technique mentioned above to group and normalize data into meaningful data sets that can be leveraged by reporting and vending environments.

An enterprise can leverage the various data layers in a CDW to build an environment to support a scalable solution that reduces costs over time and delivers higher value to the organization and users of their data. The layers represent the power of the CDW and help developers navigate the environment. We know that the most effective strategy for loading a CDW is ELT:  First, extract the data from the source exactly as it is and then load the data exactly as it came, regardless of data type specification. Finally, transform the data to enhance and enrich it into a usable format for 3NF or Presentation storage. Let’s take a look at how to use a data model to drive an effective CDW build, and what layers we might use to store different data assets:

  • Load layer: This is where we keep all our raw unfiltered data. The data is represented in AS IS state.
  • Stage layer: Light transformation of the data begins with setting data types and grouping of elements.
  • 3NF / Data Vault layer: Here we see truly formalized data. Data that has been curated and prepared for consumption down stream based on business rules.
  • Presentation layer:  Finally, we get to see the end result of the data model in action. Do we have facts that are single grain atomic that we can aggregate meaningful statistics around. The presentation layer highlights how we have transformed the data from the raw source system into our final data warehouse output.

By building a corporate data model for an enterprise’s CDW that includes all of its on-premises relational database management systems, its team can promote data democratization and make data available to a greater audience. It gives the business a deeper understanding of where its data is, where it comes from and how it’s used, and the relationships within it. The organization will have the confidence to introduce and expand data self-service, making more data available to more users in less time and ensuring that it’s curated so that they only draw on data that is relevant to their needs. Building a data model for the CDW is one way to gain control of data and move toward achieving faster time to insight for an entire organization.