To create a new Bespoke Demographic for a company survey, your schema change to do this should contain a number of pre-defined steps. Whilst every Bespoke Demographic may be slightly different from the next, the following steps should form a basis for your schema change. A basic template script has been attached, with each of the numbered steps in the script referring to the numbered steps below:
- Define all the variables ready to use for the rest of the script.
- Create a table with all of the employees who are going to be in this new demographic, along with the answer they should be given. In the script, the employees' Unique Identifier/Payroll Number has been used, but you could use something like their Email Address instead.
- Create the new demographic in the Ref_Questions table.
However, this may not always be necessary. For example, if the company used the same named bespoke demographic in a previous survey, you could replace the insert with a select statement to get the existing QuestionID from the database.
- Create the new answers for the demographic, based on the values you define for each of the employees. Again, if the answers already exist, there is no point in creating them again.
- Connect the possible answers up to the questions. If you are reusing a previous demographic, there would be no need to do this.
- Create the new demographic group. If you are reusing a previous demographic, there would be no need to do this.
- Create the new survey demographic group, which links the demographic group created in step 6 to the survey.
- Create the new survey demographic, which links the demographic created in step 3 to the survey.
- Assign the new survey demographic and survey demographic group to the company survey.
- Add employees 'Local' Question and Answers. This will help to collate all the new values required in a local table variable to create the actual answers in the next step.
- Insert the answers for each employee into the Dat_Survey_Demographic_Answer table.
- Assign a new WIT feature to the company survey to allow it to be able to access the new demographic in WIT.
- Don't forget to request a WIT refresh in Default3 to make the demographic available in new WIT (this step is not part of the script).
To test that your new demographic has been created successfully:
- Rebuild your WIT cube.
- Log into WIT as the company you have created the demographic for.
- Click on Demographic Responses.
- Click on the Demographic Split tab near the bottom of the page and then click on the Add/Remove button.
- Select the new demographic from the list and click on Generate.
- The chart displayed on the screen should be Overall by <Your new demographic>.
If the chart is displayed correctly, then you have successfully created the new demographic.