Custom Fields (Columns) BETA

Custom fields can be known by various names in different applications - here are just a few that I am aware of.

1. Custom Fields

2. Additional Attributes

3. Analysis Codes

4. User Definable fields\columns

5. Multicolumn attributes

6. Tags

…… I could go on.

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.

We have a product table.

This table holds information about the products that we sell. For example the Products Name, and it’s Price. It is fair to assume that these are fields that all users of the system would require. There are probably other columns as well.

So the data in our very simple example may look a little like;

I could and probably will create other columns above into my Panintelligence table, but I’m going to keep it simple here.

Now my software starts to become very popular, and I get lots of new customers (organisations / tenants). Each of these customers uses my software to mange the stock they hold. Each of them runs a business that sells different types of products. Every time I make a sale to a new customer they ask to add attributes or columns to my table to help them describe the products that they sell.

Organisation 1 Stocks and sells Fish.

Organisation 2 Stocks and sells Fridges.

🐳 The Fishmonger wants to record;

• Fish Species

• Where the fish was caught

• Whether it was netted, or line caught

👩‍🔧 The Fridgemonger wants to record;

• The energy rating.

• The fridges height, width, depth.

• It’s capacity in litres.

😭 If you keep making changes to your application every time a customer would like a new field then it quickly becomes unmanageable.

1. It’s expensive, as it requires a software developer to make a change.

2. It is slow, and customer does not to wait.

3. It makes you application complicated, the fishmonger does not want to have to trawl (Sorry for the pun!) through a list of fields about fridges to capture the vital details about fish.

So at some point you add flexibility to your application and allow a user to add their own fields. In fact you probably allow them to add new fields to multiple different tables within your application.

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.

So I create a query - imagine a table in Panintelligence, I put Product Name, Price And Attribute Value in the column list, and add atrtibute name = ‘Fish Species’ as a filter and I get this data;

I can even rename the column headers so it is easier to read

So good so far, but now I want to add in where the fish was caught,

So I add another filter item in the list to say atrtibute name in (‘Fish Species’,'How Caught') and I get this back.

This is probably not what I want, I was probably expecting

Again you could achieve this like it by creating two tables in Panintelligence and merging them.

Now say I want to create a filter for a customer because they want a list of all fish caught by line in Scotland. I’d have to again create multiple tables and use them as subqueries. Perfectly achievable in Panintelligence (and sometimes could the the right solution), but all just a little too complicated.

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 a lot of solutions end up looking like this;

In Panintelligence - you’d add the attributes table multiple times as an alias. You’d make sure that the objects are in a category so that they are only visible to the relevant organisations.

You can now use the objects together, as filters, restrictions sorts. It looks simple to the customer. It’s a good solution.

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.

You would then end up with

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;

You can use merge’s, subqueries, Linked data connections, heck you can even generate everything through the pi API and in doing so make objects automatically created without any human intervention, you hear a pre-2024.09 pi consultant 🥵 cry.

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.

1. Engineer Date Of Birth - They like to give their engineers the day off on their birthday.

2. Engineer In Probation - When a new engineer starts they have a probation period.

3. Engineer Dependants - How many dependants the engineer has. So they now that they may need more flexible shift patterns.

4. Product Colour - Not everything is white in the world of white goods.

5. Job Location Steps - Engineers hate steps, this lets them add more time to a job.

6. Notes - Engineers can take a note about the job.

7. Next Visit - Engineers can record the date of the next visit.

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.

We have 3 tables we will consider.

1. A USERS table - this holds details of the engineers, name, email etc.

2. A table name ADDITIONAL_ATTRIBUTES. This holds the definition of custom fields created within the service IQ application.

3. 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.

Essential

• The name of the attribute (Dependants, Birthday, Probation, Colour……)

• What the value is (2,16/08/1973,No,Pink……….)

• The foreign key that links it back to the core (USERS,PRODUCTS,JOBS) tables.

Good To Have

• The table it links to ie, USERS, PRODUCTS or JOBS

• The Column Type i.e. Date, String (text) or Number. …. Anything you can deal with types

• A Customer / Organisation / Tenant ID - (You can use user restrictions - but this will help perfromance)

✍️ Now what you must do;

If these things exist in a single table for you then - great. You have what you need. If like in our example above - we (pi) need you to combine these into a single view / materialized view / table. (yes you can do an inline view / but we are going to this in the database itself which I highly recomend)

In my example I am going to create a view.

I create a view V_CUSTOM_FIELDS

It contains the columns I need (I have all 6 here, the essential and the good to have)

Feel free to ignore but the SQL (MySQL) that combines the 2 Attribute tables into the view looks a little like. Feel free to ignore my SQL.

create or replaceview `v_custom_fields` as
select
    `a`.`name` as `Custom_Field_Name`,
    `a`.`attribute_type` as `Custom_field_Type`,
    `a`.`entity` as `Custom_Field_Table`,
    `v`.`Attribute_id` as `Custom_field_Link`,
    `v`.`value` as `Custom_field_value`,
    `a`.`tennant_id` as `tennant_id`
from
    (`additional_attributes` `a`
join `additional_attributes_values` `v` on(`a`.`id` = `v`.`table_row_id`));

😍 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.

All very standard so far.

Now I am going to add the user custom field for Birthday, Dependants and Probation. Normally I’d add these as three Alis’d tables. But this time I’ll add it once.

I open the metadata panel to examine the database as per normal. I find the table or view that contains my data, I then give it a nice Alias name (No spaces / No weird characters etc). Now I click on the type in the top right, and change the table type to Custom Fields.

🤔 Notice I have not selected any columns here like I normally would.

Then as usual I click generate.

Then I drag the table to where I want it (I can of course move it at anytime).

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.

Notice I can change it’s type here as well

But let’s select the Objects section

And click add object + on the right.

Ultimately it is the value that we want so I’ll check that.

And then generate.

This object view looks very similar to regular objects but there is an additional field for the Dynamic Attribute Column, and some non applicable fields are also removed.

I need two of the mandatory fields to be in my object.

So I my Example I end up with

Then we need to use the third mandatory item to join our table back to the Engineer (USERS).

For this we are going to put the custom field link in, and link it back to the id in the engineers 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 start building a table as per normal

But notice in my data connection I have a folder called Custom_field_value

If I drop this down, pi automatically creates dimensions for any custom fields it detects.

I can click on these like any other dimension and add them to my table.

They just act like any other dimension object, I can use in any combination, and indeed use alongside standard objects.

I can use them to filter, I wonder which of my engineers, have at least 1 dependant, where born after the year 2000, and are not in probation. Again custom fields are completely interchangeable with standard objects.

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.

Custom_field_value is not very user friendly. This folder name can be easily changed by changing the name of the object in the data connection.

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.

We have used the essential items, but now if we have them, we can use the ‘Good To Have Item’s. Here we want to make use of the table it links to and limit our objects to the users table.

We’ll add a table restriction in it’s WHERE clause.

You will now have the correct lists that apply to the correct table.

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!)

In this section we are going to create a new object in the UserCustomFields section, and use the 5th of or data fields to restrict.

So first I will create a new object in the UserCustomFields table in our data connection.

Make sure you give it a nice name - this will become the folder name for the custom fields.

Now treat it as a measure, changing the object type to measure, The data type in this case to be a number, then use the data object type column to restrict via the where clause to a number. Also we can set the Decimals and Separator etc. Notice that I have added a where clause now to the previous object to limit it to Strings and Dates (My decision about types).

Now I can use the objects to build my chart. The folder lists follow the colour conventions for dimensions (blue) and measures (red).

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.

And then we add the tenant ID as a user restriction (or role restriction)

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!

Yes you can.

The custom fields are displayed in exactly the same way as they are in the edit chart screen.

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.

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.

it is recommended that you apply the following indexes within your database - either on the tables directly, or the underlying tables if you are using a view.

• The name of the attribute (Dependants, Birthday, Probation, Colour……)

• What the value is (2,16/08/1973,No,Pink……….)

• The foreign key that links it back to the core (USERS,PRODUCTS,JOBS) tables.

(These are the mandatory items that we required earlier)

You may also find an advantage in partitioning your tables / data around your Tennant ID limitation.

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.