Hi,

The following piece of VB 6 code used to be working fine with SSAS2K.
Basically, you launch an MDX Query by opening on AdoMd CellSet, then perform
an Update cube statement on the same connection, then retreive the value from
the CellSet itself directly, without having to perform any type of refresh
action.

Now, when using AdoMd agianst SSAS2K5, the CellSet is not refreshed anymore.
Is there any mecaninsm I can use (apart from reopening the query, which is
not acceptable for my users in terms of performance) ?

Many thanks in adavance.
Regards,
Francois

Public Sub Main()

Dim m_CellSet_Cube As ADOMD.Cellset
Dim m_ConMdx As ADODB.Connection
Dim Updatecommand As ADODB.Command
Dim MdxString As String
Dim m_ReportQuery As String

m_ReportQuery = "select " + _
" { ... } on rows, " + _
" {[Measures].[...]" + _
" } on columns " + _
" From [myCube] "

Set m_ConMdx = New Connection

m_ConMdx.ConnectionString = "Provider=MSOLAP;Data Source=myServer;Real
Time Olap=true;auto synch period=250"
m_ConMdx.Open
m_ConMdx.DefaultDatabase = "myDatabase"
Set m_CellSet_Cube = New ADOMD.Cellset
Set m_CellSet_Cube.ActiveConnection = m_ConMdx

m_CellSet_Cube.Open m_ReportQuery, m_ConMdx

MsgBox "Initial value is :" & CStr(m_CellSet_Cube(0).Value)

Set Updatecommand = New ADODB.Command

MdxString = "Update Cube [myCube] " + _
" SET (...)= 100 USE_EQUAL_ALLOCATION"

Set Updatecommand.ActiveConnection = m_CellSet_Cube.ActiveConnection
Updatecommand.CommandText = MdxString
Updatecommand.ActiveConnection.BeginTrans
Updatecommand.Execute
Updatecommand.ActiveConnection.CommitTrans

MsgBox "New local value is " & CStr(m_CellSet_Cube(0).Value)

End Sub

Unfortunately there is no way other than re-executing the query. The change
in architecture in AS 2005 means that the client no longer has the
capability to know about the update and cannot automatically refresh the
cell values in the active cellset (and it doesn't have the capability to do
so).

But since you are using ADOMD cellsets, you should be able to use the form:
cellset(i) = value
which does the cell update and refreshes the cellset in the background
automatically -- the cost is still high though.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

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 (AT) discussions (DOT) microsoft.com> wrote

Thanks a lot for answering !

I have tried adding the following line in my enclosed code

m_CellSet_Cube(0) = 100

but I get the following eror : Unspecified error -2147467259 (80004005)

Any idea ?

Otherwise, do you think using an introp ADOMD.Net CellSet is an option ?

Many thanks again.
Regards,
Francois

"Akshai Mirchandani [MS]" wrote:

I'm not sure what the error below is caused by, but it actually looks like
you may be going against AS 2000 and not AS 2005. Some possibilities are:
- Cube is not writeable
- Cell(0) is not writeable

You could use the ADOMD.Net cellset but the issue is the same. The point is
that the client-side code in AS 2005 does not have the capability of
re-calculating results because of the change in architecture. So whichever
object model you use, the updates need to go to the server and the changes
to affected cells need to be returned to the client.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

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 (AT) discussions (DOT) microsoft.com> wrote