CODEWITHSUNDEEP
excelexcel-2010olapolap-cubevba
Tom M.
Tom M.

Reputation: 149

Distinct Values in OLAP Cube Pivot Table

I regularly interact with massive OLAP based Pivot Tables. Usually these are more or less undocumented. And thus they are difficult to really wrap my head around and use effectively.

One of my strategies for learning what data is being presented is to mouseover a field in the pivot table field list and then pressing the down arrow to see a distinct list of the values within the option. See image below.

You can't see my mouse, but it's clicking o

I thought "Woah, maybe I can write some VBA to do this for me and I can generate a list of unique values in the cube so I can quickly scan/search for what I am looking for."

Now, I'm reasonably adept at VBA, but I just can't seem to get it. I am guessing that the method that I need doesn't exist. However, I thought I'd try here.

First attempt:

Sub list_cube_fields()
Set objNewSheet = Worksheets.Add
objNewSheet.Activate
intRow = 1
For Each objCubeFld In Worksheets("Sheet1").PivotTables(1).CubeFields
    objNewSheet.Cells(intRow, 1).Value = objCubeFld.Name
    intRow = intRow + 1
Next objCubeFld
End Sub

This generates a list of all of the available pivot table fields (which can actually be sort of useful!), but there doesn't seem to be a way to get the children/leafs/descendants of a Cubefield

Next I tried:

Sub test_field_list()
Worksheets("sheet4").Activate
With Worksheets("sheet1").PivotTables(1)
    c = 1
    For i = 1 To .PivotFields.Count
        r = 1
        Cells(r, c) = .PivotFields(i).Name
        r = r + 1
        For X = 1 To .PivotFields(i).PivotItems.Count
            name1 = .PivotFields(i).PivotItems(X).Name
            'cleans up name by eliminating the hierarchy labels
            splitName = Split(name1, "&")
            Cells(r, c) = splitName(2)
            r = r + 1
        Next
        c = c + 1
    Next
End With
End Sub

This gets a list of all of the unique values that I have used in the pivot table, but doesn't even touch unused fields. Similarly, this can be sort of helpful, but it isn't quite there.

What I really need is a little bit of each to suit my fancy, again, I don't think the methods exist. I do understand that what I'm asking might be computationally expensive and likely quite slow and that I'm not using the right tools for the task at hand. "OLAP respects [the application] permissions, while giving [me] direct SQL access would not" is my IT dept's excuse.

Anyway, if you have any ideas, I'm all ears.

Thanks! Tom

Upvotes: 1

Views: 2366

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

I would connect directly to the OLAP server with a client tool that allows for writing plain MDX requests : you still meet the security requirements of your IT people then ;-)

In MDX it is pretty easy to get the list of members in any hierarchy and filtering them. If using SSAS each hierarchy is even available as a cube as well (something with $ in the name) but this is not required.

If no tool is available, then you can write the plain MDX requests within Excel - less interactive I guess but should be ok using the Pivot Table to identify the hierarchies first; I'm not a specialist of Excel but here are links explaining how to :

https://msolap.wordpress.com/2008/10/07/returning-mdx-query-results-in-an-excel-matrix-formula/

http://cwebbbi.wordpress.com/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/

Upvotes: 2

Related Questions