With the SAFEQ6 data inserted into a SQLite database and then aggregated by the datafeeds, we can perform our last step of data manipulation inside of the datasets.
What sets datasets apart is their ability to perform additional calculations and formatting that a normal SQL query may not be able to recreate easily and seamlessly.
We’ll focus today on just the “User Data - 12 Months " dataset as the topics today will apply to the other seven, but after this module we’d implore you to look at them as well. You may have also noticed that each of the naming conventions for the datasets follows the datafeed naming conventions. It is possible to have multiple datasets capturing data from a single feed, but for ease, we have it on a 1-1 basis due to our needs for the Print Management solution, and maintainability.
Going back to the dataset, however, we can split this into three main areas. Our attributes in blue, measures in red / orange and pivots in green.
Starting with the attributes, these are the points of data we want to measure the costs and volumes against.
We can sum up the attributes themselves into three main sub-groups, date formats, null values and flags. The date formats take the raw timestamp of a DD-MM-YYYY hh:mm:ss, and with the various Display Types, formats it into different types, such as D-M-Y to remove the timestamp, and M-Y for when we wish to show data monthly.
You may want to explore the other date display types here too. You can aggregate by quarters, be it calendar or financial, along with month names, date differences and so on. There are other articles on our knowledge base that delve into that in more detail.
Attributes in datasets also allow you to handle NULL or empty fields. In some cases, the NULL fields are by design where we want no data to come through, so the dataset can redefine it in ways the components can use it to drill further into data. One example of this is the “Show All Users” field, where all NULLed data is redefined as “Show All Users”.
The “Show All Users” and “Show All” attributes ultimately stem from the same field inside of the datafeed, “NULL_PARAMETER”. Datasets allow us to bring in the same field from the datafeed multiple times, as we may need to redefine that data into many different ways, such as totals and percentages, but we’ll cover that in a moment under the attributes section.
One area where we don’t want to see blank fields is really for the rest of the attributes. In an ideal world, a customers’ SAFEQ6 tenant should be fully configured, with devices and users’ fully set up and up to date. However, in some instances the dashboards can highlight where that is not the case. When we receive unintentional NULL / empty fields for the OU / Cost Centre, Device Name, Serial Number and so on, we display them as <unknown>.
This recategorises the blank fields to have the exact same name, <unknown>. This allows you or the customer to analyse the data later in the dashboards and filter by this parameter to help you better sense check the SAFEQ6 data.
Attributes are also what we use for security filtering. When setting up users to have access to the dashboards, they can be given the exact same dashboards as one and another but have user or group security set on them to have an unremovable pre-filter to the dashboards, so they can only see data pertinent to that attribute. For example, if you’re given the requirement that a head of Sales should only see Sales related data, you can use the OU_Name attribute to build a security filter, so they only see their own Department.
For that process to be facilitated, you’d need to edit each of the datasets, expand the filter icon at the top and add a security type to the attribute you wish to filter on. This gives the software the logic needed to fulfil the security requirements, if it knows that the dataset security equals the department, and the users’ security is a string value that exists in that attribute.
In a standard implementation of Intuitive SAFEQ6 in default settings, no security has been set up ahead of time. This will either need to be changed post-implementation or agreed as part of your customers’ installation plan.
With the attributes out of the way, we’ll focus on the measures.
Starting with our volumes, let’s take a look at “Total Pages” and “Color Pages”. If you take a look at either one, the expression we use is a sum of the associated feed volume, as we largely need to work out a sum for that attribute or combination of attributes. Total Sum’s are typically used for overall percentages, ratios or an overall sum.
Another piece of analysis we show on most dashboards surrounds the counts around a device estate and number of users. This is performed by running a count distinct on attributes such as the ID of the printed job, and the device’s serial number. These are figures which will be unique, and they will be ID’s that shouldn’t change over time. Examples of this are the “No. Users” and “No. Printers” measures.
For example, we wouldn’t want to build a count on the device ID, as the device could be removed and readded to SAFEQ6 and may be recognised as a new printer inside of that software. Therefore, we count that device based on the serial number, as that should be static throughout the devices’ lifetime.
Scanning across to “Color %”, this is one of the percentages we calculate from the colour page volumes against the total page volumes. Looking at the expression for this calculation, this is a standard percentage calculation of one figure, represented against a total figure, multiplied by 100 to find the percentage.
In this case, we’re not using a total sum as this percentage will be quite flexible for working out the colour percentage in combination with the other attributes in that grid component. If we introduced a total sum here, we’d be working out the colour percentage overall to the organisation, and that figure would be static, regardless of the other attributes in that grid.
Before we move on, percentages in our datasets also have a symbol at the end / a post-fix. In the case of percentages, we can still work out the figure in the expression but add a ‘%’ symbol to the end of any time this calculation appears in components. The same is also true of costs which we’ll cover after this, where we can place a currency notation before the figure.
Moving on, if you recall the previous modules, we have introduced a costs table into the Intuitive for SAFEQ6 solution which contains a single line of cost-associated metrics for us to use inside of the dashboards. The datafeeds take those cost metrics, or coefficients, and calculate the overall cost for that particular job. When that comes through to the dataset, we just need to perform a simple sum, prefix the calculation with a currency symbol, and they’re ready to be used by our components.
Averages are also used in the dataset, and one example is the “AVG Page per Printer” measure. This is an overall average, so takes the total sum of all pages, and divides it by a total count distinct (the overall number of unique objects) of the devices in the estate.
With the attributes and the measures covered, there is only one pivot that we use across this solution. "Activity by Pages” is a pivot of the job types we receive from SAFEQ6 (print, copy etc) by a sum of the total pages. This is used by the grid components, typically on the Executive Summary to show line by line the volumes associated to the job types, but against attributes such as the User Name or the Device Name.
Outside of this module, go through each of the dataset columns and see how each one has been performed, and also how they link to other datasets. Another area of interest is familiarising yourself with how the dataset matches attributes / measures to the datafeeds, and which columns they are using.