Welcome to PowerOLAP: Concepts and Benefits Congratulations! You have acquired a powerful new software application called PowerOLAP, in now in release version 4. You may already know that PowerOLAP is a multidimensional database software tool used for real-time analysis and reporting, as well as advanced, online budgeting and forecasting. You may also be aware that PowerOLAP functions in conjunction with Microsoft Excel, your Web browser and other applications you may already be using at your organization. In order to start benefiting from this powerful business application, you need to know how PowerOLAP works and what it can do to improve your business—more so now, since version 4 of PowerOLAP adds benefits across the board, in performance, feature capabilities and, especially, from an end-user perspective, in the way it allows data analysts to do a much better job—intuitively, intelligently, speedily and very powerfully. What makes PowerOLAP a vital business solution? How does PowerOLAP leverage the existing technology your company uses today to become the core of your Business Operating System? What functions and features of PowerOLAP bring unprecedented analytical and planning capabilities to your PC in an instant? And, finally, how easy is it to use? This introduction will explore those questions in the pages to follow and prepare you to “be up and running” quickly on PowerOLAP. We will start by talking about spreadsheets and their historical use and looking 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. Building on that background information, we will next provide the practical information you need to start using PowerOLAP right away: using the PowerOLAP Manuals, installing PowerOLAP and getting customer support, and understanding key concepts and the PowerOLAP user interface.
Planning, Analysis and Reporting Knowledge is the foundation of all successful decisions. Successful businesses continuously plan, analyze and report on sales and operational activities in order to maximize efficiency, reduce expenditures and gain greater market share. Statisticians will tell you that the more sample data you have, the more likely the resulting statistic will be true. Naturally, the more data a company can access about a specific activity, the Access to more likely that the plan to improve that activity will be effective. More and large amounts of more information is becoming available to us about our businesses, data leads to particularly in today’s increasingly “online” business environment. A better company that can take advantage of reliable information and turn it into business shared knowledge, accurately and quickly, will surely be better positioned to decisions make successful business decisions and rise above the competition.
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. 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, Relational Phone Number, Date of Hire, Health Plan, etc.) and a Submitted Claims databases alone table (consisting of Claim Number, Health Plan, Date, Employee, etc.) are inefficient to produce a report that indicates the employees who submitted claims tools for retrieving for a particular health plan between January and June of the preceding critical information year. It is essential to note that while relational database systems are welltuned 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-bycolumn 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 multidimensions
•
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 Today’s worksheet, the calculation on it—say, Profit by Month—within the same view (sales by most common, months), gives analysts the opportunity to identify precise areas of concern or familiar interest. Further, transforming worksheet information into graphical financial reporting tool, representations provides businesses with new insights about the effectiveness has significant of their plans. limitations
Despite their obvious benefits over paper, there are several shortcomings to spreadsheet reporting. As businesses grow, the number of spreadsheets (or worksheets, as they are called in Excel) produced increases dramatically. For example, for a production 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 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 “pivoted” 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 organizationwide 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 not dynamic: 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
OLAP—The Multidimensional Solution Business is a multidimensional activity. Businesses track their activities by considering many variables. When these variables are tracked on a spreadsheet, they are set on axes (x and y) where each axis represents a logical grouping of variables in a category. For example, sales in units or dollars may be tracked over one year’s time, by month, where the Businesses are sales measures might logically be displayed on the y axis and the months run on might occupy the x axis (i.e., sales measures are rows and months are decisions columns). based on multiple dimensions
Sample Excel spreadsheet grid
To analyze and report on the health of a business and plan future activity, many variable groups or parameters must be tracked on a continuous basis—which is beyond the scope of any number of linked spreadsheets. These variable groups or parameters are called Dimensions in the On-Line Analytical Processing (OLAP) environment. OLAP is a new term and concept for most spreadsheet users. Unlike relational databases, OLAP tools do not store individual transaction records in two-dimensional, row-by-column format, like a worksheet, but instead use multidimensional database structures—known as Cubes in OLAP terminology—to store arrays of consolidated information. The data and formulas are stored in an optimized multidimensional database, while views of the data are created on demand. Analysts can take any view, or Slice, of a Cube to produce a worksheet-like view of points of interest. On the next page are several representations of Slices of data from a simple 3-dimensional Cube, including Cost, Product, and Time.
Imagining Data Modeling in a Cube— Viewing Cost by Product by Time through various Slice views
Rather than simply working with 3 Dimensions, though, companies have many dimensions to track—for example, a business that distributes goods from more than a single facility will have at least the following Dimensions to consider: Accounts, Locations, Periods, Salespeople and Products. These Dimensions comprise a base for the company’s planning, analysis and reporting activities. Together they represent the “whole” business picture, providing the foundation for all business planning, analysis and reporting activities.
Sample Dimensions required for Planning, Analysis and Reporting
The capability to perform the most sophisticated analyses—specifically, the multidimensional analysis provided by OLAP technology—is an organizational imperative. Analysts need to view and manipulate data along the multiple dimensions that define an enterprise— essentially, the dimensions necessary for the creation of an effective business model.
How Multidimensional Cubes Support Business Decisions Cubes are the multidimensional structures in which an organization stores and models data. The Dimensions that make up a Cube, in turn, are made up of Members. For example, the Members of a Region dimension might be World Total, Europe and United States. Placing members into a Hierarchy defines natural parent-child aggregation points: World Total (parent) as the sum of Europe plus United States.
Example Region hierarchy: Total World ‘Parent’ member as an aggregation of United States and Europe ‘children’ members.
Once a Hierarchy is placed into a Cube, along with other Dimension-hierarchies like Accounts, Months, etc., a “Slice view” of the Cube can be organized to display Dimensionmember intersection points. For example, an analyst might produce a Slice to display World Total by Accounts for January, or expand the Slice to show January Accounts for World Total’s children, Europe and United States.
Example Slice: Units Sold by Region and Month
Cubes are populated with all necessary information to support daily decision needs
If more dimensions are included in the cube—Actual versus Budget, Salesperson, Department, etc.—one could simply choose to view, for example, Budgeted Sales, for United States, for January, for Salesperson A, in Department A, and compare it to the identical data set for Salesperson B, in Department B. This is the essence of OLAP: the capability to rapidly create and re-create modeling scenarios with drag-and-drop, point-and-click ease—and to share models throughout an organization, so that one user’s change will be reflected in all users’ views of the model.
Example slice: Tom's Actual Units Sold in January in the US in Department A
Until the advent of PowerOLAP, OLAP products have required some form of manual importation of data from a relational database system, using a variety of methods for storing and loading data. Some OLAP products store a mirror image of the relational data in a proprietary multidimensional database; others store consolidation and formula information in the OLAP cube and leave the relational data in the data store; still other products use a combination of these two methods. PowerOLAP has made a leap in OLAP technology—it interacts dynamically with relational database management systems. No longer is there a “disconnect”, requiring manual intervention to bring data from “there” (a relational system) to “here” (a multidimensional Cube).
The PowerOLAP Solution By combining the most advanced features of OLAP, relational database and worksheet technology, along with new functionalities, PowerOLAP empowers end-users to access numerous data sources throughout an organization, in real time, for sophisticated planning, analysis and reporting tasks. In this way, PowerOLAP PowerOLAP delivers limitless flexibility to businesses of all sizes. sources data from relational systems, and delivers it dynamically to Excel or via the Web
PowerOLAP enables analysts to create models with remarkable ease and offers the choice of viewing data in its own grid interface, in Excel’s or via the Web. You will gain the immediate benefits of working in a new, more sophisticated, yet easier-to-work-with multidimensional worksheet—a worksheet that is optimally designed to take advantage of the data in your relational database system and provide you with advanced
sharing capabilities. Use PowerOLAP to create multidimensional perspectives of your business.
Example Slice: the dimensions (Accounts, Regions, Months) appear in the list boxes on the left, data points are displayed in a spreadsheet-like grid on the right.
Then use Excel to generate and print formatted reports or produce graphs that dynamically changes as your business changes. Dec Nov Oct Sep Aug Jly Jun May Apr Mar Feb Jan Total Months
United States 10 14 15 15 14 13 12 14 14 11 12 13 157
Europe 30 35 33 32 29 27 28 34 38 44 42 41 413
50 45 40 35 30 25 20 15 10 5 0 Dec
Total World 40 49 48 47 Uni ted States 43 Eur ope 40 40 48 52 55 54 54 Nov Oct Sep Aug Jl y Jun 570
May
Apr
Mar
Feb
Jan
A fast and easy Excel worksheet and graph generated from the PowerOLAP slice pictured previous page.
PowerOLAP provides you with full flexibility in viewing the Dimensions that make up your business. Whether you use PowerOLAP alone, or with Excel as your final report writer along with the Web, you will substantially increase productivity and gain greater understanding and awareness about your business processes and activities. In addition to the Excel front end, PowerOLAP overcomes the drawbacks of predecessor OLAP tools by providing live, scheduled or on demand relational database polling, in effect providing users throughout an organization with updates to analytical models as individual data transactions are recorded. By incorporating this functionality, via OLAP Exchange™, PowerOLAP seamlessly integrates all the organizational tools necessary for reporting and analysis tasks—the relational database, the network server, an optimized multidimensional database, and the worksheet—into a faster, more powerful, more flexible single decision support application (for more information about OLAP Exchange, please see the OLAP Exchange User Manual).
Financial Analysts speak Excel
IT speaks SQL Integration Tool ODBC
Tables
Excel OLAP Exchange ODBC
Live Link
PowerOLAP multidimensional db
Drill Through
Calculations: Excel Consolidation Hierarchies Cube-based Formulas
Tables
Web browser
No matter what the analytical application, PowerOLAP is the decision support tool of choice that answers the business questions management asks today. Welcome to a revolution in On-line Analytical Processing!
Key Terms RDBMS
Real-time
OLAP
Data-modeling
Dimension
Aggregate
Cube
Parent/Child
Slice
Hierarchy
Member
Summary PowerOLAP enables organizations to create dynamic solutions quickly—in hours rather than days—between relational and multidimensional databases. With OLAP Exchange, users have true online access to transactional data—as that data is recorded in real time—within a multidimensional format. Users have the ability to drill through to source relational tables, to examine records that constitute data points in PowerOLAP cubes. OLAP Exchange also enables users with requisite rights “write back” changes and store multidimensional data in relational format. PowerOLAP leverages investments and knowledge in existing database systems, providing unprecedented flexibility and speed for the back-end integration of transaction and analytical systems. By employing Excel and the Web browser as a front end, PowerOLAP connects users throughout an organization with underlying data sources via the tools they know best, direct to their desktops—another example of how PowerOLAP leverages company investments and knowledge. With real-time transaction data at their fingertips, analysts are far more productive: the business models they create contain up-to-the-instant information; they no longer waste time keying in figures or trying to reconcile data from various sources, including individual spreadsheets. Working with real-time data, analysts can combine information from different business models/Cubes via PowerOLAP’s rich formula language to support “what if” analytical scenarios and to produce standard and ad hoc reports. OLAP Exchange facilitates the immediate updating of budget and forecast models, so companies can react with exceptional speed to changing business conditions. PowerOLAP is one single solution, closing the gap between relational and multidimensional worlds.
Main Advantages of PowerOLAP •
Multidimensional cube structures.
•
Intuitive, drag-and-drop Slice viewing.
•
Reports generated in Excel, allowing full Excel functionality along with graphical enhancements to reporting, and user ease in accessing PowerOLAP engine from within Excel.
•
Ability to customize and “pivot” views and set constraints on data appearing in the Excel interface
•
Extremely fast calculation time
•
“On-the-fly” flexible analysis, and capability to make data additions and changes to parameters
•
Up-to-the-instant real-time, online data sharing
•
Data security maintains data integrity and protects access to specified areas of Cubes, across data ranges or even by cell.
•
Internet connectivity via Web Services, enabling staff or customers to create unlimited reports and analytical views, including graphs; also, to enter figures into Cubes from wherever they can make an Internet connection.
•
Synchronization Server component tool enables users to work as Remote Clients and then share and update Cube through a shared file; also provides relational backup to multidimensional data
•
Dynamic connectivity and interaction with your company’s relational database, with the ability to drill through to the RDBMS source of PowerOLAP data points