Excel and Oracle OLAP - Reporting No-Agg Measures

I've run into this a few times recently, so here's a quick tip related to using Excel with Oracle OLAP (via the Simba MDX Provider for Oracle OLAP, of course).

Here's a situation that's been reported as a bug, but you really just need to know the right Excel Pivot Table option to choose. Consider a cube that has measures that do not aggregate but is dimensioned by a dimension with a hierarchy. In this case, there is a cube with a Store dimension with levels Store > Store Type > All Stores. The stores are located in different countries and sell in local currencies. There is a Local Currency measure, with sales reported in whatever the local currencies might be (Euros, Dollars, Yen, etc.) and a Dollar Sales measure with the U.S. Dollar conversation. As a common currency, Dollars can be aggregated. Local currencies can't be aggregated.

Here's a sample report in Excel.



Note that Dollar Sales is reported for Direct and Indirect but Local Sales is not. That's correct because Local Sales doesn't aggregate.

But what if I happen to select only Local Sales (which is null at the aggregate members Direct and Indirect). By default, Excel will display the report as shown below.



This isn't very useful because I can't drill down on the Direct member to get at the stores. The solution is simple, but a lot of people seem to miss it. Just choose the Show items with no data in rows PivotTable option.



Now you will be able to see the Direct and Indirect members, allowing you to drill to stores.



Now, after the drill.

Oracle Exadata: A Single Source of Truth (New Video)

Over the last 6-9 months we have been releasing a series of videos on YouTube under the banner of "DBA2.0" (a quick search on YouTube will list all these videos). These videos follow the work of a dedicated team of DBAs and business users as they try to get their jobs done in an environment that just keeps throwing up new challenges. Fortunately, Oracle is there to save the day...

This latest scenario deals with data warehousing and specifically Exadata. It is in two parts and the first installment has just been released - "Oracle Exadata: A Single Source of Truth". Here is the story so far:

A stroll through the halls of your IT department may reveal that there is no love lost between the DBAs and business analysts they support. In today's hyper competitive environment, business analysts need to perform more and more predictive analytics and they want the answers yesterday, but managing separate BI and OLAP servers and ensuring fast query performance can be a challenge for DBAs.

Watch this short video to see how the dynamic DBA duo address this challenge using Oracle Exadata, forging a truce with their new business analyst and even getting her to crack a smile - sort of.

Enjoy this video at http://www.youtube.com/watch?v=WgVSu-4Mizs and stay tuned for Part 2 which will be available shortly...

(Please Note: No DBAs or Business Analysts were harmed in the making of these videos)

Using MindMapping to view OLAP hierarchies...

I came across this interesting article by accident. One thing I have always wanted within AWM is a way of viewing a hierarchy or series of hierarchies. In the old days of Express I wrote a various Express programs, EIS code (who remembers Express EIS?), Express Objects extensions, that would list out a hierarchy. However, this article by Robert Brooke takes this idea way beyond anything I have seen because it uses the open source tool called MindMapping. Take a look at the series of articles Robert has written:

http://ofaworld.wordpress.com/2010/02/09/ofa-mindmapping/
http://ofaworld.wordpress.com/2009/06/10/sample-olap-dml-code-gplcode/
http://ofaworld.wordpress.com/2009/12/01/oracle-olap-mindmapping/

Now that is cool!

Now I am thinking if we could just add the MindMapping GUI into AWM and somehow allow people to build a hierarchy visually using the MindMapping GUI that would be something. This would give AWM a very powerful way of designing a hierarchy in a live/interactive manner. One for the OLAP PM team - I think. In the short-term I think it might be possible to use the AWM extension API to add the MindMapping GUI to AWM menus but this would simply launch an the MindMapping tools outside of AWM but that might be sufficient for the moment?

Nice one Robert!