Two-Layer Merged Dimension Cross Reference - Is it possible?

Note: I am unable to alter the universe structure. I can only work within the universes I’m provided.

The universes do not provide me a direct way of getting the information I need, but they do provide a roundabout method.

Universe 1/Query 1: Sales Record, Including Record ID
Universe 2/Query 2: Record ID and User ID of person who entered the record
Universe 3/Query 3: User ID and User Name

Merged Dimensions:

  • Record ID (Queries 1 & 2)
  • User ID (Queries 2 & 3)

I need to attach User Name to the Sales Record. It’s no problem to attach the User ID to the sales record, but to go one level deeper, I can’t figure out how to attach the User Name up the chain.

Is this possible? I’m getting #Context or #Unavailable errors (as I’d expect).

johnabianchi (BOB member since 2015-02-06)

Welcome to B :mrgreen: B!

Yes it is just merge the queries at the intersections and if needed make details of the objects (ie User Name as a detail of UserID merged dimension) you require in the report and pop those in what you have to display

BarkyBaloo (BOB member since 2007-07-04)

Thanks for your post-I am attempting the same. Did you find a solution? I am familiar with merging and creating detail variable when all 3 reports have a 1 to 1 to 1 join but I always end up bring to excel for vlookups when I get this far.

heidiatmn (BOB member since 2013-10-28)

pay attention to the dimension-object in the detail-variable
and pay attention to the merged-field that is used in the report
(with different fields it is possible to build inner/left/right-joins)

try to build your own dimension-variables as base of merging the querys.

n8aktiv :de: (BOB member since 2018-12-29)