Dataset Builder
Take a glance at your data.
Last updated
Take a glance at your data.
Last updated
In the overview screen of the dataset builder, you are able to access and share existing datasets or create new ones. Datasets are used for widgets, which will then be part of dashboards. They are also used in automations and scripts. To see in which elements a dataset is used, click on the on the right side of the overview screen and then on show details.
Add columns to your dataset either from events, dimensions, or functions (which can include both data from events and dimensions). Dynamic events are deprecated.
Access to filters, aggregations, and groupings for transforming data.
Define the name of your dataset and describe it in detail. The name and description will be shown in the overview screen for the datasets.
If you are waiting for new data you can refresh your query.
Further options are available here.
If you change something the save button will be enabled (green) and will save the current settings of your dataset.
Collapse and expand the right sidebar.
Event data usually comes from the SF edge device. The schema for the entries or "events" is defined in the Event Schema Management, which should be available only for admin or key users.
One can think of the top-level entries (e.g. Metadata, DemoData, Sample Machine Data) as the table names and the lower-level entries (e.g. Thing, (S7Data)Timestamp, Distance) as the column names. The metadata is basically the keys, which relate the tables to each other. Clicking one of the entries will add it to your dataset.
The same principle holds for dimensions with the difference that this data holds static information related to your machine or thing, e.g. serial number, location, machine type, and model.
Adding a function column allows you to specify a formula using your data as input. We will see how this works in one of the following sections.
Note: If you add a column of type timestamp it will have a filter set to "today" by default. A column of any other type will have no default filter.
After adding columns to your dataset the first 200 rows from your data should be visualized.
Now you can switch between filtering and aggregating/grouping (Action Panel Setting) by selecting the regarding tab in the top left corner of the dataset builder.
Now that you are familiar with where to find the filters and the Action Panel Settings let's talk about what you can do with this. It is possible to filter your data, the filters available for each column are depending on the data type. For timestamps filters like "last x days" are available. For text, the "like" filter might come in handy. The same holds for aggregations and groupings. Numerical values can be aggregated to an average value, text values do not have that option. For more details on the individual filters have a look at them,
Using only Filters will apply the given filters to each column leaving you with only the rows which satisfy all the filter conditions. Using only Aggregations and Groupings can summarize the rows of your table. Use groupings to define which rows should be put in the same category and aggregations to define how to summarize the values within the same category. To get a table as a result we need to provide actions for every column. For example consider a table with 3 columns, the name of your machine (thing), timestamp, and speed values. To get the average speed for each day, select grouping by day for the timestamp and average as an aggregation for the speed column. A warning in the "thing" column will tell you that it does not know what to do with this column and therefore can not summarize the table. If you want to include the "thing" in your grouping (the result will be average speed per day per thing) activate the grouping. Otherwise, deactivate the "show data" option or delete the column to get the average speed per day. Using Filters and Aggregations/Groupings, in general, the filter is applied to the result of the column (the grouped or aggregated version of the column). The timestamp column makes the exception, here the filter is applied before the aggregation. If you want to filter your data before aggregating you can do so by disabling the "show data" option. These columns will not be included in the aggregation but will still be filtered accordingly. Sorting, you can sort your table by the values of any column by clicking the symbol to the right of the column name. If the result is not as you expected you might need to deactivate the sorting of another column. Altogether, you can transform your dataset in 3 steps. In the first step the timestamp filter and the filters applied to columns where "show data" was disabled. Secondly, the grouping and aggregations are applied. Finally, the remaining filters are applied to the results of the aggregation.
Do not worry too much about the specific thing or time frame you are filtering for. For the use of the datasets on a dashboard or automation the filter can be overwritten from the dashboard (or automation).
Adding a function column will open the formula editor, where you can specify how your data should be processed. In the formula editor, you can concatenate different functions from a catalogue. If you use a statistics function (e.g. maximum, average, ...) your data will get aggregated just as if you would use an aggregation outside of the formula editor, hence you need to specify how to group/aggregate the other columns. Also, make sure that other branches of your formula are also aggregated with a statistics function.
If you want to change the order of your rows you can do so by selecting a different column to sort by. To do so just click on the symbol right next to the column name.
If you want to sort by multiple columns (e.g. first by thing name and then by speed) you can do so by selecting the ascending (or descending) option for the referring columns and order the columns according to what you want to be sorted first (from left to right). E.g. if Thing is the first column (most left) sorting will first be applied to the thing column. If we are also sorted by speed values for the same thing will get sorted according to their speed value.
Clicking the filter icon in the top left corner of the dataset builder will open the filter options.
If you reorder your columns refresh your query to get the new sorting.
