Skip to main content

Graphing with PowerQueries

John Tan
  • Updated

Introduction

DataSet graphs can now be configured to use PowerQueries for added versatility. Furthermore, our dashboards have been expanded to include pie and donut charts (which require PowerQueries in their configuration) -- more information on their setup can be found here: Number, Pie & Donut charts.

PowerQueries function a lot like SQL (and have a similar structure), so aggregating and manipulating data is relatively straightforward. If you haven't used PowerQueries before, we invite you to review the following articles:

Since this feature is evolving, we (always) welcome your feedback!

Configuration

Introduction

Classic Graphs

Bar charts, line graphs, and breakdowns will default to the conventional graph setup, which involves entering a facet (attribute), search filter, and function (ex. mean, count, percentile).

New Graphs

Data tables, pie charts, and donut charts only support PowerQueries via the "query" field. The primary difference is that classic graphs do not have a "query" field displayed on their configuration dialog, whereas new graphs do. However, all dashboard graphs are capable of using PowerQueries to supply data. If the "query" field isn't displayed in the dialog, it can be added via the JSON editor.

Performance

As a rule of thumb, use PowerQuery graphs if your use case requires aggregation or advanced SQL functionality. Otherwise, conventional DataSet graphs are simpler to configure and will load more quickly.

Example

In this example, we've created a PowerQuery that totals the logBytes associated with hosts over time:

'metric='logBytes' host contains 'appserver'
| group totalBytes=sum(value) by host

Please visit the "Search" -> "PowerQueries" page and run the above query. A result similar to the following is displayed:

| host        | totalBytes  |
| ----------- | ----------- |
| appserver-1 | 9,187,474   |
| appserver-2 | 26,031,239  |
| appserver-3 | 16,071,226  |
| appserver-4 | 38,557,278  |
| appserver-5 | 57,685,552  |
| appserver-6 | 70,249,228  |
| appserver-7 | 65,762,868  |
| appserver-8 | 37,482,780  |

Since we will be graphing this information, we need a reference to the time corresponding to the metric. Since the logBytes metric is only generated when log volume is observed, we'll aggregate it by the hour. Otherwise, the results could be extensive. This is achieved with the timebucket function (in bold):

metric='logBytes' host contains 'appserver'
| group totalBytes=sum(value) by timestamp=timebucket('1 hour'), host

As you can see, log volume is now calculated by the hour when it was observed:

| timestamp              | host        | totalBytes |
| ---------------------- | ----------- | ---------- |
| Nov 3 · 2:00:00.000 am | appserver-1 | 2,545,775  |
| Nov 3 · 2:00:00.000 am | appserver-2 | 7,263,511  |
| Nov 3 · 2:00:00.000 am | appserver-3 | 4,335,971  |
| Nov 3 · 2:00:00.000 am | appserver-4 | 10,338,125 |
| Nov 3 · 2:00:00.000 am | appserver-5 | 15,536,040 |
| Nov 3 · 2:00:00.000 am | appserver-6 | 18,830,968 |
| Nov 3 · 2:00:00.000 am | appserver-7 | 17,764,996 |
| Nov 3 · 2:00:00.000 am | appserver-8 | 10,016,026 |
| Nov 3 · 3:00:00.000 am | appserver-1 | 7,118,358  |
| Nov 3 · 3:00:00.000 am | appserver-2 | 19,284,916 |
| Nov 3 · 3:00:00.000 am | appserver-3 | 12,235,848 |
| Nov 3 · 3:00:00.000 am | appserver-4 | 29,905,377 |
| Nov 3 · 3:00:00.000 am | appserver-5 | 44,019,427 |
| Nov 3 · 3:00:00.000 am | appserver-6 | 53,819,370 |
| Nov 3 · 3:00:00.000 am | appserver-7 | 49,597,674 |
| Nov 3 · 3:00:00.000 am | appserver-8 | 27,802,451 |

Note: The timestamp always uses the UTC time zone

Rearrange the Data

One final step remains before we can graph the above PowerQuery data. We will use the transpose function (in bold) to rearrange the table so that the rows (values) of the aggregation column (host) are displayed as columns:

metric='logBytes' host contains 'appserver'
| group totalBytes=sum(value) by timestamp=timebucket('1 hour'), host
| transpose host
| timestamp              | appserver-1 | appserver-2 | appserver-3 | appserver-4 | appserver-5 | appserver-6 | appserver-7 | appserver-8 |
| ---------------------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- |
| Nov 3 · 2:00:00.000 am | 1,135,512   | 3,167,095   | 1,826,382   | 4,371,280   | 6,751,233   | 8,160,055   | 7,808,418   | 4,312,492   |
| Nov 3 · 3:00:00.000 am | 8,765,849   | 23,885,358  | 14,777,508  | 36,499,849  | 53,895,782  | 66,122,484  | 61,369,270  | 34,020,771  |

This layout is recognized by the graph and results will be broken down by the appserver* columns relative to the timestamp. Additionally, when using transpose, please note the following:

  • There is a limit of 100 columns which are created by the transpose function within a PowerQuery. Please note that this limit is further reduced when graphing with a PowerQuery, as the considerations of legibility and performance are balanced when rendering complex datasets.
  • Once the transpose function is used, no additional functions can be performed after the transpose statement.
  • The transpose function is specific to PowerQuery graphs and supersedes the breakdownFacet parameter (which is only used for conventional graphs).

You can directly preview your graph by clicking the "Table" button and selecting the visual graph type from the side panel:

For simplicity, you can directly save the PowerQuery graph to a dashboard by clicking the "Save" button (located to the right of the "Table" button) and selecting the "Save to Dashboard" option.

Configure the Graph

For this example, we will be graphing a line graph with the results of our (above) query. An example of the conventional graph settings is provided for comparison.

Conventional Graph Setup

mceclip0.png

Several notes about the Conventional Graph:

  1. With the conventional (line) graph, it's not possible to calculate results by the hour. However, the barWidth parameter (see Official Documentation) enables this with the bar chart.
  2. The breakdownFacet parameter calculates results by the available host values
  3. The plots parameter contains the search query used for the graph
  4. With Conventional Graphs, display settings will almost always be defined in the JSON configuration of the parent dashboard.
... 
 {
   breakdownFacet: "host",
   graphStyle: "line",
   plots: [
     {
       facet: "value",
       filter: "metric='logBytes' host contains 'appserver'",
       label: "value"
     }
   ],
  title: "appserver Log Volume",
  layout: {
    h: 14,
    w: 20,
    x: 0,
    y: 14
   },
   lineSmoothing: "straightLines"
 }, 
...

PowerQuery Graph Setup

mceclip1.png

The JSON configuration of the PowerQuery graph is fairly straightforward:

  1. The plots parameter has been replaced by the query parameter
  2. The breakdownFacet is no longer used, since this is a PowerQuery-based graph
  3. Since we control the data structure with the PowerQuery, the graph depicts a summary of log volume by the hour.
  4. With PowerQuery graphs, most display settings are controlled within the PowerQuery.
...
 {
   graphStyle: "line",
   query: "metric='logBytes' | group totalBytes=sum(value) by timestamp=timebucket('auto'), host | transpose host",
   title: "appserver Log Volume - PQ",
   layout: {
     h: 19,
     w: 36,
     x: 0,
     y: 14
   }
 },
...

 

Related articles

Comments

0 comments

Please sign in to leave a comment.