Database technologies are constantly changing, and it is important to understand and differentiate some of the different uses of databases which include transactional databases and data warehouses. Each of them has a different purpose and different design associated with them.
Transactional databases emphasize correct and consistent data while keeping the speed of the operations in the database. These types of transactional databases focus on time sensitivity. This means that purchases on a website or shipment of inventory need to have that data reflected in the database in real-time for those day-to-day operations. They have to be recorded accurately and immediately. The type of database is called an operational database, an online transaction process database, or a production database.
In transactional databases, we constantly have insert, update, delete and select statements running so the data is transitioning all of the time. All of these transactions have to execute quickly to provide responses to the end-users as they need them. The results should be immediately displayed when the user runs one of the commands in the database. Since these databases change often, data integrity is crucial as such, the data is normalized in third normal form. We will get into those details in an upcoming tutorial.
Analytical databases on the other hand focus on historical data and business metrics. There’s a lot of data manipulation on the data to help produce information that’s needed for the organization to help produce some of the key analysis of the data such as sales forecasts or market strategies. These analytical databases will allow the end-user to be able to perform some complex analysis of the data using complex applications.
Analytical databases generally consist of two main components with the data warehouse and the online analytical processing front end. The data warehouse is a specialized type of database that stores the underlying data in a format that’s optimized for decision support rather than transactions. As such, we typically will have data loaded from transactional databases or other sources and mostly select statements. It’s rare to see any update, insert or delete statements as the data has already been verified and validated against when it has gone into the transactional database. Online analytical processing (OLAP) generally contains a set of tools that would be used to process that data from the data warehouse.
These analytical databases are used to help with business intelligence to be able to generate information that is used for business decision-making. The volume of data in these analytical databases can be quite massive due to the historical data and the added data sets that may be added from third parties. Since the data does not change often, space is not as much of a concern so we will see more data redundancy by denormalizing the database since the focus is on performance.