Custom Cube Changes - Multiple Responses and Field Types in Analytics

Contents

What’s changing?

This guide explains changes to Custom Event cubes that affect how multiple responses and different field types are processed in Analytics.

To support multiple responses in Analytics while reducing custom cube size and processing times, all form-field values in Custom cubes are being stored as text. This includes multiselect fields, fields within item collections, fields from supporting forms added more than once to an event, and fields used within repeating workflows.

Instead of creating a separate row for every response, multiple responses are stored together as comma-separated values and can then be expanded visually in Analytics. This reduces the number of rows generated while still allowing each response to be analysed separately.

Existing dashboard widgets and filters that use multiselect fields or fields within item collections will need to be updated. The following sections explain how to use these fields and what to do if a field in Fact_Form_CustomEventsCube requires conversion to another data type.

A demonstration of the dashboard changes is included below; the recording explains the expected impact during rollout and shows how to add these fields to a widget.

How multiselect and item collection fields are processed in Analytics

Multiselect fields and fields within item collections can contain multiple responses for a single event.

Each selected response can create a separate row. When a custom cube contains multiple multiselect fields or fields within item collections, these responses can multiply together, causing the total number of rows to grow rapidly. This increases the size of the custom cube and can significantly affect processing times. For example, 100,000 events could generate 8 million rows.

To prevent the number of rows from multiplying in this way, multiple responses are now stored as comma-separated values in a single row. They can still be expanded visually in Analytics when needed, allowing each response to be analysed separately while reducing the amount of data that needs to be processed.

This makes it easier to:

• report on each selected option;
• group results accurately in charts and tables;
• filter dashboards by an individual response;
• use more than one multiselect field from the same form; and
• report on fields within item collections.

Example: If a multiselect field asks for the location of a fall and three options are selected in the same event - Bathroom, Patient Bedroom, and Communal Space - the previous method would generate three rows:

When multiple multiselect fields are used in the same event, the responses can multiply together. For example, one field with three selected options and another with five selected options would generate 15 rows for that event.

Under the new method, the responses are stored in a single row before being expanded in Analytics:

The responses can then be expanded visually in Analytics so that each selected value can be analysed separately. For instructions, see Adding multiselect fields and fields within item collections to a dashboard.

Adding multiselect fields and fields within item collections to a dashboard

The following example uses a multiselect field called Request Category. The same approach applies to other multiselect fields and fields within item collections.

Because multiple responses are now stored as comma-separated values, existing widgets will need to be updated as follows. These steps are also demonstrated from 03:21 in the recording.

1. Add a FieldDisplayName widget filter
   • Open the dashboard containing the widget and select Edit.
   • Open Filters > Widget Filters.
   • Select Add +.
   • Search for and select FieldDisplayName from the Key Pair table: View_Fact_EventCustomCubeKeyValueResponses.
   • Select the name of the field you want the widget to report on, such as Request Category.
   • Apply the filter.

2. Add the Response field to Categories

   • In Categories, select Add +.

   • Search for and select Response from the Key Pair table: View_Fact_EventCustomCubeKeyValueResponses.

   • The Response field contains the individual responses that can be analysed by the widget.

   • Apply the changes.

3. Remove the original field

   • If you are updating an existing widget, remove the original version of the field, such as Request Category, if it is already being used in Categories.

   • The widget may temporarily display aggregated results until the replacement field and filter have been added (steps 1 and 2).

4. Review dashboard filters

   • Review any dashboard filters that use a multiselect field or a field within an item collection.

   • Add or edit the filter and select Contains.

   • This ensures that records are returned when the selected response appears within a comma-separated list.

   • Apply the filter and confirm that the expected records and categories are displayed.

Adding DATE, INT, FLOAT, BIT and TIME fields to a dashboard

The following instructions explain how to add fields with DATE, INT, FLOAT, BIT or TIME values to a dashboard widget.

Fields in  View_Fact_EventCustomCubeKeyValueResponses

Fields in View_Fact_EventCustomCubeKeyValueResponses that can contain multiple responses have type-specific response columns. These can be added directly to dashboard widgets without manually converting the field type in Sisense.

1. Add a FieldDisplayName widget filter
   • Open the dashboard containing the widget and select Edit.
   • Open Filters > Widget Filters.
   • Select Add +.
   • Search for and select FieldDisplayName from the Key Pair table: View_Fact_EventCustomCubeKeyValueResponses.
   • Select the name of the field you want the widget to report on, such as Date of Request.
   • Apply the filter.

2. Add the relevant response field to Categories
   • In Categories, select Add +.
   • Search for and select the response field that matches the field type:

   • Field type	Response field
     Date	ResponseDATE
     Integer	ResponseINT
     Decimal	ResponseFLOAT
     Boolean	ResponseBIT
     Time	ResponseTIME

   • These fields contain the individual responses for the selected field type. For example, ResponseDATE contains responses for fields whose type is DATE.
   • Apply the changes.
   • If you are updating an existing widget, remove the original version of the field after adding the replacement response field.

Fields in Fact_Form_CustomEventsCube

Fields in Fact_Form_CustomEventsCube are stored as text. If a field needs to be converted to DATE, INT, FLOAT, BIT or TIME for use in a dashboard, please contact Radar Healthcare Support. We can advise on the required change.

FAQs

During rollout, what will happen to existing dashboards?

Who will update existing dashboard?

Do all widgets need to be changed?

Why are multiple responses stored as comma-separated values?

Why should I use the Contains filter?

Need help?

Please contact Radar Healthcare Support if you have questions about these changes or need assistance creating or maintaining an Analytics dashboard.