Galaktikasoft

Ranet OLAP Pivot Grid Overview

Galaktikasoft

Let's briefly overview features of the dynamic pivot grid included in the Ranet OLAP library.

Ranet OLAP Pivot Grid is comprised of:

We shall use demonstration database Adventure Works build on the Microsoft SQL Server for demonstrating the features of the pivot grid. Database Adventure Works includes a number of multidimensional cubes. We shall choose Finance cube for analysis. After the cube is selected, the metadata area (Cube Metadata) will display only metadata of the Finance cube. The cube has a number of measure groups. We will select Financial Reporting measure group. Now, only dimensions and measures belonging to Financial Reporting measure group are shown in the metadata area.

The cube metadata are displayed as a hierarchy (in the form of a tree). The tree shows KPIs, Measures, Sets, and Dimensions.

The Report Structure area is intended for designing the structure of the report. It includes such areas as:

The report is designed by simply dragging the cube metadata with the mouse and dropping them into the respective area of the report structure. We select measure [Amount] in the Financial Reporting measure group, and drag it to the cube Data area using the mouse. As a result of selecting the [Amount] indicator, an MDX query is automatically generated and run, and the result of the query appears in the Query Result table.

Let's continue designing the report

If we select dimension [Account] and drag it to the Rows area of the report using the mouse, an MDX query is automatically generated and run. The result of the query appears in the Query Result table. We select hierarchy [Date].[Calendar] in dimension [Data] and drag it to the column area. An MDX query is again automatically generated with the result visible in the Query Result table.

We select dimension [Scenario] in the cube metadata and dragging it to the column area. When a dimension is selected, all members of the dimension are included in the report automatically.  In order to limit this set of members, we have to set up a filter and select only the dimension members that we need.

The result of the selection is shown in the Selected tab. It is generated based on the rules of the MDX query language, and it is a Set.

The report is updated automatically as a result of this selection. Let's continue designing the report by adding dimension [Department]. The metadata tree permits to browse and select cube data, in addition to browsing and selecting cube metadata. In this example, we can see a list of subdivisions. We add the desired members to the report using the Drag and Drop function of the mouse.  A filter is generated automatically for the data of the dimension. Let's look at the contents of the filter. In the Filter building window, we can see the selected subdivisions.

We proceed to create the report. The cube metadata that were used for designing the report, are shown in bold font in the metadata tree. Let's briefly review the main features of Ranet OLAP Pivot Grid.

We open the MDX query generated as a result of designing the report, in a special area. In addition, it is possible to see the MDX query in a special window opened using a command from the local menu of the Pivot Grid. This query changes automatically as the user calls up commands for working with the data: Drill Down, Drill Up, Expand, Collapse, etc.

The MDX query generated as a result of designing the report can be edited manually. We can do it by entering the editing mode using a special command from the toolbar menu.  For example, let's delete the selected text from the MDX query.

We run the query and obtain an updated result which is based on the changes made to the MDX query manually.

Ranet OLAP Pivot Grid designer

Ranet OLAP Pivot Grid has a built-in designer for customer calculations. The designer permits to describe any indicators or sets using all features of the MDX language. It allows to set up any report. Let's create a simple calculated member. We will add 1000 to indicator [Amount]. To do so, we write a respective formula for calculating the indicator. Now let's create a simply calculated set of members. We shall add members from dimension [Account] to the set. It is possible to determine format parameters for calculated members in the designer: FORMAT_STRING, FORE_COLOR, BACK_COLOR, etc. In addition, it is possible to write a script which will determine the formatting conditions.

Let's add the calculated members to the report structure area and run the query. The result of the design is shown in the Pivot Grid.

Let's save the report settings so that we can use them again. The settings are stored under a name in the server. The user can access them from any workstation. Let's load the report settings that we saved previously. All report settings can be saved and loaded again, including the settings for calculated members, settings for formatting, filters, search conditions, etc. We see the loaded search settings with complex search conditions.

The settings determine the conditions for searching members by level [Product] of dimension [Product]. The elements are returned, if the value of property [Caption] includes text "ML road" and property "Color" is "red", or if the value of property [Caption] includes text "Sport" and property "Color" is "blue". In such manner, we can describe any conditions for the search for members in the dimension. In addition, it is possible to browse all properties of the elements returned by the search in a separate area.

In addition to searching by conditions, it is possible to setup various filters:  Value Filter, Label Filter, TOP N items, and set up conditions for the filters. Let's find products with the sales amount, measure [Sales Amount], between 1000 and 5000. The pivot grid displays the formats based on the cube settings or MDX query settings. However, the user can setup the cell display style by his own, when necessary. A special designer module is available for this purpose.

Let's highlight products with the sales amount, the value of measure Sales Amount, more than 2000, using a background color. The Pivot Grid offers multiple options for conditional formatting of the displayed cell, including showing images in the cells. Let's look at an example.

We connect a library of images and select a suitable image from the library. Now the items with sales amount more that 2000 are highlighted with an image, in addition to the background color.

Let's look at other features of Ranet OLAP Pivot Grid, which are accessible by the user. These are: displaying properties of members of the dimension in the table, sorting and grouping values, showing actual data used for calculating an indicator (DrillThrough), etc.

The Pivot Grid can display properties of members of the dimension, as well as sort members of the dimension and values in the table in the descending or ascending order. In so doing, the sorting feature can take into account the current data context.

In addition, it is possible to view properties of the cell in a special window and copy the tuple describing the cell to the clipboard.

And finally, it is possible to view all actual data used for calculating the indicator displayed in the cell, in a special window using the Drill Through feature.

Pivot Grid services

The Pivot Grid offers a number important data analysis services to the user.

First, the user can export a report, generated in the Pivot Grid, as a Microsoft Excel file for further analysis. In so doing, the data will be exported in formats based on the formatting settings implemented in the cube and in the MDX query. It is possible to save the pivot grid data to a local Excel file. This feature gives the user an option to do detailed data analysis using a familiar tool, or while working offline.

Second, the Pivot Grid can display any MDX query, including very complex queries. It can be a query designed by the user himself, or a query prepared manually by an IT specialist. This feature is not very common in similar products. In so doing, all services intended for detailed data analysis (such as Drill Down, Drill Up, Expand, Collapse, DrillThrough) are still available to the user. The user can modify the MDX query in real time and run it immediately. When editing the query, it is possible to use all functions available in the MDX query language and a limited set of Visual Basic functions.

The pivot grid is capable of displaying MDX queries that include more than two axes, not just the Rows and Columns axes. In order to run the same MDX query from the Management Studio, it is possible to convert the query to two axes automatically by means of the pivot grid, after moving axes into a subordinate cube and filter. Working with the Clipboard permits efficient sharing of the source code of the MDX query with other applications.

Third, the Pivot Grid can work with the cube data in the cube data editing mode, if the cube supports editing (if the measure group has a Writeback-partition). The data changes can be entered into the cube directly. This is a unique data analysis feature, so we will review it in detail in a separate video. In addition to the reviewed pivot grid, there is an alternative implementation with a different design. The substantial differences are:

The Active filters Area displays the dimension members, for which data filters are configured, in a convenient form for the user. With a mouse click, the user can open a setup form for a specific filter, or, on the contrary, clear the filter.

The possibility to create a number of reports concurrently in the same form, offers additional advantages to the user, at it permits to analyze various data cross-sections without changing the report settings. A new report is created in a separate tab, and the user can easily go from one report to another by switching between the tabs.

Please see the video demonstrating key features of Ranet OLAP Pivot Grid.