VSS Newsletter May 2004

In this monthly newsletter we inform you about recent and upcoming events in Vlamis Software and the Oracle business intelligence and data warehousing communities. We hope you find this information helpful.

To have your name removed from this mailing list, follow the directions at the bottom of this message.

Vlamis Software Solutions is a consulting company specializing in Oracle-related business intelligence and data warehouse solutions. Learn more about us at our web site at www.vlamis.com. Contact us at sales@vlamis.com or (816)781-2880.

IOUG Live! Conference

This year, IOUG Live! was north of the border, in Toronto. There was a significant increase in attendees from last year! VSS taught two university classes and had three technical presentations. There was significant interest this year in using Oracle OLAP as well as using the new OLAP additions to the Discoverer product, coming out later this year, and the new Excel Add-in for OLAP.

Next year the conference will be May 1-5, 2005, in Orlando, Florida. Mark your calendars now. If anyone is interested in speaking at the conference be sure to contact Chris Claterbos at sales@vlamis.com or visit the IOUG website at www.ioug.org.

The presentations and papers we gave are available on our website at http://www.vlamis.com/papers2004. Viewlets (demonstrations) will be available soon.

VSS Presenting Class on Oracle OLAP May 10-14

As mentioned in last month's newsletter, we are conducting a week-long training class on Oracle OLAP and BI Beans. We still have a couple of slots left, but please call us immediately, as this is next week!

OLAP 9.2.0.5 Released

Oracle database 9.2.0.5, including Oracle OLAP 9.2.0.5 has been released. This is mostly a collection of bug fixes, packaged up in a single patch set. This will be the required database version for Oracle's upcoming release of Enterprise Planning and Budgeting. As always, you can get the patch from http://metalink.oracle.com (patch number 3501955) or specifically at http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=3501955&release=8092050

Business Objects on Oracle OLAP

Business Objects accessing Oracle OLAP data continues to be a hot topic. As reported earlier, there is a white paper on how to enable Business Objects to run against Oracle OLAP data. As a sign of the times, see Business Objects's press release.

ODTUG Advance Registration Closes May 27

Vlamis Software will be well represented at the Oracle Developer Tools User Group Conference, with two presentations and participation on two panels. See us at the following sessions:

An End-to-End Solution Using OWB and JDeveloper to Analyze Your Data Warehouse
Effectively Using Oracle9i OLAP in Business Intelligence Applications
Ask the Experts Panel--Data Warehouse
Ask the Experts Panel-Reporting/BI/Discoverer

We will be discussing and demonstrating the latest developments in Oracle OLAP solutions, including the new thin BI Beans 10g release, Discoverer for OLAP, and the Excel add-in for OLAP. The conference will be held at the Westin Kierland Resort and Spa in Scottsdale, Arizona from June 20 to 24, 2004. Conference advance registration (and the associated discount) closes on May 27, 2004. Get more information and register today at http://www.odtug.com.

Dan's OLAP Tip: What Does BI Beans Enabling an Analytic Workspace Really Do?

This month's tip is long, but hopefully valuable to you all. Let us know what you think!

BI Beans accesses analytic workspace data through a series of views. These views use the OLAP_TABLE function to present multi-dimensional data as rows that can be selected. The views also translate SQL WHERE clauses into OLAP DML LIMIT commands in an analytic workspace.

So you have an analytic workspace and you've BI Beans enabled it, but the data isn't right in BI Beans? The key to diagnosing the problem is in investigating these views.

Let's say you have an analytic workspace such as DEMO.SALES_AW that you have BI Beans enabled. You'll find a view with a name such as DEMO.DEMO_SALES_SALES_CU391VIEW. In this case, my view is named DEMO.SALES_CUBE. You can describe the view with Oracle Enterprise Manager, but you may want to paste the view definition in WordPad since the view definition has no line breaks in it. It will look something like the following:

SELECT 
  "CHANNEL_ET","CHANNEL_GID","GEOGRAPHY_ET","GEOGRAPHY_GID",
  "PRODUCT_ET","PRODUCT_GID","T_TIME_ET","T_TIME_GID",
  "COSTS","PROMO","QUOTA","REVENUE","UNITS",
  "PROD_TYPE","R2C",
  "CUST_MEAS_TEXT1","CUST_MEAS_NUM1","CUST_MEAS_TEXT2","CUST_MEAS_NUM2"
  FROM TABLE(CAST(OLAP_TABLE(
    'DEMO.SALES_AW duration session', 
    'DEMO.DEMO_SALES_SALES_CU390TBL',
    '',
    '&(DEMO.SALES_AW!olap_sys_limitmap(DEMO.SALES_AW!olap_sys_viewdim
''DEMO_SALES_SALES_CU391VIEW''))'
            ) AS DEMO.DEMO_SALES_SALES_CU390TBL))

Actually, you will see columns defined for CUST_MEAS_NUM3 through CUST_MEAS_NUM100 and the same for CUST_MEAS_TEXT, but I've omitted them here for clarity. I have also added some line breaks to make this more readable. This is the "fact view" that BI Beans uses to get at the data. You can select from this view to see what BI Beans sees. Unfortunately, you cannot see the limit map part of the view definition because the view definition references the analytic workspace object olap_sys_limitmap to refer to this object--that's why the ampersand is in the definition. If you want to see that definition, you have to execute a show command in OLAP Worksheet to see it. Here's what you do:

Start Analytic Workspace Manager (see VSS Newsletter article on getting AWM)
Select Tools-OLAP Worksheet to start OLAP Worksheet
Attach your analytic workspace with a command such as:

AW ATTACH DEMO.SALES_AW

From the view definition, copy the expression from & to )) to your clipboard
Paste it into your OLAP Worksheet command window
Place the command SHOW in front of the command
Change two single quotes into one single quote
The resulting command for the view above would be:

show DEMO.SALES_AW!olap_sys_limitmap(DEMO.SALES_AW!olap_sys_viewdim 'DEMO_SALES_SALES_CU391VIEW')

This will give you the limit map that the view is using. In our example this would show the following:

MEASURE COSTS FROM DEMO.SALES_AW!COSTS
MEASURE PROMO FROM DEMO.SALES_AW!PROMO
MEASURE QUOTA FROM DEMO.SALES_AW!QUOTA
MEASURE REVENUE FROM DEMO.SALES_AW!REVENUE
MEASURE UNITS FROM DEMO.SALES_AW!UNITS
MEASURE PROD_TYPE FROM DEMO.SALES_AW!PROD_TYPE
ROW2CELL R2C
DIMENSION CHANNEL_ET FROM DEMO.SALES_AW!CHANNEL
  WITH HIERARCHY DEMO.SALES_AW!CHANNEL_PARENTREL(CHANNEL_HIERLIST 1) 
    INHIERARCHY DEMO.SALES_AW!CHANNEL_INHIER 
    GID CHANNEL_GID FROM DEMO.SALES_AW!CHANNEL_GID 
DIMENSION GEOGRAPHY_ET FROM DEMO.SALES_AW!GEOGRAPHY 
  WITH HIERARCHY DEMO.SALES_AW!GEOGRAPHY_PARENTREL(GEOGRAPHY_HIERLIST 1) 
    INHIERARCHY DEMO.SALES_AW!GEOGRAPHY_INHIER 
    GID GEOGRAPHY_GID FROM DEMO.SALES_AW!GEOGRAPHY_GID 
DIMENSION PRODUCT_ET FROM DEMO.SALES_AW!PRODUCT 
  WITH HIERARCHY DEMO.SALES_AW!PRODUCT_PARENTREL(PRODUCT_HIERLIST 1) 
    INHIERARCHY DEMO.SALES_AW!PRODUCT_INHIER 
    GID PRODUCT_GID FROM DEMO.SALES_AW!PRODUCT_GID 
DIMENSION T_TIME_ET FROM DEMO.SALES_AW!T_TIME 
  WITH HIERARCHY DEMO.SALES_AW!T_TIME_PARENTREL(T_TIME_HIERLIST 1) 
    INHIERARCHY DEMO.SALES_AW!T_TIME_INHIER 
    GID T_TIME_GID FROM DEMO.SALES_AW!T_TIME_GID

If we were to simplify this command to:

show olap_sys_limitmap(olap_sys_viewdim 'DEMO_SALES_SALES_CU391VIEW')

those that know the OLAP DML language will recognize that this is simply a qualified data reference to the olap_sys_limitmap text variable where the limitmaps are stored.

That is the limit map that maps the analytic workspace structures to the columns in the view. If you want to know more about the OLAP_TABLE function, see our January 2004 newsletter tip.

You can also investigate the "dimension views" that provide the dimension information in a similar way. This is how BI Beans obtains information about dimensions such as dimension value names, parentage, attributes, etc.

With this information, you can investigate how BI Beans is getting at data stored in your analytic workspaces. The key is knowing that there are views you can describe and knowing that the limit maps are actually stored in analytic workspace objects.

Look for other tips in upcoming VSS Newsletters or contact me at dvlamis@vlamis.com for more information.

Dan

Prior Newsletter Articles

We feature breaking news in the "Headline" section of our home page at www.vlamis.com. More news items can be found on our news page at www.vlamis.com/news. An archive of our newsletters going back through 2002 can be found at http://www.vlamis.com/newsletters.

Our April 2004 newsletter featured the following articles:

Our March 2004 newsletter featured the following articles:

Vlamis Software Solutions, Inc.

For More Information Visit

Contact Us