Tips and Techniques « Vlamis Software

Archive for the ‘Tips and Techniques’ Category

BIWA Techcast – Data Visualization with OBIEE 11g

Monday, July 12th, 2010

See new visualization features in OBIEE 11g!

Dan Vlamis and Tim Vlamis will present “Data Visualization Best Practices Using OBIEE 11g: Improve your BI & EPM reports, dashboards, and queries” as part of the BIWA Techcast series on July 14 at 11:00am Central time. This will be one of the first times that you can see OBIEE 11g features, now that it has been officially announced. For details see the BIWA SIG web site or click here to register and to connect to the webcast.

Here is the abstract for the presentation:

Now updated for OBIEE 11g! As BI and EPM systems continue to grow in scope and importance throughout corporations and organizations of all sizes, the need for superior data visualization skills and knowledge has never been more urgent. You’ll learn when and how to use bar charts, pie charts, line charts, and bubble charts, how and when to incorporate motion into your management dashboards, and how to avoid the ten most common errors of uninformed BI systems designers. We’ll cover the latest in Oracle BI tools including OBIEE 10g and OBIEE 11g. See specific examples of how OBIEE 11g will enable new types of data visualizations including sliders, VCR controls, zoomable graphs, and maps. Join BI mavens Dan Vlamis and Tim Vlamis for an entertaining session on how to strengthen your BI and EPM presentations, avoid the big mistakes that undermine the acceptance of analytical systems and visualizations, and leverage the tremendous power of Oracle BI and EPM toolsets.

We hope you can join us!

Posted in Business Intelligence, Events, News, OBIEE, Product Updates, Tips and Techniques | 1 Comment »

Color is a Funny Thing

Thursday, May 13th, 2010

In the visualization of data and BI dashboard design, color is often overlooked and overused as an element of design, despite the central role it plays throughout our lives in how we dress ourselves, paint our houses, and display monthly sales figures.

Randall Munroe’s Blag on XKCD.com (a robust sense of humor is required and rewarded on this übergeek comic site) recently released the results of a color survey that included more than 5 million named colors recorded over 222,500 user sessions. It is impossible to read much of the results and not burst out laughing and also realize that color is both intensely personal and emotional. Why do we pay it so much attention at some times and so little at others?

Part of the reason is that color is highly context sensitive, that is, we perceive colors to be very different depending on the background on which they appear and whatever colors happen to be adjacent to them. When Dan and I were giving a presentation titled “Data Visualization Best Practices: Know How to Improve Your BI and EPM Reports, Dashboards, and Queries at Collaborate 2010” a couple of weeks ago, we threw in some slides featuring some traditional “optical illusions” including some with color (mis)perception. Invariably, more people comment on the optical illusions than on the more substantive parts of presentation, despite the fact that most of us have been exposed to them since we’ve been in the fourth grade.

The best “simple” advice I have is to use color sparingly and carefully. Don’t underestimate its power.

Posted in Business Intelligence, Tips and Techniques | 1 Comment »

Running Discoverer for OLAP against 11g OLAP

Wednesday, February 4th, 2009

For those folks wanting to run 11G OLAP and see their OLAP data in a front-end tool things have been limiting. I have worked with the Oracle BI beans based tools for years now and have been searching for a way to use them against 11g OLAP. Well I think I have found a way to use Discoverer for OLAP (D4O) against 11g Cubes (11g and 10g mode!). This way is currently not certified by Oracle but it sure seems to work for what I needed to do.

It is quite simple really but does take a few non-standard steps to run D4O. So here are the steps:

1. You will need a copy of the D4O executable jar file (that jar that is downloaded to the machine cashe) . The file is named d4o.jar. (If you need instructions on where to find this send me an email and I can send you instructions).
2. You need to install the Java JDK 1.5 or higher. To test which version you have you can execute the following command: java -version

This is what I have on my machine (OSX)

java version “1.6.0_07″
Java(TM) SE Runtime Environment (build 1.6.0_07-b06-153)
Java HotSpot(TM) 64-Bit Server VM (build 1.6.0_07-b06-57, mixed mode)

If you need to install new version go to www.java.com to get it.

3. Now for the fun part. You will need the olap_api.jar file from the 11g AWM. This is usually found in the $ORACLE_HOME/olap/api/lib directory. But it will vary depending on the OS. Just do a search for olap_api.jar in your 11g directories. You should be able to find it.
4. Once you have found it you might want to copy it to the same directory that you have put the d4o.jar file, for simplicity.
5. Now to get it running! Go to a command prompt and change to the directory where you have your 2 jar files (call this JARHOME for this examle).
6. Issue this command:

java -Xmx256m -classpath /JARHOME/olap_api.jar:/JARHOME/d4o.jar oracle.dss.d4o.gui.D4OFrame

You should now get a login screen for D4O. Enter in the host/port/sid for your 11g instance and the login credentials for the schema that has either a 10g cube or 11g cube.

You should be able to build a workbook and view your 11g data, just like you do in 10g!

The one thing that is not working is the Disco Catalog so you can only save data to the Schema Root. But at least it is a start.

Now if you just want to see 10G Cubes you will need to add the following files to your DB parameters (and bounce the instance):

_XSOLAPI_SUPPORT_MTM=YES
_XSOLAPI_METADATA_READER_MODE=10_ONLY

Remember! This is not Certified and not supported so don’t call Tech Support! IF you want this to be supported please let Oracle know and maybe they will finish tying up the loose ends and offer support!

With respect to using BI Beans applications and the Worksheet Addin. I am working on BI Beans and have not had any success getting Worksheet Addin working… “Where there is a will there has to be a way!” I will blog about my success if I have any!

Good Luck! Let me know how this works for you!
Chris Claterbos

Posted in Business Intelligence, OLAP, Product Updates, Tips and Techniques | 3 Comments »

Oracle BI EE and Apple iPhone

Wednesday, March 26th, 2008

I just downloaded the iPhone SDK the other day and I wanted to run the simulator to see how some of our applications worked in the iPhone. Much to my surprise BIEE ran fairly nicely using Safari on the iPhone. There is definitely a problem showing charts, no Flash support, but the tables looked very nice. With the zoom in and zoom out displaying the data was extremely easy and FAST!

I thought I would post a few pictures so you can see for yourselves.

Now we have to wait for Apple to support Flash and Safari to support BIEE! Now to try BI Beans!

Posted in Product Updates, Tips and Techniques | 1 Comment »

Using TODATE and AGO measures together in BIEE

Monday, March 24th, 2008

You know how you sometimes have one of those problems that just won’t let you sleep? Here’s one:

I’m updating the classware for our publicly available training classes.

I created an AGO measure. Works great.
I created a TODATE measure. Works great.
I displayed the AGO and TODATE measures side-by-side in BI Answers. No problem.

I then added a third column, just a simple base measure no less, and … it failed. The query returned an error ORA-00942 – table or view does not exist.

I spent several hours trying to figure out what was wrong. Checked out the sesion log. Ran the query in little pieces. Added code, deleted code, trying to find the answer.

So…. buried in the OBIEE Release Notes, version 10.1.3.2, is this little tasty tidbit:

If you use a version of Oracle Database 10g Release 2 prior to 10.2.0.3, you might encounter an issue with a Full Outer Join that causes severe database performance issues… (The VMware image that I’m working with uses 10.2.0.2.)

To work around this issue when using Oracle Database 10g Release 1 or 2, upgrade to Oracle Database 10g Release 2 Patch Set 2 (for 10.2.0.3). For performance reasons, this is the preferred workaround.

To work around this issue without installing Patch Set 2, modify the Database Features table using the Administration Tool. This configuration prevents the Oracle BI Server from sending SQL constructs that expose the issue in an unpatched Oracle Database 10g.

To modify the Database Features table:
1. In the Administration Tool, open the properties of the Physical Database metadata object.
2. Navigate to the Features tab.
3. Ensure that the values for
- PERF_PREFER_MINIMAL_WITH_USAGE, and
- PERF_PREFER_INTERNAL_STITCH_JOIN
are selected, that is, set to True.

My thanks to Duc Huynh for his assistance on this one!

OSA

Thank you for shopping a Woolworths.

Posted in Tips and Techniques | No Comments »

Using Oracle OLAP 11g with Essbase Hybrid Analysis

Thursday, February 21st, 2008

I just finished my latest set of benchmarks using OLAP 11g query rewrite to accelerate our data warehouse demo. I thought while I was at it I would see what hooking Essbase to Oracle OLAP would do. Since I already had a star schema alot of the work was done. I decided to use Essbase Integration Services (EIS) to model the warehouse and then build the essbase outlines and load the data. I wanted to test both loading a full Essbase database and also see how well Hybrid Analysis would work using the OLAP query rewrite functionality. This would allow me to also allow me to use the Essbase compatible front-end tools, such as excel add-in and visual explorer, to look at my warehouse data.

Setting up the Model and Metaoutline using the EIS tools was a little different than using Analytic Workspace Manager (for OLAP) but is easy enough to use. I first defined a standard ASO cube that would contain all the data. I was able to exactly match the design that I have in Oracle OLAP without any problems. Once I built the outline I set off to load the dimensions and the cube. To test the Query Rewrite I first turned rewrite OFF and performed my loads. Times were not very fast… AWM was faster! This may be due to ODBC vs OCI direct loads… but it was not horrible! Once loaded I ran the aggregation wizard to optimize the ASO database this took a while to perform. I then looked at the data in Excel. All my data was there and performance was excellent! So now I wanted to see if Rewrite affects the data loads… So I turned on Rewrite and rebuilt the database. Load performance was slightly better but the aggregation wizard was much faster! This is pretty much what I would expect.

Now for the big test–Hybrid Analysis–what what I came for. I went back into EIS and modified the metaoutline to have the last two levels of my Geography (which had 150,000 members) be in the hybrid (ROLAP) and the last level of my Product in the Hybrid. Rebuilt my database. The Essbase cube is now Tiny and loads in minutes! I turned off rewrite and went into Excel. Drilling at the upper levels was normal… drilling down geography became much slower–the deeper I went, the worse it got. I then did the same test with rewrite turned on. It made a significant difference! Performance using query rewrite was 5-10 times faster than without it. And I did not have to tweak anything or build materialized views or anything. So this looks this has promise! And now I can look at my data using some of the Essbase compatible tools!

So in general using Oracle OLAP 11g with Essbase is very promising! I can see where it has several value propostions. Anyone else out there doing this, please share your experiences.

Posted in Case Studies, Tips and Techniques | 1 Comment »

Impressions of Essbase

Sunday, February 10th, 2008

All of us at Vlamis Software recently got a chance to get some in-depth understanding of Essbase. It was very interesting. I had always had a healthy respect for the technology, but didn’t know a lot about the underpinnings of it. Now, after spending several in-depth hands-on days with several of the top Hyperion (now Oracle) people I see that Essbase can be a very compelling choice for OLAP implementations. Oracle has now two very compelling “options” for OLAP — the Oracle OLAP option to Oracle Database and Essbase. It would be a very neat story if we could say “for this, you use Oracle OLAP, and for that you use Essbase.” But the reality is that each has its advantages and each is a very capable multi-dimensional database. You really can’t go wrong with either one.

Look for further information here in subsequent blog entries and at various conferences (including Collaborate 08 in April, 2008).

Posted in Product Updates, Tips and Techniques | No Comments »

Enhancing Performance of YTD Measures in Oracle OLAP

Friday, August 17th, 2007

If your Time dimension uses daily data, and your YTD measures run slowly…
If you would like to compute month-to-date or week-to-date information from your daily stored data…
If you would like to automatically run a program following dimension maintenance for a given dimension…

This post is for you!

The Situation:
How does Oracle OLAP compute YTD amounts? To use a simple example, let’s assume that you’re looking at a report for May07, and you want to see the YTD Sales as of May07. This YTD amount is computed by adding up the Sales for a specific list of time periods (Jan07, Feb07, Mar07, Apr07, May07). That list (called a valueset) is stored in an object called TIME_YEAR_TO_DATE_VSET, and is populated automatically by Oracle’s dimension maintenance routines. When a dimension is defined as a ‘Time Dimension’ in AWM, the dimension maintenance routines populate several time-series related metadata objects in the AW.

Each value of the Time dimension has its own list stored in TIME_YEAR_TO_DATE_VSET, a list that the YTD function looks at to determine which values to add to compute a YTD amount. It’s a pretty simple, very straightforward concept.

The Problem:
By default, that valueset is populated with dimension values from the lowest (leaf) level of the Time dimension. This is fine for a Time dimension with a lowest granularity of Month, because the system never needs to add more than 12 numbers together to return a YTD amount. But what if the Time dimension goes down to Daily data? In that case, the YTD formulas for days falling later in the year must aggregate many dimension values. For example, December 2nd must sum 335 days of data, because Oracle OLAP’s default population of the valueset includes all 335 leaf-level days from January 1 through December 2. Performance is usually affected adversely and noticeably.

The Vlamis Solution:
The Vlamis enhanced method changes Oracle’s default valueset, so that it references (in the December 2nd example) Q1, Q2, Q3, Oct, Nov, Dec 1, and Dec 2, thus reducing the number of data points that must be aggregated and thereby speeding up the display of YTD calculated measures. This method can be customized to accommodate a week-level in the time hierarchy. It can also be extended to compute Month-To-Date and Week-To-Date valuesets when data is stored at the Day level, computations which are not offered by the AWM calculated measure wizard.

But wait a minute – each time the Time dimension is maintained in AWM, wouldn’t Oracle’s maintenance routines repopulate the valuesets with the default values? Yes! And that requires that the Vlamis method be reapplied. Since we don’t want to have to remember to do that every time the Time dimension is maintained, we have also created a method by which we automatically repopulate that valueset with the “smarter” values each time the Time dimension is maintained. And this method can be applied not just to the Time dimension and the population of the YTD valueset, but to ANY dimension for which an otherwise manual procedure needs to be run after that dimension is maintained. That method is dimension specific. For example, if you need to run a procedure to sort your Product dimension values, that procedure would run automatically after you maintain the Production dimension.

So…
If your Time dimension uses daily data, and your YTD measures run slowly…

If you would like to compute month-to-date or week-to-date information from your daily stored data…

If you would like to automatically run a program following dimension maintenance for a given dimension…

Contact Mark Thompson (mthompson@vlamis.com), or call Vlamis Software Solutions at 816-781-2880.

Posted in Tips and Techniques | No Comments »

Running AWM 11g (linux version) on Mac OSX and Windows

Friday, August 10th, 2007

Now that I have the production version of Oracle 11g I wanted to run AWM on my Mac or Windows. This is a simple thing to do. You first have to find all the library files that AWM uses and put them in a directory on your machine. Then create a batch or command file to start up AWM. To do this on a Mac you need to create a awm.command file (script file) with the following commands:

#!/bin/sh
AWMDIR=/Users/chrisclaterbos/Projects/11G/AWM
JDK_ROOT=/System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home
JAVA_RT=$JDK_ROOT/bin/java

CLASSPATH=$AWMDIR/awm.jar:$AWMDIR/xdb.jar:$AWMDIR/xs.jar:
$AWMDIR/DiscoEnabler.jar:$AWMDIR/jdiscoe.jar:$AWMDIR/jndi.jar:
$AWMDIR/kodiak.jar:$AWMDIR/share.jar:$AWMDIR/xsjwork.jar:
$AWMDIR/workdep.zip:$AWMDIR/ewt3.jar:jewt4-ls.jar:
$AWMDIR/jewt4.jar:$AWMDIR/help4.jar:
$AWMDIR/oracle_ice.jar:$AWMDIR/awmhelp.jar:$AWMDIR/awxml.jar:
$AWMDIR/olap_api.jar:$AWMDIR/olap_api_spl.jar:
$AWMDIR/ojdbc5.jar:$AWMDIR/xmlparserv2.jar:
$AWMDIR/xmlcomp.jar:$AWMDIR/dbui4.jar:
$AWMDIR/jle3.jar:$AWMDIR/collections.jar:$AWMDIR/awmdep.zip:
$AWMDIR/orai18n-utility.jar:
$AWMDIR/orai18n-mapping.jar:$AWMDIR/orai18n-translation.jar:
$AWMDIR/orai18n-collation.jar

$JAVA_RT -mx1024m -Dsun.java2d.noddraw=true -cp $CLASSPATH oracle.olap.awm.app.AwmApp

Now you can save the file and once you set the attributes on the file to be executable you can click on it and it should run.

In order to do the same thing in windows you can create a bat or cmd file with the following commands:

set classpath=awm.jar;xdb.jar;xs.jar;DiscoEnabler.jar;jdiscoe.jar;jndi.jar;
kodiak.jar;share.jar;xsjwork.jar;workdep.zip;ewt3.jar;jewt4-nls.jar;
jewt4.jar;help4.jar;oracle_ice.jar;awmhelp.jar;awxml.jar;olap_api.jar;
olap_api_spl.jar;ojdbc5.jar;xmlparserv2.jar;xmlcomp.jar;dbui4.jar;jle3.jar;
collections.jar;awmdep.zip;orai18n-utility.jar;orai18n-mapping.jar;
orai18n-translation.jar;orai18n-collation.jar;

java -mx1024m -Dsun.java2d.noddraw=true -cp %CLASSPATH% oracle.olap.awm.app.AwmApp

Make sure you have installed the Java 1.5 JRE and you should be good to go.

If you need any assistance with this you can contact me at: claterbos@vlamis.com

Posted in Product Updates, Tips and Techniques | 2 Comments »

Prompting for Attach Mode in AWM

Monday, May 21st, 2007

As long as I needed to write this up for a client project, I thought I would post it here….

To have AWM prompt for the attach mode when opening an AW, edit your AWM.PROPERTIES file (yes, the one that starts with “# Preferences for the Analytic Workspace Manager, do not edit this file”, mine is in C:\Oracle\awm10203\awm\bin) and include the following line:

_aw.model.show.attach=y
That will cause AWM to ask you how to attach an AW (instead of assuming R/W) when opening an AW with the following dialog box:

AWM Screenshot

Be aware that this switch assumes that you know what you’re doing. For example, if you attach in R/O mode and then forget and spent 30 minutes tweaking your structures, all changes are still lost. Sorry. You’re in read-only mode; no changes are saved to the AW. Period.

Posted in Tips and Techniques | 2 Comments »