Table Configuration

Owned by Karen Hogan (Unlicensed)
Last updated: Sept 07, 2022
4 min read

For a given table/ view we can specify parameters than apply to the entire table by clicking on the grey header bar.

If the ‘Details’ tab is selected, you will see:

Table Name - this is the name of the table (or in this case view) that you are connected to. This can be changed for example, if you had originally pointed to a table but now wanted to point to a view holding similar data instead by clicking the down-arrow on the right-hand side.

Table Alias - this is the alias applied to ‘Table Name’. This cannot be changed at this stage because it has been copied down to all the fields/ columns in this table. You can only change it when the object is first created, specifically the ‘as’ in the example below becomes the alias.

In the following example, the ‘as’ name has been changed before the table is generated.

Once the table has been created we will be able to see this name in the Table Alias field.

There are two main reasons for using an alias:

  • Brevity / Ease of Use

  • Wanting to use the same table/ view in the same data connection more than once. Commonly this situation arises for parameter tables like AccountingPeriods or for functionality tables such as the view PreCannedDates which we often use to derive ‘Last Week’ or ‘Last Year’ for example.

Table Priority - defaults to 999 and you rarely need to change this. This is used when there is more than one way for piDashboard to join tables together given the metadata you have configured, a situation best avoided on the whole. Consider for example a theoretical layout similar to the one shown below.

Customers are joined to Jobs and Regions. Jobs are also joined to Regions. If you build a chart or data table using only objects from ‘Customers’ and ‘Areas’ then there are two routes the SQL could take;

  • Customers -> Jobs -> Regions -> Areas

Or

  • Customers -> Regions -> Areas

piDashboard will take the route of least resistance based on the Table Priority you have specified. If Jobs remains at 999 and Regions is set to 800 then it will go Customers -> Regions -> Areas. On the other hand if Jobs has been set to 750 and Regions is 999 it will take the Customers -> Jobs -> Regions -> Areas route.

As stated, we’d rather this situation didn’t arise in the first place, it might even be better to use the ‘Table Alias’ functionality, mentioned earlier, to have two Regions and Areas tables.

This same functionality can also be used to force the ‘driving’ table, if a table has a lower number the SQL will drive off that table first, i.e., it will be placed first in the FROM clause.

If you experience any issues with how your data is displaying, we advise checking that table priorities are not incrementing. By default, they should all be set to 999 and that means that one table will take priority over any of the others.

Where Clause: This is an overarching WHERE clause that will be added to all SQL generated using this table. For example, if we don’t want to see any records where No Fraud has been found we could enter something like this.

This means that we don’t need to remember to filter every single chart to only show Fraud cases.