Search code examples
excelssasmdxcubefunctions

CUBEFUNCTIONS - one fails then all fail?

Does anyone have any experience writing CUBE* functions in Excel?

I'm writing these functions against an OLAP db and firing MDX strings at the db, via these functions.

Seems that in certain circumstances if one of the functions fails and returns #NA, then they all fail.

Are there specific MDX functions that should not be used within these Excel functions?

Here are some examples:

1. MDX string: 

PARALLELPERIOD([Date].[Date - Calendar Month].[Calendar Year],1,TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0))

Cube function:

=CUBESET("connectionToCube","PARALLELPERIOD([Date].[Date - Calendar Month].[Calendar Year],1,TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0))")

2. MDX string:

TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0)

Cube function:

=CUBEMEMBER("connectionToCube","TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0)")

EDIT

This workbook also contains several pivot tables that contain custom sets. These sets are saved with the following settings:

enter image description here

Could this be causing some sort of conflict?

EDIT

Another slightly more complicated MDX string is what I'm using to capture the 7 day period prior to the last 7 days:

MDX: 

Tail([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0).lag(7):Tail([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0).lag(13)

CUBE* FUNCTION:

=CUBESET("LiveDealer_LiveDealer2",("Tail([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0).lag(7):Tail([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0).lag(13)"))
Solution

  • I haven't found any MDX functions that I can't use in Excel cube functions. I tried a variation of your cube functions against the AdventureWorks Tabular model and they worked fine. On the first example you have: 

    =CUBESET("connectionToCube","PARALLELPERIOD([Date].[Date - Calendar Month].[Calendar Year],1,
TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0))")

    In the AW tabular model the date hierarchy is called calendar and has the levels year, semester, quarter, month, day. My cube function is: 

    =CUBEMEMBER("AWCube","PARALLELPERIOD([Date].[Calendar].[Year],1,
TAIL([Date].[Calendar].[Day].MEMBERS,1).item(0))")

    I'm not sure why you chose to use cubeset there. You are always returning only one item, so I made it a cube member so it would directly return the member without me having to caption it. If you use a cubeset here and don't caption, it will return a blank cell in Excel, but it's working. You can check that by writing a cuberankedmember function in excel using that cubeset.

    On your second example, you had: 

    =CUBEMEMBER("connectionToCube",
"TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0)")

    My formula is: 

    =CUBEMEMBER("AWCube",
"TAIL([Date].[Calendar].[Day].MEMBERS,1).item(0)")

    I don't see any major differences with this, so check your member names and parentheses for any small discrepancies.