Browsing OLAP Data Sources with Excel

The dominant database type in most organizations is the OLTP (On-line Transaction Processing) database. Indeed, most of you are probably working with some form of an OLTP database as you read this. The main characteristics of this type of database are: they typically contain many tables, each table usually contains multiple relationships with other tables, and records within any given table can be routinely added, deleted, or updated.

Although OLTP databases are effective in gathering and managing data, they typically don't make for effective data sources for reporting, for three main reasons:

□ Complexity: The large number of tables and relationships that can exist in an OLTP database can leave you wondering exactly which tables to join and how the tables relate to each other.

□ Volume: OLTP databases normally contain individual records. Lots of them. To create any number of aggregate reports and views, you would have to run views that group, aggregate, and sort records on the fly. The sheer volume of data in the database could very well inundate you with painfully slow reporting.

□ Consistency: By its very nature, the records in a transactional database are ever-changing. Building a reporting solution on top of this type of database will inevitably lead to inconsistent results from month to month, or even from day to day.

Some organizations avoid these woes by building their reporting solutions on top of OLAP (OnLine Analytical Processing) databases. OLAP databases are data islands that are isolated from the hustle and bustle of transactional databases. An OLAP database can help alleviate these problems in the following ways:

□ Structured Data: In an OLAP database, all of the relationships between the various data points have been predefined and stored in what are known as cubes. These cubes contain the hierarchical structures that allow for the easy navigation of available data dimensions and measures. With this configuration, you no longer have to create joins yourself or try to guess how one data table relates to another. All of that complexity is taken care of behind the scenes, leaving you free to develop the reports you need.

□ Predefined Aggregations: The data in an OLAP database is not only organized, but it is aggregated. This means that grouping, sorting, and aggregations are all predefined in OLAP databases. In addition, OLAP databases make heavy use of indexes, a technique that allows a database to search for records more efficiently. All of this amounts to reporting solutions that are optimized to provided the reports you need as fast as possible.

□ Consistent Results: OLAP databases only contain snapshots of data. That is to say, the data in an OLAP database is typically historical data that is read-only, stored solely for reporting purposes. New data is typically appended to the OLAP database on a regular basis, but the existing data is rarely edited or deleted. This allows you to retrieve consistent results when building your reporting solutions.

Excel has some effective built-in tools that allow for the exploration and reporting of data from OLAP databases. In this chapter, you will discover some of the ways you can browse the OLAP data sources in your organization via Excel and VBA.

You cannot create an OLAP database using Excel. OLAP databases are typically created with SQL Server Analysis Services. If your organization does not utilize OLAP datbases, you may want to speak with your SQL Server DBA to discuss the possib-lity of some OLAP reporting solutions.

0 0

Post a comment