Why Your App Reporting Solution Needs Its Own Database

Reporting can be a secondary thought when apps are designed and developed. Because of this, it is common to see reporting solutions running off of the application database, also known as the transactional or operational database.

Out of the gate, this solution can work, but as time goes on, there will be problems that impact both your app and your users.

In this article, we talk about the issues that exist for systems that have the reporting solution on the app database, and your options for avoiding or correcting these problems.

Slowing Your Application’s Performance

The transactional database is constantly storing, updating and retrieving data as users interact with the system. When reports are run against the same data, they can tax the database by accessing large amounts of data to the extent that the application slows down noticeably or even stops responding all together.

In the most simple of terms, when you allow your reports to run on your transactional database, you have multiple resources playing tug of war with your database. Ultimately as your application grows, both the reporting and your users will lose because there will be more and more data to access and process simultaneously.

Growing Your Database By Storing Historical Data

The data needs of the application and the data needs of reports are different. Your application may be storing data that it doesn’t actually use in order to support the needs of the reporting system. Because of this, your database can grow exponentially.

For the most part, applications need recent or real-time data. Reports on the other hand typically need historical, pre-calculated and aggregated data. Because of these opposing needs, your system is storing data both for the application and for reporting, causing a steady growth in data over time.

As databases grow, data computation and access slow down and require more resources for processing, storage, and backup.

Complex Store Procedures To Produce Reports

Transactional databases typically store data in a normalized fashion. Normalization allows the data to keep its integrity by storing each attribute of data once and then relating it to other pieces of data as necessary.

In the simplest of terms, if you take an example of customers and orders, a normalized database would store the customer information once, and have associated records for each order.

Without a normalized database, you would have the customer information repeated for each order, with the potential for varying versions of the customer information.  Is Bob Smith, Bobby Smith and Robert Smith the same person? You don’t know.

Normalization is great for apps but not great for reporting. To analyze data, look at historical values over time, perform calculations and aggregations and get it into a format to provide the type of reporting output desired, it may require complex queries with multiple temp tables, joins, unions, etc.

These complex queries are hard to code, hard to debug and maintain, and can weigh down your database resources while they are running.

What’s The Answer?

There are two options when taking your reporting off of the transactional database. The first option is simple, but not ideal, which is to put your reporting on a copy of your transactional database.

Although this solves the problem of both your application and the reporting both fighting for the databases’ resources, it doesn’t solve the issue of growing your database over time and the complexity of your stored procedures.

The ideal approach is to create a data warehouse. A data warehouse is a specialized way of storing just the data you need for your reports.

A data warehouse stores data de-normalized which may sound bad, but it allows you to report off of data as it was back in time.  Your application database does a great job of showing recent or real-time data, but reports often want to provide data at a particular point in time.

For example, going back to the customer database, a data warehouse can report on customer details as they were on 2/2/2012 vs today.

By implementing a data warehouse, you allow your app to access and store only the data it needs and the data warehouse to pull from the app database and generate the data needed for reporting through a process called ETL (Extract, Transform, Load).  Both the application and the reporting win in this scenario.

Summary

There is a lot to implementing a data warehouse well, but we will not go into that here. The purpose of this article is to bring awareness to the fact that when designing a new application, it pays to consider reporting early on in the design.

If you are concerned about cost, ask for estimates detailing the cost difference between doing the work upfront vs. retrofitting it afterward. Data warehousing and reports don’t have to be as expensive as you think. It is typically a lot more expensive in terms of labor and user impact if you wait to add in a reporting solution once your application is live and in use.

Resource: 6 Items That Are Non-Negotiable When Building Software

Before you get started in developing your software app, discover what is non-negotiable in building a successful software application. In this guide, we share how to build your application on a solid foundation from nearly twenty years of our own personal experience.

You can grab a copy of the guide below and share it with your team!