Concepts and Benefits
proCube is a multidimensional database software tool used for real-time analysis and reporting, as well as advanced, online budgeting and forecasting. It functions in conjunction with Microsoft Excel, your Web browser, and other applications you may already be using at your organization. To start benefiting from this powerful business application, you need to know how proCube works and what it can do to improve your business.
What makes proCube a vital business solution? How does proCube leverage the existing technology your company uses today to become the core of your business operating system? What functions and features of proCube 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 proCube.
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 the background from which proCube evolved, known as on-line analytical processing (OLAP), what it’s all about and what it enables you to accomplish. Building on that background information, we will provide the practical information you need to start using proCube right away: using the proCube guides, installing proCube and getting customer support, and understanding key concepts and the proCube user interface.
Knowledge is the foundation of all successful decisions. Successful businesses continuously plan, analyze, and report on sales and operational activities 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 more likely that the plan to improve that activity will be effective.
More and more information is becoming available to us about our businesses, particularly in today’s increasingly “connected” business environment. A company that can take advantage of reliable information and turn it into shared knowledge, accurately and quickly, will surely be better positioned to make successful business decisions and rise above the competition.
Today, most organizations collect information in a relational database management system (RDBMS). The purpose of an RDBMS is to store data about 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 Structured Query Language (SQL): 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. An SQL (often pronounced ‘sequel’) 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, analytical information or unique reports at a moment’s notice. The underlying structure of the relational database is simply not conducive to the aggregation of numerical data. This is because they are deliberately broken into large quantities of separate tables to optimize small transactions, whereas analytical reporting requires a very different organization. It requires a relatively small number of tables with key business facts—sales or expense amounts, for instance—supported by simple tables that qualify the information.
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 sales. But with proCube, making such a multi-faceted request is a point-and-click procedure, and just as importantly, the underlying data is structured in advance to give you the results quickly!
The primary disadvantages of relational databases include the following:
Static, two-dimensional format, although businesses need views of data from multiple 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 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.
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 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 cannot be easily “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 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: 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 to manipulate row-by-column formatted data.
The primary disadvantages of spreadsheets include the following:
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.
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 sales measures might logically be displayed on the y axis and the months might occupy the x axis (that is, sales measures are rows and months are columns).
Figure 1. 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 multidimensional environment.
Multidimensional technology is known by the shorthand name, OLAP—online analytical processing. OLAP is a new term to 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—called a slice—of a cube to produce a worksheet-like view of points of interest.
Figures 2-5. show several representations of slices of data from a simple 3-dimensional cube, including Cost, Product, and Time.
Figure 2. Cost Cube
Figure 3. Product Cube
Figure 4. Time Cube
Figure 5. 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.
Figure 6. 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 everydimension that defines an enterprise—essentially, the dimensions necessary for the creation of an effective business model.
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.
Figure 7. Example Region Hierarchy
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 dimension-member 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:
Figure 8. Example Slice: Units Sold by Region and Month
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.
Figure 9. Example Slice: Tom's Actual Units Sold in January in the US in Department A
Until the advent of proCube, 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 store a mirror image of the relational data in a proprietary multidimensional database; others store consolidation and formula information in the cubes and leave the relational data in the data store; still other products use a combination of these two methods. proCube 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).
By combining the most advanced features of OLAP, relational database and worksheet technology, along with new functionalities, proCube empowers end-users to access numerous data sources throughout an organization, in real time, for sophisticated planning, analysis and reporting tasks. In this way, proCube delivers limitless flexibility to businesses of all sizes.
proCube 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 proCube to create multidimensional perspectives of your business.
Figure 10. Slice with the three dimensions for Page (Accounts), Columns (Regions), and Rows (Months).
Then use Excel to generate and print formatted reports or produce graphs that dynamically changes as your business changes.
Figure 11. Excel Worksheet and Graph Generated from a proCube slice
proCube provides you with full flexibility in viewing the dimensions that make up your business. Whether you use proCube alone, on the Web, or with Excel as your final report writer, you will substantially increase productivity and gain greater understanding and awareness about your business processes and activities.
In addition to the Excel front end, proCube overcomes the drawbacks of predecessor OLAP tools by providing live, scheduled, or on-demand relational database polling through its Xchange component. This provides users throughout an organization with updates to analytical models as individual data transactions are recorded. The following figure shows how Xchange fits into your data architecture.
Figure 12. proCube Xchange in a Corporate Data Environment
By incorporating this functionality, Xchange™ seamlessly integrates all of 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 go to Xchange.
No matter what the analytical application, proCube is the decision support tool of choice that answers the business questions management asks today. Welcome to a revolutionary business intelligence and business performance management!
The primary advantages of proCube include the following:
Multidimensional cube structures.
Unlimited memory for stacking dimensions.
Combined interface for server management and data browsing.
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 proCube 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.
Permits creation and management of business rules (formulas) that are stored in single location
Data security maintains data integrity and protects access to specified areas of cubes, across data ranges or even by cell.
Internet connectivity via the optional proCube Web application, enabling unlimited reports, analytical views, and graphs to be created anytime, anywhere with no software download. proCube Web also enables you to enter data into cubes from any Internet connection.
Dynamic connectivity and interaction with your company’s relational database, with the ability to drill through to the RDBMS source of proCube data points.