> ## Content Index
> Fetch the complete content index at: https://blog-test.kensobi.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to build a 3D SPC dashboard with KensoBI
- URL: https://blog-test.kensobi.com/how-to-build-a-3d-spc-dashboard-with-kensobi/
- Published: 2021-02-09T01:43:36.000Z
- Updated: 2026-08-10T18:16:50.000Z
- Description: Learn how to create a 3D SPC dashboard to monitor continuous manufacturing process. You will see how to calculate common SPC indicators using SQL and how to customize balloon’s views to create a unique experience for your users.
- Author: Tomasz Czerkas
- Tags: Dashboard, Guide, KensoBI, QDAS, SPC, #Import 2026-09-12 00:57

Mean monitoring or control charting in Statistical Process Control (SPC) is used to monitor trends or shifts in the manufacturing process. It helps to understand anomalies and define the typical production process variation (natural process variation or common cause).

In this guide, you will learn how to create an SPC dashboard to monitor the continuous manufacturing process. You will see how to calculate common SPC indicators using SQL and how to customize balloon’s views to create a unique experience for your users.

Tools needed:

- Any SQL database. We will use free and open-source [PostgreSQL](https://www.postgresql.org/?ref=blog-test.kensobi.com).
- Database schema from [Q-DAS](https://www.q-das.com/?ref=blog-test.kensobi.com) \- popular statistical software. You can download [SQL scripts](https://www.q-das.com/fileadmin/mediamanager/Software/Software-Version12/AdditionalFiles/SQL-Scripts.zip?ref=blog-test.kensobi.com) for the schema from Q-DAS’ [website](https://www.q-das.com/en/service/software-downloads?ref=blog-test.kensobi.com#tab1649).
- [KensoBI](https://kensobi.com/?ref=blog-test.kensobi.com). Sign up for your free cloud account at [https://cloud.kensobi.com/signup](https://cloud.kensobi.com/signup?ref=blog-test.kensobi.com).
- [Download](https://demo.kensobi.com/d/z67hEPJGk/?ref=blog-test.kensobi.com) and import dashboard from the previous [article](https://blog-test.kensobi.com/how-to-build-3d-dashboards-with-kensobi/).

We will be extending the dashboard we build in our previous blog post. If you already have it, open it, go to dashboard settings and click *Save As* to save it under a different name.

**Note**: when you re-import the same dashboard, you will be required to change UUID - just add or remove a letter to make it unique.

![](https://blog-test.kensobi.com/content/images/2020/12/import_sm.gif)

### SPC calculations in SQL

We need to expand our existing database query to calculate some common SPC indicators. Navigate to the Queries tab and replace query A with the following:

```sql
--calculate SPC indicators
SELECT 
	substring(memerknr from '(.*)\[') as feature,
	substring(memerknr from '\[(.*)\]') as control,
	'point' as featuretype,
	Menennmas as value,
	Meugw as lsl, 
  	Meogw as usl,
	avg(sampleMean) as grandMean, 
	max(sampleMean)-min(sampleMean) as range,  
	min(sampleMean) as min,
	max(sampleMean) as max,	
	stddev_pop(sampleMean) as stdDev,
	avg(sampleMean) + (0.577 * (max(sampleMean)-min(sampleMean))) as ucl,
	avg(sampleMean) - (0.577 * (max(sampleMean)-min(sampleMean))) as lcl,
	(Meogw - Meugw)/(6*stddev_pop(sampleMean)) as cp,
	LEAST (((Menennmas + Meogw) - avg(sampleMean))/(3*stddev_pop(sampleMean)),
		   (avg(sampleMean) - (Menennmas + Meugw))/(3*stddev_pop(sampleMean))) as cpk
FROM(
	--3) calculate sample mean for each characteristic 
	SELECT 
		characteristicId,
		avg(measurementValue) as sampleMean, 
		sampleNumber
	FROM(			
		-- 2) group measurements for each characteristic in samples size of 5
		SELECT
			w.wvmerkmal as characteristicId, 
			w.wvwert measurementValue, 			
			wvdatzeit as measurementDate, 
			(row_number() OVER (PARTITION BY wvmerkmal 
            					ORDER BY wvmerkmal, 
                                wvdatzeit DESC) - 1) / 5 AS sampleNumber 
		FROM 
			wertevar w
		WHERE
			w.wvteil = 1 
			-- 1) filter only X,Y and Z characteristics for part 1
			AND w.wvmerkmal in (SELECT memerkmal FROM merkmal WHERE 
            					meteil = 1 
								AND memerknr like '%[x]' 
                                OR memerknr like '%[y]' 
                                OR memerknr like '%[z]')
			AND $__timeFilter(wvdatzeit)					
	) means
	GROUP BY 
		sampleNumber,
		characteristicId
) sampleMeans, merkmal characteristic
WHERE
	sampleMeans.characteristicId = characteristic.memerkmal
GROUP BY 
	 memerknr, Menennmas, Meugw, Meogw
```

Ok, this might look wild at first glance. No worries. Let’s go over what is happening here.

1. This is a simple performance trick to filter this massive measurement table quickly. In this example, we will focus only on in X, Y, and Z characteristics of part with ID=1.
2. This statement filters measurements and groups them into samples of size 5.
3. Calculate the sample mean for each characteristic
4. Calculate SPC indicators:
- Grand mean
- Range
- Minimum
- Maximum
- Standard Deviation
- Lower Control Limit (LCL)
- Upper Control Limit (UCL)
- Process Capability (Cp)
- Process Capability Index (Cpk)

### Balloon chart data

Our balloon charts should display sample means instead of raw measurement values. Let’s reuse query A. We only need a sample mean for each characteristic so remove all other calculations. Paste in following SQL in query B and change *Format As* dropdown to table.

```sql

SELECT 	
	substring(memerknr from '(.*)\[') as feature,
	substring(memerknr from '\[(.*)\]') as control,
	sampleMean,
	sampleNumber +1 as sampleNumber
FROM(
	--calculate mean of each sample
	SELECT 
		characteristicId,
		avg(measurementValue) as sampleMean, 
		sampleNumber
	FROM(			
		-- group measurements for each characteristic in samples size of 5
		SELECT
			w.wvmerkmal as characteristicId, 
			w.wvwert measurementValue, 			
			wvdatzeit as measurementDate, 
			(row_number() OVER (PARTITION BY wvmerkmal 
            					ORDER BY wvmerkmal, 
                                wvdatzeit DESC) - 1) / 5 AS sampleNumber 
		FROM 
			wertevar w
		WHERE
			w.wvteil = 1 
			-- 1) filter only X,Y and Z characteristics for part 1
			AND w.wvmerkmal in (SELECT memerkmal FROM merkmal WHERE 
            					meteil = 1 
								AND memerknr like '%[x]' 
                                OR memerknr like '%[y]' 
                                OR memerknr like '%[z]')
			AND $__timeFilter(wvdatzeit)
	) means
	GROUP BY 
		sampleNumber,
		characteristicId
) sampleMeans, merkmal characteristic
WHERE
	sampleMeans.characteristicId = characteristic.MEMERKMAL

```

### View groups

Switch to the Visualization tab, select *Point* template in *Balloon Settings*, scroll down to *Balloon Views,* and click on *Add* button to add a new view. Enter the following settings:

![](https://blog-test.kensobi.com/content/images/2020/12/X-top.png)

First column with value X should be set to static value. Set other cells to type *Number* and *decimals* 2.

Create another table view called X bottom:

![](https://blog-test.kensobi.com/content/images/2020/12/X-bottom-1.png)

And another one:

![](https://blog-test.kensobi.com/content/images/2020/12/X-group.png)

Switch to X view. Scroll down to Graph Axes section and change *decimals* to 2 in *Left Y* settings. Next, in X-Axis change:

**Mode**: Data series  
**X column**: sampleNumber  
**Y column**: sampleMean

In Graph Thresholds, add lines for grand mean, UCL and LCL.

![](https://blog-test.kensobi.com/content/images/2020/12/thresholds.png)

Exit edit mode and resize balloon with X characteristic, so both tables and chart are visible. We made a lot of changes, so let’s save the current dashboard.

### Conditional styling

A proper monitoring dashboard has to include color highlighting to spot indicators requiring attention quickly. KensoBI can dynamically change balloon colors, and individual table cells based on values returned from the database.

We want to turn balloon, label, and 3D feature red when Cp and Cpk for X, Y, or Z go below our limit. To do this, go back to editor, select Point template, and press the +Add Color Mapping button.

Add mapping for each control and characteristic.

![](https://blog-test.kensobi.com/content/images/2020/12/cond-styling.png)

The last step is to add conditional formatting to individual values in the balloon table. Scroll down to the Balloon Views section and then select X bottom view. Click on the Cp column, then Add Style button. Change = to < and set value to the desired limit. Repeat for Cpk and standard deviation.

Now, let’s duplicate it for Y and Z characteristics. Select each view, press the green clone button and change the control value.

Congratulations! Your SPC dashboard is ready!

![](https://blog-test.kensobi.com/content/images/2020/12/final.png)

You can download the latest version of this dashboard from our demo instance. Send us a [message](https://kensobi.com/contact?ref=blog-test.kensobi.com) if you need an account.