Big Bigger BI

A scribbling blog about BI, Big Data and stuff like that.

[bigbiggerbi]

Mview Complete Refresh and Atomic_refresh parameter

This post discusses issue faced by me while working on Materialized view (mview) creation at new site(database). Starting Oracle 10g, how atomic_refresh works with complete refresh has undergone a ...

[bigbiggerbi]

Materialized View create much faster than MV refresh

All this started with the observation that the initial create of an MV is much faster than a complete refresh of the same MV. Although the same amount of data has to be processed in the same way. Why?!

Since Oracle 10g a complete refresh of an MV is implemented as a Delete + Insert instead of an Truncate + Insert as in Oracle 9i and older. The point in Delete+Insert is there is always data provided. As long as the refresh is running the old state. As soon as the commit occured the refreshed data. This can be crucial in an OLTP environment. In a nightly batch run in an DW environment it's not.

DBMS_MVIEW.REFRESH ( { list IN VARCHAR2, | tab IN OUT DBMS_UTILITY.UNCL_ARRAY,} method IN VARCHAR2 := NULL, rollback_seg IN VARCHAR2 := NULL, push_deferred_rpc IN BOOLEAN := true, refresh_after_errors IN BOOLEAN := false, purge_option IN BINARY_INTEGER := 1, parallelism IN BINARY_INTEGER := 0, heap_size IN BINARY_INTEGER := 0, atomic_refresh IN BOOLEAN := true); (Source: http://docs.oracle.com/cd/B10501_01/server.920/a96568/rarmviea.htm)

There is an option called "atomic_refresh". By default this otion is true. Oracle says: "If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction." (Source: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_mview.htm ).

So if there's just one MV in the list, it shouldn't change anything. However it does. To put it in easy words: it changes the behaviour to a truncate+insert -> http://www.orafaq.com/wiki/Materialized_View

BEGIN DBMS_MVIEW.REFRESH ( '<name of materialized view>', 'C', atomic_refresh => false); END;

[bigbiggerbi]

MDX OLAP on Cassandra

MDX and NoSQL? Heck yeah. With the Pentaho Analytics Suite, I was able to point Instaview to my Cassandra cluster, select a keyspace and issue MDX queries to slice & dice (OLAP-style) my Cassan...

[bigbiggerbi]

[bigbiggerbi]

[bigbiggerbi]

[bigbiggerbi]

[bigbiggerbi]

Why Traditional BI is poor at write-back

[bigbiggerbi]

This Hadoop tutorial shows how to refine website clickstream data using Hadoop and how to analyze and visualize using the Power View in Microsoft Excel 2013

[bigbiggerbi]

[bigbiggerbi]

Top 3 Hadoop distributions

One of the biggest buzzwords of the past year or so has been “big data.” Many organizations have been struggling with how to approach it, and that...

[bigbiggerbi]

[bigbiggerbi]

[bigbiggerbi]

Hadoop distribution comparison of Cloudera, MapR, and Hortonworks

[bigbiggerbi]

[bigbiggerbi]

How to Avoid a Hadoop Hangover

It is quite natural for a ground breaking technology to arrive, be celebrated, enable new types of applications, and then to have expectations to grow beyond the technology’s ability to deliver. This crest of this cycle is now occurring in the world of Hadoop, which could be headed for a hangover. For those of us who are interested in understanding how applications will make use of big data to do new things, it is vital that we not just declare that claims for Hadoop’s power are exaggerated. We must understand what Hadoop does well and what it cannot do and why. It is also vital to understand the way that Hadoop is evolving so that we can understand what it will do.

[bigbiggerbi]

On a Web site, clickstream analysis (sometimes called clickstream analytics) is the process of collecting, storing, analyzing, and reporting aggregate data about which pages visitors visit in what order - which are the result of the succession of mouse c