MDX Monday - Running MDX in Smart View

I've been a bit rough on Oracle for their MDX output -- I think with good reason. Trying to use it in a production batch environment is challenging. But there is some good news. Most of us have a tool at our disposal that gives beautiful MDX output: Smart View!

Simply right click on a connected database in the Connections Panel and select Execute MDX.

Then paste your query into the dialog box and click Execute. There's no need to include the database name specification or the semi-colon.

Here are the results. I got a neatly formatted Excel sheet and Smart View even put the where clause members into my POV! Very nice.

What happens when I use the calculated columns query from this post?

It works! Unfortunately you lose the calculated fields when you refresh the sheet.

It would be great if we could print member properties too. If we could do that, then we could do things like export dimensions in a parent child format directly into Excel. Let's try it. (This is the first query in the post with a PROPERTY_EXPR addition which should print each Market member's parent.)

<sad face> Smart View appears to ignore the properties returned in an MDX query. We don't get an error but we also don't get what we want. Hopefully there will be a future enhancement to Smart View that will add this functionality.

Essbase Crashes

Welcome to laid back Friday. I want to talk a little bit about Essbase crashes today. Since I started working with Essbase in 1998 I've experienced many Essbase agent and application crashes along with a fair share of agent hangs. I've spent countless days troubleshooting the problems, searching logs, trying to figure out which user was the last to perform an action in an application with the hope that I could call them and find out what they were doing just before the problem occurred. The burden of proof with support, whether it be Arbor, Hyperion or Oracle has always been on the customer. I'd have to figure out exactly what to do to replicate the problem -- only then could I feel confident that they'd call the issue a bug and address the problem. Better yet, if I could reproduce the problem in Sample.Basic, then I wouldn't have to send them my outline or data.

Flash forward a decade and I still see seemingly random crashes and hangs. I still search through the logs but mostly there's nothing I can do. Maybe play with some thread settings or increase a cache here or there. I feel a bit powerless. I'm sure others of you are in a similar boat. I don't have much advice to share other than, keep on doing your due diligence. Check the logs, talk to users when applicable and open tickets with Oracle. I will say that the crashes and hangs are few and far between compared to the Essbase 6 and prior days.

Troubleshooting Guide

This brings me to a recently released Oracle document that helps guide you when you do experience a crash. It's available HERE on the Oracle Suport Website which does require a login.

One section in the document is about the rda.sh/rda.cmd script (Oracle Remote Diagnostics Agent) which will zip up a bunch of your server information for easy sending to Oracle Support.

Process Monitor

In most of my Essbase server environments I've had a script that runs regularly throughout the day which will check if the Essbase server is responsive. If not, it will send me an email (or page in the old days). I'd recommend having some type of process monitor running as it's better to find out about an issue and start working on it before the users start calling.

Exporting data using MDX Part 3 -- More Totals

A question came up on Network54 (the greatest forum in the history of forums) the other day about how to create an MDX report where the column value is from the parent of a row member. This isn’t easy to do in the Excel Add-In (we’d probably have to create a calculated Measure in the outline) but MDX makes it relatively simple. Once again I’ll use our beloved Sample Basic database.

Using MDX we can create a member that crosses dimensions. In this case we will create a Measure whose value is Sales for a relative of a Market member. I’m going to create a report that shows Sales for the States in one column and the Sales for that State’s Market (i.e. East, Central, etc...) in the column next to it. Just for fun I’ll add a calculated column showing what percentage of Sales each State accounts for. The key here is to create a tuple with the member from the Measures dimension you want along with the reference to the row member.

1:  WITH  
2:  SET [_States] AS '[Market].Levels(0).Members'  
3:  MEMBER [Measures].[_MarketSales] AS '([Sales],[Market].CurrentMember.Parent)'  
4:  MEMBER [Measures].[_MarketSales%] AS '([Sales],[Market].CurrentMember)/([Sales],[Market].CurrentMember.Parent)'  
5:  SELECT  
6:  {[Sales],[_MarketSales],[_MarketSales%]} on columns,  
7:  NON EMPTY  
8:  [_States]  
9:  on rows  
10:  from [Sample.Basic]  
11:  where ([Year].[Qtr1],[Scenario].[Actual],[Product]);  

Line 2 creates the _States SET which will give us the list of states.

Line 3 creates the calculated member called _MarketSales which is defined by the tuple crossing Sales and whatever the Parent is of the Market member on the row -- in this case it will be a state.

Line 4 creates our calculated member that calculated the % of sales a state contains for the given market.

Lines 6 defines our columns which are made up of the members we just created along with Sales.

Line 8 references the SET we created on Line 2 which will make up our row selection.

And there we have our report. New York has sales of 7,705. New York's Market (East) has sales of 20,621. New York makes up 37.36% of the sales for the East.

Overwriting Data with #MISSING in ASO Calcs

The way ASO Custom Calculations and Allocations update data in an ASO database is through the loading of slices. This is something to keep in mind if you perform a lot of them – eventually you should merge the slices together if retrieval performance becomes an issue. The other thing to keep in mind is that you can’t use a Custom Calculation or Allocation to copy a #MISSING cell over a cell with data. Why is this important?

Suppose we have a database where we copy Actual data over Forecast data after each month is closed. Suppose further our company exits the Sasparilla business in Massachusetts in May so we end up having no sales going forward. In this case, if I copy May Actual data to Forecast, it won’t do what I want which is to clear out the 15 in the May Forecast cell.

1: Execute Allocation Process on Database SampA.Basic with
2: POV "{([Sales],[May],[Massachusetts],[Sasparilla])}"
3: AMOUNT "([Actual])"
4: TARGET ""
5: RANGE "{[Forecast]}"
6: SPREAD;

The work around I came up with is to clear out the POV before the Calculation/Allocation is run.

1:  Alter Database SampA.Basic clear data in region "{[May],[Forecast]}";  

So if you have a Custom Calculation or Allocation where there is a potential that you’ll need to copy #MISSING values over data, make sure you add a clear statement ahead of it.

Note: I did log a Service Request with Oracle on this subject and they acknowledged this as a bug (#19025751).