Select from Cube Through VBA

[D_Marcel]

Hello gurus, here I am again. As always, I did some research before posting this thread and, unfortunately, could not figure out a solution from my findings.
I did some research here at VBAX as well and I believe that, to this one, XLD is the master.

I've started working in a new company last week and here they make intensive use of OLAP cubes to retrieve and analyse data. My first challenge is automate a report that get sales data to all the products and then create smaller reports to our customers. As I've never worked with OLAP cubes before, I spent some hours doing some reversal engineering to get at least the big picture.

I found this code at MSDN forum:

[VBA]Public Function GetCell(server As String, database As String, cube As String, tuple As String)

Dim cnn As ADODB.Connection
Set cnn = New Connection
Dim cs As ADOMD.Cellset
Set cs = New ADOMD.Cellset
Dim mdx As String
mdx = "SELECT (" & tuple & ") on Columns from " & cube
cnn.Open "Provider=MSOLAP;Data Source=" & server & ";Initial Catalog=" & database
cs.Open mdx, cnn
GetCell = cs.Item(0)
cs.Close
cnn.Close


End Function[/VBA]

The code stops at cnn.Open, so I changed to this, and works:

[VBA]cnn.Open "Provider=MSOLAP.5;Integrated Security=SSPI;" _ & "Persist Security Info=True;Initial Catalog=" & DATABASE & ";Data Source=" & CUBE _
& ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error"[/VBA]

Now the code stops running at the following line.

As a turnaround, I'm using the "=CUBEVALUE()" and "=CUBESET" functions through VBA to get the data and it's working fine, but get data directly with that VBA function would be easier I guess, sending the names of the tuples and receive the data instead of writing huge pieces of code like this:

[VBA] For LV_RINDEX = 2 To LV_LAST_ROW LV_PRODUCT_ID = Cells(LV_RINDEX, 1).Value
Cells(LV_RINDEX, LV_COL_NUMBER + 1).Value = "=CUBEVALUE(""OlapTroca"",""[Parceiro].[Nome Parceiro].[All]""," _
& """[Fornecedor].[Nome Parceiro].[All]"",""[Detalhes Trocas].[Troca Válida].&[S]""," _
& "CUBESET(""OlapTroca"",""{[Canal Troca].[Canal Troca].&[1],[Canal Troca].[Canal Troca].&[53]}"")," _
& """[Fornecedor AAAA].[Nome Fornecedor AAAA].[All]"",CUBESET(""OlapTroca"",""{[Período].[Período].[Mês].&" _
& "[" & LV_MONTH3 & "]," _
& "[Período].[Período].&" _
& "[" & LV_MONTH2 & "]," _
& "[Período].[Período].[Mês].&" _
& "[" & LV_MONTH1 & "]}""),""[Measures].[Valor Real Total Troca - Reais]""," _
& """[Produtos Trocados].[Código Produto Troca].&" _
& "[" & LV_PRODUCT_ID & "]"")/CUBEVALUE(""OlapTroca"",""[Parceiro].[Nome Parceiro].[All]""," _
& """[Fornecedor].[Nome Parceiro].[All]"",""[Detalhes Trocas].[Troca Válida].&[S]""," _
& "CUBESET(""OlapTroca"",""{[Canal Troca].[Canal Troca].&[1],[Canal Troca]" & _
".&[53]}""),""[Fornecedor AAAA].[Nome Fornecedor AAAA].[All]""," _
& "CUBESET(""OlapTroca"",""{[Período].[Período].[Mês].&" _
& "[" & LV_MONTH3 & "]," _
& "[Período].[Período].[Mês].&" _
& "[" & LV_MONTH2 & "]," _
& "[Período].[Período].[Mês].&" _
& "[" & LV_MONTH1 & "]}""),""[Measures].[Trocas]]"",""[Produtos Trocados].[Código Produto Troca].&" _
& "[" & LV_PRODUCT_ID & "]"")"
Next LV_RINDEX[/VBA]

Can someone please help me giving some ideas of how making that VBA function works perfectly?
Thanks a lot for reading, I know that I write a lot but I always try to be as clear as possible.

Kind regards,

Douglas Marcel

[SamT]

I am not familiar with Cubes
What is this & """[ and this ]"",""[ in that long string? Ampersand and square brackets included for ease finding the rest.

To my Cube-blind eye, Those should be & """ & "[ or & ""[ and ], [

Because it can be difficult to comprehend the logic of multiple ''''''''' or even see how many and which kind, I usually use
Const SQ as String = "'"
Const DQ As String = """
And substitute them as needed in the string
& DQ &"[Fornecedor]

I hope this somehow helps you

[D_Marcel]

Hello SamT!
These data inside the brackets represents the parameters to select data from the cube, and each of them should be identified by quote marks. Take a look on how it looks in the cell:

ScreenHunter_02 Aug. 02 10.54.jpg

"=VALORCUBO()" means "=CUBEVALUE"
"=CONJUNTOCUBO()" means "=CUBESET()"

[D_Marcel]

I'm marking this thread as solved. After asking for help to the BI area, I realized that I was sending the name of the server in the argument "cube", instead of the name of the cube itself.

Now I have other doubts but I'll open a thread in the MSDN forum, I realized that this kind of question is more related to SQL Server Analysis Services, not that much with Excel.

Thanks anyway!

Douglas

[Bob Phillips]

Why are you usiong cube functions via VBA, why not use them directly from Excel. I use them tons, and I don't think I have ever done that from VBA. Of course, a lot of CUBE functions can have a performance impact as each one sends a request to the cube, but you can also add MDX to the cube function query to make it even more powerful, MDX like Top 10, distinct, and so on.

[D_Marcel]

Hey Bob. I'm using cube functions through VBA exactly because of the performance issue you've mentioned. I have to perform this operation to 127.108 rows, selecting data from the cube:


(TOTAL OF SALES IN VALUE / TOTAL OF SALES IN QUANTITY)


Using '=CUBEVALUE' and '=CUBESET' functions, each cell remains in the status "#GETTING_DATA" and after several minutes, a error message is issued.
Reading the MSDN threads, I found that in 2010 you was facing a similar situation:


https://social.msdn.microsoft.com/Fo...alysisservices


Do you know how can I perform this operation quickly, Bob? I've started working with these OLAP cubes on wednesday at my new job, so I'm stumbling a lot!


Thanks a lot.

[Bob Phillips]

But if you use CUBE functions through VBA you will get the same performance hit. The overhead is caused by the function issuing a query into the cube, so that will happen whether the function is called directly from Excel or VBA (In fact VBA will be less efficient, as you have to call into Excel to issue a function call).

Have you thought about using a regular pivot against your OLAP cube? You will be subject to the constraints of pivots, but it will be fast. You can always add a few CUBE formulas for summarised data (which is what they are really all about, not 127k rows).

The other alternative is to use Power BI, it will manage cubes although I haven't really used it that way (seems somewhat redundant as the data model creates its own cube). It would imagine that it would not be blindingly fast for a table that large.

[D_Marcel]

Greetings!
I firstly thought in getting these data through VBA and, in case of issues, to test the =CUBEVALUE and =CUBESET formulae. In both options, I'd have performance impact, as you said. I thought about using Pivot Tables already but was a "Plan C", in case of having problems with the both previous options . I've just tried using the Pivot Table and I realized tha's the best option indeed, much more faster.


I've created a Pivot Table with the OLAP data and filter only the dimensions that I need, then did a INDEX + CORRESP to my database.


Thanks!


Douglas

[Bob Phillips]

Douglas,

Just as an aside, what do you mean by INDEX + CORRESP?

[D_Marcel]

Sorry, I forgot to translate. I meant INDEX and MATCH. I got the information that I need simply applying these formulae in the main database, using the OLAP Pivot Table as reference.

[Bob Phillips]

Sorry to prolong this one, but I am a tad confused. Presumably you are INDEX/Matching into the pivot table? Are there many such formulae, if not and the pivot gives the main analysis wouldn't this be where to use CUBE formulae? If there are lots, would it not be an idea to reduce the number by filtering what you show, so instead of showing everything all of the time, use slicers and such (which UBE formulae recognise) to show a subgroup of the data.

[D_Marcel]

xld, you're a "Grand Master," you can prolong whenever you want.
I wondered the same when I was trying to get the values from the cube to another worksheet using that VBA function. Doesn't the cube has all the required data, so that we can create the analysis directly, by using a Pivot Table? To the desired report, there's two basic prices:

1. Average price as a result of (Sales Values / Sales Volume);
2. Market prices sent periodically by our suppliers, in XML files;

I'm at my current job for only two weeks, so I'm still trying to understand the processes here, including why the second information is not available in the cube's source database. This way, I need to "cross" both data to create this report and then send to some internal and external clients.

I'll investigate more because I'm sure that all analysis could be done in the Pivot Tables, without needing of VBA. For now, I'll finish the automation as my manager requested, hehehe.
I'll also take a look in the MDX expressions, perhaps it can be useful to create some calculated fields and members.

Thanks again for your help, comments and insights.

[Bob Phillips]

I would expect all of the analysis could be in pivots as you say. Pivots are more the analyst's tool, a laid out report (aka the cube functions) is more for the manager (don't expect them to do any looking ).

An example of how MDX can help is if you get a set of years from the calendar. You might use the following CUBESET function

=CUBESET("ThisWorkbookDataModel","[Dates].[CalendarYear].[All].Children)","Years")

This is fine, but it will bring back all years in your calendar. You would then list the years and show the sales for those years (using CUBEVALUE). But some of the years may not have any sales so the row would be blank. You can get a set of only the years that have sales using the MDX NonEmpty clause,

=CUBESET("ThisWorkbookDataModel","NonEmpty([Dates].[CalendarYear].[All].Children,[Measures].[TotalSales])","Years")

A very simple, but effective example I believe.