Business Intelligence and OLAP-System

instantOLAP is a lightweight and scalable Reporting- and OLAP-system which combines a modern Web 2.0 frontend – the instantOLAP Portal – with a powerful integrated OLAP engine.

The instantOLAP Portal

Since version 2.5, instantOLAP comes with a new Web 2.0 frontend – the instantOLAP Portal. With the Portal, users can not only view predefined reports – they can also create new Pivot-Tables and reports with drag&drop inside the Browser, share Pivot-Tables, reports and other documents or customize the frontend and create bookmarks or EMail automations on their own.

Though the new frontend includes a large number of new tools and features, it still contains the same flexible reporting engine as previous versions. But now, the portal can load and execute multiple reports at the same time. Because the Portal makes usage of the newest AJAX technologies, this all happens inside the Browser and without any Java-Applet, Flash-Component or Active-X component.

Powerful reports

In instantOLAP, reports are more than simple tables, the reporting engine offers a large number of elements which can be combined to interactive and impressive reports. They can contain elements like interactive and customizable selectors, styleable Pivot-Tables with drilldown and data-input, various chart types, geo-charts and other elements. Everything can be arranged next to each other, below each other, in a tabulator view or as animations. And nearly every property of every element can be changed manually or can be computed with formulas based on real data.

Pivot-Tables can also be configured to input new data or to change existing values. This allows to build planning or consolidation systems in the Browser.

All reports can be exported to PDF documents, Excel sheets or CSV files – manually, by using the export tools in the report viewer, or automated in the background. Reports can also be sent as Email, manually or automated, in these formats.

A powerful reporting system rather contains one single report than a number of reports including magement overviews, detail views or technical reports. In instantOLAP, reports can be linked with each other in order to create a information network and exploring data becomes as easy as surfing in the Internet. When creating a information system with instantOLAP, the result is a very easy to use reporting system where the user does not need to know anything about OLAP and the model or databases on which the reports are based on.

Adhoc analysis

Whenever users need to analyze data on their own, they can use the new interactive instantOLAP Analyzer. In the Analyzer, dimensions and measures can be combined to Pivot-Tables or charts with drag&drop.

The instantOLAP Analyzer uses the same technique as the standard reports, like selectors or Pivot-Tables. But the Analyzer only shows a single table or chart in a full screen mode and with fixed headers when scrolling the table. While viewing, the user can sort the table, suppress empty rows or columns, add totals and subtotals, rotate or rearrange the table and change between the table- and chart-format at any time.

To extend an existing Pivot-Table or to create a new one from scratch, the user has access to the model and can drag dimensions, levels, single keys or facts out of the model and create new selectors or headers inside the table. This all happens in realtime – every time a table is changed and the Analyzer is in the preview mode, the table will be re-queried and display its new data. It is also possible to disable the preview or to hide the editable elements inside the Analyzer.

Sharing documents

Beside reports and Pivot-Tables, other kinds of documents, like PDF files or HTML pages, can be added to the portal repository. Users can upload documents and share them with other users, or they can create or edit HTML files inside the browser.

Documents in the repository can be used in many ways. Long running reports could be automatically provided as PDF or Excel files in the repository, external reports or Excel files could be added to deliver missing information or news and ToDo lists could by managed by an administrator using the HTML editor.

The bookmark system

Each user can create and manage his own bookmarks inside instantOLAP. The bookmark system does not only enable users to open from their personal report list, it also allows to configure the parameters for reports and to open one or more reports, Pivot-Tables or documents automatically when logging into the portal.

When creating bookmarks on reports, the user can decide for each single parameter of the report whether he wants to use his current selection or the standard default value.

This allows to keep computed default parameters as “the current day” instead of overriding them with a fixed value when bookmarking a report. Whenever the bookmark is opened, the day
would still use the “current day” formula, but other parameters as “customer” or “region” could use the bookmarked values.

EMail automation

Since version 2.5 users can create and manage EMail automations on their own with the integrated Automation manager. The EMail automation allows users to execute and export reports on the server and to send the result to their EMail account without the need to be logged into the portal.

The time schedule for sending EMail is fully configurable and different export formats (PDF, Excel and CSV) are available for the attached report results. E.g. a sales manager could configure the system to execute the sales reports every day at midnight and to send it as Excel file to his account.

Access and user management

instantOLAP contains a flexible access management. Whole folders, single reports, complete dimensions or single keys and facts can be protected from unauthorized access with access rules.

Before you can protect elements from unauthorized access, you need a user management which identifies users and assigns them to groups and roles. In instantOLAP you can either use the integrated user management or connect the server to an existing user management. The integrated user management organizes users in groups (which can be nested in other groups). This allows to rebuild you organizational structure with companies, departments, groups and single users.

The instantOLAP engine

instantOLAP models

Like most OLAP systems, instantOLAP is a model based reporting system. No report directly sends SQL statements to any database, they are bound to a model and only query this model using abstract elements like dimensions, hierarchies and measures and the build in instantOLAP functional language.

This makes reports independent of databases and reduces the complexity of reports. Users creating a report do not need to know anything about the database, and many changes in the database do not need to be visible for report (e.g. if a table name changes).

Virtual or real OLAP cubes?

instantOLAP offers both, “virtual” adhoc cubes – which access databases in the moment the data is needed – and classical pre-loaded offline cubes which load all data in ahead. Both kind of cubes can be combined in a single model.

Virtual cubes

One of the basic features of instantOLAP is its implementation of virtual cubes: instantOLAP analyzes each query and generates an internal load list when executing a report in order to decide to query which database for which data. The internal SQL generator then creates the neccesary statements and queries the databases for the needed values. In the last step, the system joins the data and creates the report result with its tables, charts, colors and so on.

The SQL generator is extremely flexible and allows to bind nearly every database structure to a cube – a star schema or any other fixed structure is not needed. This allows to load data directly from data-warehouses or production databases without copying them in data-marts or cubes before. An internal caching mechanism can reduce the execution time by caching the results or parts of the results for later executions or other reports.

Offline cubes

Since instantOLAP 2.5, virtual cubes can additionally be converted into offline cubes which hold all their data in a local storage and do not query any database at runtime.

instantOLAP uses a fast and strong compressing algorithm for loading and aggregating the database data into its offline storage. The data can either be loaded into cube with multiple SQL statements, performing a query for each possible permutation of the dimensions and their levels, or by loading the base data with a single statement and rolling it up.

The advantage of offline cubes is, that the source databases do not need to be connected and available while performing a query on the model. Also, offline cubes have constants and usually much faster response time than adhoc cubes. However, a large cube with many dimensions can result into a very large offline cube with a long loading time, because the size of the offline cubes increases exponentially with the number of dimensions and keys. In this case, it is also possible to combine offline and online cubes, e.g. to load the upper aggregation levels into a offline cube and to load the rest (the detail levels) from the databases in realtime using adhoc cubes.

The Workbench

The administration tool for instantOLAP is the Workbench – a Desktop application which must be installed on the administrator’s workstation. The Workbench is basically used to configure models and to build complex reports. It also offers a number of management functions, like the Session- and Process-Manager or the User-Manager. Normal users do not need the Workbench to view reports or to build simple Pivot-Tables and reports in the Browser.

Creating models with the Workbench

Configuring models is only possible with the Workbench and for administrators. The Workbench contains the configuration editor, which allows to connect and explore databases and to configure dimensions, cubes, formulas and all other elements of a model.

The first task when creating a configuration is to connect the source databases and to design their ERM structure. The Workbench contains a ERM modeler, which can be used to visualize the used tables and their columns and to define the entity relationships between them. If a database can deliver the entity relationships between its tables, instantOLAP will optionally import and display them. Otherwise, if the database is not able to deliver the relationships or if the relationships are not configured in the database, this can be done manually in the ERM editor.

After connecting the databases, the next task is usually to build the dimensions from their master tables. The Workbench allows to comfortably design dimensions with their keys, attributes and hierarchies using drag&drop, but all elements of a dimension can also defined and configured manually.

Databases are not the only possible source for dimensions, keys can also be defined manually or be generated from the internal calendar of instantOLAP.

The final tasks is to define the cubes, which define how to load facts from the fact-tables whenever a query is executed. Because instantOLAP allows to define an arbitrary number of cubes and each cubes can deliver different facts for different dimensions, the cube editor visualizes the cubes in a matrix view to give a fast overview about where the data comes from. Like dimensions, simple cubes can be created with drag&drop but they also offer a large number of options which can be edited manually and allow to transform nearly every database structure into cubes.

Creating reports with the Workbench

The Workbench is also the main tool to create complex reports for end users. The integrated query editor allows to build all kind of reports, from very simple tables up to complex reports and dashboards.

The editor displays the schematic structure of reports with their selectors, blocks, Pivot-Tables and headers. All elements can be created and arranged with the mouse using drag&drop. Complex elements – especially pivot table headers – also offer a large number of design properties which can be edited manually and calculated with formulas. A build-in preview function allows to check the reports and their result at any time within the Workbench. Since version 2.5, instantOLAP uses the original HTML view from the Portal for previews to eliminate any visual difference between the Workbench preview and the later production version of a report.

Server management

Beside the creation of configurations and reports, the Workbench allows to perform a number of other tasks to manage the server and its users. The following tools give you support when managing the server:

• The user manager is used to create and manage users accounts, to cluster them in user groups and to assign roles and functionalities to single users or whole groups. This manager is only available if the internal user management of instantOLAP is used.
• The model manager allows to control the models, their dimensions, cubes and caches. Dimensions, Cubes or whole models can be rebuild at any time, even while the model is productive and in use.
• The session- and process manager enable the administrator to control and to cancel current user sessions and their processes.
• The automation manager is used to control the background automation of instantOLAP, e.g. the automatic execution of reports or the distribution of reports by email. The automation manager is a larger version of the manager in the portal, because an administrator can create more task types and also change the automation for other users.
• The logging tool displays the execution logs for queries and other activities and can be used to find configuration errors for cubes, optimize performance or other tasks. The logging tool can display events from own sessions or even from other sessions.
• The license manager is used to add and manager server licenses from the Workbench.

The user and the license manager are only available for “managers” (users with the manager role) to avoid security problems. E.g. no admin can give other users the admin role unless he is a system manager. Managers also have full access to any file in the server repository and can reset access restrictions at any time.

Used technologies

instantOLAP is completely written in the platform independent Java language. It is installable and runs on every platform for which Java is available, including Microsoft Windows, Unix and Linux, IBM z/OS, OS/400 and others.

The software is a J2EE application and must be installed on a J2EE compatible application server – like IBM Websphere, Borland Application Server or the freeware server Apache Tomcat. The standard distribution of instantOLAP comes with an integrated Tomcat server, but you can also download the instantOLAP components separately and install them on your existing J2EE server. You can also use an existing user management instead of the integrated user database which is installed in the instantOLAP standard distribution.

The communication between instantOLAP and the source databases uses the JDBC standard. JDBC drivers are available for free for almost every database, including all versions of Oracle, IBM DB/2, Microsoft MS SQL and Access, Sybase, SAS or MySql. With JDBC it is also possible to access Excel sheets or CSV files or to use ODBC drivers and datasources.