Background and Justification of PowerOLAP

 

It is useful to evaluate technology in the context in which it is used or appreciated most.  In this short background, we will talk about spreadsheets, their historical use, and look at the financial reporting tools being used today. Then we will focus on what OLAP technology is all about and what it enables you to accomplish. Next, we shall explore where we are technologically today with PowerOLAP® software. Building on that background information and the information in the Introduction to PowerOLAP®, we will next provide the practical information you need to start using PowerOLAP® application right away: using the PowerOLAP® Manuals, and understanding key concepts and the PowerOLAP® user interface.

Gathering the Information

Today, most organizations collect information in a relational database management system (RDBMS). The purpose of an RDBMS is to store data that details business transactions. The particulars of the sale, including product, territory, price, customer and the like are captured quickly and accurately. An RDBMS stores transactions in static, two-dimensional “tables”, familiar to us as the standard row-by-column spreadsheet format. Each row in a table contains a single record, and each time a transaction occurs, another record is added to the appropriate table. Although RDBMS systems are good at collecting and storing vast amounts of information, relational databases alone are inefficient tools for retrieving critical information.

The language of relational systems is SQL—Structured Query Language—a formalized set of commands used to extract data from a table or related tables (thus the term, relational) to produce new summary tables of records. A SQL statement might, for example, take names from an Employee Information table (consisting of Name, Address, Phone Number, Date of Hire, Health Plan, etc.) and a Submitted Claims table (consisting of Claim Number, Health Plan, Date, Employee, etc.) to produce a report that indicates the employees who submitted claims for a particular health plan between January and June of the preceding year.

It is essential to note that while relational database systems are well-tuned to store transaction records and produce reports of this nature, they are not effective at providing flexible, time-sensitive analytical information or unique reports at a moment’s notice. Constructing and processing an SQL request requires specialized knowledge. As a result, sophisticated queries often require the involvement of MIS staff. Further, even the most elegant “translation” of a complicated request into SQL runs up against the underlying “grammar” of the relational database: numerous separate tables (at least in an average-sized or larger business), each of which contains lines of records in two-dimensional, row-by-column format.

The sophisticated analyses required by today’s business operations—what-if scenarios to be done “on the fly”—are beyond the capabilities of end users running SQL queries on a relational database system. For example, it would be nearly impossible for an analyst using a SQL RDBMS to produce a report that breaks down Sales, per Month, per Product, per Customer, per Region, for the current fiscal year, and compares those numbers to a forecast for the coming year that assumes a 15 percent increase in certain regions, and adds a new region that averages sales figures of previous years’ totals!

Main Disadvantages of Relational Databases

  • Static, two-dimensional format, although businesses need views of data from multi-dimensions
  • Inflexible, aged data–not time-sensitive, up-to-the-instant data
  • Requires specialized knowledge to produce reports (MIS Staff)–not end-user friendly
  • Time-consuming report generation process on large data volumes
  • No “on-the-fly” data or parameter access, additions or change capabilities

The Dawning of the Spreadsheet

The electronic spreadsheet era of the 80’s and 90’s provided management with new opportunities to perform analyses on business information. Spreadsheets enable individuals to organize and consolidate information (through the use of formulas) quickly and intuitively within the row-by-column format. The ability to view information—say Total Sales and Cost of Sales according to Months of the year—and then quickly perform a calculation on it—say, Profit by Month—within the same view (sales by months), gives analysts the opportunity to identify precise areas of concern or interest. Further, transforming worksheet information into graphical representations provides businesses with new insights about the effectiveness of their plans.

Even more than twenty years later, with technological innovations of every front, the spreadsheet is still the most common and familiar reporting tool.  This is a mystery considering there are several shortcomings to spreadsheet reporting. As businesses grow, the number of spreadsheets produced (or worksheets, as they are called in Excel) increases dramatically. For example, for a planning and reporting system in a manufacturing company, an analyst would have to link multiple worksheets in order to compare different departments’ outputs, in different locations, at different times of the year, for each product type. This type of multiple-worksheet system creates serious maintenance issues for analysts who need to add new product types or even adjust the cost of a single item in the manufacturing process. Worksheets are only static snapshots of data at a certain point in time: they are unable to adjust easily to the addition of new rows and columns, they are not malleable—they can not easily be manipulated to provide alternate views of the same data. Further, because worksheets store data and formulas for every cell, recalculation is slow and inefficient when working with large volumes of data.

Another well-known drawback to worksheets is that they are severely limited for organization-wide planning and reporting purposes. Take a simple scenario: an analyst copies a single worksheet from a group of linked worksheets and sends it to a co-worker; she updates several key figures and sends it to a third person for his input; he then adjusts the numbers, and sends the worksheet back to its source. The analyst must re-key the changed numbers correctly into his linked worksheet to update the entire system.

One final consideration of a worksheet reporting system should be taken into account: that analysts must get their base information from an organization’s record-keeping store—namely, the underlying relational database system. Worksheets are notdynamic: they do not provide a mechanism for connecting to the relational transactional data they must analyze. Thus there is a fundamental “disconnect” between the data source and the application where end-user analysis is performed.

In spite of these problems, this is precisely the situation found at most organizations today, whether small, large or multinational: relational databases store transactions; MIS staff run SQL queries through an RDBMS to extract basic summary data; and end-users either import or re-key figures into worksheets in order to manipulate row-by-column formatted data.

 

Main Disadvantages of Spreadsheets

  • Multiple worksheets and workbooks are needed to cover all business analysis needs.
  • Hard to access information and maintain templates.
  • Inability to add new data fields (rows and columns) without undoing calculations and links between other worksheets and other workbooks; structure not malleable, can’t change views.
  • Slow and inefficient calculation times.
  • Difficulty in sharing and updating information with multiple users duplicating keying efforts.
  • Data disconnect: information always originates in the RDBMS but with no mechanism for connecting from Excel to the relational transactional data the worksheets are analyzing loss of timeliness, accuracy, and flexibility.

If you are reading this, you are taking a step in the right direction in learning about PowerOLAP®.  Implementing PowerOLAP® as your business system means never having to grapple with the disadvantages of spreadsheets or the difficulties of SQL queries and extracting data from relational databases.  Learn more about how to start using PowerOLAP® for your benefit in the QuickStart guide. Enjoy!