Got a thought or idea for our BI Bloggers ? Email biinactionblog@ebizq.net
BI in Action Blog
|
« Breaking BI Bread, or Mixing Livestock Fodder? | Main | BI as a Service: An Idea Whose Time Has Come? » August 02, 2007Oracle 11g -- Not "The BI Release" You Were Hoping For
So Oracle has announced its next generation database platform Oracle 11g that packs in nearly 500 enhancements and new features, promising improved performance, accelerated change management, higher scalability, easier administration and reduced cost. But what's in it for business intelligence and data warehousing? Well basically there are three areas that might put a smile on the faces of Oracle data warehousing gurus: SQL-Flavored OLAP Put simply; Oracle has embedded an OLAP engine into 11g to store and efficiently manage millions of these materialized views. So why are materialized views important? One reason performance. Materialized views are sort of pre-fetching used to speed multidimensional queries -- for example to calculate sales across products, regions or customers -- by presenting logically pre-aggregated data sets to users. Oracle uses an OLAP cube to store millions of materialized views so that they can be managed more quickly and efficiently. First it uses OLAP cubes as a transparent performance accelerator inside the relational database system itself. Then it offers the core manageability features of 11g to track data changes in the underlying data sources so that those changes are incrementally refreshed (usually daily or nightly) to the materialized views stored in the cubes. But performance and manageability aren't the only benefit to users. The ability to use standard SQL tools and applications to access and slice-and-dice multidimensional OLAP cubes, without users knowing they are using OLAP, is also key. The Oracle 10g database allowed users to access OLAP cues. But they had to write specific SQL to specific views. 11g lets users do this more transparently using the SQL syntax they know and love. In other words, call it Oracle's attempt to push OLAP from a specialized market to a much broader constituency of SQL-savvy users. Interestingly the underlying OLAP engine used to drive these materialized views is neither Essbase, the marketing leading OLAP server that oracle gained from its recent acquisition of Hyperion Solutions, nor Express, its legacy product that acquired from IRI Software over a decade ago. Rather it’s a separate OLAP server that was designed by Express engineers to be more embedded into the database. The feature is deemed important by Oracle as it claims over 60% of its data warehousing customers use materialized views in their implementations today. Oracle hopes its new embedded support will grow this figure, with the stated aim of pushing OLAP into everyone of its data warehouse implementations. Advanced Partitioning Oracle calls 11g "its most significant partitioning update" over the last six major database releases and says that almost all of its data warehousing customers use the partitioning capabilities. It notes that partitioning plays a key role in the base enterprise data warehouse foundation scheme, where data is typically held in a granular 3NF schema and where the largest tables, joins and data loading reside. Accelerated Query Performance Oracle claims that 11g's cache implementation is far more sophisticated than a standard cache that simply stores query results and is consulted every time a query is resubmitted. It goes further in two ways: • Users can make intelligent decisions on which results to put in the cache based in criteria like query format, how long the query took to run, how big the results set is, etc. Oracle expects its Cache to absorb more sophisticated layers of functionality like: dependency tracking (to make sure the cache is up to date); invalidation logic (to determine if the results are out of synch with underlying data changes); and automatic updates (to keep pace with data changes). Wrap-Up The most pertinent upgrade for BI is 11g's OLAP-cube based management of materialized data views. But users shouldn't overplay then significance of that technology. A materialized view is useful for what. But it is only an enabling technology. By itself it won't deliver a new generation of end-user analytics. By the same token, the performance-enhancing query cache will put a smile on the faces of performance-pressured DBAs. But it won't necessarily transform BI. The real BI story in 11g is really a combination of OLAP technology that is accessed transparently by SQL-based applications. In other words OLAP cubes are used as a query performance accelerator inside the relational database without SQL applications and tools knowing they are accessing those cubes. But wait a sec: isn't that really a way of Oracle saying that traditional OLAP slice-and-dice, drill-up/down and pivot analysis has been too expensive, slow and complex to achieve among its own data warehousing customer base up to now? If so then the materialized views feature now attempts to fix a sub-optimal workaround that oracle had previously offered. However there is another rub. The reliance on materialized views certainly harks back to a need for massive pre-aggregation of data that traditional multidimensional OLAP (MOLAP) engines used to be attacked for. Think data explosion. Nevertheless the new BI features in 11g do represent another big step in the commoditization of BI. In the case of materialized views, Oracle is offering OLAP as a core function of the relational database platform. Of course that's an area where Oracle is playing catch-up to Microsoft which has spread BI to the masses through SQL Server’s OLAP Services. What's notable however is that Essbase isn't being tapped in 11g. But as a market leading OLAP server that is arguably more robust that Oracle's own offerings, it is only a matter of time before it is pushed closer to the core relational database kernel. Posted by madansheina in Business Intelligence | Digg This | Add to del.icio.us Trackback Pings TrackBack URL for this entry: Do these materialized views need to be refreshed manually? Posted by: manisha at August 2, 2007 02:03 PM Manisha, your answer is below... Thanks William Endress from Oracle Cube organized materialized views are just like any other MVs in that they can be refreshed using the same methods as table based materialized views: on demand (dbms_mview.refresh), on commit and scheduled (start/next). Posted by: Bud Endress at August 3, 2007 07:20 AM Post a comment |
