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.
On a tangent in Excel 2010 if you build a PowerPivot it gets treated as a pivot on an olap source and you can then covert the pivot table in a “formula table” by clicking on OLAP Tools – convert to formulas.
Each element of the pivot gets converted in to a formula based on Cube functions.
Slicers continue to work on the “formula” pivot table… its a really nice improvement
install OLAP PivotTable Extensions and then look at the MDX tab for the Excel specific MDX behind a pivot table.
http://olappivottableextend.codeplex.com/