Building a data model for customer-facing analytics in 10 steps

Building a data model for your SaaS business is no joke. A simple data model has many benefits, but it can easily become a monster of Frankenstein. Even under the guiding eye of the best product and engineering teams. Here is a 10-step process to build a data model that answers business needs. Without all the complexity.

Step 1: Set your objective

First, ask yourself why you are starting a data modeling project. Your organization may need a new or improved data model for many reasons, to name a few.

  • You want to start managing and treating your data as a critical resource
  • You need a single source of truth for all your data sources
  • You want to do real-time reporting
  • You want to create business intelligence reports on top of your data
  • You want to build customer-facing reports for your software application

The requirements for your data model will depend on what you want to use it for. Without a clear objective, it’s impossible to make the right decisions.

The process of this article works for any of these objectives. However, we will focus on the last scenario: building a data model for customer-facing reports in SaaS applications.

Download the customer analytics experience whitepaper

Step 2: Gather your squad

The best teams for a data modeling project are cross-functional, without having too many chefs in the kitchen.

For SaaS product teams, a well-balanced data model squad could look like this.

1. Business representatives

For a data model to be successful, you need input from subject matter experts who will be using the data model. In SaaS product teams, this can be a client-facing colleague, e.g. customer success, account manager,…

💡 Pro tip: involve a product user or ambassador to consider the final user’s point of view.

2. Source systems expert

This person knows how all your systems and data sources link up. The usual suspects for this role are the following.

  1. A data manager or data science specialist
  2. DevOps or Operations Manager
  3. Developer or engineer for smaller SaaS teams

3. Database administrator or ETL team

This expert role will help keep the model simple. Typically your database owner, data scientist or (data) engineer can best assess how to trade off the complexity of ETL processing with simplicity and predictability.

4. Data governance and project management

Poor communication and a lack of consensus are often the root cause of complex data models. Product managers are crucial moderators. They will work closely with all stakeholders and agree on the business rules and definitions. This person will also define and align the KPIs and metrics for the data model.

With this agile, yet balanced team, you’ll ensure faster progress.

💡 Tip: If you don’t have data modeling expertise in-house, it pays off to hire an external consultant. Are you using data modeling tools or embedded analytics software? Many vendors offer professional services to fill the knowledge gap.

Step 3: Schedule and planning

Start building a roadmap for your project with clear timelines and deliverables. A data modeling project typically has two phases.

  • Outlining and prioritizing your business processes (steps 4-5)
  • Designing and developing each data model (steps 6-10)

In theory, you will only need to outline your business processes once. If necessary, you can always revisit and make adjustments.

Designing a data model, however, is a repeatable process. Repeat steps 6 to 10 for each of your customer’s business processes. Agility guaranteed!

It takes 3 to 4 weeks to design a single business process — or a group of tightly related processes. Remember this when planning your design sessions.

Step 4: Outline business processes and requirements

The first step in your data modeling process is to agree on the business questions addressed. You can gather business requirements within your squad in many ways.

  • One-on-one interviews: individual engagement and easier to schedule 
  • Facilitated group sessions: faster end result, but more time commitment of your participants
  • Surveys: with little room for in-depth discussions, we don’t recommend this option
  • A hybrid approach: starting with interviews, followed by group sessions to reach a consensus

Below, you will find 4 simple steps to outline all the business processes of your customers. These definitions will be the basis for your conceptual data model.

1. Identify Business Processes

Business processes are operational activities. For example, taking an order, processing a claim, registering students for a class, etc.

A good tip is to focus on business outcomes, for example:

  • Project managers want to monitor the status, time spent, resources allocated, and budget spent for every project
  • Marketing managers want to get alerts when monthly sales drop below a certain level

In your final data model, a fact table will represent one single business process.

2. Identify Grains

The grain is the most atomic level of insight a business user cares about. It’s impossible to predict what each user needs precisely. So, a good data model will support the lowest level of information possible.

In your final data model, the grain will become a single row of your fact tables. Let’s exemplify what the grain could look like for the business process of placing an order.

❌ One row per individual order placed

✅ One row per line item on a person’s order

With every line item included as a separate row, your customers can do a richer analysis if required.

âš  This is the most critical data modeling step because your entire design depends on the grain of the fact table. Keeping the grain on the lowest level ensures that your data model supports slicing and dicing across all relevant dimensions and measures, while also making sure it can be easily scaled out in the future!

3. Identify Dimensions

Dimensions give descriptive context to the business process. Imagine this as the who, what, where, when, why and how.

In your final data model, every dimension will be represented in a separate dimension table to give more context to your facts. For placing an order, dimensions could be the product name, customer name, purchase date, etc.

4. Identify Facts

Facts are numeric, additive figures. They are a measurement or metric of your business processes. If your business process is “processing a claim”, facts could be

  • The number of items in your order (#)
  • The revenue per order ($)
Illustration of the 4 steps to outline your data model for customer-facing analytics

The Business Matrix is a great planning tool for Product Managers to keep oversight. This tool maps how your business processes relate to common dimensions. It’s a simplified version of your conceptual design.

Image source: The Data WarehouseToolkit 

As you dig deeper into the business processes, you may discover new relationships. When that happens, your business matrix will keep the design process organized.

Step 5: Prioritize business processes 

In the previous step, you identified all your business processes. You will need to go through the data modeling process for each one separately. To keep your model simple, prioritizing the right processes is essential.

Evaluate all your customer’s business processes from step 4 on two axes.

  • Impact: how important is this process for my customer?
  • Feasibility: how tricky will it be to expose that data?

Start with the low-hanging fruit – high impact, low effort – and work your way down.

Image source: Nielsen Norman Group

Step 6: Design your conceptual data model

Once you defined and agreed on the business processes and their prioritization, you can start building a conceptual data model for the first business process. Consider this an ugly first draft of your desired data model.

Instead of immediately building the model in your database, sketch it out in business terms first.

  • What data entities do we need to add to the model?
  • How do they relate to each other?
  • Is our data in a usable state?
  • Where does the data currently live?
    • Internal databases
    • External databases
    • APIs and web services
    • Flat files
    • Other business assets
  • Do we need new data sources we don’t have yet?

With the outlined business process from step 4, you can now map the model onto a high-level bubble chart. Don’t pass this task to an experienced designer. Instead, engage your entire squad in this process. Your model will be clearer and more understandable for your customers.

Example of a high-level bubble chart mapping of a rough data model for customer-facing analytics
Example of a high-level bubble chart for your data model

The following tools can help you with the data model design.

Dimensional data modeling tools

Data modeling tools

For the first mock-up of your data model, a simple spreadsheet will do. They’re quick and easy to iterate on a flat conceptual model.

Later on, data modeling tools are helpful in turning your draft into a dimensional model. Database administrators can forward-engineer the model into the database. This is where you’ll create tables, indexes, partitions, views, etc.

Data profiling tools

Data profiling tools help you get a better understanding of your data entities. For each attribute or column, you’ll be able to define

  • The specific variables needed
  • How they are formatted
  • Naming conventions
  • Other rules or logic to apply

With data profiling tools, you won’t rely on outdated or incomplete documentation. With a few simple SQL statements, you can query your source systems to find actual content and relationships.

Usually, you’ll be able to do data profiling within any major ETL tool.

Tips for naming conventions

Naming conventions are a must to keep your data model simple. Label each element descriptively and consistently.

  • Agree on standard definitions and labels — and stick to them
  • Use business-friendly labels for table and column names
  • Use this common structure for labels
    • Prime word: the business term for the object you’re referencing (e.g. a contract)
    • Qualifier: adjectives or nouns that add more context (e.g. the start of a contract)
    • Class word: the domain category of that element (e.g. the date is related to the start of a contract)
      • The resulting label: “Contract Start Date”

Step 7: Develop your data model

Most of your strategic work is now done. You have defined, mapped out and agreed on all the elements of your data model.

Now, your technical team members can finally develop the data model. However, you’ll want to keep your business reps engaged during that process. Their feedback will ensure that customers understand the data model and want to use it.

Design sessions work best in blocks of 2 or 3 hours, 3 or 4 days per week. We recommend mixing morning and afternoon sessions. As such, your designers and modelers will have enough time in between to prepare or finish tasks that might otherwise block the other.

Here are a few tips for smooth design sessions.

  • Start with Dimension tables, before working on Fact tables
  • Pick the most straightforward dimension first. Early success will motivate your team from the start.
  • When identifying Facts, ensure they are all true to the grain. This also includes additional metrics that are derived from Facts.
  • Document detailed table designs, with each Dimension and Fact table in a separate worksheet
  • Track every issue you discover during the design process: definitions, rules, or quality challenges. Your source system experts can help unblock you in between design sessions.

Step 8: Review and validate the data model

Your data model is almost ready for use! We recommend a thorough review and validation with your core stakeholders.

  • IT
  • Core user
  • Broader business user

Let IT review first, because they know the system that runs the business processes of your data model. IT can review fact and dimension worksheets, as well as resolve any open issues.

When you validated the tech, it’s time to test your model with product users who will use the data model. Make sure to test it with power users first. They will assess your data model with much more detail and accuracy than any other user.

Finally, a review with broader business users will truly validate your data model. Illustrate how the model supports their business requirements. Walk them through some examples of real user questions and scenarios.

For the broader user review, you can target users who won’t use the data model right away, but might adopt it in the future.

Step 9: Finalize the data model

With these finishing touches, your data model is well-documented and ready for use.

  • Write a brief description
  • Draw a high-level diagram of your data model
  • Complete your detailed worksheet for each Fact and Dimension table
  • Resolve any open issues

Step 10: Repeat data modeling steps for each business process

As mentioned before, each business process needs a separate data model. Repeat steps 6 to 9 for all the business processes you’ve outlined in steps 4 and 5.

And that’s it!

…

Well, almost. 

Data modeling is only the beginning. Now, your team can start building client-facing dashboards for your product users. Or you can even expose your datasets and let customers build their own dashboards. This experiment will really put your data models to the test.

With a simple, yet powerful data model, the world is your oyster!

Do you need to build out these data visualizations faster with fewer resources? Then embedded analytics software is a great choice. Whether you’re starting out or optimizing your current data stack, our team of experts is here to help. Book a consultation and get expert advice today.

Book time with an embedded analytics expert

Add a Comment