Selecting Your Approach for Data Modeling: BI vs. Database

Selecting Your Approach for Data Modeling: BI vs. Database

As analytics professionals, we want to ensure that our data models are not only efficient and accurate, but also flexible enough to meet the needs of the data analysts who rely on them. When data analysts create dashboards and analyses to inform business decisions, they often need to build new data models with additional data transformations or new metrics. Analysts usually start building these in their business intelligence tool of choice (like Sigma, Tableau, or Power BI), where the analyst has the necessary expertise and access to quickly make changes. Yet keeping data models in business intelligence platforms can create siloes, result in queries that are non-performant, and duplicate logic across the organization, leading to inconsistencies in how metrics are calculated.

Does that mean you should create a model in your database for every single table needed by business intelligence (BI) tools then? Probably not, as moving the data model to the data engineering backend (e.g., dbt, Snowflake, Databricks) comes with its own set of trade-offs.

So, when should you move the data models to your database and when should you keep them in your BI tool? We’ll explore the trade-offs of both approaches in this blog post and provide a set of questions to guide your decision. 

‍

What Are Data Models?

Data modeling is the process of defining the structure, relationships, and constraints of data in a system. It represents business logic used to control data across multiple data sources. Well-designed data models simplify data relationships, make data accessible, improve trust in the data, and support informed decision-making. For more on data modeling, see our blog Data Modeling Best Practices.

How Does Data Modeling in BI vs the Database Differ?

Data modelling in your database involves creating objects that store the data and business logic. Data models in databases are usually built with programming languages like SQL or Python. It enables a single source of truth and allows for greater control over data integrity, performance optimization, and scalability. However, it demands a higher level of expertise and gives data analysts less flexibility to update the models as needed. 

In contrast, data modeling in your BI tool involves connecting to and combining (i.e., unions and joins) objects from your database, and augmenting them with new columns of data (i.e., creating dimensions and measures with calculations). BI tools offer different types of connections. For the sake of comparison, we are assuming that the BI tool is pulling data via live connections to a database rather than pulling data from files stored locally. BI tools have varying names for data models. For example, data modeling may be done with “Data Models” or “Datasets” in Sigma, “Data Models” in Power BI, or “Relationships” and “Joins/Unions” in Tableau.

Regardless of the naming conventions, data modeling in BI tools is often simplified with intuitive, drag-and-drop interfaces, which makes it easy to connect data to reports and dashboards, but is ill-suited for complex transformations and massive datasets. To keep pace with changing business needs, analysts require flexibility, which means that some models will need to live in BI tools.  

Weighing the Trade-offs

We’ve identified two key trade-offs to consider when deciding where your data model should live:

1. Performance vs. Maintenance: Building a data model in your database tool of choice allows you to optimize compute performance, particularly for heavy transformations, but can make it more complex to maintain the model.
2. Centralization vs. Flexibility: Centralizing a data model in the database enables metrics and logic to be consistently defined across an organization. However, it can be more difficult for data analysts to make changes to the model.

Let’s dig into these trade-offs in more detail.

Performance vs. Maintenance

By building data models in a database, you can pre-compute results at a desired refresh frequency. This results in lower compute costs and faster results than if the model were created in a BI tool, where transformations are performed at query time. However, it introduces additional complexity in maintaining and orchestrating the models.

You should value the more powerful performance of database tools if:

• Queries are complex, costly, and/or time-intensive: By building data models as views and tables in the database, heavy transformations can be optimized and pre-computed to reduce compute time & costs. 
• Many teams need to transform the data in the same way: If many teams require the same data transformations or metric calculations, moving these data models from the BI tools to your database can cut down the number of repetitive queries, reducing compute costs. 

You should value the lower maintenance of BI tools if:

• There are many tables that have only slight variations from each other: Putting every table into your database can quickly fan out the number of tables. This creates a bloated schema that becomes increasingly difficult to manage and makes it more difficult for analysts to find the data they need. (And if analysts don’t know data exists, they won’t use it—defeating the purpose of building the model!)
• Data engineering team is spread too thin: Shifting data modeling from the BI tool layer to the database puts the onus on the data engineering team to properly manage and orchestrate the model. If the team is already overloaded with requests across the organization, light data modeling and final data transformations should be maintained by the data analyst directly in their BI tools, allowing your data engineering team to focus on high impact activities. However, any data models in the BI layer should be simple and focused on a specific analysis, not on intensive data processing.

Centralization vs. Flexibility

Putting data models in databases allows metrics and logic to be consistently defined across an organization. This provides a single source of truth and improves trust in the data, but limits the ability of analysts to easily interact with and modify the models.

You should value the centralized nature of database models if:

• There should be a single source of truth for business logic that can be used across various tools: When metrics or logic are being used by a variety of stakeholders, building the business model in the database allows for consistent and reliable metrics across multiple BI tools and other use cases (for example, data science projects). 
• Using imperfect data would have significant consequences: If data errors or unreliability would have serious repercussions (for example, when you are selling the data as a product), data models should live in your database, where version control, orchestration, and good documentation guarantee data quality.
• Portability is important: If data analysts need to use the same model in more than one BI tool, or if the company is planning to migrate from one BI tool to another, putting the data model in your database means it can be used wherever needed. Considering a BI migration? Aimpoint Digital’s Analytics Practice is here to help.

You should value the flexibility of BI models if:

• Data analysts need autonomy to meet tight deadlines: In a high-pressure environment where quick decision-making is crucial, BI tools give data analysts the flexibility to rapidly develop and modify models. This autonomy ensures they can respond to changing business needs without bottlenecks.
• The model is being used for exploratory or ad hoc analysis: Modeling in BI has a faster feedback loop. If the requirements make it hard to know what to model, analysts can benefit from BI tool interactivity for initial development and exploration and move the finalized model to the database once the needs are fully established. 
• There would be a long delay in updating models: If a model needs frequent updates and the data engineering team has limited capacity to handle change requests, the data model should live in the BI layer and be owned by the data analyst.

Data Modeling in Practice

In many cases, it’s not a question of whether a data model will be pushed from the BI layer to the backend but a matter of when. You should experiment and iterate to make sure your data model does what you need it to, and "write it into law" in the database to make sure it is used and governed in the long term. As business needs evolve, you may need to return to the BI tool to iterate on the data model before pushing the changes back to the database.

Choosing the Right Approach

So how should you use the trade-offs discussed above to guide your decision-making? We’ve put together a series of questions to help you decide how much to value performance, centralization, flexibility, and maintenance for a given data model. Note that you should make decisions on a model-by-model basis rather than at the organization level, as most organizations will need to take a hybrid approach.

Empowering Your Team

Data analysts and data engineers usually sit in different teams—and may even sit in different organizations if data engineering is outsourced. Making sure data models live in the right place requires effectively bridging the gap between analysts and data engineers.

Data engineers should proactively make sure they’re aligning their data models with business needs. We recommend conducting in-depth interviews with a range of stakeholders and hosting workshops to ensure analysts across your organization are kept up-to-date with best practices and data availability.

Data analysts should know how to request to move a data model from the BI tool to the database. We recommend having an analytics engineering team handle these requests, as they sit between data analysts and data engineers and can effectively translate business needs to technical requirements (for more on this, see our blog 6 Reasons Why Every Business Needs Analytics Engineering). We also recommend putting together internal training and SOPs to help data analysts understand the review process associated with moving a model to the backend, as data analysts often have less experience with CI/CD processes than data engineers and analytics engineers.

Conclusion

There is no one-size-fits-all answer to where your data model should live. If a model’s transformations are expensive—or if the logic is duplicated across multiple BI tools—it’s likely best to centralize the data model in your database. However, if the model is lightweight, keeping it in your BI tool can reduce the maintenance burden and give your data analysts greater flexibility to make the changes they need. 

Beyond the trade-offs we discussed, you may also need to factor in other considerations (like access control, security, and governance) when deciding where to put your data model. We’ll discuss these considerations as well as platform-specific factors in future blog posts. If you're unsure which approach best fits your needs, Aimpoint Digital can guide you through the decision-making process. 

At Aimpoint Digital, our team partners with organizations of all sizes to enable self-service analytics and tackle complex use cases. If you would like to learn more about our services and offerings, please reach out and we would be happy to discuss how our expertise can support your needs.