This tutorial explains how the Visual KPI SQL Remote Context Server works with a SQL data source and walks you through a 10-step process for creating your own SQL RCS.
Visual KPI gives you the power to automate the configuration of Visual KPI sites based on external applications or systems. Using a Visual KPI Remote Context Service (RCS), you can integrate with applications like OSIsoft AF and other historian databases.
Introduction
What Is a Context Server?
How Does a Remote Context Server Work?
Why Use Visual KPI Remote Context Services?
Types of RCS and Visual KPI Integration
Start the Tutorial
Create a SQL RCS to Spin Up a Visual KPI Site
Step 1: Install a New Visual KPI Instance
Step 2: Configure Visual KPI Designer
Step 3: Create a Database for Mining Sample Data
Bonus: Examine and Understand the Data
Step 4: Install an ODBC Interface
Step 5: Install the RCS
Bonus: Understanding Definition Queries
Step 6: Start the RCS
Step 7: Add More Data
Step 8: Add Visualizations
Step 9: Use Queries to Create Dashboards
View Profiles and Dashboards
What Is a Remote Context Server?
A remote context server facilitates the development and deployment of context-aware applications. These context-aware applications gather information that can be sensed by the application’s operating environment. Data, such as location, temperature and state, may be gathered via sensors. Other types of data include system or application data, such as users and roles.
The remote context server can aggregate data from multiple sources and provide interoperability so that data can be stored and shared.
A remote context server can:
- Access context data via a network API
- Store context data and history in a database
- Perform operations on context data to extract meaning
- Share context data via networked applications
- Provide privacy protection via access control
How Does a Remote Context Server Work?
A remote context server grabs raw data from other context-aware applications and stores it in a database.
Networked applications can query the remote context server or ask to be notified when changes in state occur. These network applications understand the storage model of the data in the remote context server in order to query data. They can then retrieve data at any point in time, whether past or present. Calculations can also be encoded to predict future changes in the data.
Visual KPI uses a remote context server and interfaces to automate the configuration and visualization of data for Visual KPI sites. Visual KPI Remote Context Services (RCS) operates with interfaces and queries for reading metadata or configuration information from business applications or other sources. This retrieved information is required to create and configure objects in Visual KPI sites. In other words, the RCS allows Visual KPI to be dynamically configured (or automated) so another system can be the master source of metadata.
Why Use Visual KPI Remote Context Services?
A remote context server allows you to aggregate raw data from multiple sources, store it, track it and perform operations that give the data meaning. For example, a retrieved value of 75 means nothing, until you tag it as temperature or flow rate.
By retrieving and storing data, you can then perform analysis that provides more meaning. For example, yesterday the temperature was 75, but today it’s 85. That’s a simple example, but you can imagine the complex analysis possible with a history of stored data. Identify deviations, changes in state, efficiencies, batch processes and additional data associated.
The Visual KPI RCS is not an interface for reading data. An interface only gives you access to raw or calculated data. The Visual KPI RCS uses attributes or configuration information to make data truly meaningful in real time.
With the Visual KPI RCS, you can create, define/edit or delete objects.
When to use Visual KPI RCS:
- If you already have a hierarchy and KPIs defined in another application, use the RCS to read that information so KPIs, Groups and other objects are created automatically with no extra work.
- When you have existing data in multiple sources, rapidly changing, or large amounts of configuration data, use the RCS to support automatic updates. Configure once. Your data sources are updated live with one connection.
- When you want a hands-off approach with zero cost to maintain and no manual data entry. New KPIs or objects are added to your master applications, the RCS reads those changes (on a periodic basis of your choice), and the new objects appear in Visual KPI without any manual intervention. The same is true for making changes or deleting objects. Everything is automated in Visual KPI every time the RCS runs.
Types of RCS and Visual KPI Integration
The Visual KPI RCS supports custom integration to any SQL data source and other historians. It natively integrates with applications such as
- OSIsoft AF
- VMWare
- Zabbix
- Xompass
- enterprise:inmation
The Visual KPI RCS can read many types of information from these external applications. It reads almost all metadata and attributes that are used to manually create objects in the Visual KPI Designer including:
- Group definitions; hierarchy
- KPIs, including actual, target, limits, and all attributes
- All chart and table definitions
- Alert information
- Dashboard definitions
- Security information
Visual KPI RCS supports customized integrations to any ODBC-compliant database or a vertical database built on top of relational data. In the tutorial, we’ll create a custom SQL remote context server and use queries to define data and relationships.
Tutorial: Create a SQL Remote Context Server
In this tutorial, we’ll build a Visual KPI site to visualize data from a fictitious mining company, and we’ll do it using a custom SQL remote context server. The Visual KPI RCS will automate the creation of groups, KPIs, trends, bar charts and dashboards.
The next steps walk you through creating a working SQL remote context server and installing the sample data. Before we’re done, you’ll get to add more data, more visualizations and dashboards. Then we’ll take a look at your data live in a Visual KPI site.
What you will need before starting the tutorial:
- An installed version of Visual KPI Server Manager on a machine with all the prerequisites (like IIS) and required permissions
- An installed version of Visual KPI Designer
- Access (admin) to a SQL Server on which we will create a couple of databases (one installed by the Server Manager for Visual KPI and one created manually and populated via a SQL script)
- SQLRCSTutorialScripts.zip, which contains the two scripts required for this tutorial
Follow these links for more in-depth information about Visual KPI prerequisites, permissions and installations:
- Getting Your Machine Ready for Installation
- Prerequisites and Download Links
- A Guide to Installing Visual KPI Server
- A Guide to Installing Visual KPI Designer
To install the most recent versions of Visual KPI software components, go to transpara.com/dl.
Once your machine is ready, you can go to Step 1.
Step 1: Install a New Visual KPI Instance
In this step we will install a new Visual KPI instance, then we will configure it and use it for our SQL remote context server demo.
After you have installed Visual KPI Server Manager and Visual KPI Designer, open Server Manager to install a new Visual KPI instance.
Start the Installation
- Right-click Sites at the top level for a dedicated Visual KPI site, or right-click one of the sites in the list to install Visual KPI on an existing site.
- Select Install New Visual KPI Instance.
- Follow the instructions for installation, including adding and verifying your license key.
- Name the Visual KPI Instance. In our example, we named the instance MiningDemo.
- Under Database Server, select your SQL Server and give the database a name.
- Connect using Windows authentication if you have permissions, or enter your SQL Server username and password.
- Set up SQL Server authentication.
IMPORTANT: During installation, please uncheck Manufacturing Demo under Install prebuilt demos and sample data. We do not want other demo data cluttering our Mining demo!
IMPORTANT: SQL Security needs to be set in order for the web server to communicate with the SQL Server.
- If this is a single-box install (SQL and IIS on the same server), then you will have to grant permissions in order for Network Service (our default security context) to read and write to SQL.
- If this is a two-box deployment (separate IIS and SQL), then you will have to grant these permissions to <IISSERVER>$ where <IISServer> is the name of the IIS Server.For example: ComputerA is our SQL Server and ComputerB is the name of our IIS server. We would need to grant privileges to ComputerB$, which is how Network Service is viewed across the network.
In this demo we have a single-box deployment, therefore we have granted db_owner rights on the MiningDemo database (created by the Visual KPI Installation) to NT Authority\Network Service via SQL Server Management Studio.
Browse to Your New Visual KPI Site
- From the Server Manager, right-click on the name of the Visual KPI Instance MiningDemo.
- Click Browse.
This will open a browser and take you to the newly installed Visual KPI Instance.
Because we haven’t added data yet, you’ll see a blank Visual KPI dashboard.
Note: Your URL might look different based on the port of the website you selected to install Visual KPI on. We used a site on port 71, which is why you see localhost:71 in the URL.
Step 2: Configure Visual KPI Designer
Next, let’s configure a few things via the Visual KPI Designer to work with our SQL remote context server.
Once Visual KPI Designer is installed, you can add a new connection via the Instance Connection dialog.
Create Attributes
The first thing we need to do is create a list of attributes to use in our SQL remote context server demo. Attributes are used for many things, including relationships of objects to groups, but for our demo we will be using them to view data by attributes (by Mine Type or Truck Model, etc).
For this tutorial, we will add custom attributes using Visual KPI designer, but then we can populate the data for these attributes using the RCS.
Add attributes in Visual KPI Designer
- Click on the gear icon in the right panel of Visual KPI Designer
- Select Advanced > Attributes
- Add the following attributes:
- Asset Type
- Make
- Model
- Capacity
- Mine
- Ore Type
- Mine Type
- Ensure the “x” is entered in the first column to select each attribute. (Visual KPI Designer actions such as Save and Delete are based on “selected’ lines. The “x” signifies selection).
- Once you’ve added the attributes, click the Save icon to save these attributes back to the Visual KPI database.
Disable the Stale Age Default Setting
Finally, we will change a default setting for KPI Stale Age. By default, this is set to 60 minutes. This means when any data retrieved via an Interface (think Truck or Mine metrics) is more than 1 hour old (timestamp), the KPI status will be overridden to display Stale.
For this demo, data will be per hour or per day so we want to ignore the concept of Stale. This setting can also be set as an override per individual KPI, but for our exercise we will simply change the global setting.
- In Visual KPI Designer, click Sitewide Settings (gear/configuration icon).
- Select Features & Config > Website.
- Set the Value of KPI Stale Age to 0 (zero), which will in effect turn off this feature. (You’ll find this setting way down on the list.)
- Don’t forget to click the Save icon.
Note: Items under the gear icon are not modifiable via the RCS at the time of this document’s creation. We do plan to address this in a future build.
That’s it for our first step. If you have any issues to this point, or if your screen looks different from the images, please give us a call or email support. We are always happy to jump on a virtual meeting and walk you through it.
Step 3: Create a Database for Mining Sample Data
In this step we will use SQL Server Management studio to create a database for our mining data that contains both metadata (what mines do we own, what trucks do we own, what mines are the trucks in, etc.) and metric or process data (how much are we mining per hour, truck utilization, etc).
First, we need to create a database to hold this data.
Create the Database
- Using Microsoft SQL Server Management Studio, right-click on the Databases folder and select New Database.
- Give the database a name. We have named this database MiningData.
IMPORTANT: Just like in our Visual KPI database (MiningDemo), we need to allow Visual KPI access to the database. As described in Step 1, give Network Service or Boxname$ access to the database.
Execute the script to add data
- Once you’ve created the database, make sure you are connected to the correct database, MiningData.
- Open demoscript1.sql and execute the SQL. This script is creating a year of fake data, so it will take about 20 seconds to complete.
SQLTutorialscripts.zip
After the script runs to completion, you will see three tables (Metrics, Mines and Trucks) along with a handful of stored procedures.
Bonus: Examine and Understand the Data
Next, let’s dig in and look at this newly created data. Understanding what data you are working with is fundamental when trying to leverage a SQL remote context server. You can’t expect to automate based on data if you don’t know what’s in the data.
In our demo example, we have two tables that we would consider metadata tables. These tables are labeled Mines and Trucks. The third table created during the script run is Metrics, which is meant to be process data, or metric data, for our assets. This is where things like DailyHours or Daily efficiencies are stored. This is our time-based or time-series data table.
First, let’s look at our Mines table.
This is a list of mines our company currently owns (only two, but we will add more later). The table is purposefully simple to separate learning more complicated SQL from learning how to use the SQL remote context server. The table contains the ID in the foreign system, a name, description, location data (latitude and longitude) and some other metadata, like material mined and type of mine.
Second, we have a Trucks table. After all, what good is owning a mine without owning some really big trucks!
Again, this is a purposely simple table with an ID, Name, location and some other metadata like Make, Model and Capacity. This table also includes a Mine column, which maps the truck to a mine (relationships). Here, we can see we have two trucks per mine. Truck01 (ID of T01) and Truck02 (ID of T02) belong to Mine 01, and the other two trucks belong to Mine 02.
You can infer a hierarchy here. We can imagine a Mines group with a list of mines (along with KPIs) and a list of Trucks (along with KPIs) and a way to see what trucks belong to what mines. We will do just that later in this tutorial.
Our last table is Metrics. This is a much bigger table and has a list of metrics (column-based) per timestamp. Some metrics are hourly, while others are daily rollups.
Your timestamps may or may not line up with mine as the script we ran creates this data somewhat randomly. You will notice we also have future data (no we didn’t create a flux capacitor) and the purpose of this is so that you can play with this demo and still see new data come in. The script created six months of historical data and six months of future data.
This table will be used to gather KPI metrics via an ODBC interface. You can imagine the KPIs we will build in later steps. We can see things like Daily Hours per truck and Waste Rates per mine. With this type of data we can have live KPIs that can be trended over time.
Step 4: Install an ODBC Interface
Let’s create a working interface to our Metric table. We will do this before we create our RCS as the RCS will refer to the interface when creating KPIs. KPIs will need to get their data from somewhere, and we will need to reference the interface name in order to properly define a live KPI (moving data behind them).
Install the ODBC
You’ll install the ODBC using Visual KPI Server Manager.
- Right-click on the Interfaces node under our newly created Visual KPI instance (MiningDemo).
- Select Install New Visual KPI Interface.
- Scroll over the correct interface and select ODBC Interface.
- Follow the Interface Setup Wizard, accepting defaults. We’ll name the interface and update the Connect String to point to the MiningData database.
- Give your ODBC interface the name Metrics. This is important as we will reference this name later.
IMPORTANT: Be sure you change the default connect string to point to our MiningData database. If you fail to verify and the Connect String is not correct, then we have problems with security on the database. Step 2 talks about this, but if you are still stuck then please give us a call and we can walk you through it.
Once you close the Wizard, you’ll see the newly installed ODBC interface in the Interfaces folder of Visual KPI Server Manager.
Verify and Test the ODBC Connection
Once you have installed and configured the ODBC interface, you should verify the interface is able to connect to the database. Use Visual KPI Server Manager to verify the connection.
- Right-click the ODBC interface.
- Select Verify Connection.
A message displays indicating the connection was successful, or if the connection failed, a message displays indicating the failure. If there were any errors, make the required changes to the Connect String and/or Queries and repeat the verification. See Use Connect Strings with the Interfaces.
Verifying the connection does not ensure proper security has been implemented. To ensure you can access the data within the database, you should test the ODBC connection.
Write Queries to Access Data
We now have an installed interface that points and connects to our MiningData database. Next, we need to write three queries that will access the data needed for our KPIs.
While interface training is beyond the scope of this tutorial, we will do a quick review. If you need more help on interfaces, see our Interfaces & Data Sources articles.
An ODBC interface requires the following three queries:
- Get Current Value query – given an input or set of input parameters return a single value and timestamp (most current value not in the future)
- Get History Value query – given an input or set of input parameters and a timestamp, return a single value and timestamp (historical value closest and not after the timestamp)
- Get Trend Data query – given an input or set of input parameters along with a start and end timestamp return a set of values and timestamps that fall between these values. It is also best practice to return the previous and following value/timestamp pair to avoid white space at the start and end of the trend.
For this tutorial, we will provide you the three queries. Copy and paste the following queries into the appropriate fields for the ODBC-Metrics interface.
Get Current Value Query
select top 1 {0} as Value, Timestamp as Date from Metrics where Timestamp <= GetDate() and {0} is not null order by Timestamp desc
Get Historical Value Query
select top 1 {0} as Value, Timestamp as Date from Metrics where Timestamp <= {TIMESTAMP} and {0} is not null order by Timestamp desc
Get Trend Data Query
select {0} as Value, Timestamp as Date from Metrics where Timestamp BETWEEN {STARTDATE} AND {ENDDATE} and {0} is not null union
select Value, Timestamp as Date from (select top 1 {0} as value, timestamp from Metrics where Timestamp < {STARTDATE} and {0} is not null order by Timestamp desc) as First union
select Value, Timestamp as Date from (select top 1 {0} as value, timestamp from Metrics where Timestamp > {ENDDATE} and {0} is not null order by Timestamp asc) as Last order by Timestamp asc
Don’t forget to click Apply.
Verify the Interface
We can easily test to ensure we are getting data from the interface. Let’s do that for our current value.
- Select the ODBC-Metrics interface in the left panel.
- Select Get Current Value Query in the right panel.
- Click the more icon (…) after the Get Current Value Query string.
- Click the Test button.
- Enter one of the column names (this will be the parameter used later in the Actual field for our KPIs).
We used T01_HourlyTons, which will show us our most recent hourly tonnage for Truck01.
Note: Your data will look different than ours (remember randomly created metrics), but you still should see a value and a timestamp.
At this point, we have a working and tested ODBC interface to our Metrics table.
Step 5: Install the RCS
Now we get to the point of this tutorial: setting up the RCS. We will install a SQL remote context server and dig in and understand the queries needed. Don’t worry, we have provided these queries for you.
Start the RCS Install
We’ll continue working in Visual KPI Server Manager for the RCS install.
- In Server Manager, right-click on the Remote Context Services node and mouse over Install New Visual KPI Remote Context Service.
- Select SQL RC Service.
For more information about the other types of RCS we offer, contact us and we will be happy to help.
- Accept the defaults, which will leave you with an installed but not configured SQL RCS.
Connect to a Database
We need to configure the source connect string to connect to our MiningData database (the one with Mines, Truck and Metrics). Remember the interface interacts with the Metrics table and the RCS will interact with the Mines and Trucks tables.
- In Server Manager, make sure you’ve selected Remote Context Services> Visual KPI SQL RCS Server (MiningDemo).
- In the panel on the right, select Source Database Configuration>Source ConnectString.
- Select the MiningData database.
- Click Apply.
Configure RCS Properties
Let’s look briefly at each section of properties for the SQL remote context service we just installed and consider the settings for each.
Logging Settings
By default we will only log (to the Windows Event Log) errors that occur during RCS execution. When trying to track down why objects are or are not showing up during RCS query development, turning this to Full and watching the event log is a good way to try to understand what is happening.
SQL Remote Context Service Settings
The RCS runs on an interval, and by default the interval is one minute. This means after each run of the RCS, it will wait one minute and run again. Remember, remote context services are looking at metadata and not process data. Metadata by nature does not change (or shouldn’t) change second to second. This is slower moving data (like ‘what trucks do I own’ and ‘what mines do I own’), so there should be no need to run this too quickly. Keeping the interval at one minute during development is a good idea, but once it’s working, consider tuning this to hourly or even daily.
Also in this section is Enabled (will it run) and Allow Updates, which controls whether the RCS will update objects it has already created (for example, if our mine named Mine 01 with an ID of M01 changes). If set to False the RCS will only create objects and not update them. We can leave this as True.
Definition Queries
This is where we supply queries that will pull out the definitions for what Groups, KPIs, charts and tables the RCS should create from the metadata (more later).
Relationships Queries
This is where we supply queries to create the relationships between Groups and other objects. The definition queries are used to build objects and the relationship queries are used to build the hierarchy (more later). These queries go hand-in-hand in that we can’t position an object in a hierarchy (relationship query) if the object has not been defined (definition query).
Profile & Dashboard Queries
These queries are used to generate dashboards and all that goes with them (profile groups, profiles, widgets and even bookmarks).
Pre & Post Process Queries
This can be useful for any data manipulation needed on the metadata. These are just queries that can be run (before and after the other queries) in order to do things like process the data or handle any sort of ETL functionality.
Error Handling
This section determines how the RCS treats supplied queries that return no data. Each time the RCS runs we use a MetaID to determine the mapping of the foreign system object (stuff in the MiningData database) to Visual KPI objects (Groups, KPIs, Charts, etc).
The RCS will either
- create Visual KPI objects (we detect an unknown MetaID)
- update Visual KPI objects (we detect a known MetaID)
- delete Visual KPI objects (we have a MetaID stored that is no longer being passed to use via a definition query)
If we have one of these set to True and a supplied query returns nothing, we will throw an error (Event Log) and stop processing. If we created 20 KPIs in the first run and the next time the query ran we got no KPIs, we will either delete the existing 20 KPIs (often not preferred) or just throw an error and stop the process until the next round.
Bonus: Understanding Definition Queries
Let’s start by inspecting some of those stored procedures we added during the database installation. The queries supplied to the RCS can either be
- typed out using SQL (select X from Y)
- encapsulated into a stored procedure (or function in postgres), and then supplied with an exec StoredProcedureName SQL statement. We will use the latter for this tutorial.
Remember that definition and relationship queries go together.
GetGroups Queries
We will first take a look at the Group queries (GetGroups is the definition query and GetGroupRelationships is the relationship query). You can find them in SQL Server Management Studio (SSMS) under Programmability > Stored Procedures.
When you examine the query, the stored procedure for GetGroups looks like the image below.
Executing the stored procedure will give us the results we see in the next image.
The contents of the stored procedure, while a good learning tool, is not as important as the results. The shape of your metadata and the queries used to select what you need can vary vastly, and the SQL RCS doesn’t care at all. What we are looking for is a retrieved dataset that contains at a minimum a MetaID and a Name.
MetaID Identifiers
The MetaID is an important concept and core to how the SQL RCS operates. MetaID is the unique and consistent identifier for an object that comes from the foreign system or metadata store. This MetaID (can be a string, guid, integer, etc.) will be used as a mapping between the source system data (MiningData) and the Visual KPI objects (guid based internally).
From the data above Visual KPI will create 10 groups. We will have a group for each truck, a group for each mine, and some rollup or navigation groups (Mines and Trucks – Groups where we will place out trucks and mines). The MetaID will be used each time the RCS runs to determine whether we have a new object, an object to update, or an object to delete.
For example, if you changed the MetaID of Mine 01 from M01 to something else, then the RCS will delete the Mine 01 group, which had a MetaID of M01, and create a new group called Mine 01 with a new MetaID. This will result in unnecessary adds and removes. Furthermore, any users who have added the previously created Mine 01 group (MetaID: M01) and added it to a dashboard will find that object no longer available next time they open the dashboard.
The bottom line is to keep each MetaID unique and consistent. A MetaID should not change between each run of the SQL query.
Now that we understand the importance of a MetaID (used by all queries), we can move to the other fields.
Other Identifiers
We can see we are returning a name, description, location data (Latitude and Longitude), as well as some other columns for things like AssetType, MineType, etc.
The columns returned by definition queries should match (with or without spaces) the name of the columns in the Visual KPI Designer. Remember when we added our custom attributes? We chose those attributes because of the metadata we had in our Mines and Trucks database.
The GetGroups query will pull that data, which will then populate those attributes in Visual KPI.
There are a lot of fields in the Visual KPI Designer for each object type (Groups, KPIs, Charts, etc) and nearly all of them can be populated by returning a column that matches via these definition queries. If we wanted to have a Display Order for our groups, we could have generated a numeric display order value and returned those in our GetGroups query. Instead of the following…
we could have the following…
Bottom line is that we need a column returned for each column that we want populated in Visual KPI Designer. Use Visual KPI Designer to decide. Once the RCS runs, you can actually load the objects in the Designer and see what has been pulled over.
I hope you now understand (at least a bit) what we are looking for in definition queries.
GetGroupRelationships Queries
Now that we understand what our GetGroups query does, let’s look at the GetGroupRelationships query.
This one is even more simple, and we will see that all relationship queries need to return two columns: ParentID and ChildID.
These will map the parent-child relationships based on the foreign systems (MiningData) MetaID. Let’s take a gander at our query, and more importantly, the results.
You will notice that the first two rows have a null for ParentID. This means that the Groups with a MetaID of M_ROOT and T_ROOT (these map to groups called Mines and Truck) will be placed right under the Visual KPI home group.
This table is just mapping the parent/child relationships for what groups belong to what other groups. The group T01 will appear under both a group with a MetaID of M01_Trucks (a group called Trucks that lives under a mine) and a group with MetaID of T_ROOT. This shows a many-to-one type of relationship. Once we run the RCS, you will see this in action; but don’t do that yet!
GetKPIs Queries
We have two more queries to cover before we turn on our new SQL remote context server.
We are missing KPIs, so let’s look at GetKPIs (definitions) and GetKPIRelationships (Group to KPI relationships).
If we open the GetKPIs stored procedure and run it, we see what appears in the image below.
It’s similar to GetGroups in that we have a MetaID and Name, but this time we have fields that mean something to KPIs like Actual and Actual Interface. Not only are we telling Visual KPI what our KPIs are, but also where to get data from (Metric data) by defining our Actual and Actual Interface pair.
Here we have also defined some limits (Min, Low, Target, High, Max) as needed, along with populating some of our custom attributes (Asset Type, Make, Model, etc).
Again, for a list of all possible things we can map, look in Visual KPI Designer and return columns that match (with or without a space). Some of the Visual KPI Designer fields have drop-down choices. You can reference not only the column name that matches, but also the values in the drop-down options.
You could select a column called DefaultKPIView with a value of Geo Map, for example.
GetKPIRelationships Queries
Our final query (which will look very familiar) is GetKPIRelationships. Running that query via SSMS gives us what appears below.
Again, relationship queries are a list of ParentID and ChildID. In the case of KPIs, the ChildID will be the MetaID of the KPI, and ParentID will be the MetaID of the Group in which the KPI will reside.
This pattern (both for definition and relationships) is the same for all Visual KPI chart visualizations and tables: one query for definitions and an associated query for relationships (parent\child).
Anyway, enough background. Let’s see something. Go ahead and add the four stored procedures to the appropriate fields in the Server Manager. Don’t forget to add the exec command. Your RCS configuration inside the Server Manager should look like this after you add the exec StoredProcedure commands.
Don’t forget to click Apply.
Step 6: Start the RCS
Ahh, the fun part is here! We can now look at the fruits of our labor.
So far, we have installed some data, installed a Visual KPI Instance, installed and configured an ODBC interface, and installed and configured a SQL RCS that will generate both Groups and KPIs. Let’s see if it works.
Right-click on the SQL RCS in the Server Manager and select Start.
Once started, the RCS will run the queries and create (or update or delete) what it was told to create via our queries. Once it has completed and a cache cycle has run (this gathers the actual process data), we should see (on the Groups tab) the following in our Visual KPI site.
Your rollup bars might look different as our data was random and created in order to enable KPI statuses to change, but you should have two groups at the Home level and more groups and KPIs under each group. Since we added location data, we will also be able to view Geo Maps.
Because we created and referenced the ODBC interface, we can also see historical data on trends. All of that from a few lines of SQL.
Go ahead and pull up the Groups and KPIs in the Visual KPI Designer. You can see everything that was created has been updated.
Because we allow the RCS to update by default, any changes you make in Visual KPI Designer will be overwritten on the next run of the SQL RCS. Remember, our master is now the MiningData database and we are just along for the ride.
You might be thinking, “Hey, I could have just typed in the handful of trucks and mines and their KPIs much quicker than writing SQL and configuring an RCS, so why would I do that?” You are correct, and for small numbers of objects it makes no sense to use an RCS. But what happens if you have 1000 mines and 50,000 trucks? What if your trucks are moving weekly or daily between mines? If you are dealing with a lot of metadata or metadata that changes, you’ll want to eliminate human error and automate via an RCS.
So, let’s not change any code we have written, but instead, let’s add some more mines and trucks.
Step 7: Add More Data
Now let’s imagine we have great insight into our new mining operation and have streamlined operations because our whole organization is using their mobile phones to monitor KPI metrics. The organization is growing and we’re going to prepare to increase the size of our mining company.
Let’s run some scripts to add the new data.
Run demoscript2.sql against our MiningData database in SSMS.
This script should take about 20 seconds to run. We are adding more Mines and Trucks along with hourly/daily metric data for all the new equipment, so be patient. This type of merger and acquisition work takes time.
After the script runs, let’s look at our data. You will notice we still have three tables (Mines, Trucks and Metrics) and we have added some more stored procedures we will use in the next couple of steps.
If we query the data we see what appears in the image below.
Ok, so maybe it’s not 1000 mines and 50,000 trucks, but we did generate a 400% increase in operational assets. Not bad for 20 seconds of work!
The tables remain the same. We’ve just added more data for the RCS to consume.
Speaking of which, if you haven’t stopped your RCS and it has been running all along, then take a quick look at your website. You guessed it; we have created new Groups for the mines and trucks, and new KPIs with the same set of definition and relationship queries we used before. That’s right. No code change required, and we still have the same insight into our operations with 10 mines and 20 trucks as we did with two mines and four trucks.
Step 8: Add Visualizations
Ok, we are now able to monitor our newly enlarged mining empire that we have modeled out with groups and KPIs. Let’s add more visualizations. Visual KPI has bar charts, managed trends, Pareto charts, Gantt charts, pie charts, XY plots, embedded content, tables and more. We can create those using the SQL remote context server.
While we won’t go through creating every one of these, we’ll pick a couple (managed trends and bar charts) and add them via the RCS.
Remember, we need two queries per object type. A definition query (think of filling in the Visual KPI Designer spreadsheet) and a relationship query (to denote in what Group or Groups the new charts will live).
During our last script execution we provided four queries: GetBarCharts, GetBarChartRelationships, GetTrends and GetTrendRelationships.
GetaBarCharts Queries
Let’s take a look at the GetBarCharts query and what happens when we run it.
This stored procedure returns a bar chart per mine and will have 10 bars (1 per mine) with the bar colored to highlight which mine you are viewing. It is important to note that we have a MetaID, Name and some bar definitions that are returned by the query.
GetBarChartRelationships Queries
Take a look at the GetBarChartRelationships query. We are returning each bar chart MetaID from the GetBarChart query as a child to the mine MetaIDs from the GetGroups query.
It’s very similar to GetKPIRelationships. The Group with the MetaID of M01 will now contain a bar chart with the MetaID: M01_BarChart.
The next two queries for trends follow the same pattern. Match Visual KPI Designer column names in the definition and supply parent-child relationships based on MetaIDs in the relationship query.
GetTrends Queries
The definition query GetTrends returns 10 trends (1 per mine) comparing Production Rate and Efficiency metrics. The point of this exercise is to understand how you go from a SQL table to a Visual KPI site.
GetTrendRelationships Queries
Finally, let’s look at our trend to groups mapping query GetTrendRelationships.
M04 is the MetaID of the Mine 4 Group and M04_Trend is the MetaID of a trend that will be created and live inside the Mine 4 group.
Let’s add these four queries to our RCS configuration in the Server Manager and wait for it to run.
Don’t forget to Click Apply, or you will be waiting a long time for those trends and bar charts to show up.
After the RCS and Cache Server finish running, you will see the following in your Visual KPI site.
Learn more about using queries to create charts via Visual KPI Designer or the RCS.
So far so good. Let’s now go for the extra credit and give your boss his much anticipated dashboards.
Step 9: Use Queries to Create Dashboards
Dashboards belong to profiles, which in turn can belong to profile groups. A dashboard is just a collection of widgets.
In order to create functional dashboards in Visual KPI sites, we need to create the following:
- At least one profile group (unless we use the default one)
- At least one profile
- One or more dashboards (which are just widget containers)
- Several widgets
These stored procedures are provided for you and are called GetProfileGroups, GetProfiles, GetDashboards and GetWidgets.
Since we have a more rigid structure of widgets to dashboards to profiles to profile groups, we don’t need the relationship queries, as the relationships are defined in each query.
To create profile groups, profiles, dashboards and widgets, use Visual KPI Server Manager.
- Select the MiningDemo RCS.
- Enter each of the four queries in the appropriate fields in the RCS.
- Click Apply.
GetProfileGroups Query
The GetProfileGroups query returns a single profile group called Mines.
GetProfiles Query
The GetProfiles query is similar to the GetProfileGroups query, except it references the MetaID of the profile group just created. It will create a profile called Mines and make it a member of the profile group with the MetaID (referred to as ProfileGroupMetaID) of the Mines profile group.
GetDashboards Query
The GetDashboards query will return 10 dashboards (1 per mine), all of which belong to the profile called Mines with the MetaID (ProfileMetaID): ProfileMines.
GetWidgets Query
Think of a widget as a visualization block. It must have a position and size, but most importantly it must point to something. If we want a KPI block, then we need to provide the MetaID (ObjectMetaID) of the KPI we want to show. Some widget types have lots of options, KPI Blocks, for example, can be told to hide their sparklines. Some widget types have no options.
The GetWidgets query will return seven widgets per dashboard (70 rows). Each widget has the following elements:
- MetaID
- DashboardMetaID (MetaID of the dashboard it will belong to)
- Positioning data (RowStart, ColumnStart)
- Sizing data (Height and Weight)
- WidgetType (a KPI Block, a trend, KPI map, Geo map, etc)
- ObjectMetaID (type of object)
- Options (such as Expand)
We can also set default profiles, lock them, and assign default ranges to dashboards–all via the RCS. You just need to include the columns as shown in Visual KPI Designer (view Profile Groups, Profiles and Dashboards in Visual KPI Designer). If you want your dashboards to be locked, just add a column (attribute) called Locked to your dashboard query and give it a value of 1 or True.
View Profiles and Dashboards
After you’ve entered each query and clicked Apply, go to your Visual KPI site in a web browser and see the new dashboards. The site will likely go to the default profile (named Default) so you will have to change that to see the new dashboards.
So let’s change the profile and look at our dashboards.
- Click the Profile icon on your Visual KPI site.
- Select Default (Change Profile).
- Use the drop-down menu to select the Mines profile group and Mines profile.
- Click Select.
Here’s your new dashboard, created via the SQL remote context server.
You can navigate to the other dashboards (remember, we automated one per Mine) using the dashboard drop-down menu.
Well done! You have created your first SQL remote context server that automates the creation of Groups, KPIs, trends, bar charts and dashboards.
If you have any questions setting up your SQL remote context server, contact us.