Accessing transactional and dimensional data

It's interesting to note that a Business Intelligence system can work directly with transactional data, dealing with data reflecting current business operations. From the Business Intelligence's standpoint, though, not only current, but also the historical view of business operations is important.

Reporting against a transactional database

It's often the case that transactional data is stored in a relational database. Relational tables represent relational entities, such as products, orders, details, and customers, storing information about current transactions. As mentioned, a Business Intelligence solution can be built directly upon such a transactional system, containing data that you can use for analysis and reporting.

Note

You might be asking yourself: isn't dimensional data what Business Intelligence is all about? Well, like figure skating is not only about jumps, Business Intelligence is not only about multidimensional cubes. Rather, it's about answering analytical business questions, deriving information from both relational and dimensional sources.

It's important to understand, though, that a transactional system should remain mobile and highly responsive, enabling new transactions to be processed and stored quickly. So, a common problem with such systems is that they are not designed to store large amounts of data—old data should be removed from time to time.

While data set optimization is good for a transactional system as the performance increases, it's not so good for analysis and reporting purposes. This is why Business Intelligence solutions are often built upon a data warehouse, consolidating both old and new data that can actually be stored in different sources.

Using historical data

Like a transactional system, a data warehouse represents a relational database. Unlike a transactional system however, a warehouse accumulates data, which represents the business history of an organization, and is structured for reporting, analysis, and decision support. In warehouses, data is organized around business entities such as products, regions, and customers.

Data in a warehouse can be organized so that it concentrates on a certain subject matter, say, purchasing; that is, it's optimized to simplify the task of finding answers to questions about purchasing. So, aside from historical data, a warehouse often uses summaries containing pre-processed data to speed up access to frequently queried information.

Using historical data can provide a clearer picture of the status of your business, leading to improved predictive capabilities. As you might guess, historical data is derived from a transactional system that often cannot afford to keep large amounts of data due to performance requirements. In fact, Business Intelligence can utilize both transactional and historical data sources for reporting and analysis purposes.

Diagrammatically, this might look like the following diagram:

It's interesting to note that although transactional data is not stored in a warehouse, many Business Intelligence tools access that data through a warehouse, taking advantage of summaries and other warehouse features. Warehousing for analysis and reporting will be discussed in more detail in Chapter 5, Warehousing for Analysis and Reporting, later in this book.

Aggregating dimensional data

A multidimensional data model is often used to perform complex analysis of historical data. Multidimensional data is named so because it is organized by dimensions, such as products, times, customers, regions, and departments. Although there may be more than three dimensions in such data structures, they are often referred to as cubes.

The following diagram illustrates a cube that contains sales figures for groups of products for each month of Q4 in different regions.

What you can see in the figure is a three-dimensional data structure, enabling you to concentrate on solving a particular business problem. Looking at this structure, you might wonder what operations can be applied to it. Operations often applied to multidimensional data to extract meaningful information include the following:

• Pivoting

• Drilling

• Sorting

• Filtering

These techniques will be discussed in detail in later chapters of this book.