How to build 3D dashboards with KensoBI

Guide Dec 15, 2020

A KensoBI 3D dashboard is a single page interactive report that tells the story of data through visualizations. It usually represents critical performance indicators and relevant business data accompanied by a 3D model.

In this guide, you will learn how to create a simple 3D dashboard to monitor the manufacturing inspection process.

Here are the tools we are going to use:

Any SQL database. We will use free and open-source PostgreSQL.
Database schema from Q-DAS - popular statistical software. You can download SQL scripts for the schema from Q-DAS’ website.
KensoBI. Sign up for your free cloud account at https://cloud.kensobi.com/signup.

Create new dashboard with CAD panel

In KensoBI, click on the + sign, then Choose Visualization, and then double-click on CAD panel.

In the CAD section, press the Add button and then paste in the path to your STL or 3MF file. You can link the model stored in your own cloud storage or use a professional version and upload it directly to KensoBI. You can load as many models as you need, but try to keep your models below 10 MBs for the best user experience. Larger files will take longer to load, and on old devices, the browser may appear slow or unresponsive.

Next, exit the editor, resize the panel, and adjust your model. Use the mouse’s left button to rotate, the right button to move it, and the scroll wheel to zoom in or out. If you use a touchscreen device, you can pinch-zoom and move it around with your fingers. This is an excellent time to stop and save your work.

Add features and characteristics

Let’s go back to the panel editor. Click on the panel’s header, then select edit. Click on the Queries tab if you not already there. Query editor allows us to communicate with data sources and get data for visualizations. Depending on your data source, this editor may look a little different.

You can use the query builder to build your request or switch to the raw mode and start typing raw SQL query.

KensoBI can construct a feature from the dataset in two formats - feature per row and characteristic per row.

Feature per row - where each characteristic has its own column.

SELECT feature, x, y, z FROM features

Characteristic per row - feature is constructed from characteristics in rows by grouping them by feature name.

SELECT feature, control, value FROM characteristics

Default Q-DAS schema does not come with a feature table to link characteristics. You can either create it yourself or come up with some clever naming structure for your characteristics. This exactly what we are going to do. Our characteristic name will consist of a feature name and control in the following format:

featureName[control] i.e. TX9019[x]

We will use substring functions available in Postgres to derive feature name and characteristic control.

Here is the full query:

SELECT 
	substring(memerknr from '(.*)\[') as feature,
	substring(memerknr from '\[(.*)\]') as control,
	Menennmas as value
FROM
	merkmal 
WHERE
	meteil = 1

We expect our result to be in a table format, not time series, so select TABLE format in the dropdown below the input box. Enter the query in query window and click away. It will trigger a database call and features should show up on our model.

Remember, at the very minimum, we need x,y,z characteristics to construct a feature and draw it in the 3D space.

Feature labels and balloons

Our features are now displayed correctly in 3D space. You can click on any feature to see its label. You can show all labels by going to the panel menu -> Balloons -> Open all.

You can also pin balloons by clicking on them. Move pinned balloons by dragging the balloon’s header and place them around the model.

Templates

KensoBI comes with a predefined balloon template for basic feature types. If we provide feature type (feature type column) in our query, each feature balloon will be formatted according to its template.

Templates can have more than one view. Let’s modify the “Point” template and add LCL and UCL values to our table view. Before we can add a new column, we need to include it in our query.

SELECT 
	substring(memerknr from '(.*)\[') as feature,
	substring(memerknr from '\[(.*)\]') as control,
	Menennmas as value,
	(Menennmas + Meugw) as lsl, 
    (Menennmas + Meogw) as usl,
	'point' as featuretype
FROM
	merkmal 
WHERE
	meteil = 1

Now, go back to balloon editing. Change columns from 2 to 4. Click on the column header and type LSL. Next, click on the cell in X row. Select “lsl” and provide control value – X. Repeat for Y and Z coordinates.

You can see values changing in our balloons. Do the same for USL. Exit the editor and adjust balloons.

Show measurements in feature balloons

Go to Query Editor and click on the Add Query button. In our query, we need to provide time, value, and a metric name. The metric name needs to be in a specific format: feature[space]control .

SELECT 
	concat(substring(memerknr from '(.*)\['),
    	' ',
        substring(memerknr from '\[(.*)\]'))
    as characteristic,
    WVDATZEIT as "time",
    WVWERT as "value"
FROM
	WERTEVAR, merkmal
WHERE 
	WVMERKMAL = merkmal.MEMERKMAL
    AND wvteil = 1 
    AND $__timeFilter(WVDATZEIT)  
ORDER BY
	WVDATZEIT ASC

Notice $__timeFilter function. It is a built-in function to automatically take the current dashboard’s time range and add it to the query.

Again, our query is a bit convoluted because we are embedding feature name and control in our characteristic name. Anyhow, let’s add chart views. Go to the Visualization tab, select Point template, and create a new view by clicking the +Add button in the Balloon Views section. Set name to X, view type to chart, and control to X. Duplicate this view for Y and Z and by clicking the copy button and set the control to Y and Z.

Click on the balloon header and select Y. You should now see the chart with the latest measurements. We can make our chart look a little better by modifying chart settings. In draw modes, disable points, set fill to 0 and line width to 2.