When using multiple queries or universes it is helpful to merge dimensions so that the objects from the different queries or universes can be used in the same tables within the report.
Merged dimensions create a merged object from the shared objects within the multiple queries. This object allows for "communication" between the two queries so that the objects can be used in the same table. The best fields to use for this are identifiers: Participant Site ID, Enterprise Site ID, etc. The data in these fields must match exactly in order to be merged properly.
For example: using a name field as a merged dimension is not a good idea as two or more participants could share a name.
Common examples for creating a merged dimension include:
- Finding the name of a program that a TouchPoint was completed in
- Combining demographic information and TouchPoint data
- To include Entity details when accessing Entity Cross Reference responses from TouchPoint data
- When adding demographics or Program Enrollment to a report based on the Standard Touchpoint Universe NEW
First, you will want to identify the objects that the two queries share in common. Here are some examples.
The process of putting objects from two or more queries in the same table has two major steps:
- Merging the Objects
- Creating the detail(s)
STEP 1 - Create a Merged Dimension
In order to create a merged dimension in ETO Results, follow these steps:
- Navigate to the Data Access tab of the toolbar at the top of the screen.
- Under the Data Objects sub-tab, select Merge.
3. Select objects from individual queries while holding the CTRL key.
4. Click OK when complete.
5. The merged dimension will have now been created and will be visible in the Merged Dimensions folder in the Available Objects window.
Best practice tip: Change the name by right clicking and selecting Edit Properties. This will help you easily identify the correct object when creating details or when using the merged object in a variable.
Adding an Object to an Existing Merge
If you want to add a new object to an existing Merged Dimension, follow these steps:
- Under the Available Objects window, click the new object and the Merged Dimension while holding the CTRL key.
- Right click on the new object, Click Add to Merge. Under the Merged Dimension, it should show the previous objects that were merged, as well as the new object.
Using Merged Dimensions
STEP 2 - Create a Detail
Details often provide a bridge between two data points with commonalities. When we merge Participant Site ID or Subject ID, we can make details for demographic information or unique TouchPoint answers.
Details are generally one-to-one ratios in that only one Demographic Value would be associated to each Participant Site ID.
In this example, we are creating a detail using a custom demographic called 'Hometown'. This is linked on our Merged Subject ID. This will allow us to use this demographic with objects from another query.
- Create a detail
- Associate it to the merged dimension
- Choose the detail you'd like to make. Typically, the detail only exists in one of the queries.
OTHER TIPS FOR MERGED DIMENSIONS
- Results may create merged dimensions automatically for objects with the same name in different queries. This can cause issues between queries, confusing the report. To disallow a report from automatically creating Merged Dimensions:
1. Select Document under the Properties tab at the top of the document
2. Uncheck the box for Auto-Create Merged Dimensions.
- If the same TouchPoint is being brought into a report from different queries, [Response ID_xx] can be used as a merged dimension. Do not use [Response ID] if two different TouchPoints are being pulled in, as this variable is only unique to each specific TouchPoint (every TouchPoint will have a Response ID_1, 2, 3, 4, etc.).