Once you have chosen the option „Chart/Table“ shown in the top row of Creating an Analysis, the Chart Configuration screen as shown in Figure 3.11 will appear. The following sections will explain the different options available when configuring a chart or table.
Figure 3.11: Charts and Tables - Sample Configuration
The preview slot is the most easily understandable part of the configuration screen. While configuring your component, simply click on the slot to see what your current component looks like. That way you can easily try different forms of presentation for your data and select the one most suitable for your purposes. Since the preview slot does not update automatically, you need to click on it every time you want changes to appear.
In the Main Settings you can specify a title for your component as well as the chart type and data source used for it.
Figure 3.12: Chart with Labels, Border and Tool Border
Your selection of dimensions and aggregations defines the data basis for the analysis component. Which dimensions or aggregations are available depends on the data source you selected in the Main Settings.
Dimensions basically represent the columns of the table you selected as data source in the Main Settings. In contrast to aggregations, there are no restrictions as to which data types can serve as dimensions therefore all columns available in the table can be selected. Your choice of a dimension will define the possible level of aggregation for your data. If, for example, you add a dimension containing dates and want to round all dates to month, there will be a maximum of twelve possible aggregations for one year. However, if you round the dimension to years, there will – for one year – only be one entry with one aggregation in your analysis component.
Aggregations are functions that consolidate a set of values belonging to a single occurrence inside a dimension into one single value. Consolidation can be done by accumulating the values, by calculating the average, minimum or maximum or simply by counting the number of occurrences. To give a short example let’s assume your data cube contains a table listing all invoices you received from vendors and their respective order values. Now if you choose “vendor” as dimension and the sum of “order value” as aggregation, for each vendor all entries in your data cube will be accumulated regarding their order values. Your result set will contain one entry for each vendor and his respective sum of order values. If you choose “avg” as aggregation function, the average of the order values will be calculated for each vendor. If you choose “min” or “max”, the minimum or maximum order value will be selected and presented in the result set.
Apart from the function “count” all aggregation functions (“sum”, “avg”, “min” and “max”) need to be based on another column than the dimension column. Since these functions can only be performed on numerical data, only columns containing numerical data types will be available for selection. Basically, the table selected as data source will be scanned for numerical data types and all columns meeting the criteria will be provided as bases for aggregations.
The function “count” simply counts the number of occurrences for each value in the dimensions column (so for our vendor example, the result set would contain an entry for each vendor and the number of invoices you received from him).
If you use aggregations with two or more dimensions aggregations will be calculated for each unique combination of all dimension values occurring in the data source.
Depending on the Chart Type you selected in the Main Settings, the number of possible dimensions and aggregations will vary (please see the following table).
Chart Type | #Dimensions | #Aggregations |
---|---|---|
OLAP Table | ∞ | ∞ |
Time Series Chart | 1 | ∞ |
Time Series Spline Chart | 1 | ∞ |
Line Chart | 1 | ∞ |
Spline Chart | 1 | ∞ |
Bar Chart | 1 | ∞ |
Column Chart | 1 | ∞ |
Area Chart | 1 | ∞ |
Area Spline Chart | 1 | ∞ |
Pie Chart | 1 | 1 |
Donut Chart | 1 | 1 |
Scatter Plot | 1 or 2 | 1 or 0 |
Bubble Plot | 1 | 2 |
Spider Web Chart | 1 | ∞ |
Table 1: Dimensions and Aggregations of Chart Types
A Dimension can be added by simply clicking on the + symbol next to the column name. After adding a dimension, you will see it in the list of dimensions on the right side of the dimension selection field. Each list entry contains the name of the dimension, the first few letters of the field formula as well as two symbols for configuring and deleting the dimension. If you click on the name or formula of the dimension, an input field will appear allowing you to edit the respective field. The name and formula of a dimension can also be changed in the dimension settings which will be explained later in this Section.
Whenever you choose to add a date field, you will be asked if you want to show the data without grouping, if you want to round on one of the date components (year, month, day, hour, minute, second) or if you want to extract one of the components. For example, rounding a date like “2014-01-01 23:59:59” on month will result in “2014-01” whereas extract month will only result in “01”.
An Aggregation can be added by simply clicking on the + symbol next to the column name. After adding an aggregation, you will see it in the list of aggregations on the right side of the aggregation selection field. Each list entry contains the column name the aggregation is based on, the first few letters of the field formula as well as two symbols for configuring and deleting the aggregation. If you click on the name or formula of the aggregation, an input field will appear allowing you to edit the respective field. The name and formula of an aggregation can also be changed in the aggregation settings which will be explained later in this section.
Apart from the field “Count”, whenever you choose an aggregation, you will have to select one of the four aggregation functions that is to be performed on the column selected. These four functions are “Sum”, “Average”, “Minimum” or “Maximum”. “Sum” will lead to the column entries being accumulated. “Average” will give back the average value of the column entries and “Minimum” and “Maximum” will lead to the minimum or maximum value of all column entries being chosen respectively. To learn more about Aggregations, please refer to the previous section “General Information on Dimensions and Aggregations”.
When choosing “Count”, no aggregation function needs to be selected. That is because “Count” simply counts the number of column entries for each occurrence.
You can also define your own aggregations based on PQL Syntax by adding an arbitrary column, e.g. “Count” and then entering any PQL based formula in the Formula field. How to edit the Formula field will be explained later in this Section.
Once you have added a dimension or aggregation, it will be shown in the configuration menu as can be seen in Figure 3.13.
Figure 3.13: Dimension in Dimension List
Once you have added dimensions and aggregations to the chart, they will appear in a list as shown in Figure 3.14. After clicking on the small screwdriver icon, you will be forwarded to the edit menu. The following sections will explain the settings that can be made within that menu.
All font and format configurations are only relevant when an OLAP Table is used. Configurations will influence the appearance of the table entries inside the columns.
Font
Format
Layout
Other
In the lower right corner of the Chart Configuration menu you will find the Filters button.
The Filters button is available in most components and gives you the possibility to define a filter that will be applied only to this component. If you apply a filter, only the data conforming to the filter criteria will be chosen as basis for the component. For the definition, any statement made in PQL Syntax can be used.