Creating a simple time series chart with SAP IT Operations Analytics
What is the “Analytics Builder” and what are time series charts?
The Analytics Builder within SAP IT Operations Analytics (SAP ITOA) is a powerful tool that allows you to build numerous analytical artifacts, e.g. dashboards. In this blog post, we would like to focus on how to build a specific chart type using the Analytics Builder: a time series chart.
Time series charts
Imagine you would like to visualize e.g. the number of errors over time. What you need to do this are basically two attributes: a timestamp and the number of errors that occurred at this timestamp. A line chart plotting the timestamps on the X axis and the number of errors on the Y axis would for instance be a very first, very simple time series chart.
This could also be a slightly more complicated one like the number of errors by host over time as shown in the picture below:
Supported types of time series charts
The so-called “Chart Container” block in SAP ITOA’s Analytics Builder is currently (i.e. as of version SAP ITOA 2.0 SP03) supporting the following chart types for time series:
- Time Series Column Chart
- Time Series Line Chart
- Combined Time Series Column Line Chart with 2 Y-Axes
- Time Series Bubble Chart
- Time Series Scatter Chart
Why do I need a time series line chart when there are normal line charts?
Well, the easiest way to answer this question would be to say: “Look at this!”
Comparing this chart with the actually targeted one, we can tell the difference. A regular line chart will not treat the time stamp attribute as a a time stamp, but only as a regular value like a string of text. It will put the time stamp (text) on the X axis and simply plot lines in case there are values for these corresponding values. The actual time stamps will be aligned in an equidistant way, i.e. if there happens to be more time between to individual time stamps, this will not be properly expressed in the chart.
You can follow this tutorial on your own system with your own data, or you can create yourself an instance from our Cloud Appliance Library (CAL) solution for SAP ITOA 2.0 SP03 and follow the tutorial there. This tutorial was built on the demo data set that comes with said CAL image.
Step 1: Time range selector
Most time series charts and dashboards in general are supposed to visualize data from within a particular time range. If you are going to make use of the smart series round function (optional step which we are going to cover in the last step of our tutorial), you even have to use a time range selector.
Within SAP ITOA, navigate to the Analytics Builder and choose to create a new Story. Give this story a name of your choice and start dragging and dropping your first blocks onto the canvas. For our example we will start with the ITOA Grid block, which is like the default UI block holding any UI-related ITOA content together, and the Timerange block. In order to let the time range selector in the resulting dashboard show the availability of events, you need to specify the table that is holding your events of choice, by connecting a Select block to the Timerange block’s Data socket and connecting a Table block to the Select block’s From socket. in the dropdown menus of the Table block you first select the schema and then the table or view of your preference – in the case of the CAL image, we are choosing the following table:
Step 2: Chart container
Next, we are going to add a so-called Chart Container block. The beauty of this block is that it will allow ITOA users to define and switch the visualization at a later point in time without going to the Blockly layout of your story.
Drag a Chart Container block from the palette and drop it into the ITOA Grid block right beneath the Timerange block. Similar to before, we need to tell the Chart Container block which table to query for its data. To do so, proceed as before and add a Select and a Table block.
Hint: You can do so more easily by right-clicking on the previous Select block and choosing Duplicate. This will duplicate the whole block including its sub-blocks which you can now reuse in the Chart Container block.
In the screenshot below, we are wrapping the Table block in an Alias block, which will allow us to address this table via the given alias from now on.
In the case of the Chart Container block, our Select block is not complete yet. In order to pass the results from our query to the chart container for visualization, we need to specify which columns of the table to pass (this is similar to specifying the columns in a SQL command where statements like “SELECT * FROM…” are also discouraged).
To do so, click on the little gear-wheel icon within the Select block to open the configure pop-up. In this pop-up, you can now drag and drop columns to their respective places. In our case, we will need three columns:
- No. of errors
And two group-by columns, as we will group the number of errors by timestamp and host.
After creating the sockets in the configure pop-up, close it by clicking on the gear-wheel icon again and start dragging and dropping columns from the palette onto the canvas. You can also you aliases for the columns again. These aliases will then be used as end user texts on the final GUI, so that end users do not have to deal with sometimes cryptic column names.
Note: The Alias blocks for tables and columns are different ones. You will find them in their respective sections within the palette and you can tell them apart by their slightly different shade of blue.
The result of adding columns and group-by statements should look like this now:
To determine the number of errors, we are using the count function counting the occurrences of the ERROR_STATUS field.
Step 3: Configuring the chart visualization
Clicking on the Run button and switching to the results view of your story, you can see the first results of your story: the time range selector on top and the chart container in the bottom. The chart container, however, is not configured initially and should look like this:
To configure it, click on the Open settings menu (little gear-wheel icon) which will open a pop-up. In this pop-up, you can choose the type of chart you would like to use for your visualization, you can configure which of the attributes you have defined are used for which parts of your chart and you can do some more sophisticated configurations, which we are not going to cover in this tutorial.
For this tutorial, please select the Time Series Line Chart, the Error Count as Primary Value, the Time Stamp as Time Axis, and the Host as Color. Click on OK to close the pop-up.
Looking at the chart, we are seeing, however, that the time range chosen in the selector does not yet have any effect on the chart below. The chart is rendering all data since beginning of time. That is, because we have not yet defined the Where-clause of our Select block. So, let’s go back to the Blockly view.
Step 3: Refinement of SELECT Statement
To add the Where-clause to our Select block, we go to the configure pop-up again and tick the Filter check box at the top. This will add the Where socket to the Select block. We can now attach logical conditions to this socket.
In the Logic section within the palette, you will find all blocks we need to express the logical condition. Our logical condition shall express that the time stamp of all events should be within the selected time range. This could look like the following using pseudo code:
timestamp of events >= FROM timestamp of time range selector AND timestamp of events <= TO timestamp of time range selector
To express the logical condition, we need to use blocks from the Logic section. Let’s use one “and” block and add two “=” blocks to it. You can change the layout of the “and” block by right-clicking on it and choosing External Inputs. Now, change the logical comparator of the “=” block by choosing a different logical comparator from the drop-down menu, e.g. “≥” for the first one and “≤” for the second one. The columns can be drawn from the Column section, as we have just done for the Select block before.
To draw on the values of the time range boundaries that are defined by the Timerange block, we can make use of the blocks from the Binding section, the Timerange binding block in particular. Within these blocks, you can choose the block to read the data from in the first drop-down and the field from that block in the second one. The result should look like this:
Running the story again, we can now see that changing the time range selection on top has an effect on the results rendered on the time series chart below.
Congrats! You have just developed your first simple time series chart!
Hint: Do not forget to save the precious results of your hard work by click on the disk icon in the top right corner and selecting Save to current story.
Step 4: Smart series round function
In our simple example above, we are only rendering a few data points. In realistic scenarios, however, you can easily face situations when the chart is trying to render hundreds or even thousands of data points. This may bloat your chart and make it very hard to read. Hence, there is a nifty little function at your disposal.
In the Function section within the palette, you can find a Smart Series Round block that will help you with this challenge. This block needs to be applied to the time stamp columns.
Note: If you are applying this Smart Series Round block on a time stamp column, which you are using for grouping, then you need to apply the same Smart Series Round block in the grouping too.
The Smart Series Round block expects three inputs:
- The time stamp column on which the function should be applied on
- The Source is meant to specify the Timerange block that defines the time range, which is essential for defining the grouping intervals
- The number of maximal data points on, i.e. the max. number of groups (Note: the min. value to specify here is ‘100’)