D_Marcel
08-02-2017, 05:17 AM
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:
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
The code stops at cnn.Open, so I changed to this, and works:
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"
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:
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
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
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:
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
The code stops at cnn.Open, so I changed to this, and works:
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"
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:
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
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