BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Materialized View fast Refresh Problem


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
Tarunvig
Principal Member
Principal Member



Joined: 18 Jan 2011

Posts: 222
Location: Alexandria,VA


flag
PostPosted: Mon Apr 25, 2011 10:42 am 
Post subject: Materialized View fast Refresh Problem

Hi All,

I have been trying to Implement Materialized View in my Current Job.
Here is the complete scenario of what I have done so far.

1) Created Materialized View Logs for all required Tables.

Syntax for MV Logs : -
Code:
CREATE MATERIALIZED VIEW LOG ON Schema.<Table_name> with primary key


2) Created Materialized Views on all Tables.

Syntax:-
Code:
CREATE MATERIALIZED VIEW Schema.<MV_Name>
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT * FROM Schema.<Table_name>
3)

Made it sure to include all Constraints/Indexes and Partitions on MV's where ever Base Table had those implemented.

I hope all the above Syntaxes are correct as I do not get any Issue.

After Creating the MV's. Used mentioned Below Code in a Script in BODI.

Code:
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (list=>\'MV_NAME\',method=>\'F\'); end;');


Now the Issue is when I executed the JOB, Somehow almost all the MV logs did not Capture the Changes which occured to the base Table.
and Thus the MV refresh did not fetch any changes.

Has anyone ever come across such Issue?
As a work around, I am doing the complete refresh of the MV's at the Database level.

Code
Code:
exec dbms_mview.refresh(list=>'my_MV',method=>'C')


Also what Is the difference between these codes.
1)Code

Code:
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (list=>\'my_MV\',method=>\'F\'); end;');


2) Code
Code:
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (\'my_MV\',\'F\'); end;');


So the Summary is that MV Logs are Not getting refreshed, and Thus MV's dont see any records on FAST Refresh. banghead.gif


Thanks for Help..[/code]
Back to top
Tarunvig
Principal Member
Principal Member



Joined: 18 Jan 2011

Posts: 222
Location: Alexandria,VA


flag
PostPosted: Mon Apr 25, 2011 2:07 pm 
Post subject: Re: Materialized View fast Refresh Problem

Was Hoping for atleast a Single reply By now..!!!
crazy.gif
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3686
Location: Denver Area


flag
PostPosted: Mon Apr 25, 2011 3:49 pm 
Post subject: Re: Materialized View fast Refresh Problem

Tarunvig wrote:
Was Hoping for atleast a Single reply By now..!!!
crazy.gif

You had 36 views when I looked. People looked at your problem and don't have a solution.

I haven't worked with MV logs so I don't have any experience upon which to draw. The only thing I can see is that your command to refresh from within BODI and the command used directly against the database are different.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Tarunvig
Principal Member
Principal Member



Joined: 18 Jan 2011

Posts: 222
Location: Alexandria,VA


flag
PostPosted: Mon Apr 25, 2011 4:24 pm 
Post subject: Re: Materialized View fast Refresh Problem

Thanks for replying Jim.

Yes The commands are Different because I had to do a Complete refresh on the MV's .

A full refresh was not working as MV logs did not capture any changes..

Hope this Helps understanding why there is a difference between the commands...
Back to top
ganeshxp
Forum Addict
Forum Addict



Joined: 17 Jul 2008
ASUG Icon
Posts: 3675
Location: Denver, Colorado, USA


flag
PostPosted: Mon Apr 25, 2011 4:31 pm 
Post subject: Re: Materialized View fast Refresh Problem

If you could contact the DBA's I would ask you to sit with them, kick on your job and see what are all the complete list of SQL Scripts sent to Oracle by the user id that is on the Datastore and see what scripts goes into the DB.

I know you are going in rounds for this issue. nopity.gif

_________________
Cheers
Ganesh
Back to top
Bryan Martin
Forum Member
Forum Member



Joined: 26 Feb 2009

Posts: 8
Location: USA - Ohio


flag
PostPosted: Tue Apr 26, 2011 5:25 pm 
Post subject: Re: Materialized View fast Refresh Problem

I have some experience working with materialized views, logs and refreshes. I have observed behavior similar to what you describe in scenarios where I have experimented with bulk/direct loading. Conventional DML, including inserts, are recorded in the materialized view log along with additional details (rowids, primary key, filter columns, etc) based the options you specify when the materialized view log is created. However, if you are employing bulk loading, you may find that rows inserted in that fashion are instead recorded in some behind-the-scenes system tables.

There is an ALL_SUMDELTA view/public synonym that sort of gets at that, but you'd need to join back to some other system tables to unravel the details. If you are doing bulk loading, then you might consider snagging the object ID for your table from the dictionary and taking a look in ALL_SUMDELTA for activity in the relevant time frame.

Have you done some hash counts or validations and confirmed that the incremental, fast refresh is in fact leaving the materialized view inconsistent with the base tables? Or are you just concerned because the MLOG$ tables appeared empty?

If you are doing conventional DML, I would agree that the activity should appear there. The materialized view logs are cleared down automatically once ALL dependent, associated materialized views have been refreshed. There again, I think there's an off-limits system table buried somewhere that stores the last refresh timestamp at a dictionary level for a given materialized view. That timestamp is bumped up against timestamps in the the materialized view log(s) and ALL_SUMDELTA to determine which base table activity needs to be propagated to the materialized view. If your Data Integrator job is updating base tables and then calling the fast refresh all in one swoop, then those logs may well be cleared down before you get in to look for the activity (except for entries that the system perceives to be needed by other un-refreshed materialized views).

With respect to the "list=>" option, I think that's just if you want to invoke multiple refreshes and pass in a list of comma-delimited materialized views. There are several parameters you can include if you check the package documentation. At our site, I tend to do a simple call and pass in just the name and let the refresh method default to whatever I specified during the initial creation.

Code:
'begin dbms_mview.refresh(\'MY_MATERIALIZED_VIEW\'); end;'


Hope that helps a little or at least sparks some ideas. If none of that seems to fit your case, my next step would be to break out individual DF's and run them independently to validate how DML against each base table is being logged.

Good luck,

Bryan
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Wed Apr 27, 2011 1:30 am 
Post subject: Re: Materialized View fast Refresh Problem

This is a pure Oracle question. There are supporting stored procedures/Enterprise Manager screens to figure out what causes a fast refresh to be ignored and a whole set of pages in the Oracle docs and Ask-Tom for a list of things that need to be fulfilled.

Example: API Bulkload bypasses all extra database logic, hence the MView Log is not populated. Columns missing in the MView Log? Can't be used.
On top of that, when your tables are partitioned you can speed up the entire process as well. Sometimes. Primary keys,...

As a guideline, when we build DWHs with Oracle as a target we put two stored procedure calls around the dataflow where we maintain indexes, MViews, MViewLogs. The details and the code for an example of such stored procedures can be found in the sub-pages of this chapter:
https://wiki.sdn.sap.com/wiki/display/BOBJ/Fact+Tables


The difference between the two
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (list=>\'my_MV\',method=>\'F\'); end;');
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (\'my_MV\',\'F\'); end;');

is, the first uses a parameter-by-name logic, so it does not matter which parameter you specify first as you name the input parameter. You said the "list" parameter should be assigned to the value 'my_MV'.
In the second format you use a positional syntax. So if the REFRESH procedure has "list" as first parameter, then it will get the value 'my_MV'.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Tarunvig
Principal Member
Principal Member



Joined: 18 Jan 2011

Posts: 222
Location: Alexandria,VA


flag
PostPosted: Thu May 05, 2011 2:48 pm 
Post subject: Re: Materialized View fast Refresh Problem

Hi,
Firstly @Bryan and wdaehn -- Thank you very much for helping me out on this issue.
Also Sorry for Replying Late as I got stuck with different issues.

I have implemented the Materialized views and As of now things look Better.

There are still some scenario's where MLOGS are not capturing Inserts correctly icon_neutral.gif .

I am not doing BULK load on any of the base Tables where I have Materialized Views.

As still in the testing Phase so I did not implement the Exec Refresh login in DI scripts. This helps me to check MV logs

I also had a Look at the ALL_SUMDELTA Table and was surprised to see that there were a couple of Insert operations captured for the Base Table for which MV logs did not show any Insert records.

I will be debugging more on it and will update once I complete implementing them.

Thanks Again,
Tarun Vig
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0578 seconds using 17 queries. (SQL 0.0126 Parse 0.0350 Other 0.0102)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo