Write-enabled cubes with Oracle data source

Francois Babin

unread,

Aug 13, 2003, 9:49:58 AM8/13/03

to

Repost : my email was wrong - sorry about that.
----------------------------------------------
Hi,

I'm trying to write-enable a cube (MS OLAP 2000) that uses
an Oracle 9.2.0.1 db as data source (through the Oracle
oleDB9 provider). Source user is SYSTEM.

My cube is built, processed and I can access its data.

Then, I am able to write-enable it *but* :

1/ once the writeback table is created, I cannot do
anything (ie select, update, insert) manually. I first
need to grant select, update, insert and delete to user
SYSTEM manually.

2/ when I try to browse the writeback data in Analysis
Manager, I get a message saying the weriteback table
cannot be found.

Is this a known bug or just a configuration issues ?

thx&rgds,
FBA

Francois Babin

unread,

Aug 13, 2003, 9:49:24 AM8/13/03

to

Michael Shao [MSFT]

unread,

Aug 14, 2003, 9:41:51 AM8/14/03

to

Hi Francois,

I'm looking into this issue right now and will update you later.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Bill Cheng [MSFT]

unread,

Aug 15, 2003, 3:50:12 AM8/15/03

to

Hi Francois,

Is your SQL Server 2000 Analysis Services applied with the latest service
pack?
320736 FIX: Writeback Fails with "Relational data provider reported error:
http://support.microsoft.com/?id=320736

Since using non-Microsoft SQL Server data sources for "write-enable" cube
is complex, I suggest that you open an incident with Microsoft Product
Support Services via
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS, so that
we can have dedicated engineer to assist you.

You may also post event log, detailed error message encountered here.

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Francois

unread,

Aug 20, 2003, 4:05:39 AM8/20/03

to

I applied SP3 to my OLAP server. Same problem occurs.

Bill Cheng [MSFT] a écrit:

Francois

unread,

Aug 20, 2003, 5:46:01 AM8/20/03

to

I've slightly changed my configuration. My data source is now using
username "SA" which was migrated from the original SQL server DB.
Note that all dimensions and fact tables are under db schema "SA".

I am now able to create and browse data in the writeback table provided
I give an UPPERCASE name to the writeback table when I write-enable my cube.

Now, I get another error when trying to actually write into the cube.
Here is what I do :
0/ launch an MDX SELECT query
1/ open a new transaction.
2/ launch the UPDATE CUBE statement
3/ commit the transaction.

This leads to the following error message "no active transction was
found" (well, that's my english translation of the french error message
I get).

Any idea ?

thx&rgds,
Francois

Francois a écrit:

Bill Cheng [MSFT]

unread,

Aug 21, 2003, 3:13:54 AM8/21/03

to

Hi Francois,

As I mentioned, using Oracle as data source is usually complex to
troubleshoot in newsgroups. I suggest that you open an incident with
Microsoft PSS.

In addition, what program reports the "active transaction" error? Is it
Oracle? You may try to capture the SQL statements sent to Oracle to check
if there is anything wrong (However, as I know, Oracle does not have a
utility like SQL Profiler).

Which provider did you use to connect to Oracle 9? Is it Microsoft OLEDB
Provider for Oracle?

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

| Message-ID: <3F434359...@caramail.com>
| Date: Wed, 20 Aug 2003 11:46:01 +0200
| From: Francois <fba...@caramail.com>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr-FR; rv:1.0.2)
Gecko/20030208 Netscape/7.02
| X-Accept-Language: fr-fr, fr
| MIME-Version: 1.0
| Subject: Re: Write-enabled cubes with Oracle data source
| References: <1b8201c361a1$c8b6fbd0$3501...@phx.gbl>
<JiGsbHwY...@cpmsftngxa06.phx.gbl> <3F432BD3...@caramail.com>
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 8bit
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: aaubervilliers-101-2-1-112.w193-252.abo.wanadoo.fr
193.252.44.112
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.olap:41927
| X-Tomcat-NG: microsoft.public.sqlserver.olap

Francois

unread,

Aug 21, 2003, 4:23:44 AM8/21/03

to

Hi Bill,

Thanks for answering. Yes, I will open a case on PSS.
In the meantime, here's a few answers to your questions - hope that
other people in this forum might find this subject usefull :

The active transaction error message is reported by two different
programs. One is ProClarity 4 and the other is simple VB6 program that
works fine when using SQL server 2000 as data source.
It likes like this (extract):

Set Updatecommand = New ADODB.Command
Updatecommand.CommandText = mdxstring
'note : mdxstring is the UPDATE CUBE statement

Updatecommand.ActiveConnection = m_MidReport.ConMDX
' note activeconnection to the cube is defined in another module -
' provider is OLE DB for OLAP

Updatecommand.ActiveConnection.BeginTrans
Updatecommand.Execute
Updatecommand.ActiveConnection.CommitTrans

Please note that when I remove both BeginTrans and CommitTrans
statements, the Execute statement does not report any error but also
does not update the cube (nothing in the writeback table)

The provider I am using for my cube's data source is the OLE DB provider
for Oracle 9 provided by Oracle.

I'm not sure that helps a lot. But, maybe you can answer these few
questions:
How does the OLAP transactions work especially with source DB
transactions ? does an OLAP transaction open a relationnal transaction
as well ? does it have anything to do with distributed transactions ?

Also, is there any difference if the cube storage is MOLAP or ROLAP (it
is MOLAP right now) ? does write-enabled cube stored in ROLAP also use a
writeback table ?

Many thanks again.
Regards,
- Francois

Bill Cheng [MSFT]

unread,

Aug 22, 2003, 4:38:28 AM8/22/03

to

Hi Francois,

Thanks for the update. If you submitted an incident, please let me know the
case number so that I can monitor the progress and post the benefits for
the community.

According to my research, if you call Connection.BeginTrans and
Connection.CommitTrans in ADO, it will call the underlying OLEDB provider
to open transaction with the data source.

According to my experience with SQL Server 2000, it will create a
write-back table on the data source, whether it is MOLAP or ROLAP.

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

| Message-ID: <3F448190...@caramail.com>
| Date: Thu, 21 Aug 2003 10:23:44 +0200

| From: Francois <fba...@caramail.com>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr-FR; rv:1.0.2)
Gecko/20030208 Netscape/7.02
| X-Accept-Language: fr-fr, fr
| MIME-Version: 1.0
| Subject: Re: Write-enabled cubes with Oracle data source
| References: <1b8201c361a1$c8b6fbd0$3501...@phx.gbl>
<JiGsbHwY...@cpmsftngxa06.phx.gbl> <3F432BD3...@caramail.com>

<3F434359...@caramail.com> <6jLFPP7Z...@cpmsftngxa06.phx.gbl>

| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 8bit
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: aaubervilliers-101-2-1-112.w193-252.abo.wanadoo.fr
193.252.44.112
| Lines: 1

| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.olap:41975
| X-Tomcat-NG: microsoft.public.sqlserver.olap