Please note! Pivotal Analytics is not available for all subscription plans. Please contact our Sales Team for further info.
Overview
Sometimes we need to get the member value of an attribute from another cube. A good example would be Product Sales cube, Product dimension in Linnworks Pivotal Analytics doesn't Default Supplier Name as an attribute, but we would like to report product sales by supplier. There is a trick, we can use LOOKUPCUBE to query Inventory History cube, Inventory Products dimension.
Hang on...
But LookUpCube returns a set, we just need a value for each SKU we need to get its supplier name. Here is a trick we can get a tuple from a set at position 0 and then a value from a tuple at position 0 as well.
Solution
In Product Sales or Product Sales (composite), create a new Calculated Member called Supplier,
The syntax of the calculated member is:
LOOKUPCUBE("[Inventory History]","(([Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&["+[Products].[SKU].CurrentMember.Properties("Key")+"]).Item(0)(0)).MemberValue")Lets break it down:
LOOKUPCUBE("Inventory History") - queries Inventory History cube
the expression itself is to get Inventory Product. Default Supplier Name set, here we essentially cross join to a SKU member.
[Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&[--*OUR SKU key goes here*--]
When we are evaluating the expression for every SKU we will insert the SKU key member from the CurrentMember
([Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&["+[Products].[SKU].CurrentMember.Properties("Key")+"])Ok, that is a set. Let us get a tuple and then the actual member (Default Supplier Member)
([Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&["+[Products].[SKU].CurrentMember.Properties("Key")+"]).Item(0)(0)That's our member, we just need to get its value,
(([Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&["+[Products].[SKU].CurrentMember.Properties("Key")+"]).Item(0)(0)).MemberValueDone
Remark
To better understand how LookUpCube returns a set and what it consists of, a good hint is to convert the LOOKUPCUBE result set to a string and for visual reference, using SetToStr
LOOKUPCUBE("[Inventory History]","SetToStr(([Inventory Products].[Default Supplier Name].[Default Supplier Name],[Inventory Products].[SKU].&["+[Products].[SKU].CurrentMember.Properties("Key")+"]))")Crazy looking thing, but you can see very visually what the output of the LOOKUPCUBE is. Very handy for figuring out how it works.