Custom fields can be known by various names in different applications - here are just a few that I am aware of.
...
However the good news is that they often follow a very similar pattern.
Why are custom fields a thing?
In our first example we have a system which sits over a database. In this database we have a main ‘core’ table. That is a table which holds data that is common to everyone who uses the system.
...
Life is great everyone is happy! except your Business Intelligence developer. 😖
Traditional Solution
How this is solved historically in Business Intelligence tools.
Pattern 1 - You just model the database as is
I Can now look at any of my objects by any value.
...
This is not a great solution if you are wanting to enable your customers to be able to self-serve.
Pattern 2 - Bespoke Elements for each organisation
You end up having to create bespoke content for each organisation. You can short cut this in Panintelligence if you want to by creating linked data connections.
...
But. We’ve now introduced the issue we had in our own applications in the first place. Every time a customer adds a field, they need to ask for it to be added to the data connection. You can make this self service and use Linked data connections to allow each organisation to do it for themselves (can be a good solution). But but but…. That doesn’t fit with your application. They don’t need to do anything there, they just add it. Now they have an additional step!
Pattern 3 - You Pivot the data in the database
You can ‘pivot’ the data within your custom table to create columns within a new table/view. A common way to achieve this is by using UNION statements.
...
As in Pattern 2, you could use categories - to show and hide columns from different organisations. This pattern has the same advantages and disadvantages as Pattern 2. It has a small advantage over Pattern 2 in that the config is simpler + it may have a significant performance advantage.
Common Flaws in the Patterns
Well it’s just the fact that they can ever be very limited (Pattern 1), or that they require manual intervention, but;
...
😇 Wouldn’t it be great if you didn’t have to do any of the above! - wouldn’t it be great if it just happened automatically. Shouldn’t a world exist where fishmongers and fridgemongers could live in a happy unison.
How to configure Custom Fields / Table
The good news is, there is a special table type that will automatically detect and create objects on the fly for your custom fields.
An Example
In this example we are going to build against the service IQ database. The Service IQ application manages teams of service workers on the road fixing ‘jobs’ and installing ‘products’. The Service IQ application allows users to add new fields to engineers, jobs, products etc. A customer (organisations / tennants) within the application called Freezing Point, has added the following custom fields. Freezing point install, maintain and fix commercial fridges and freezers.
...
These custom fields are not common to other Customer (organisations / tennants). They only have meaning to Freezing point.
The Database Structure
This database contains a set of structures called additional attributes. These can be joined to multiple tables. This may be a simpler or more complicated structure than you have, as it uses multiple tables.
...
A table ADDITIONAL_ATTRIBUTE_VALUES, this holds all of the values entered into the attributes. Here we can see entries about the engineers (USERS) number of dependants.
...
Your Structure Should Look Like
Now your structure is not going to be called additional attributes, and it probably has a different structure, but pi wants to know the following.
...
😍 Now we have what we need.
Adding a Custom Table to pi
I start by creating a normal data connection, adding tables in the standard fashion.
...
I can see that pi knows that it is a special type as it has a CF (Custom Field) in the top right corner.
...
Adding Custom Fields (Columns)
I can now click into the CF table.
...
So my data connection looks something like this.
...
Building a Chart With Custom Fields
Now all the ‘complexity’ sits in the data connection, To our chart designers, they don’t have to worry about anything that we did above.
...
I can easily use the custom fields in chart as well.Folder
...
Folder Name
You may notice that the generated folder name in our example is not very pretty.
...
Then makes the list in the chart editor look much nicer.
Restricting By Custom Field Table Mapping
In our example we have all of our custom fields listed under the User Fields. This is not correct. Colour should not be here as it relates to Products.
If we remember the objects we specified.
| Info |
|---|
Essential
Good To Have
|
...
NOTE: you may need to hit the refresh button jumping in and out and making change to the definition as we are here.
Multiple Custom Fields
Above we removed the Colour for Product. Lets add it back in linked to the product table.
...
Then my object list in the edit chart screen will look like
Using A Custom Field As a Measure
One of our object Dependants we may like to use as a measure. i.e. I want to know the total number of dependants for engineers in and out of their probabation period. (Don’t ask me why - just go with me!)
...
These objects can be used in any way you want (they just act like any other dimension or measure)
...
Making Sure Tenants Can Only See Their Own Objects
In a multi-tenanted environment we want to make sure that the fields we show are the ones allowed / relevant to the tenant.
This can be achieved using regular user restrictions
or
Restricted by variable.
User restriction limit objects, normal user restrictions apply.
...
User restrictions are observed / enforced when fetching any part of the data connection including the user fields.
Variable limit objects
Simply add the restriction referencing the variable / or variable conditions in the table where clause.
...
The variable could be on the pi user, role, organisation or global / any combination of these.
...
So where Can I use Custom Fields In Charts / Tables Etc.
I can use / manipulate custom fields in;
As Data points
In Filters
In Sorts
Additional Data
Colours
Drill Paths - Standard
Not Yet Dynamic drill paths
{{Variable}} replacements. ({{Employees+cf=gender}})
Chart Types - All
Custom Fields In Category Objects
So you want to filter dashboards by custom fields don’t you - of course you do!
...
And they then operate as any other category object. You add default values / [[VARIABLE]] values as you would with any other category objects.
...
Temporary Category Objects
...
The custom fields are added as the are in any other part of the application, so users can just add them as they require.
Passing Custom fields through the URL for external filtering
Custom fields can be controlled through the URL as per any other category object, the syntax is
[[User Fields+cf=Probation]]{{Yes}}. We use the same syntax in the URL as we do in {{object}} replacements using +cf to indicate this is a customer field.
So…
[[Object Name+cf=Custom Field Name]]{{Value}}
...
Custom Fields In A User Restriction
Any custom fields can be used in a user restriction. Add the object as per normal user restrictions.
...
And add the value - This could be a [[VARIABLE]] if required.
Considerations For Perfromance
Custom fields creates on the fly in line selects with the query from clause. This is a convenient way of adding dynamic data items, but could create an overhead.
| Code Block |
|---|
SELECT
Engineers.role AS TC4,
UserCustomFields_U1988n.Custom_field_value AS TC1,
CONCAT(Engineers.first,' ', Engineers.last) AS TC0,
SUM(UserCustomFields_U2314s.Custom_field_value) AS TC3,
UserCustomFields_U1869y.Custom_field_value AS TC2,
UserCustomFields_U1988n.Custom_field_value AS TC5
FROM
service_iq.users Engineers
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Probation'
and UserCustomFields.Custom_field_Type In ('String','Date')) UserCustomFields_U1988n ON Engineers.id = UserCustomFields_U1988n.Custom_field_Link
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Dependants'
and UserCustomFields.Custom_field_Type = 'Number') UserCustomFields_U2314s ON Engineers.id = UserCustomFields_U2314s.Custom_field_Link
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Birthday'
and UserCustomFields.Custom_field_Type In ('String','Date')) UserCustomFields_U1869y ON Engineers.id = UserCustomFields_U1869y.Custom_field_Link
WHERE
( UserCustomFields_U1988n.Custom_field_value IN ( 'Yes' ) )
GROUP BY
CONCAT(Engineers.first,' ', Engineers.last),
UserCustomFields_U1988n.Custom_field_value,
UserCustomFields_U1869y.Custom_field_value,
Engineers.role |
Indexing
In our own testing we have run items from tables containing 100k + rows.
...
| Info |
|---|
You will need to experiment on your database - and tweak for perfromance. It could be the case that using composite indexes including multiple columns could provide the most optimal queries for you. |
Number of objects
As every object creates an additional inline SELECT statement, if a query where to contain as large volume of these selects then this could also have a impact on query perfromance. At very high volume you may still need to consider instantiating the data into a normalised Data Warehouse structure.
Additional Notes
When creating a custom table you will notice that the table alias get enclosed in `backticks` regardless of the database types. Please leave these in place. Panintelligence uses these backticks as a reference point for table alias substitution.
...