MS SQL Server OLAP - SSAS2005 and ADOMD : update cube problem

Author

SSAS2005 and ADOMD : update cube problem

Francois Babin

2005-11-24, 9:23 am

Hi,

I'm trying to port an existing piece of VB6 code that works fine on SSAS2000
onto SSAS2005.
This code uses ADOMD to perform an MDX select statement by opening an
ADOMD.CellSet object.
It then performs an Update Cube statement on the same data and then displays
the CellSet updated value

again.

Both select and Update fonctions work fine except that the value in the
cellset is not updated. When I

close and reopen the connection, I can control that the cube has been
correctly updated.

I enclose here a piece of sample close which illustrates.

Is there any way to get this corrected (eg by setting some conenction
property) or is this a (un)kown

issue/limitation ?

Many thanks in advance
Rgds,
Francois

---------------------------------------------------------------------------------------------

' Form contains the folowing components:
' Label : Label1
' Button cmdLoad : performs the Select statement
' TextBox txtNew : use to type in the new value
' Button cmsSave : performs the Update cube

Private m_CellSet_Cube As ADOMD.Cellset
Private m_ConMdx As ADODB.Connection
Private m_ReportQuery As String

Private Sub cmdLoad_Click()

On Error GoTo gest_err

Label1.Caption = "Initializing"
DoEvents
m_ReportQuery = "..."

Set m_ConMdx = New Connection

m_ConMdx.ConnectionString = " Provider=MSOLAP;Data
Source=spantik;"
m_ConMdx.Open
m_ConMdx.DefaultDatabase = "Analysis Services Project1" 'm_DatabaseName

Set m_CellSet_Cube = New ADOMD.Cellset
Set m_CellSet_Cube.ActiveConnection = m_ConMdx

Label1.Caption = "Executing query"
DoEvents

m_CellSet_Cube.Open m_ReportQuery, m_ConMdx

Label1.Caption = CStr(m_CellSet_Cube(
0).Value)
DoEvents

Exit Sub

gest_err:

Label1.Caption = "Query Nok"

End Sub

Private Sub cmdUpdate_Click()

Dim Updatecommand As ADODB.Command
Dim cs As ADOMD.Cellset
Dim MdxString As String

Label1.Caption = "Performing update"

Set Updatecommand = New ADODB.Command

MdxString = "Update Cube ... USE_EQUAL_ALLOCATION
"

Set Updatecommand.ActiveConnection = m_CellSet_Cube.ActiveConnection
Updatecommand.CommandText = MdxString
Updatecommand.ActiveConnection.BeginTrans
Updatecommand.Execute
Updatecommand.ActiveConnection.CommitTrans
Label1.Caption = "updated local value is :" &
CStr(m_CellSet_Cube(
0).Value)

End Sub

Akshai Mirchandani [MS]

2005-11-29, 8:24 pm

Can you run SQL Profiler against AS and SQL Server and see if it shows any
events at the point of committing the transaction to the writeback table?

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Francois Babin" < FrancoisBabin@discus
sions.microsoft.com> wrote in message
news:BE94C870-8435-4933-AB7C- 9B3BED873B8A@microso
ft.com...
> Hi,
>
> I'm trying to port an existing piece of VB6 code that works fine on
> SSAS2000
> onto SSAS2005.
> This code uses ADOMD to perform an MDX select statement by opening an
> ADOMD.CellSet object.
> It then performs an Update Cube statement on the same data and then
> displays
> the CellSet updated value
>
> again.
>
> Both select and Update fonctions work fine except that the value in the
> cellset is not updated. When I
>
> close and reopen the connection, I can control that the cube has been
> correctly updated.
>
> I enclose here a piece of sample close which illustrates.
>
> Is there any way to get this corrected (eg by setting some conenction
> property) or is this a (un)kown
>
> issue/limitation ?
>
> Many thanks in advance
> Rgds,
> Francois
>
> ---------------------------------------------------------------------------------------------
>
> ' Form contains the folowing components:
> ' Label : Label1
> ' Button cmdLoad : performs the Select statement
> ' TextBox txtNew : use to type in the new value
> ' Button cmsSave : performs the Update cube
>
> Private m_CellSet_Cube As ADOMD.Cellset
> Private m_ConMdx As ADODB.Connection
> Private m_ReportQuery As String
>
>
> Private Sub cmdLoad_Click()
>
> On Error GoTo gest_err
>
> Label1.Caption = "Initializing"
> DoEvents
> m_ReportQuery = "..."
>
>
> Set m_ConMdx = New Connection
>
> m_ConMdx.ConnectionString = " Provider=MSOLAP;Data
Source=spantik;"
> m_ConMdx.Open
> m_ConMdx.DefaultDatabase = "Analysis Services Project1" 'm_DatabaseName
>
> Set m_CellSet_Cube = New ADOMD.Cellset
> Set m_CellSet_Cube.ActiveConnection = m_ConMdx
>
> Label1.Caption = "Executing query"
> DoEvents
>
> m_CellSet_Cube.Open m_ReportQuery, m_ConMdx
>
> Label1.Caption = CStr(m_CellSet_Cube(
0).Value)
> DoEvents
>
> Exit Sub
>
> gest_err:
>
> Label1.Caption = "Query Nok"
>
> End Sub
>
>
> Private Sub cmdUpdate_Click()
>
> Dim Updatecommand As ADODB.Command
> Dim cs As ADOMD.Cellset
> Dim MdxString As String
>
> Label1.Caption = "Performing update"
>
> Set Updatecommand = New ADODB.Command
>
> MdxString = "Update Cube ... USE_EQUAL_ALLOCATION
"
>
> Set Updatecommand.ActiveConnection = m_CellSet_Cube.ActiveConnection
> Updatecommand.CommandText = MdxString
> Updatecommand.ActiveConnection.BeginTrans
> Updatecommand.Execute
> Updatecommand.ActiveConnection.CommitTrans
> Label1.Caption = "updated local value is :" &
> CStr(m_CellSet_Cube(
0).Value)
>
> End Sub
>

Francois Babin

2005-11-30, 3:23 am

Akshai,

The Profiler on AS shows a '14 - CommitTransaction' event with following
details :
<CommitTransaction
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"/>

The Profiler on SQL shows different Insert events into the WriteBack table
(let me know if you want me to copy the details here)

The strange thing is that the update Cube action is performed correctly : if
I close the connection and start a new one, I will see the correct value. It
is only the CellSet that is not refreshed.
Is there any attribute to the OLE DB connection that can help ?

thx&rgds,
Francois

"Akshai Mirchandani [MS]" wrote:

> Can you run SQL Profiler against AS and SQL Server and see if it shows any
> events at the point of committing the transaction to the writeback table?
>
> Thanks,
> Akshai
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
>
> "Francois Babin" < FrancoisBabin@discus
sions.microsoft.com> wrote in message
> news:BE94C870-8435-4933-AB7C- 9B3BED873B8A@microso
ft.com...
>
>
>

Sponsored Links