Re: A transactionally consistent view on real-time data with MicroStrategy

> <erik.ykema_at_gmail.com> wrote in message
> news:1149284215.556809.269580_at_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.

> > I have considered several options:

...
> > - insert an 'AS OF SCN xxx' in all CTAS statements. Unfortunately MSTR
> > doesn't faciliate this desire.

Jonathan Lewis wrote:

> No guarantees that this would work, but
> Create a context:, and procedure to set it.
>
> create or replace context scn_ctx using scn_proc ;
>
> create or replace procedure scn_proc as
> begin
> dbms_session.set_context(
> namespace => 'scn_CTX',
> attribute => 'scn',
> value => sys.dbms_flashback.get_system_change_number
> );
> end;
> /
>
> Then you specify all data to Microstrategy through views
> that had a one-off declaration like:
>
> create view v_a
> as
> select * from a as of scn(sys_context('scn_ctx','scn'))
> /
>
> If you can get Microstrategy to run a pre-query SQL that
> can call your scn_proc then all your scratch tables should
> be populated with read-consistent data.
> --
> Regards
> Jonathan Lewis

Hi Jonathan, and others,
Thanks for the replies

I made mistake in the pseudo SQL MSTR generates, and I created some confusion. The correct sample is:

create table A1 as select from A join dim_product join dim_location join dim_time where product in {} and location in {} and time in {}; create table B1 as select from B join dim_product join dim_location join dim_time where product in {} and location in {} and time in {}; create table C1 as select from C join dim_product join dim_location join dim_time where product in {} and location in {} and time in {}; create table Z as select from A1, B1, C1 "fully outer joined"; drop table A1, B1, C1;
-- read Z into the report engine and present it to the user drop Z;

Jonathan's suggestion using a context indeed solves my challenge! Many thanks.

I was not aware of the field of usefullness of contexts untill know. I tried using a global temporary table for storing a session specific SCN and passing that to the AS OF construct, however I got an "ORA-22818 - subquery expression not allowed here".

I also managed, now being pointed into the right direction using a package
and referencing a session specific package global variable in the "AS OF" views:

CREATE OR REPLACE package p_scn as
  g_scn number;
  function get_scn return number;
  procedure set_scn;
end p_scn;

CREATE OR REPLACE package body p_scn as
function get_scn return number is
begin
  if g_scn is null then
    set_scn;
  end if;
  return g_scn;
end get_scn;
procedure set_scn is
begin
  g_scn := sys.dbms_flashback.get_system_change_number; end set_scn;
end p_scn;

create view v_a as select * from a as of scn (p_scn.get_scn);

Thanks for reading my extensive example, I hope other's can reuse this in cases of near-real time periodically refreshed environments, I will also post it to the MSTR user forums.
Best regards,
Erik Ykema Received on Mon Jun 05 2006 - 14:00:24 CDT