A transactionally consistent view on real-time data with MicroStrategy

From: <erik.ykema_at_gmail.com>
Date: 2 Jun 2006 14:36:55 -0700
Message-ID: <1149284215.556809.269580@i40g2000cwc.googlegroups.com>

Hi,
This is about getting a transactionally consistent view the data while the reader is issueing commits as part of the read activity. I can't figure out how to get it right in an elegant way, after having studied the Concepts Guide.
Following I will draw the situation and the options I have considered. We are on Oracle DBMS 9.2.0.7.

I am working on a near real time OLTP environment, we are running MicroStrategy ('MSTR') reports against a set of tables and materialized views.
During report runs users are active in the system (i.e. committing transactions through their client applications) and every fifteen minutes the MVs are refreshed by a scheduled process (which takes 7 minutes, to be improved later).
Several MSTR reports can be running concurrently.

The challenge:
run the report against a transactionally consistent set of data. "Isolation_level = serialized" or "transaction read only" would conceptually point in the right direction. However, Microstrategy, AFAIK, creates so called multi pass SQL for fetching it's data, and _commits_ (by issuing DDL) in between reading from different fact tables.
DDL is not allowed in a read only transaction, and my consistent view in serializable mode ends with the implicit commit.

A typical MSTR generated SQL scenario could look like this: alter session set isolation_level = serialized; -- my addition, trying to get it right, using a custom pre-report SQL statement

create table A1 as select from A;
create table B1 as select from B;
create table C1 as select from C;

create table Z as select from A, B, C fully outer joined; drop table A1, B1, C1;
-- read Z into the report engine and present it to the user drop Z;

The CTAS has an implicit commit, AFAIK not disablable in Oracle by any means. So I am unfortunately ending my transaction after the first CTAS and loosing my transactionally consistent view on the data.

I have considered several options:
- copy all OLTP tables also to 1:1 MVs (fast refreshable) and use
dbms_lock to serialize the read and update action between readers (MSTR) and writers (the client app).
This could work, though the moment of refresh becomes unpredictable, since if many reports are fired up between to subsequent refresh jobs, the refresh process might have to wait untill it's turn.
- insert an 'AS OF SCN xxx' in all CTAS statements. Unfortunately MSTR
doesn't faciliate this desire.
- Have MSTR create all intermediate helper tables in the first part of
the multi pass scenario as an create table A1 as select from A where 0=1 (etc.); setting isolation level serializable, and inserting into these tables. (Hence I don't commit over reads and do not end my serialized transaction too soon.) However MSTR doesn't facilitate this.
- find a way in Oracle for having serializable mode for the session in
stead of the transaction. Unfortunately I have not come across such a feature.
- Copy the report's to-be-read data to Global Temporary Tables (GTTs)
in a pre-report custom SQL statement, so the report will create and read from it's own fixed data set. This is not elegant and not feasible if the amount of data is considerable.
- Maintain several different copies of the data in partitioned MVs,
pass the value of the partition to a view from which MSTR reads using a helper table, and maintain the data untill all users of that transactionally consistent set have finished reading. (Pre- and post-sql MSTR statements calling a stored procedure) could facilitate this. Again the data volume issue spoils the fun, and I do not lookforward to the complexity of the mechanism.
- Dbms_Flashback.Enable_At_System_Change_Number(xxx); as a pre-report
sql statement. Does not allow DML or DDL... hence MSTR cannot fill it's helper tables.

So I feel like not being able to solve this elegantly, and perhaps needing to file enhancement requests with both Oracle (serializable session) and MSTR (AS OF option and/or CTAS to be changed in Create All Tables First and Later Insert Into Them.)

Any comments and or suggestions are highly appreciated. Best regards,
Erik Ykema Received on Fri Jun 02 2006 - 16:36:55 CDT