The OLAP Extension is now available in SQL Developer 4.0

The OLAP Extension is now in SQL Developer 4.0.

See http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html for the details.

The OLAP functionality is mentioned toward the bottom of the web page.

You will still need AWM 12.1.0.1.0 to

• Manage and enable cube and dimension MV's.
• Manage data security.
• Create and edit nested measure folders (i.e. measure folders that are children of other measure folders)
• Create and edit Maintenance Scripts
• Manage multilingual support for OLAP Metadata objects
• Use the OBIEE plugin or the Data Validation plugin

What is new or improved:

• New Calculation Expression editor for calculated measures.  This allows the user to nest different types to calculated measures easily.  For instance a user can now create a Moving Total of a Prior Period as one calculated measure.  In AWM, it would have required a user to create a Prior Period first and then create a Moving Total calculated measure which referred to the Prior Period measure.  Also the new Calculation Expression editor displays hypertext helper templates when the user selects the OLAP API syntax in the editor.
• Support for OLAP DML command execution in the SQL Worksheet.  Simply prefix OLAP DML commands by a '~' and then select the execute button to execute them on the SQL Worksheet.  The output of the command will appear in the DBMS Output Window if it is opened, or the Script Output Window if the user has executed 'set serveroutput on' before executing the DML command.
• Improved OLAP DML Program Editor integrated within the SQL Developer framework.
• New diagnostic reports in the SQL Developer Report navigator.
• Ability to create a fact view with a measure dimension (i.e. "pivot cube").  This functionality is accessible from the SQL Developer Tools-OLAP menu option.
• Cube scripts have been renamed to Build Specifications and are now accessible within the Create/Edit Cube dialog.  The Build Specifications editor there, is similar to the calculation expression editor as far as functionality.

Oracle SQL Developer Data Modeler 3.3 now available

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Attention all data modellers - we are pleased to announce the release of SQL Developer Data Modeler 3.3. This release includes a new search, reports can be generated from search results, extended Excel import and export capabilities and more control and flexibility in generating your DDL. Here are a few links to get you started:

• For downloads see here: http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html
• For information about new features see here: http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodeler-33-newfeatures-1869156.html
• The release notes are available here: http://www.oracle.com/technetwork/developer-tools/datamodeler/data-modeler-releasenotes33-1869517.html
• The documentation can be found here: http://www.oracle.com/pls/topic/lookup?ctx=E29463_01&id=homepage

For data warehouse data modellers there are some very important new features around logical models, multi-dimensional models and physical models. For example:

• Support for surrogate keys during engineering to relational model which can be set on each entity. 
• More flexible transformation to relational model with mixed engineering strategies based on “engineer” flag and subtypes setting for each entity in the hierarchy
• Export to “Oracle AW” now supports Oracle 11g OLAP
• Support for role playing dimensions in export to Oracle AW.
• Level descriptive attributes can be created without mapping to attribute in logical model.
• Multidimensional model can be bound directly to relational model. 
• Support EDITIONING option on views, and support for invisible indexes in Oracle 11g physical model.

Lots of great features that will make life a lot easier for data warehouse teams.

Starbucks 1TB cube in production

Check out the customer snapshot Oracle has published which describes the success Starbucks Coffee has achieved by moving their data warehouse to the Exadata platform, leveraging the Oracle Database OLAP Option and Oracle BIEE at the front end.    10,000 users in HQ and across thousands of store locations now have timely accurate and calculation rich information at their fingertips.

Starbucks Coffee Company Delivers Daily, Actionable Information to Store Managers, Improves Business Insight with High Performance Data Warehouse

( http://www.oracle.com/us/corporate/customers/customersearch/starbucks-coffee-co-1-exadata-ss-1907993.html )

By delivering extreme performance combined with the architectural simplicity and sophisticated multidimensional calculation power of the in-database analytics of the Database, Starbucks use of OLAP has enabled some outstanding results. Together with the power of other Oracle Database and Exadata benefits such as Partitioning, Hybrid Columnar Compression, Storage Indexes and Flash Memory, Starbucks is able to handle the constant growth in data volumes and end-user demands with ease.

A great example of the power of the "Disk To Dashboard" capability of Oracle Business Analytics.

Webcast: Using Oracle OLAP data in multiple BI clients. Feb. 5 at 9:00 PST

Want to learn how to connect multiple BI clients such as SAP BusinessObjects Analysis, Cognos and Microsoft Excel to Oracle OLAP, quickly and easily, using the XMLA version of the MDX Provider for Oracle OLAP?  Check out the Oracle webcast on February 5 at 9:00 AM PST.

You will see how it works and how customers in different industries are using this solution.

Just sign up here and join us on February 5th.

Webinar: Using XMLA with Cognos and Oracle OLAP Cubes

When:  Thursday, Dec 13, 2012 at 9:00am PST / 12:00pm EST / 6:00pm CET.

To attend:    Sign up here.
 
 
If you use a business intelligence tool such as IBM Cognos, Microstrategy or SPA BusinessObjects Analysis that uses XMLA to connect to multidimensional data sources, check out a free webinar by Simba Technologies which offers a "sneak peak" of the Simba XMLA Provider for Oracle OLAP.  The Simba XMLA Provider for Oracle OLAP is an XMLA version for the Simba MDX Provider for Oracle OLAP, the gold standard in MDX connectivity to Oracle OLAP.   (The Simba MDX Provider for Oracle OLAP allows MDX based clients such as Microsoft Excel PivotTables to query Oracle OLAP cubes.  The XMLA version allows clients that use XMLA rather than ODBO to connect to Oracle OLAP.)

Simba will demonstrate using IBM Cognos using the XMLA provider to query Oracle OLAP cubes.  Here's a brief outline of the session.

See how:

• Familiar business intelligence applications such as IBM Cognos can connect to an Oracle OLAP cube.
• Ad-hoc querying and data analysis can be performed directly in IBM Cognos on your OLAP data.
• The most advanced application that responds to XMLA requests available enables users to interactively build reports, drill into details and slice and dice data
• Connectivity can be established without the need to install any software on the client machine.
  Simply connect to the XMLA service and everything works!

See you there!

Creating Oracle BI Presentation Layers from Oracle Cubes

Beginning with Oracle BI 11.1.1.5, the Oracle BI Administration tool has the ability to import Oracle cubes, dimensions and hierarchies into the Physical layer of the Oracle BI repository.  This provides an alternative to the Oracle BI plug-in for Analytic Workspace Manager.  Each method works somewhat differently and each has certain advantages that might make it the best choice for different situations.

The Oracle BI plug-in for Analytic Workspace Manager pushes metadata into the  Oracle BI repository using UDML code.  The result is a ready to use subject area with the Physical, Business Model and Presentation layers fully built for you.  It creates hierarchies in the Business Model layer, but it does not add hierarchical columns to the Presentation layer.  You can easily add hierarchical columns to the Physical layer by dragging hierarchies from the Business Model layer.

Hierarchical column in the channel dimension.

The Oracle BI plug-in for Analytic Workspace Manager creates a Physical layer that maps to hierarchy and cube views (these views make the dimensions, hierarchies and cubes look like a star schema to SQL).  In the Business Model, it assigns the SUM aggregation operator to measures selected from the cube view.  This approach allows SQL to aggregate data above the cube.  This is useful for queries that first filter and then aggregate and attribute break outs.  For example:

• Report by Year where Quarter of Year is 3 and 4.  Data at the Year level will be the aggregate of child quarters 3 and 4. 
• Report by Year, Color and Size where Color and Size are attributes of Item.  Oracle will SELECT year, color, size, SUM(sales) .... GROUP BY year, color, size.

In both cases, Oracle BI will get the highest aggregate level data it can from the cube and do some additional aggregation in SQL.

When cubes are imported using the Oracle BI Administration tool the Physical layer is mapped directly to the cube and dimensions, bypassing the cube and hierarchy views.  The SQL created in this case selects from the OLAP_TABLE table function.  Oracle BI has optimized this SQL to take advantage of some features in OLAP_TABLE, which offers better query performance with hierarchical columns in some situations.

When the cube is imported with the Oracle BI Administration tool, aggregation is set to 'External Aggregation'.  This will disable the ability to create queries that require aggregation above the cube.  This is good in that it prevents users creating queries that might not work as they expect in some cases, but it prevents filter before aggregate and attribute break out queries.

Also, the import method only creates the Physical layer.  It does not create the Business Model and Presentation layers.  You do that by dragging the Physical layer to the Business Model layer and then dragging the Business Model to the Presentation layer.  That's all quick and easy, but there will be some things you will want to update in the Business Model and Presentation layers.  For example, deleting some columns you don't really need and arranging columns in hierarchical order.  I've posted the paper Creating an Oracle BI Presentation Layer from Oracle Cubes on OTN.  This paper will walk you through the process of refining the Business Model and Presentation layers.

My recommendation is to try each method, learn about the strengths of each and choose which is best for any particular application.  Since all the data comes from the same cube, there's nothing wrong with using both methods.

Introduction to Oracle OLAP Web Presentation Series

I've posted a series of three videos introducing Oracle OLAP.  This is a great series for people how are interested in learning about what Oracle OLAP is and what it's used for.  I suggest starting viewing these in order.  Here are the links:

Oracle OLAP Overview:  Part 1 - Architecture
Oracle OLAP Overview:  Part 2 - Key Features
Oracle OLAP Overview:  Part 3 - Use Cases