There are many situations where you might need to build a report off multiple cubes. Sometimes you might need a cube for some parts of a report, and a relational source for another. Relational sources are easy, but how can you build prompts in queries from multiple cubes?

The first thing to do is to analyse the members in your cubes. Different cubes have different member structures.

Let’s take a look at random member unique names as they appear in different databases.

Note, I don’t have Essbase listed here, simply because I don’t have it installed on my laptop.

SSAS	[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&[2011]]&[3]]]
DMR	[Sales (analysis)].[Time].[Time].[Month]->[Time].[2011].[20111].[201103]
TM1	[Reporting].[Product].[Product].[Level1]->:[TMR].[Product].[Product].[987]
PowerCubes	[Cube].[Date].[Date].[Date]->:[PC].[@MEMBER].[20140101-20140101]

As you can see, MUNs are logically laid out. Assuming the MUNs are consistent, we can use these to build our queries.

Dates and Months

Let’s say we have a date prompt. In the DMR we need to use a date MUN, but in SSAS we need to use the month from it. Using various macro functions, and a few tricks, it will be easy to build the queries.

In the SSAS example above, the year and month values are split. So we need to extract those values from the selected date.

First we need a way to convert the selected date to a timestamp, because the macro date functions all need timestamps and not dates. Using dates would be too easy.

#toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z')#

toUTC converts the specified string to a 0 point timezone. The T and Z shortcuts in the macro are simple shorthand to fill out the timestamp string. Using toLocal converts the string to the system timezone, which is dangerous for something like this as time-zones may cause inconsistent results in the query. So don’t use it.

Once we have the date in timestamp format, constructing the MUN is simple.

#
'[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'YYYY')
+']]&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'mm')
+']]]'
#

Hint – wrap the macro function in sq() so you can see the expression it’s building. For example:

#
sq(
'[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'YYYY')
+']]&['
+timestampMask(toUTC(prompt('date','date','2011-03-01')+'T00:00:00Z'),'mm')
+']]]'
)#

becomes

[Adventure Works].[Date].[Date.Calendar].[Month]->:[M17].[[Date]].[Calendar]].[Month]].&[2011]]&[03]]]

DMR is actually a little easier. Despite the complex MUN above, we can actually reduce it to a single key. Note, this only works when the key is UNIQUE in the hierarchy. If the key appears multiple times with different parents, you will have to construct the full path.

Consider this example: [Cube].[Dim].[Hier].[Level]->[Product Group A].[Product B].[Red] Red may not be unique, so in order to get the correct member, the entire path with all of the pertinent keys would need to be included.

#'[Sales (analysis)].[Time].[Time].[Month]->['+timestampMask(toUTC(prompt('date','date','2011-04-01')+'T00:00:00Z'),'yyyymm')+']'#

The generated month MUN doesn’t match the MUN from the cube, but it will still work. If you validate in Cognos, you’ll get a warning like this:

XQE-WRN-0054 When the data source is queried for member "[Sales (analysis)].[Time].[Time].[Month]->[201104]" it returns member "[Sales (analysis)].[Time].[Time].[Month]->:[OL].[Sales (analysis)_Time].[Time].[Time].[2011].[20112].[201104]". It may not be the member you expect to see. query: "Report" dataItem: "SelectedMonth"

which is remarkably coherent for a Cognos error.

TM1 also lets you reference any member by simply calling its unique key, even if that member has different parents (possibly due to a multi-root hierarchy). In that case, using family functions may not work as expected.

In those cases we are constructing the member from scratch using the key. But what happens if you have a model that isn’t built using the keys as the underlying code? So often I see cubes where the caption of the member also works as the key. First you should smack the cube developer over the head and get them to fix the model.

The problem is that it may be impossible to switch the code of a level to a key, especially if the cube is already in a production environment. In those cases there are still a few possible solutions. One is to use queryValue, so long as your package also contains a relational model. You could use that to run a lookup to get the caption

queryValue(table.description,table.key = prompt('selectedKey','token'))

Older versions of Cognos, without the queryValue macro function can get the same effect by using parameter maps.

$productNameLookup(prompt('productKey','token'))

If the cube does have the key as a member property, you could use the filter function. In general I dislike this solution, and would bend over backwards to avoid it. Filter loops over every member in the referenced set, and if your level contains millions of members, this can be incredibly slow. The solution would be something like:

filter([cube].[dimension].[hierarchy].[level],[cube].[dimension].[hierarchy].[level].[keyProperty] = #prompt('selectedKey','token'#)

Ultimately by constructing MUNs using macros, or filtering on properties, you can safely query multiple cubes or relational models against the same keys and dates.