ESOLAP Designer

Από

OLAP Designer

The OLAP Designer is a data summarization tool that is used in the context of data processing. They are a good solution when you need to summarize and analyze large amounts of data. OLAPs are used to summarize, sort, reorganize, group, count, etc. data retrieved from two types of data sources.

The aforementioned data sources are the two following:

  1. View (Scroller)

To use a view as the data source, you must have previously designed and develop the view. For detailed information about views, please refer to the ESBooks\ EBS-ReportingEN

  1. SSAS (SQL Server Analysis Services)

To use SSAS as a data source, you must connect to the SSAS database using the ESDBConnectionEdit.exe. After running the previously mentioned file, you have to provide the following information:

  • SQL Server (OLAP)
  • Database (OLAP)
  • Define the connection type.

If the windows user is authenticated and have access to the database you select ‘Windows Authentication (OLAP), else you insert the user credentials in the next two columns; User (OLAP) and Key (OLAP)

For detailed information about SSAS, please refer to the related documentation file.

ESOLAP Designer-image1.png

New OLAP

To create a new OLAP, open the OLAP designer (menu: Tools & Configuration \ Additional development tasks \ Olap designer…) and go to File > New. A pop-up screen appears in order to define the following:

Field Description
Code OLAP code
Area The area where the OLAP will be stored
Title OLAP title
Data source The data source that will be used for retrieving data
View The dataset of the data source
Description OLAP description

Add tab

Each OLAP report could be consisted of one or more tabs. To add a tab, select the option Add \ Tab \ New. You need to use this option only if you wish to add multiple tab pages.

ESOLAP Designer-image2.png

Add cumulative table

To add a cumulative table, click ‘Add’ in the toolbar and select ‘Cumulative table’. In the pop up screen you have to define the title.

ESOLAP Designer-image3.png

Table layout

After naming a cumulative table a pop up dialog box is shown in order to design the layout of the cumulative table. Each table is divided into four (4) areas in which the available fields of the selected data source can be placed. These areas are:

  1. ESOLAP Designer-image4.pngFilter
  2. Row
  3. Column
  4. Data

ESOLAP Designer-image5.png

By default, when creating a new OLAP table, all fields are placed into the ‘Available fields area’

You can place a field from one area to another by dragging it from the ‘Available fields area’ and dropping it to the target area. Using the drag and drop method, you can change the area in which a field is placed. To apply any changes to the table you have to click the ‘Update’ button.

Date time fields

By default, for each date time field the system will provide the following standard dimensions:

  • Year
  • Quarter
  • Month
  • 10-day - In this context, this dimension must be placed under the month dimension
  • Week

Cumulative table properties

The cumulative table properties are accessed through right-click on the table and by selecting ‘Cumulative table properties’. A pop up dialog box with two sections are shown with the following options:

Generic properties

  • Table title
  • ESOLAP Designer-image6.pngAllow field drag & drop
  • Filter area
  • Row area
  • Column area
  • Row - Open levels (number)
  • Column – Open levels (number)

Totals

  • Row total
  • Column total
  • Row grand total
  • Column grand total

Fields visibility

Show/Hide fields

You can show/hide fields by using one of the two (2) following actions:

  1. ESOLAP Designer-image7.pngRight-click on a field and select from the actions list Field and Hide field.
  2. Right-click on a field and select from the actions list Show field list. To hide a field you drag and drop it in the field list section. To show a field, select it, then drag and drop it into the section you wish to be shown.

ESOLAP Designer-image8.png

ESOLAP Designer-image9.png

Reordering fields

  1. Drag & drop method. Drag the field header and drop it to the new position you wish to be placed.
  2. ESOLAP Designer-image10.pngThrough the field list. A list with the available options is shown by right-clicking on any field of the table. Select Show field list action and reorder the fields you wish.

ESOLAP Designer-image8.png

ESOLAP Designer-image9.png

Sorting

Field values

ESOLAP Designer-image11.pngTo change the sort order of field values, click the field header:

Summary

ESOLAP Designer-image12.pngTo sort the values of a column based on the values of a row or vice versa, right-click the column/row and choose the field whose values should be sorted by the column/row summaries from the context menu:

Filtering

ESOLAP Designer-image13.pngTo apply filtering to a field, click the filter button on the field header and select the field values that should be displayed in the table.

Conditional formatting

The appearance of individual data cells could be changed based on specific conditions using the Format rules context menu. This feature helps to highlight important information, identify trends and exceptions, and compare data.

To invoke the menu, do the following:

  • Right-click the data cell corresponding to the intersection of the required data, row and column fields.
  • ESOLAP Designer-image14.pngSelect the required rule from the list.

Custom field creation

ESOLAP Designer-image15.pngTo create a custom field, right-click on any data field and then Field… and Create field

A pop-up screen appears in order to define the following:

Field Description
Title Field title
Type The data type of the field.
Area The area in which this field will be shown
Formatting type The formatting type of the field
Format The format
Expression The expression used for calculating this field.

ESOLAP Designer-image16.pngTo write the expression, click on the ESOLAP Designer-image17.png button in the expression field to open the expression editor. The expression editor provides lists of the available fields of the data source, constants, operators and functions which can be used for writing an expression.

Custom field creation (ES function)

To create a custom field using functions provided by Entersoft, right-click on any data field and then Field… and New field based on parallel period.

ESOLAP Designer-image18.png

Declare the title of the field you wish to create and select one of the functions listed. The available functions are the following:

  • Difference in relation to the first value

The difference between the first value of a hierarchy and each one of the following. A hierarchy in the column area should be defined.

  • Difference (%) in relation to the first value

The percentage of difference between the first value of a hierarchy and each one of the following. A hierarchy in the column area should be defined.

  • Running total

The cumulative sum. A hierarchy in the column area should be defined.

  • Difference

The difference between the previous and next value of a defined field in a hierarchy. A hierarchy in the column area should be defined.

  • Difference (%)

The percentage of difference between the previous and next value of a defined field in a hierarchy. A hierarchy in the column area should be defined.

Add chart

You can add a chart to an OLAP report based on a previously created table. To add a chart, select the Add option and then Chart based on table. A dialog box opens, in which the title of the chart must be declared and the table that this chart will use for retrieving data. By default the chart is placed in the rightmost part of the designer. To place the chart wherever you wish, click on the ESOLAP Designer-image19.png button and enable the layout design and then drag and drop the chart to the position you wish to place it. Afterwards, disable the layout design and close the designer pane by clicking on the ESOLAP Designer-image19.png button.

ESOLAP Designer-image2.png

ESOLAP Designer-image20.png

To select the measures for depiction in the chart, right-click on the chart and click Select measures and select the measures that will be depicted in the chart.

ESOLAP Designer-image21.png

ESOLAP Designer-image22.pngESOLAP Designer-image23.png

ESOLAP Designer-image24.png

The chart does not create multiple Y axes for fields of different units, for instance, turnover and quantity, so it is highly recommended to use fields of the same unit.


PDF Version