Your data may contain multi-response fields. These are common in survey data, where a respondent may give multiple answers to a single question. For example:
Q3: How do you usually travel to work (please tick all that apply)? [ ] Walk [ ] Bus [ ] Car [ ] Train [ ] Bike [ ] Tram [ ] Other
To model this type of data in SuperCHANNEL, your source data must have a fact table for each multi-response question, and a classification table for all of the possible responses.
The multi-response table needs to be linked back to the primary key of the main fact table (so that SuperCHANNEL can associate each response with the relevant row in the main fact table) and must also link to a multi-response classification table that contains the descriptions for each response.
For example, the above question might be modelled in the source data as follows:
In this example we can see that James walks and catches the bus, while David only travels by bus.
To model this data in SuperCHANNEL, do the following:
- Add the main fact table and the multi-response table as fact tables:
- Add the multi-response classification table to the classifications:
In the Target View, select the multi-response fact table and then select the Multi-Response check box in the Target Attributes pane:
This ensures that the table is treated internally as a multi-reponse fact table. If you do not select this option then the multi-response table will be treated as a standard fact table and will appear as a summation option in SuperCROSS and SuperWEB2.
- Create a reference from the ID column of the multi-response table to the ID column of the main fact table:
- Create a reference between the multi-response table and the multi-response classification table:
You can now repeat these steps for any additional multi-response tables, as well as defining the references for your other classified fields as normal, then build your dataset.
The multi-response data can then be viewed in SuperWEB2 and SuperCROSS as follows.
As you can see from this example, the totals show the total number of employees (20 in this case, 7 or whom are female and 13 are male), and there is no double counting of employees in the totals even though each individual employee may have ticked multiple transport options.