5.3.2 Programmable Alerts
7.3 Data Management and Data Model Handling
7.3.4 Working with Data Model
7.4 Control Center
7.4.1 License Entitlements
7.5 Platform Settings
Working with Data Model > New Data Model Creation
To add a new Data Model, simply click the + icon on the Data Models home page.
To create a new Data Model, click the “Create” button and follow these four simple steps:
You’ll land on the Data Model’s configuration page, where you provide basic information about the data model.
It is a helpful feature that offers users a quick view of the information stored in a Data Store. It provides information about tables or indices, their schema, and sample data.
This feature assists users in selecting and configuring the correct values in the data model and workspace stages. Additionally, it offers insights into the raw data through visualizations.
A data model query in data models is like a database select query used to fetch data from a database. Keep in mind that the query language or syntax can vary between different databases, so some familiarity with these queries is helpful to understand how they work.
A model query can be built in two ways:
“Prepare Data Query” is one of the three important sections in a Data Model creation.
You need to pick a Data Source when creating a query, but remember that your data sources should have already been set up using Data Store configurations.
A model can have multiple queries based on your needs. For example, if you want to use data from different Data Stores, you can create more than one query and use transformations to combine them later.
Now, you can create a database query using the Query Builder. This tool helps you build basic select queries for various supported databases using Vunet’s Query Language.
In the “Select Table” section, choose the table or index from which you want to retrieve data. This listing may include Elasticsearch data sources and more.
Note: The “Field of Time Filtering” column, which is optional, includes settings related to time. For instance, you can select options like Timestamp or Date for time-related configurations.
Specify the timestamp column of that table.
If the selected Datastore is elasticsearch, we’ll have a few more fields that involve:
1. Number of rows
You can set the maximum size limit for the query results. This field is specific to Elasticsearch Data Sources.
2. ES Filter
You can choose a filter based on your needs. Please note that this is an optional field and is only visible when using Elasticsearch Data Sources.
To support Aggregation in ElasticSearch you have the following 2 options:
Choose an Aggregation from the dropdown based on your requirements.
To add new fields, click on the + button.
Group By (Optional)
Click on Add Group By to include the Buckets.
Following Buckets supported by Group By.
1. Date Histogram: You can use the “Date Histogram” to group time series data into continuous intervals:
In the Date Histogram, we further have 3 field that needs to be filled to make term aggregation work which involves:
2. Histogram: It is used to group numerical data into contiguous intervals based on the frequency of occurrence. The “Interval” represents the size of these intervals, typically in seconds.
3. Terms: The “Terms” query is used to find documents based on a precise value.
Under “Terms,” there are four fields you need to fill in to make the term aggregation work. These fields include:
Time Zone: You can select a specific time zone by enabling Advanced Settings and choosing the desired time zone.
You can specify a raw database query for any data source if you want to use more advanced source-based queries.
To do this, click on Edit Query and enter the raw query. For example, a sample SQL query (for data sources like PostgreSQL, MySQL, etc) could be:
SELECT timestamp, host, the value from network_heath where type==’in_bandwidth_p’ and $__timeFilter(“timestamp”)
$__timeFilter(“timestamp”) is a mandatory step to be given in any raw query
Note: This is used to pass the time range to the query, and you cannot use any other custom time range based on the original query language.
After completing this query, you can preview, validate, and move on to the next step.
You can now preview any query that is created. There are two ways to preview,
Run Query: Click on the run query option to get a preview of the selected query.
The results will be displayed as shown below.
To run all the queries, click on the Run all queries option at a model level to preview all the queries in a data model.
The results will be displayed as shown below.
Once the preview looks accurate click on Validate and Next to move to the third step.
When you click Validate and Next sometimes it throws an error.
You can skip the validation by clicking ticking Skip Data Checker as shown below. This helps in creating Data Models for future use when the Data is unavailable.
Such skipped Data Models will have a warning tag before the name on the landing page as shown below.
There are other operations you can use to Copy, Delete, and Disable a query.
Copy: This allows you to copy an existing query to create a new query within the same data model.
Disable: You can disable a query in a data model. When a data model is used somewhere, a disabled query won’t be executed.
Delete: You can delete a query both at the local and global levels. This means you can remove a query entirely from your data model.
After successfully validating the query section, you’ll enter the transformation section.
Transformations are used for data manipulation. Keep in mind that they are not always mandatory for data models with only one query, but they are necessary, especially joint transformations, for data models that have more than one query.
On this page, you’ll see a list of supported transformations. Each transformation comes with a description of its highlights.
Clicking on a transformation will take you to the respective transformation screen, where you can create and configure the transformation.
Single Query Transform:
Multiple Query Transform:
The “Organize Fields” transformation allows you to rename fields or columns in a query. Here’s how to use it:
After you’ve organized the fields as needed, you can either run the transformation immediately or proceed to validate the transformation and move to the next section.
The “Add Field” transformation allows you to add a new field to the output of a query. You can do this using arithmetic operations, custom values, or expressions.
Select a query in the Query dropdown. Choose a mode:
With the “Arithmetic Operation” mode, you can create a new field based on arithmetic operations between existing fields. Here’s how to do it:
The existing fields will stay the same, and a new field will be added based on your specifications.
In the “Custom” mode, you can create a new field with a static value in the Query. The value can be a number (integer or float) or a string. Here’s how to do it:
Once you’ve entered this information, click on the Run Transform button to see the changes reflected in the preview.
In the “Expressions” mode, you can create a new field based on a user-defined expression that involves operations like addition, subtraction, multiplication, or division between two or more existing fields or static floats/integers in the same Query. Here’s how to do it:
Once you’ve entered your expressions and aliases, click on the Run Transformation button to see the changes reflected in the preview. The existing fields will remain unaffected, and new fields will be added according to the specifications of your expressions.
The “Filter By” transformation allows you to filter or remove specific portions of the data in a frame.
Here’s how to use it:
This transformation helps you refine your data by including or excluding specific records based on the defined conditions and matching values.
The “Aggregate” transformation is used to summarize or aggregate the output of a query. It also offers time-bucketing capabilities.
Here’s how to use it:
The “Aggregate” transformation allows you to condense and analyze your data, providing you with insights and summaries based on your chosen fields and grouping criteria. Time bucketing helps in organizing data based on time intervals, which can be particularly useful for time-series data.
1. Aggregate without Time Bucketing
This process allows you to aggregate data based on the chosen source field, perform various calculations, and create new fields with summarized information. You can add and remove multiple aggregations as needed.
2. Aggregate with Time Bucketing
This process allows you to perform time-based aggregation on data, summarizing information within specified time intervals. The other aggregation settings, like source field and calculation methods, are similar to aggregating without time bucketing.
To use the Sort By transformation:
To use the Sort By transformation, select a query and then choose the field for sorting.
This transformation helps you choose the top N rows from a data frame, allowing for further operations.
This transformation allows you to join the outputs of two queries into a single frame, functioning like a typical SQL join.
Click on the Run Transform button to see the changes in the preview.
Concatenate combines the results of two Queries into one frame.
Choose two Queries in any order from the dropdown.
Pick either Horizontal (side by side) or Vertical (stacked) concatenation.
Click Run Transform to see the merged results.
1. Horizontal Concatenate
Your data from queries look like this below
As seen above, this is aggregated data from two or more sources having a similar number of rows in them.
To concatenate these into a single stage/frame, use Horizontal Concatenate.
2. Vertical Concatenate
Your data from queries look like below:
As seen above, this is time series data with a similar schema coming from two different queries.
To concatenate these into a single stage/frame, use Vertical Concatenate.
After the transformation, your data will look like the below:
Note: Preview is restricted to a max of 20 records.
Set Properties allows you to specify the metrics that a data model will work with. This selection is crucial for further analysis and monitoring.
First, choose the timestamp column, which should be a common column across all the metrics defined in this section.
The goal is to make it more straightforward and easier for users to understand.
Each metric can now be given with information on the following properties.
To add another metric, simply click the Assign metric column.
Delete: If you want to delete a metric, you can use the delete option.
Run Set Properties: Once you’ve finished configuring your metrics, simply click the Run Set Properties button at the top right. This will validate the metric properties, and the Data Model will perform a background check based on the new information you’ve provided.
Click on the blue eye icon to preview the results.
Once validation is complete, the save button gets activated at the bottom right. Click on it to save the Data model.
With this, The Data Model creation is now complete.
Dynamic filters allow you to set up custom filters within your queries using the “$” symbol with a variable. Depending on your DataStore type, follow these guidelines:
For Elasticsearch DataStore:
For Timescale Datastore (PSQL):
Browse through our resources to learn how you can accelerate digital transformation within your organisation.
VuNet Systems is a next-gen visibility and analytics company that uses full-stack AI & Big Data analytics to accelerate digital transformation within an organisation. We provide deep observability into business journeys to reduce failures and enhance overall customer experience.
© 2023 VuNet. All Rights Reserved.