What are merge dimensions?
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 share the same table.
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)
Merging the Objects
Add objects to the query
2. Add common identifiers to both queries. The most frequently used identifiers are:
Parent Record ID
Link to Record ID
3. Run the queries
4. Select the Data Access tab and click the Merge icon.
5. Ctrl + click on each object to Select the identifiers in in each query.
6. Click OK. Merged dimension will appear in Available Objects to the left.
7. Change the name of the merged dimension because it will automatically have the same name as the first object selected. Double-click the merged dimension, enter a new name, and click OK.
Creating the detail
The next step of using the merged dimension is creating the details that reference it to pull data from both queries into the same table.
Open the variable editor by clicking on New Variable.
2. Name your detail.
3. Select detail qualification.
4. Step 4. Choose associated dimension
5. Use the following formula to finish the detail:
= [T1].[Record X Values] WHERE ([T1].[Field Name] = "Name of field")
Where T1 is the name of the query from the Tier 1 form and X is the type of value (text, numeric, date, etc). See example below:
6. Detail will appear in data to the left, drag it into table of your choice.
Tips & Tricks
If you're trying to create a merge dimension between a Tier 1 and it's Tier 2 - the commonality between them will always be the Record ID (Tier 1) and Parent Record ID (Tier 2).