Cycling Through The Fog

Dynamic Analysis

Excel 2007 provides
the capability to have a pivot table connected to an OLAP cube. Furthermore,
detailed analysis can be built by using various CUBE functions. One of the
great things about Excel 2007 pivots and CUBE functions is that you can use a
value from the pivot as a member value within a CUBE function. For instance, a CUBEVALUE might be getting the net gas
sales for a particular company and period with

CUBEVALUE(CubeName,

                       “[Transaction
Company].[Company Drilldown].&[17]”,

                       “[Activity Date].[Date
Drilldown].[Month].&[1]&[1]&[2009]”),

                       “[Measures].[Gas_Amt_Net]”)

If you also
have a pivot that is filtering the company drilldown, the current selection can
be substituted in that formula and achieve the same results,

CUBEVALUE(CubeName,

                       ‘Pivot Sheet’!B10,

                       “[Activity
Date].[Date Drilldown].[Month].&[1]&[1]&[2009]”),

                       “[Measures].[Gas_Amt_Net]”)

Obviously,
this gives us a dynamic capability, our analysis can reflect the currently
selected pivot value(s).

Next/Previous Function

I have a
spreadsheet that has built a comprehensive analysis using this technique.  This all works well, but I was looking for
another useful function. I needed to be able to provide Next/Previous buttons
that would enable cycling the analysis through each of the top-level members of
the Company Drilldown without the user having to go back to the pivot and
reselect the Next/Previous item. The Company Drilldown was a PageField in my
pivot.

As far as I
am aware, there is no built-in way to get the next or previous item in a pivot
filter, this would have been the most obvious and simplest way, but nt to be.

My next
thought was that if I could somehow obtain the MDX for the each item, I could
create a CUBEMEMBER function for
that item, and point all of my CUBEVALUE
functions at that cube member. But how to get the MDX?

My first
thought was to query the cube directly using the drilldown dimension, but that
would get the values, but not the MDX.

The MDX
must be somewhere within the pivot, otherwise I cannot see how a reference to
the pivot field works when embedded in a CUBE function, but with all of the
delving into the properties of the pivot object, I could not see where.

Retrieveing PageField members

I did
figure out how to get details of all members of the PageField by iterating
through the cube as in the example below.

 

Private
Function IterateCubeToGetMembers()
Dim mpConn As ADODB.Connection
Dim mpCat As ADOMD.Catalog
Dim mpDim As ADOMD.Dimension
Dim mpHier As ADOMD.Hierarchy
Dim mpLevel As ADOMD.Level
Dim mpMember As ADOMD.Member
Dim mpData As Range
Dim mpCell As Range
Dim i As Long, j As Long

    Set mpData =
wsApp.Range(NAME_REF_LIST_HIERARCHY)
    Set mpConn = New Connection
    mpConn.Open
“Provider=MSOLAP;” & _
                “Initial
Catalog=<;” OLAP_SERVER_NAME & _
                “Data
Source”, OLAP_DB_NAME)
    Set mpCat = New ADOMD.Catalog

    With mpCat.CubeDefs(OLAP_CUBE_NAME)

        ReDim appVecHierarchies(1 To
mpData.Rows.Count)
        i = 0

        For Each mpCell In
mpData.Columns(1).Cells

            Set mpDim =
.Dimensions(“[Company Drilldown]”)
            Set mpHier =
mpDim.Hierarchies(mpCell.Offset(0, 1).Value)
            Set mpLevel =
mpHier.Levels(mpCell.Offset(0, 2).Value)
            ReDim appVecMembers(1 To
mpLevel.Members.Count)
            j = 0

            For Each mpMember In mpLevel.Members

                j = j + 1
                appVecMembers(j) =
mpMember.Name
            Next mpMember

            i = i + 1
            appVecHierarchies(i) =
appVecMembers
        Next mpCell
    End With

    Set mpDim = Nothing
    Set mpHier = Nothing
    Set mpLevel = Nothing
    Set mpMember = Nothing
    Set mpCat = Nothing
    Set mpConn = Nothing
    Set mpData = Nothing
End Function

 

Note, mpData.Columns(1).Cells
is a list of different dimensions that I load into the PageField depending upon
user selection.

This is a
nice technique, but still only gets me the member values, it does not get the
associated MDX.

Getting The MDX

Somewhere along
my investigations, I recalled seeing that the pivotitem was showing the MDX in
the SourceName property.

Getting the
PivotItems on a PageField was problematical, so I couldn’t use that directly.
But what if the drilldown dimension was not a PageField, but was in the rows
area for example?

Obviously,
I am not at liberty to change the original pivot, but I could create another
one, load the drilldown dimension into the rows area, then loop through the PivotItems
in PivotField(1) (we set it up with just the single pivot field). These PivotItems
have the MDX in the SourceName property.

Final Solution

The
following function builds arrays of the member and MDX values. It does it by

–         
adding
a sheet

–         
setting
up the pivot table

–         
loading
the dimensions one by one into the Rows area

–         
retrieving
all of the PivotItem values.

 

Private
function LoadHierarchies()
CONST PIVOT_TEMP as string = “pvtTemp”
Dim appVecHierarchies As Variant
Dim appVecMembers As Variant
Dim mpSheet As Worksheet
Dim mpData As Range
Dim mpCell As Range
Dim mpPivotItem As PivotItem
Dim i As Long, j As Long

    Set mpSheet =
Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ThisWorkbook.PivotCaches _
        .Create(SourceType:=xlExternal, _
               
SourceData:=ThisWorkbook.Connections(CONNECTION), _
               
Version:=xlPivotTableVersion12) _
        .CreatePivotTable
TableDestination:=mpSheet.Name & “!R5C4”, _
                         
TableName:=PIVOT_TEMP, _
                          DefaultVersion:=xlPivotTableVersion12

    Set mpData =
wsApp.Range(NAME_REF_LIST_HIERARCHY).Columns(1)
    ReDim appVecHierarchies(1 To
mpData.Cells.Count)
    For Each mpCell In mpData.Cells
   
        With
mpSheet.PivotTables(PIVOT_TEMP)
          
            ‘store
the next value in the pivot rows
            With
.CubeFields(mpCell.Value)
                .Orientation = xlRowField
                .Position = 1
            End With

            With .PivotFields(1)

                j = 0
                ReDim appVecMembers(1 To
.PivotItems.Count, 1 To 2)
                For Each mpPivotItem In
.PivotItems

                    j = j + 1
                    appVecMembers(j, 1) =
mpPivotItem.Caption
                    appVecMembers(j, 2) =
mpPivotItem.SourceName
                Next mpPivotItem

               
‘we save the member details as an array within an array
                i = i + 1
                appVecHierarchies(i) =
appVecMembers
            End With

           
‘remove the pivotfield ready for next
           
.CubeFields(mpCell.Value).Orientation = xlHidden
        End With
    Next mpCell

    mpsheet.Delete

    Set mpPivotItem = Nothing
    Set mpSheet = Nothing
    Set mpCell = Nothing
    Set mpData = Nothing
End Function

appVecHierarchies and appVecMembers are global
arrays for storing the member values and MDX.

One Oddity

On a slight
tangent, I found one oddity in this technique.

When the
company drilldown was loaded into Page area, all of the companies in the cube showed
up. When loaded into the Rows area, only some of the companies showed. This was
due to the fact that some of the companies had no associated data.

Summary

I have a
solution, but it is slow to load, and I cannot say I am happy with it.

It seems to
me that the MDX must be held somewhere in the pivot so that the link from cube
functions work, but I cannot find it.

I would
welcome any thoughts on this, a better technique, or anything else.