Glenn Schwartzberg's Essbase Blog

Upcoming Opportunities

2012-01-16T13:06:00.001-08:00

There are a lot of things going on and I thought I would let you know about them. First, I’ll be speaking at a Hyperion Solutions Roadshow with the ever amusing Edward Roske on Jan 24th in Denver. I’m also excited that Toufic Wakim will be giving the keynote. Aside from being a heck of a nice guy and a great speaker, he actually knows what he is talking about and can give great insight into the Hyperion products. It is at the Hyatt Regency downtown. To see the sessions click here To register for it click here (note: you must register with a company email address)

Second, In on February 14th at 12 noon (Easter time), I will be in a loving mood and will be presenting a webcast for both interRel and ODTUG.It is titled is MDX Practical Examples. Note, it will be repeated as an interRel webcast on Thursday Feb 16th. The abstract for it is:

MDX is the direction of the future, but how do you actually code in it? Join Oracle Ace Director Glenn Schwartzberg as he walks through some of the basics of using MDX in Essbase. This session is peppered with real world examples of how you do multiple cross joins (and why), the syntax for getting descendants, the first child and what you can do in MDX that you can’t do in Calc scripts. The list goes on and on. This session is a must attend for those who are getting started with ASO cubes and MDX and provides tips and tricks for those who have already been using MDX. To register for the webcast, go here.

Next, If you are in the Dallas Area, or plan to be there on Thursday February 23rd, the ODTUG Hyperion SIG is putting on a half day mini-conference at the ballpark in Arlington. While the agenda is not set yet, you can reserve a spot by going here. There is supposed to be a sister event in Atlanta. I believe the date will be Friday, February 17th, but I don’t have information on it to share with you yet. When I do, I‘ll post another blog entry.

I’m not sure where I’ll be or what I’ll be presenting in March, but in April, I am doing a couple of sessions at the Collaborate conference. in Las Vegas and of course, in June, I’ll be presenting at the Kscope 12 conference. I’ll put more information out on those later.

Of course, while I’m not doing most of them, the interRel webcast series is in full swing with a series on HFM, FDM and the related components. To get a list of them, contact Danielle White at dwhite@interrel.com

Smart View Patch to a patch

2011-12-21T22:31:00.001-08:00

In September, Smart View patch 11.1.2.1.102 was released which gave us parity with the Excel Add-in. While it is great, I was informed by a colleague that there is a newer version of the patch available. I checked on Oracle support and sure enough, there is now 11.1.2.1.103 available. It is only for Smart View, there don’t appear to be equivalent patches for Essbase, APS or any other products. Below are the fixes in this patch. If you go to the support site and look up the patch, there are also known issues that are worth looking at.

• 6928106

General: Smart View cannot display member names and their aliases at the same time.

• 12828079

General: When Smart View is enabled in PowerPoint, images are displayed as blanks during Slide Shows.

• 12858015

General: When you perform web launch from Financial Reporting with a report that has multiple members in the POV, the POV selection is lost in Smart View.

• 12896500

General: If Smart View is located in a different directory from that specified in the server, Excel may terminate abruptly with this runtime error: "Microsoft Visual C++ Runtime Library…"

• 12957863

General: When you to open or save a Smart View Excel file, you may get this message: "Microsoft Office Excel experienced a serious problem with the 'oracle hyperion smart view for office, fusion edition' add-in. If you have seen this message multiple times, you should disable this add-in and check to see if an update is available. Do you want to disable this add-in?"

• 11779755

Essbase: When you change the POV, you may get this error: “Invalid start location.”

• 12918727

Essbase: If you are connected to a non-unique Essbase outline from a multiple-grid worksheet, you may get this error: "Invalid spread sheet data. An application protocol error might exist between server and client processes."

• 13242869

Essbase: When the Use Excel Formatting option is selected, cells that contain formulas may display the formulas rather than data after you refresh. In addition, cell formatting may change from general to text.

• 12854000

Planning: In the Smart View Panel, Planning folders and forms are not displayed the same order that they are in Planning. Instead, they are displayed in alphabetical order.

• 12902911

Planning: In ad hoc grids, cross-worksheet references in formulas do not work; the formula itself is displayed in the cell rather than the contents of the cell referenced.

• 12908185

Planning: When you search for members using the drop-down menus in the Page section of a Planning data form, Smart View may take an inordinate amount of time to return the members.

• 13001747

Planning: When you try to open Smart View from within Planning, you receive a Microsoft Excel error.

• 13253314

Planning: If the first worksheet of a composite form contains a formula, you cannot submit data from the other worksheets in the composite form.

• 13403509

Planning: After refreshing, column widths are reset even if Adjust Column Width is selected.

• 13419370

Planning: The "The form contains unsaved data." warning mesage is not displayed when you refresh a composite form.

• 13255112

Financial Management: Excel files in which Smart View is enabled may take a long time to open.

• 13401856

Financial Management: If "Member Name and Description" is selected as a member name display option, Smart View only displays only the description.

• 12559490

Reporting and Analysis: When you attempt to export a Financial Reporting query-ready report to Smart View, you get an error.

• 12982945

Reporting and Analysis: When you import a functional grid from Financial Reporting into Smart View, the formatting may be lost.

• 13093388

Reporting and Analysis: After you upgrade to Smart View 11.1.2.1.102, Excel may not launch.

• 13365904

Reporting and Analysis: After you upgrade to Smart View 11.1.2.1.102, Excel may terminate abruptly.

A different way to do currency conversion

2011-11-22T12:50:00.001-08:00

I was at a client and needed to do some currency conversion for an application I was writing (BSO). In their case they had over 50 different currencies they would need to convert to USD (and only to USD). Their requirement was no maintenance to the calc scripts would be necessary if a new currency was added. The build of the currency dimension and loading of currency rates was automated in earlier versions of the project. I got to thinking about the possibilities and the first thing that came to mind was to parse out a UDA and use it in my script. But alas, there is no function to do that. One idea down.

Next, I thought about an external table where I could pass a cost center number and pull in the currency rate for it. I would need a Custom Defined Function (CDF) to do this. This was a possible solution as my cube is built from Essbase Studio and I could write a SQL query to get the currency for any cost center and link that to the currency table to pull in the rate. Sadly, the client did not want to use CDFs in their environment. Plus, I don’t have one written to do that and am unsure of the speed as I have about 20k cost centers it would have to work against.

So finally, I thought of attribute dimensions. The client is on 11.1.2.1 and I remembered seeing some new attribute functions recently, I don’t remember what version but I think it was 11.1.1.1. So I looked through the tech reference and there was what I was looking for. @AttributeSVal. The function returns the string attribute value for the selected member. So I started experimenting.

First a little background. My cube has the following dimensions

Accounts – This is a standard account structure. It contains a balance sheet and multiple versions of a P&L. Each account is tagged with the type of rate it uses for conversion as a UDA (AVG or EOM). It also has a section for statistical accounts. Included in this section buried are Average_rate, and EOM_Rate. (and a couple of others)

Periods – nothing special here, periods rolling to quarters

Years – just a list of years. Nothing special

Currency – This dimension has two parts. First are two members USD and Local. Data is loaded in as local and has to be converted to USD. The second part of this dimension contains all of the currency codes used in the system

Scenario – again nothing Actual and multiple forecast members.There are also some calculated members in this.

Organization. This is their entity structure. I won’t bore you with the structure, but the bottom level is cost center and this is where currency conversion needs to be done.

Products –again nothing special, This is a typical product hierarchy.

To this I added a new attribute dimension called Cost Center_Currency - It had the same members as are in the second part of the currency dimension (all the currency codes) but to make the member names unique, I prefixed them with the three letters “CUR” For example “USD” would be “CURUSD” and “EUR” would be “CUREUR”. I then associated this attribute dimension with the Organization dimension and wrote a quick load rule to populate the associations between cost center and the attribute dimension. I made sure if any cost center was missing an attribute member, I defaulted it to CURUSD. I figured is there was no currency conversion USD was always set to 1 anyway.No harm, no foul.

Now for the fun part, creating the calc script. I won’t bore you with the set statements or aggregations, etc, but just the currency conversion part. I’ll warn you at this point, I have changed the dimensions from the client to make it generic and am not cutting and pasting code but typing it in by hand, so there could be minor typos in it.

First a fix statement. I need to make sure I am at level zero Organization and other dimensions. I use substitution variables for the year and period I am going to calculate. I am going to replicate the script so I have different versions for Actual and forecast (I use different budget rates for forecasts). Also note if the account does not have a UDA then I assume it is not convertible and make USD equal to Local. (This is because there are some non monetary items in the account structure). Product has multiple rollups so I only grab the primary one and I only want the income statement and Balance sheet from Accounts.

Fix(&Year,&Per,Actual,@LEVMBRS(“Organization”,0),@relative(“All products”,0),@relative(“Income Statement”,0), @relative(“Balance Sheet”,0))

“USD(

IF (@ISUDA(“Accounts”,”AVG”)

“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”Average_Rate”;

ElseIF (@ISUDA(“Accounts”,”EOM”)

“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”EOM_Rate”;

ELSE

“USD” = “Local”;

END

)

EndFIX

So what does this do. First, I check to see what rate to use.(the IF statement). Next, I am getting the local value for each account. The

@MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))

is taking the value of the attribute dimension for the cost center we are working on and getting a substring of it starting in column 3. (remember when we created the dimension we added a prefix CUR to be beginning of the actual currencies). Once we have the currency code back, it is returned as a string, so using @member we turn it back into a member name. I then am using the cross dimension operators to define the intersections in the other dimensions where rates are stored.

The one issue I has was I forgot to tag my “No organization” member with an attribute value at first. When it got to that member, it crashed the calculation.

There you have it, a dynamic calc script to calculate currency conversion that does not need to be maintained. In my case, the dimensions were not huge so it ran pretty quick. I did find if I added dimensions to the cube, it slowed down considerably. I think the best use of this type of code would be in a business rule where you are fixing on one or a few cost centers but it could be used on bigger sets of data as well.

Open World is a wrap

2011-10-06T21:22:00.001-07:00

I’m sitting at the SFO airport waiting for an 11pm flight home after a looooong 9 days in San Francisco for the Oracle Ace Directors meeting and Oracle Open World. From my earlier blogs, you should surely have seen the announcement of the Exlytic machine so I won’t repeat it here. I sat through a lot of sessions, most on stragetic direction and/or roadmap. They seemed to blur together as the slides overlapped in many of them. There was talk of many enhancements and potential new products. I say potential because until I have my fat little hands on them, they are just a dream to me.

Interestingly, I think the best sessions of the conference were today (Thursday) when most people had packed up and gone home. I sat through a session on Fusion Apps and EPM and the voice in that session was how important Essbase and the Hyperion applications are to Fusion apps. As others have reported, Essbase is the engine beneath many of the apps and the apps have been designed to integrate seamlessly with Planning, HFM, Financial Close and other applications. Since Fusion apps have now been released, we should quickly see how they work.

I next sat through a Smart View session and saw interesting things they are for HFM and what they are working on for Essbase. For OFA customers who are switching to Essbase, there will be (insert standard disclaimer here) a new “Set” retrieval that looks like the OFA retrieves.

I ended the day going to the “It’s a wrap” party where I listened to Berlin for a while, then went and got one of my favorite dinners. There is a restaurant in SF called Cioppinos that of course serves the fish stew cioppino. It was a great way to end my time in SF.

I touched a machine and I liked it

2011-10-03T08:41:00.001-07:00

With apologies to Katy Perry for the title. (My daughter would be amazed I actually know who Katy Perry is, but that’s a different story); I got my first look at the actual Exalytics machine. Well a sample box. and here is what it looks like. I would love to get my hands on one and try some client applications on it. I am going to two sessions today that give more details of the machine. Stay tuned!!!!

Larry’s Keynote

2011-10-02T19:05:00.001-07:00

Sitting with Edward Roske, listening to Larry Ellison’s Keynote.Forgive me if this post is a little disjointed as I’m typing as I listen. I’m going to post this He spoke about the success of the Exadata and Exalogic machine. His talk discussed parallel processing and the infinaband network to make it fast. Moving data fast is the key to speed of processing. Everything including the VM had to be optimized for performance and reliability. Another performance enhancement is 10X data compression which means les cost and less data movement. Again this means moving data faster. In the Exalogic case 100 times faster. Add to all of this DRAM and Flash to put almost everything in memory to create optimal speed for lower cost.
Why am I talking about all of this and how does it relate to Essbase? Well I let the cat out of the bag in my previous post. Larry announced the new Exalytics Intelligence Machine. Extreme performance In Memory Analytics. Hardware and software merged to provide speed of thought Analysis (Gee we have been saying that about Essbase for years). Now there is a machine than can support it. It uses a completely different UI. Everything is in memory, parallel versions of OBIEE< Essbase and Times 10. This means huge improvements in Essbase performance. There will be a new UI for this machine because of the speed of query. It will be adaptive self tuning of in-memory cache to optimize query based on workload.
l
Look at the hardware specs. Impressive

Here is what the performance improvement is for OBIEE

Here is Essbase performance

Notice the comparison of Essbase 11.1.2.1 vs. 11.1.2.2 for Exalytics. I guess they had to make some changes for in memory processing

Finally a summary

Daddy(Edward), I want one for the holidays. Will you buy me one?

Exciting news from Open World

2011-10-02T16:33:00.001-07:00

I attended the Hyperion SIG meeting today (Sunday) at Oracle Open World and heard something that made my heart race. There on a slide on Major announcements for this year was the announcement of a new BI machine I believe the name was Exalytics. Larry is supposed to have details in his keynote, but this is exciting. From the short announcement, it sounded like a machine designed/optimized and integrated for optimal BI performance. There could be huge performance improvements from a standard install. It follows the Exadata and Exalogic machines from past years.

Two other announcements were:

Financial Management Analytics. This is OBIEE based real time analysis from HFM

sustainability Reporting Starter Kit. Again this goes against HFM. It is built on the GRI framework.

I don’t have much information on any of these, but I’ll blog the details when I know more.

Smartview Enhancements

2011-09-15T12:44:00.001-07:00

It is rare that I have multiple blog posts in a single day, but I am so excited to see this come out. Smartview 11.1.2.102 is available as a patch set on Support.oracle.com. Big deal you say, its just another patch! Oh how wrong you are my friends. This is the patch of all patches. The patch we have been waiting our collective lives for. Why, because it makes Smartview act and behave like the Essbase add-in and more.

The first thing that I consider a fix is you can no do a submit data without first refreshing the sheet if you make changes to it. Second, we have all checked the “Use Excel formatting” option only to find the formatting disappears if we zoom in or pivot. Well, now it really does work. and it works in two ways. If checked, it leaves the excel formatting completely alone. For example, if you highlight cell C3 as red, and you pivot a page member to a row, cell C3 will still be red. But the Smartview team has figured how to set it so if you zoom in, the formatting gets carried along with the zoom in. Very nice!!

But that is just the beginning. Here are some of my favorite new things. (note, I’m writing this from my experience with the Beta, some features in the final version may not have been implemented exactly as I describe them. Since this patch came out, I’ve not had time to install and test it)

Items that are now in Parity with the Add-in

1. You can select multiple members to do Ad Hoc operations on at one time (zoom in, zoom out, Keep only, remove only). This is not limited to just row or column member, you can select both at the same time

2. Support for LROs and Linked Partitions.

3. Aliases. There are two items here. first, you can have both the member name and aliases on rows in the report and second and one of my favorites, if you use an alias from an alias table that is not active, Smartview now understands it and returns the member name or alias for the active alias table (depending on what you have your options set to)

4. SHEET LEVEL OPTIONS are back!!!!!!!!! All options are defined as sheet level options and are stored with the sheet. that means you no longer have to worry about what happens if you change options and then do a retrieve on a different sheet. Also, There is now a dropdown on the options dialog so when you change your options, you can set a default set. This default set is used when new sheets are created.

5. All of the zoom options that you know and love in the add-in are now available. They are also on the ribbon bar as a dropdown on zoom in so you can select the zoom level/type you want to do.

6. Formula preservation and Formula fill are working like they should and then some. In the add in, if you pivoted in the add in you would lose the formulas. In this release, in most cases, the formulas are preserved. They may no longer be accurate based on what you did, but they will be there.

7. While the new features document does not talk about it, I believe range retrievals are back. You can highlight an area of a report and as long as al of the dimensions are represented , you can retrieve just that part of the report

New and enhanced items.

While the above items allow for Parity, the Smartview team was not satisfied with just making things the same. In a number of areas, the went miles beyond what the Add in could do. Here are some of the things.

1. You can have multiple grids on a single sheet. Remember the old message “Multiple grids on a single sheet are not supported at this time” Well the time has come and that message can now be archived forever. You can set up multiple connections to the same or different databases and do a refresh and they all update. I had 4 different reports on a single sheet and they all pulled data properly.

2. Butterfly and reverse formatted reports are now available. A butterfly report is where member names are in the middle with number on each side (as shown below. Note, the numbers are not from sample basic. I made then up to show a report sample)

You can also do a reverse report where the member names follow the data values

3. While 11.1.2.1 allowed you to hide the POV, in this new version, you actually are not hiding it, but putting it onto the sheet. In this new version, the POV members are stored on the sheet in row 1 (or wherever you designate them to be. When you enable the POV, it hides the row on the sheet. Changing the member names on the sheet or in the POV bar affects both locations. Another nice feature with the POV is you can type in member names directly if you know them. (it was actually introduced in 11.1.2.1)

4. A Member information button has been added to the ribbon bar. From this you can get almost all of the information about a member such as generation and level, consolidation properties, aliases, attribute associations, formulas, comments and UDAs.

5. within the Smartview connection information dialog, new functionality has been included. for multiple grids on a single sheet, you can delete the connection info (currently the only way to reset multiple connections) and also copy off the connection into to a sheet to send to someone if you are having issues with your retrievals so they know what you are connected to.

6. A host of new VBA toolkit functions have been added. The development team asked what APIs people are using and turned most of them into toolkit macros. This is very handy so you don’t need to code to the API any more, you can do it all with macros.

as with any release, there are disclaimers and this is no exception. In the new features document is the following

Unless otherwise noted, the features described here are available only in Smart View 11.1.2.1.102 connected to Oracle Essbase 11.1.2.1.102 through Oracle Hyperion Provider Services 11.1.2.1.102. They are not available in other releases of Smart View, Essbase, or Provider Services. They are not available in data providers other than Essbase.

This means that you have to install the patches for Smartview, APS and Essbase to take advantage of the features. You can use this version of Smartview with older versions of APS but won’t have the new features available.

AS for upgrades, here is what the support matrix says

You can upgrade to Smart View Release 11.1.2.1.102 from the following releases:

Release Upgrade Path From	To
11.1.2.1.x	11.1.2.1.102
11.1.2.1.102	11.1.2.1.102
11.1.1.3.x	11.1.2.1.102
9.3.3.x	11.1.2.1.102

I am truly excited about this release. It gives us things we have been asking for in the add in for a long time and now puts Smartview ahead of the add in in functionality.

I should also note, Cameron Lackpour has posted on his blog an Oracle presentation you can attend to see the new features demonstrated. Read about it here

A Special track at Kscope 12

2011-09-15T08:55:00.001-07:00

Kscope12 (I actually hate the name, I much preferred Kaleidoscope, Sorry Edward) is 9 months away, so why am I writing about it now. For a few reasons. First, the call for abstracts is open and ends October 28th. I’ll post the actual announcement from an email I got from ODTUG later in the blog.
Second, I want to announce a special track that is being added and would like your feedback. The Track is a Beginner’s Essbase (and related products) track. For the past few years, I have commented how the Kaleidoscope conference is the most technical conference I have been to and how you can hear things here that you can’t get anywhere else. It has been true, but in hindsight, this is also a bad thing. I think we have intimidated some newer admins/developers/super users and scared them away from the conference. In my infinite wisdom (stupidly), I put together a proposal for a beginners track and submitted it to the conference committee. The accepted the idea and made me the track chair for it. Now you know why I say stupidity.
At any rate, I’m looking for what sessions new people would want to attend. These are not lab sessions nor are they a boot camp, but should fill in the gaps of people who have taken those or have learned themselves. There are three ways you can get me your ideas.
1. If it is something you would be interested in presenting, submit an abstract Here. You get a free conference pass if your abstract is accepted.
2. If it is a session you would want, but don’t want to present yourself. you can submit your idea here
3. If you don’t like either of the ideas above, comment on the blog post and a copy gets emailed to me.
As I promised, and so I have more content in my blog without having to actually having to come up with it myself, Here is the email I got from ODTUG about sessions.
The countdown is on – only six weeks left to submit an abstract for consideration for Kscope12.
Do you want to join the best and the brightest, the most technical minds in your field?
Submit your abstract in one of these topic areas:

Abstracts are due by October 28.
Why Present?

You will receive a complimentary conference registration worth more than $1,400.
Your paper and presentation slides will be published in the Conference Proceedings.
Your paper will be published in the ODTUG.com Technical Database where all ODTUG members can download it - giving you long-term exposure.
Presenting at ODTUG Kscope looks excellent on your résumé and demonstrates to your clients and/or employer that you are good at what you do.
It just feels good to share your knowledge with an interested group of people.
You will definitely learn something unique from your audience.

Don’t Want to Present?
No problem! We still want your feedback. Is there a session you would like to attend at Kscope12? Submit a suggestion here.
Also, make sure to register to attend Kscope12 at the Early, Early Bird Rate, which runs through October 15.

Your input is needed

2011-09-07T07:17:00.001-07:00

It is hard to believe that abstracts for KScope12 are already due. Seems like the conference was only yesterday. This provides two opportunities for you.

1. Submit an abstract to present. If your abstract is selected with you as the presenter, you get a free pass to the conference. You still have to pay your own travel, hotel and meals, but this is quite a savings for you. you can submit your abstract at Kscope12 content.

It is a good idea to read the hints on abstract submission on the page, it will help make your abstract stand out so it is selected.

2. We hear every year “I wish there was a session on XYZ” well, even if you don’t want to present here is your chance to get the session you want selected for the conference. Just go to Kscope abstract suggestions and put down your ideas. This is the conference for you and we want it to have sessions that you want to hear. Don’t worry, we will go through them and find suitable speakers.

Don’t wait, do it today as this is a limited time offer. If you act today, you get the satisfaction of knowing your ideas will be heard.

KScope wrap up

2011-07-05T15:09:00.001-07:00

Well after a few days of rest I have finally recuperated from KScope. If you missed the conference, you missed a lot. The conference for me started early Saturday morning where I joined about 80 other volunteers to refurbish a Boys and Girls club in long beach. Eager volunteers cleaned painted and set up a wireless computer network. The painting included, walls, doors, railings, pool tables, basketball courts and other various things. It only took a week to get all the paint off of me.

Sunday started off strong with the Oracle symposium. I’m not at liberty to speak about what was said there do to safe Harbor and Oracle asking that we do not blog about it. I can say the lineup of superstar presenters from Oracle was well worthwhile. The roadmaps for Hyperion in general and in depth for Essbase, Smartview, HFM, and other products was informative. I have to say, I have been on the Smartview beta and have seen the enhancements to it and am impressed by them. After the symposium, was a reception where I got to meet lots of attendees. Following that was dinner and work on the Monday midnight madness event. I think I got to bed at around 2:00 am.

Monday started off with an opening session where we were entertained by a series of reality show clips from the ODTUG board competing to see who would pick the location for the next Kscope conference. In the end Mike Riley won (I think it was fixed) and the new Marriott resort in San Antonio was selected. I think next year’s conference is intended to be more family friendly as the resort has a water park and other family type events. I’ve heard rumor of movie nights in the water. The opening also featured a Speaker Joey Asher on the topic “Even a geek can speak” talking about how to make presentations better. I came away from it with a couple of points I used to improve my presentations and one I now know never to use. Never, never, ever do a live tweet poll with a bunch of geeks.

After the opening session, it was time to get to work. For the last three years I have had presentations during the first and last sessions of the Kscope conference. I’m not sure why. This year I was the co-presenter on a session “introduction to Essbase Studio”. It went well, and was followed by a session I did on Advanced Studio tips and tricks. I polled the audience and 90 percent or more had never used Essbase Studio. It meant I had to explain a lot more what I meant as I was describing the tips. I did not get through all of the tips I wanted to give, but the ones I covered were the most important and I think people got a lot of good information.

While I wanted to attend sessions in the afternoon, I ended up being scheduled to do a book signing. It was the weirdest experience having people wanting me to sign their Essbase Studio books. Early evening was spent updating some presentations for another presenter then it was off to the Hyperion Monday Night Madness event. Yes it went from 10:00 pm to about 11:30 and we had about 150 people in the room. I would like to tank Ron Moore from MTG for sponsoring the event. The desserts were delectable it was a fun time. The SIG spent Sunday surveying the EPM attendees at the conference and so Monday night we did a Family Feud game. If you missed it, I can’t describe how fun it was. Let me just say after one question was answered, it took me 7 minutes to stop laughing. Yes someone timed me. It was an event not to be missed. I finally got to bed after that around 1:30 (see a trend starting?)

Tuesday, I started off the day with a session titled. “The exception to the rule in Essbase Design”. As I was presenting, I noticed Carol Crider from Oracle in the audience. I had never met her in person and she is someone I Idolize. She knows more about Essbase than anyone I know. It actually shook me up and I became a babbling idiot trying to present. If you did not make the presentation, I am doing it this Thursday (July 7th, 2011) as an interRel webcast. If you are interested in attending, contact Danielle White Dwhite@interrel.com to get connection info.

After this session, I was on a panel with the smartest from interRel answering different product questions. After a short break, it was yet another panel during lunch for heated discussions and opinions with other Oracle Ace Directors in the EPM/BI space. People enjoyed us battling out our positions about various questions. After that I sat in on three of Edward Roske’s sessions. I knew he had not slept since Sunday and it was interesting to see him slowly melt down by the end of the third session. Even in his altered state, he presented some great info. Tuesday night I skipped the interRel client event. I missed the hula dancers and fire jugglers to attend the Oracle Ace dinner. I met and talked with the cadre of Oracle Aces and Ace Directors who were presenting and attending the conference. I think this conference is only surpassed by Oracle Open world in the number of Aces and Directors who attend.

Wednesday was an “Off” day for me as I did not have any presentations . I attended sessions on a variety of topics. Two sessions that stood out were a panel session with Oracle Support helping to demystify how to get the most out of support and support answering questions. They were not technical questions, but more general questions on how to most effectively work with them. From the session, I was asked to email one of them to follow up on a problem I am having and am happy to say I’ve already gotten a response. It is not the answer yet, but a good beginning. I think the best session for me of the week was a session from Steve Liebermensch (Steve forgive me if I butchered your name) from Oracle on “Procedural calculations on ASO”. Steve assumed everyone in the room had heard about procedural calculations before and did not go over the basics but rather took the approach of showing how he optimized procedural calculations at a customer. Wednesday ended with the the closing night party on the Queen Mary. I actually wore a tux. You could call me a well dressed nerd. On the ship were too many things to do in the short amount of time we were there. The food was varied and good, there was a dueling piano bar, blackjack and poker for prizes, a band and what I went to. Don McMillian and Engineer comedian who uses power point to convey his points. He had two shows with different material and I attended both. He was really funny, especially for the geeky crowd listening. The night ended with a special guest artist Young MC. A rapper. I’m not really into rap, so it was ok foe me.Following him was a huge fireworks display and then the bus back to the hotel.

The final day of the conference, Thursday, I was again up early for sessions. I was coerced into going to a session on Groovy and I’m glad I did. For anyone who wants to write using the Java API but is intimidated, Groovy makes it easy. It takes and simplifies Java. Thanks to Joe Aultman for a great presentation. I then went to a session on HPCM and although it was not well attended, I got a lot out of it. My final presentation of the conference was a a co-presenter for Henry Robin from ConnocoPhilips. It was an interesting presentation of how they used LROs to add commentary to their applications. I was supposed to discuss other alternatives, but the audience was so enthralled by what Henry had to say, I didn’t get to say much.

With that the conference was ending. For some strange reason, some people wanted to take their picture with me. I guess they needed a new face for the dart board. I went back to my room, packed and headed home from one of the best conferences I’ve attended. While it was exhausting for me, I got a lot out of it and think others who attended did as well. If you missed it, you missed a great event. Start planning now for San Antonio. As a matter af fact, if you would like to present, you can already submit your abstract at kscope12.com

I look forward to seeing you all there next year.

My Kscope Schedule

2011-06-22T12:45:00.001-07:00

The Kscope 11 conference is less than a week away and I’m getting more and more anxious as it nears. For my groupie (That means Cameron), I thought I would share my schedule with you.

Sunday

Of course on Sunday, I’ll be at the EPM symposium all day long. During lunch and at various time during the day, you’ll see me wandering aimlessly with a clipboard talking to people. I’m doing some surveys and need as many people to answer them. If you see me stop me and force me to ask you questions. During the evening I’ll be at the opening reception where a few fun and surprising things will be happening.

Monday

Monday is a busy day for me.

11:15 am Oracle Essbase Studio in room 102AB. I am a co-presenter with Saundra Holloway from CareFirst. If I feel nice, I might give away a look smarter than you are with Essbase Studio book.

1:15 PM Advanced Tips and Tricks 11.1.2 Essbase Studio Room 103A

3:00 – 4:00 I’ll be signing books at the interRel booth. Well, I’ll be there. We will see if anyone wants a book signed.

10:00 PM, that is correct PM, I’ll be hosting the Hyperion Midnight Madness. Great deserts and a good time will be served. It is an event not to miss.

Tuesday

Of course there is no better way to end a late evening than to have the first session of the day.

8:30 am The Exception to the Rule: Essbase design Principles That Don't Always Apply Room 102AB

9:45am is the vendor presentation time. Edward, Tracy, a few others and I will be answering your deepest darkest questions in room 101B The session is titled “Ask interRel's Oracle EPM Guru's:more Fun Than A Barrel of Consultants” No I don’t name them so don’t complain to me

~~11:15 AM. EPM/BI Expert Panel Room 102AB. Similar to the interRel panel above, but with other experts in the field.~~ Corrected, it turns out I’ll not be on this panel so you get a rest from me

12:15-1:45pm EPM/BI Lunch and Learn. Room Promenade B & C. I’ve been asked as with a number of other Oracle Ace and Ace Directors to have a panel where you can get the answers to the questions you were not able to ask at the interRel session at 9:45 or the panel at 11:15. Bring your lunch and watch us scramble to figure out answers for you. looks like Tuesday is the day to ask your weird and troubling questions.

Tuesday evening I get to relax (yea right) and go to dinner with the Other Oracle Ace and Ace Directors at the conference. It should be a good time. At the same time interRel is hosting it’s client event.

Wednesday

I am sleeping all day long!!!! not really, but unless a tree falls on someone, I actually don’t have any presentations to do. There are of course a number of presentations I really want to listen to. Of course I’ll be at the mystery event on the Queen Mary. It will be interesting to see what is there.

Thursday

For the last two years, I have had sessions to open and close the conference. This year is no exception. I’ll be co-presenting with Henry Robins from ConocoPhillips . Sorry Henry that I added an S to the end of you last name. Of course I was referring to the infamous Henry Robin not Robins.

11:45am Commenting on Oracle Essbase Commentary at ConocoPhillips room 103C

After that I go home collapse for the rest of the day and work on multiple client projects that I will have neglected all week long.

Last chance to save money

2011-06-07T11:56:00.001-07:00

Kscope11 is almost upon us. I know my boss, came up with the Ksocpe bit, but I actually liked calling it Kaleidoscope better. I’m an old fart and don’t abbreviate everything. I’m not one of the cool kids, but I will be at Kaleidoscope. I’ve got 2 official presentations to do: Advanced Essbase Studio tips and tricks on Monday morning and The Exception to the Essbase Rule on Tuesday. In addition, I’m on a couple of panels where you can ask your questions. One with Edward and Tracy for interRel’s vendor presentation. Yes this year, the panel will actually happen. I’m also helping on a few other presentations, like the intro to Essbase Studio on Monday. so you will see me all over the place. It will be a busy week for me. Who said going to conferences was a boondoggle never went to this conference.
The early registration discounts for the KScope11 conference end on Thursday June 9th. After that the conference is full price. If you want to still save a bit more money register with the code IRC and save an additional $100. Even after the deadline you can save the $100 off registration.
Last year we had a great time at the Hyperion madness event on Monday night. Due to the time it conflicted with a lot of people eating dinner (who needs food anyway). We decided to move it back to 10:00 pm this year. This is the time we did it the previous two years. I’ve been told there will be a great dessert selection to satisfy your late night sweet tooth. Join us and be part of the madness.
If you are at the conference and you should be, look me up and say Hi. It is nice to meet the people that actually read my blog and follow my OTN and Network54 posts.

ODTUG Hyperion Sig Elections

2011-05-31T12:15:00.001-07:00

The announcement just went out that nominations are being accepted for the ODTUG Hyperion SIG board. There will be a change this year in how the elections are done.

First, instead of elections being held at KScope, the nomination period has been extended to July 8th. This change was made to allow those at KScope to think about becoming a board member and still having time to act on it.

Second, elections will be online. I have mixed feeling about this. While I think it will be more efficient, I actually think we will get less voters than when we did it on site at Kscope. Along with this change, in order to vote, you now have to be an ODTUG member. If you were not before the conference and signed up, you automatically became a member. It would be people who did not re-up their memberships that would be left out. Please prove me wrong and when voting time comes, cast your ballot for the people who will represent you on the Hyperion SIG.

Third, in order to prevent mistakes that have happened in the past, After the election the candidates will all be notified of the results and a general announcement will go out on or around July 29th.

I’ve posted the description and requirements below. I think there is a slight type, It says you have to be a paid member to be a candidate, It really should say you have to be a paid member to be a member of the board.

It is very important for the board to have client representation. That is why at least 50% of the board has to be from clients. If not, it would allow us vendors to run amok. I don’t know about you, but there is too much Mok around already. So if you are a Oracle client, consider running. The board needs new fresh input. The time commitment is really not bad, the board meets on conference calls once or twice a month and during Oct, Nov and Dec is the heavy work of selecting content for the next conference. After that, there is some work on newsletters and other projects to make the SIG better for the community.

If you are at Kscope this year, look me up and say hi. It is noce to meet the people who actually read my blog.

The ODTUG Hyperion SIG is currently seeking nominations for its board. There are three (3) open seats. 1 of these seats can be filled by a vendor/partner.
The board’s responsibilities are:
• Enable communication with Oracle Corp on direction and strategy
• Share information and experiences among Oracle Hyperion developers and database administrators
• Facilitate training and education among the Oracle Hyperion developers and database administrators (DBAs)
• Provide representation for the Hyperion SIG at Kaleidoscope and Oracle Open World
• Develop the Hyperion tracks at Kaleidoscope
• Maintain Hyperion SIG’s web page on ODTUG.com with relevant and informative content to the development community
Board member requirements include: current paid member of ODTUG; regular participation in board conference calls; coordinating/contributing to meetings and conferences including ODTUG Kaleidoscope and Oracle Open World.
As a board member, one needs to support and promote the Hyperion SIG goals by taking an enthusiastic role in the activities that help continually provide value to the technical community. Giving a presentation at ODTUG’s annual conferences, assisting with membership and awareness campaigns, mentoring new members and encouraging other developers/DBAs to participate in the Hyperion SIG are just a few ways that board members can display leadership and contribute to the SIG's growth and success.
If you would like to help shape the future direction of the Hyperion SIG by joining the board, please submit the following information to doug_burke@paramount.com:
Name
Company
Title
Address
Email
Phone Number
Head Shot
Four to five sentence bio
Please be aware of the following rules/guidelines:
• Candidates must be a current paid member of the Oracle Development Tools Users Group (ODTUG).
• Elections are for individual volunteers, not the companies that employ them. The person elected will be a board member. It is recommended that individuals seek approval to run before submission as a candidate.
• Board will consist of nine members with no more than 50% from employees of Oracle Vendors (50% vendor rule).
• A board member’s term will last three years. At the end of their term, former members may choose to run for re-election after one year.
• On any given year, there will be 3 open positions on the board.
• If positions become open on the board during the year, board members may appoint members at their discretion as long as it does not violate the 50% vendor rule.
• Nominations will be open now until July 8th.
• Voting will be from July 11-25th on the ODTUG website.
• Results will be published July 29th.

Sometimes less is more!

2011-05-10T06:54:00.001-07:00

I have a client that has two Essbase instances, a test server and a production server. Both are running on VMs and use the same number of CPUs(4), the same memory (8 gig) run the same Windows 64 bit operating system, have the same cfg file, the same SAN, running at the same time with the same exact data and nothing else running on them. So why am I writing this? The test server was processing everything much faster that the production server. When I say much faster, I mean test ran a process in 7 hours while production ran in 13 hours. Neither is fast, but this system is very complicated with a lot of data loads, calculations, extracts and reloads in the process. We were dumbfounded by the differences. Something had to be different.

We checked everything we could think of and finally found it. On the test server, the virtual memory was set at 6 gig while on production it was set at 11 gig. The normal wisdom is that you set your virtual memory (system page file) at 1.5 times your actual memory. Interestingly enough, in the very old days of Essbase we used to crank up the virtual memory as high as we could. We did some experimentation and increasing the memory on test degraded performance and reducing the virtual memory on production improved performance.

Wow was this counter-intuitive! Apparently reducing the memory allowed the 64 bit operating system to handle the memory better on its own. When you use virtual memory, it is actually writing to disk, real memory has no IO associated with it. I’m guessing when we reduced the virtual memory, it forces the OS to use more real memory since it could not write it to disk.

We thought is 6 gig is good, maybe 5 gig would be better. We were wrong. For this server 6 gig seems to be the sweet spot. Does this affect 32 bit systems? I don’t know, but I do now know this can be an impact on 64 bit systems. This just goes to show that sometimes less is more.

I know everyone who reads my blog are cool kid (That’s why I’m not allowed to read my own blog), so I wanted to remind you that KScope11 is fast approaching. As I write this it is only 47 days away. I also know you have all registered for the conference already. You didn’t? what are you waiting for? You want a deal, register and enter the code IRC and get a $100 discount on registration. Also if you are not already a member of ODTUG, you get a complimentary membership worth $99. That way you have access to all the cool stuff like papers, presentations and webcasts.

Why server file loads are quicker

2011-04-26T11:53:00.001-07:00

Most of us know that if the data file exists on the server (especially in the database directory) and we do a data load it is faster than if the file is on the client. But what do EAS and MAxL actaully do when the file is on the client machine? If you look at an EAS data load, when you start it, you see the box:

When the dialog hits 100%, in the message area of EAS, you will see a statement something like:
User selected data file ‘C:\temp\data.txt’ is uploaded to server for execution at location d:\Oracle\Middleware\user_projects\epmsystem1\tmp\tempfile9706446338807355961.txt
(your path will vary depending on what version of Essbase is installed)
EAS uploads (FTPs) the file onto the Essbase server then processes it. When the process is complete, it deletes the file. What is the implication of this. Aside from being slower that having the file on the server, it is possible that aborted loads are leaving dead files in the temp directory. I had a system where the number of files and space consumed became significant.
Talking about slower, at a client I’m at, I tested the differences in load time. I’m connected over a VPN connection and have a data file that is 65 meg. For each test, I loaded into the same empty ASO database. I’m running the web version of EAS so it is on my local machine. Running the data load process with a client file took about 36 minutes.
Running the same file on the server in the database directory took less than 1 minute. 35 minutes of savings which is significant when you are waiting for processes to run.
To improve my time overall since the files were on my local machine, I would zip them up, copy them to the server, unzip them and then process them. In all I saved about 20 minutes off EAS’s client time. This showed me that the upload does not do any file compression when it copies the file.

Update note:
I've talked with a few people that have read this post and need to clearify. When I talk about a server file, I mean the file should be in the application/database directory on the server. This way EAS recongnizes it as a server file. The easy way to check if the system thinks it is a server file is to open up the load utility in EAS and select find file. Click on the server tab. IF the file pops up, it is in the right place (It should be saved as a txt file to make life easier).

While this will not affect most people as they are doing their processing on the server or in house on fast networks, it does help to understand what EAS and MaxL do when you have a client file to load.
This is just a single tidbit of the type of information you can get at Kscope 11. I’ll be presenting a couple of sessions. One on Advanced Essbase Studio tips and another one on the exception to the rules on outline design. I think both are pretty good sessions. Come see for yourself and let me know. I’m sure I’m going to get sucked into some other sessions , like a panel or two on Hyperion.
If you want to save $100 off of registration, enter the three character code IRC (which stands for interRel consulting). I’m not sure how long the code is good for, so don’t wait too long.

Essbase Studio book released (Finally)

2011-04-11T10:15:00.001-07:00

It took a bit longer than I expected, as the editors, especially Cameron Lackpour, found numerous little issues that needed to be fixed, but the Look Smarter Than you are with Essbase Studio book has finally be released to the general public. You can go to Lulu.com and just search for Essbase Studio books. It will be the only one or if you are smarter than I are, you can just click on the link Studio Book and go right to it. If you are at the Collaborate 11 conference, you can get it quicker as they have some copies in the bookstore.

You can even preview the first chapter of it without buying it (I think it is the funniest chapter).

Wolf, Wolf, Wolf 11.1.2.1 is HERE NOW

2011-04-07T13:54:00.001-07:00

Like the little boy who cried wolf once too much, I doubt you will believe me, but EPM 11.1.2.1 is now available. What’s that you say, you just looked at edelivery.oracle.com and its not there. True, but if you go to download.oracle.com and select Hyperion Performance Management and BI, you get to Here.

Notice it says 11.1.2.1.0 So I’m not crying wolf and it actually has been released. For those of you who were waiting till April 10th, wait no more. Go and upgrade my children.

11.1.2.1 We are getting close

2011-04-05T09:55:00.001-07:00

I can not take credit for being the first to notice, I give that to Sebastian Roux on the Network 54 forum, but the documentation for 11.1.2.1 is now available at Documentation link. The actual release can’t be far off.

The first thing I see in the readme is the upgrade path (I’m concentrating on Essbase here).

Release Upgrade Path From	To
11.1.2.0.x	11.1.2.1 Note that when moving from Release 11.1.2.0.x to Release 11.1.2.1, you use the "Apply Maintenance Release" option in EPM System Installer, instead of following the documented upgrade procedures
11.1.1.3.x	11.1.2.1
9.3.3.x Note: If you were using Performance Management Architect Release 9.3.1, you can download the Performance Management Architect Release 9.3.3 from My Oracle Support.	11.1.2.1
9.2.1.x	11.1.2.1
A release earlier than Release 9.2.1.x	If you are upgrading from a release earlier than 9.2.1.x, you must first upgrade to a supported release and then upgrade to release 11.1.2.1. For upgrade procedures for the earlier release, see the product installation guides for the interim release that you are upgrading to.
An environment that includes multiple releases, which can include an environment with one instance of Hyperion Shared Services or an environment with two instances of Shared Services	See “Upgrading from an Environment with Multiple Releases” in the "Upgrading EPM System Products" chapter in the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

Note: If you are starting from Release 9.2.0.3+, 9.3.0.x, 9.3.1.x (except for Essbase 9.3.1.4.1, 9.3.1.5, 9.3.1.6, and 9.3.1.7), or 11.1.1.x, Oracle recommends that you upgrade to Release 11.1.1.3 as your interim release. If you are starting from an earlier release, Oracle recommends that you upgrade to the highest level release that directly supports upgrade from your starting release.
Security Synchronization between Essbase and Shared Services was removed in Essbase Release 9.3, starting with Release 9.3.1.4.1. Essbase and Shared Services Release 11.1.1.3, however, still synchronize security information. For this reason, if you are using Essbase Release 9.3.1.4.1, 9.3.1.5, 9.3.1.6, or 9.3.1.7, you must first upgrade all products to Release 9.3.3, instead of Release 11.1.1.3.

As time permits, I’ll go though some of the interesting enhancements (don’t seem to be many) and the bug fixes.

Not an April fool’s joke. 11.1.2.1 release date

2011-04-01T08:46:00.001-07:00

While I am writing this on April 1st, It is not intended to be an April fool’s joke. Edward Roske had blogged that the release of 11.1.2.1 was supposed to be sometime in March. Edward's Blog Post March is now history and there is no release. So Mr. Roske, WHERE IS IT? (Of course I’ll blame my boss for something he has no control over). I think this might be a career limiting move.

Is the release vaporware? No not really, it is just taking longer than expected. I’ve heard from a few reliable sources (to be unnamed) that the Release is targeted to be April 10th. I’ll put the typical and necessary disclaimer here. This is just what I have heard and not official and of course subject to change. So at 12:01 on April 10th, the thousands of people who read my blog (Ok Cameron and three others) will descend on edelivery.oracle.com to prove me wrong. We will see.

Websphere support in 11.1.2.1?

2011-03-15T21:13:00.001-07:00

While 11.1.2.1 has not been released yet, I was looking through Oracle support to see if I could sneak a peek at anything about it. I found a Statement of direction for Websphere support in a document dated 04-Jan-2011. It says:

“Statement of Direction
EPM System release 11.1.2 did not include support for IBM WebSphere Application Server. This was done only as a short-term and temporary condition. Current plans call for full certification of IBM WebSphere Application Server with EPM System release 11.1.2 Patch Set 2, with availability planned for 2HCY2011. Please note that customers who have deployed EPM System release 11.1.2 or 11.1.2.1(when available) will be able to apply this patch set to those releases.
Oracle maintains its commitment to supporting Hyperion Enterprise Performance Management System on the broadest spectrum of software and hardware platforms. It is expected that IBM WebSphere Application Server as well as IBM DB2, AIX and Tivoli Directory Server will continue playing an important role in this regard and will continue to be supported in our roadmap of EPM System releases.”

So those of you who have been waiting for the patch, it does not appear websphere will be supported – Yet. I’ve heard rumor that 11.1.2.2 won’t be available until late this year (and based on how long it has taken 11.1.2.1 I would not hold my breath waiting for it). I am going to predict that 11.1.2.1 will be released late this week or early next week, but it is just a guess.

When the patch comes out, I’ll try to review the important fixes and enhancements in it (aside from the ability to migrate apps).

Kaleidoscope Early Bird Discount is Expiring

2011-03-09T10:41:00.001-08:00

On March 25th the early bird discount for Kaleidoscope will expire. I know a number of you are “saying big deal, it always gets extended” or “its only a little bit more after that”, but it can be a big deal to register early. I’ll tell you why.

First, if you register before March 25th, you will save $300. If you use the partner code for interRel IRC you can save even more. I don’t know if the IRC code will offer a discount after March 25th. Why take the chance.

Second, The Hands on Labs are now open for registration. In a meeting yesterday, I was told the Hyperion related labs are already at 60% capacity. Signups are on a first come- first served basis. If you register now, you can also sign up for the labs. Going to a lab is like going to a free class. This IS a big deal. Think of how much you save over going to classes.

With the Sunday symposium, the sessions, the Hands on Labs, the Hyperion midnight madness (10pm Monday night). the social events, and being in beautiful downtown Long Beach in June, this is a conference that should not be missed.

Some of you are saying, “This conference is too technical for me”. I will agree that the conference has some of the best technical presentations of any conference, but we have expanded the scope of the conference to include sessions for the less technical users. You can see what is being offered at sessions and symposiums So bring your HFM and user compatriots.

Heck, it is so close to Southern California attractions like Disneyland, Knott’s Berry farm and beautiful beaches come to the conference then take the family on a mini vacation. Think of the brownie points you will earn from the kids.

You can register at Kaleidoscope Registration I look forward to seeing you there.

Oracle listens

2011-02-15T10:44:00.001-08:00

Recently, I noticed a new Oracle forum title Documentation.It is under the Business Intelligence Foundation (just like Essbase and Smart View, so I figured it was for the BI product line.

I went into this forum and noticed there were no postings. Looking at it further, I discovered it is a forum monitored by the development and document teams to report errors in documentation. The forum can be found at http://forums.oracle.com/forums/forum.jspa?forumID=1319

Not being shy, I decided to post to the forum with a few things I’ve found in Essbase Studio. I submitted my post on Jan 24th and promptly forgot about it. On Feb 1st, I got a notice that someone had posted to the thread. It was one of the document managers who said he was forwarding it to the EPM document team.

Today(Feb 15th) I go a notice that the thread was updated again. I looked and the document acknowledged my comments and were correcting the documentation. They ever told me what the changes would be.

I am impressed, not only did they listen, but they took action and provided feedback. This means we don’t have to live with errors and omissions in the documentation any more.

If you find an error in the documentation, report it so the rest of us don’t have to spend days figuring out what you already have. They are listening to us, so raise your voice so the doc team hears.

Please note, This forum will not answer technical questions as there are other forums for that. They are just concerned with getting the documentation right.

Technorati Tags: Oracle documentation,EPM documentation,Essbase,Documentation

Essbase Studio Book

2011-01-26T16:54:00.001-08:00

I have been quiet for a while on my blog and there is a reason for it. I have been busy working on a new book titled “Look smarter than you are with Essbase Studio” It is a companion book to the Look smarter with Essbase series. I have completed the writing and it is now off to the editors to make it coherent and to obliterate my attempt at humor. Like (or unlike depending on your viewpoint) the other Look Smarter books, I attempted to make the book a fun read. I of course am prejudice, but I think it came out pretty good. I am hoping it will be GA around the first of March from Lulu.com. I’ll post again when it is.

One or two of you who read my blog have seen snippets of the book and I had favorable feedback about it. When it is released, I’ll ask a few of my fellow bloggers to give honest reviews of it. I won’t even pay them(not that I ever would).

I will say if anyone asks you to write a technical book, turn and run and keep running. I can’t believe how much harder it was than I ever thought it could be.

On a related note of Essbase Studio. At Kscope11 I will be presenting a session on Essbase Studio Advanced topics and have been asked to co-present on an intro to Essbase Studio session. So if you bring the book you will have bought by then, I’ll sign it for you. It won’t make it worth any more, in fact it will most likely lower its value, but I will have gotten my 15 seconds of fame. Coming to get you book signed is worth the cost of the conference alone not to mention all of the great sessions for more talented speakers than me.

Fragmentation in ASO Cubes

2010-12-16T23:55:00.001-08:00

It seems Everything old is new again. For years we have been defragmenting BSO cubes by exporting the data, clearing the cube and then re-importing the data. When ASO came along, I thought “Great since there are no blocks I won’t get fragmentation and don’t have to worry it any more” . Turns out I was wrong.
I was at a client and needed to make a copy of their 50 gig production cube onto their test system to do some work with it. I exported the data, copied the outline over to test and re-imported the data. I looked at the file system during the load to see how far along and was surprise when the cube stopped loading the .Dat file was only 9 gig. I ran the materialization of aggregations using a saved aggregation from query tracking and the cube grew to 25 gig. About half of the size of the production cube.
There are a couple of things to know.

During the year, the client deletes some data out of the cube by deleting the members (old years)
The cube contains no slices
The cube gets an incremental update multiple times a month by clearing out the current Month/Year and reloading the data
The cube has been in production for about a year and the outline gets new members added on a regular basis.

Since this worked so well on test, we took the original exports and cleared the production cube and its size is now comparable to the test cube (we actually added additional aggregations which makes it a bit larger). OK lesson learned, the client will now be adding a process to periodically rebuild their ASO cube. I did not do a compress of the outline, but this is something else they can add to their process. Who would have thought!

ASO Dimension Deletion Limit

2010-12-09T07:34:00.001-08:00

Did you know there is a maximum number of times you can add and delete dimensions in an ASO cube? Neither did I until recently. I have a Essbase Studio model the was deleting the dimensions from my cube before rebuilding them. This was necessary because I have a number of shared rollups that can move around and the best way to deal with this is to rebuild the dimension.I t was working fine until one day it gave me error messages about not being able to add a particular dimension. I removed it from my build and the next dimension popped up with the error. After investigation, I found the error “1060190 Cannot Add Dimension – too many dimensions have been created and deleted”. It turns out the maximum number of dimensions you can have is 255 (or add and delete). Each dimension added is tagged with a dimension ID that increments. Once you reach that limit you are in trouble.

As a work-around, one solution is to create a “dummy” model. Using Studio, create an application from the same Essbase Model as your regular app but instead of deleting members first, select to delete and restore database. This will give you a fresh copy of the outline. Then stop your production application and copy the outline from your dummy cube over the production one. Since it was built from the same model, we can assume the dimensionality and members are the same. Starting the app should activate the new outline and you should be set to go. A couple of things to realize

1. There is no MaxL function to copy the outline, I do this through the file system in my batch file using copy commands

2. You should always backup the original outline and data prior to doing this. Remember even if you did the deploy directly into the production app, if members are dropped you lose the data. Safety first!

3. As always Test, Test, Test. Don’t take my word on it, make sure you don’t blow up your production application by listening to me or anyone else. Two particular areas to check out are Security and drill-through reports (in addition to data integrity)

4. While you should be able to get around this issue by not specifying delete all members, and just doing an incremental rebuild on the specific dimensions, I encountered problems doing it, thus I went the delete members first route.

5. Associated with #4 *nix systems have some issues with the deploy command causing issues.

A second solution would be to export the data (now that you can in 11) delete and rebuild the outline then import the data and re-agg.

Someone asked me if this is also the case for BSO cubes and I can’t say that I know. I’ve never encountered the issue on a BSO cube, but then again, I have not used the Studio Delete members first as much on a BSO cube.

ODTUG Election results

2010-11-03T11:17:00.001-07:00

For those of you who have been following my pleas for you to vote in Hyperion representation to the ODTUG board, I want to thank you. The election results are in and the Hyperion community is now represented by two people on the board. Interestingly the elections closed on Nov 2nd . I guess this was so the results could be announced with the other minor elections that are taking place across the contry. The 4 new board members are :

Mike Riley, Hortica Insurance
Tim Tow, Applied OLAP
Cameron Lackpour, CLSolve
Barbara Morris, Idaho National Laboratory

Two of these Tim Tow and Cameron Lackpour are from the Hyperion community and Mike Riley has been an ardent supporter of Hyperion. He is the one that invited the Hyperion group to Kaleidoscope the first year.

They will be joining the following continuing members on the board.

Bambi Price, Park Lane Information Technology
John King, King Training Resources
Mark Rittman, Rittman Mead Consulting
Monty Latiolais, SCGTS

Congratulations to all. It looks like the board is stronger than ever. I look forward to the coming year.

Let it Flow, Let it Flow, Let it Flow!

2010-11-02T15:06:00.001-07:00

I included in my MDX presentation for Kaleidoscope this last summer a few pages on the Flow property you can apply to the accounts dimension. This applies to ASO cubes only. There is some good documentation on it, but interestingly I don’t remember seeing it in any of the new features documents or presentations for 11. I guess they slipped in in on us without us knowing. There may be reasons for this and I’ll get to them later.
What the flow command is supposed to do is simplify and optimize calculations where a recursive formula might be needed. Until now, it was all academic as I didn’t have a need to try it. I found a good and typical use for it. I’ve built a ton of GL applications (if each application weights an average of 21.7 lbs and you can calculate how many) and in almost every one, I have to create a formula in the retained earnings section of the balance sheet to represent the year to date Income.
For my client I created a typical formula something like
Case When ISLeaf([Periods].currentmember) then
Sum (PeriodsToDate([Periods].Generation(1),[Periods].currentmember),[Income])
End
and it worked just fine. Then I thought about my presentation and figured this would be a good place to try out Flow. I replaced my formula with
[Income]
and changed the member type in the outline to Flow (I left it as TB last) and saved the outline. It worked like a charm giving me the desired results. It is a much simpler formula and since it is optimized, I’m sure it is faster than my manual statement.
This is great. So I went to incorporate it into my Studio process. There is no option to set flow. Next I went to the DBAG to see how to set it in a load rule, not there. So thinking swiftly, I decided to contact my friend Tim Tow to see if he encountered it when working on the Outline extractor. Tim did some research for me and found it in the Java API but not the VB api. Tim did say he was going to update the Java version of outline extractor to include this.
So it appears you can set it in the outline but not easily from anywhere else. I’ve got questions out to some people at Oracle, but until I hear more it appears you are limited to manually setting this property. It looks like flow is ahead of its time. Until you can Flow changes to the outline automatically, you are limited in using it.

Update
I got a response back from Oracle and the ability to change this from Studio will be added to a future release. I do not have privy to what release that will be. So for now, from within the outline is the only way to set this

Essbase Studio/ODI issue, KScope abstracts and more.

2010-10-25T13:53:00.001-07:00

I’m working with a client that is developing an application through Essbase Studio to get Drill-through capabilities, but most of the data will get loaded from ODI. We ran into problems with ODI where if the model was created in Studio, ODI lost the ability to reverse the model. It turns out to be a bug that Oracle is working on for the next release (It turns out this issue also occurs in EIS and ODI). As a work around Oracle suggested the following and it seems to work.

1) search for cpld14.jar in Essbase home
2) then copy the jar file to ODI Home oracledi\drivers folder, if you use an ODI Agent to run the reverse, you'll need to make sure
the jar file is copied over to the Agent's oracledi\drivers folder
3) then restart ODI or ODI Agent
4) retry the Reverse and it should work.

As everyone else in the Hyperion world has been telling you, the deadline for abstracts for Kscope 11 is TOMORROW. If you have not gotten your abstract in, you are running out of time. Who doesn’t want a free pass to the best conference for Hyperion topics? submit now, jsut go to kscope11.com and click on submit abstract.

Finally, another ODTUG piece of business, The ODTUG Board of Directors election ends on November 2nd. Don’t be apathetic, these are the people who set the direction for ODTUG. Let the Hyperion voice be heard and vote. Just go to ODTUG.com and log in.

Exciting ODTUG and KScope11 News

2010-10-13T16:17:00.001-07:00

I just heard (ok yesterday, but I didn’t have time to blog) that if you register for Kscope11 and you are not currently an ODTUG member, you get a 9 (YES NINE) month ODTUG membership free. This is only for new members to show you the value of being part of the ODTUG Family. So go out and sign up for the conference early to take advantage of the membership benefits. This includes the NEW Hyperion newsletter with tips and tricks, and interesting information, Discounts and more. So why wait, take advantage of this great offer.

Also remember, the deadline for submitting abstracts for the conference is fast approaching. It is Oct 26th. If your presentation is selected, you get an ever better deal, a free pass to the conference. I don’t think you get the free membership, but you certainly get a huge benefit. This year the conference will be accepting some case studies and overviews in addition to technical presentations. The conference is trying to expand to have a home for some of the users in addition to techie types. So if you have an interesting topic, submit it, you have nothing to lose. If you have a topic you would like covered at the conference but do not feel qualified to present it yourself, leave me a comment and I’ll bring it to the committee and we if selected, we can find a qualified presenter for it.

Technorati Tags: ODTUG,Kscope11,Kaleidoscope

Oracle Open World Day 4

2010-09-24T09:54:00.001-07:00

I’m actually awake and in a session at 9:00 am. My first session (maybe my only) is on Hyperion Profitability and Cost Management.(HPCM). The presentation was very good, but went quickly and I’ll try to hit the highlights from it.

HPCM is is an application that allows you to allocate costs to customers based on drivers. IT has multiple components including relational and Essbase.

Multiple skill sets necessary to build HPCM

Tool knowledge
Business Analysis for determining methodology
ETL skills
EPMA
Essbase skills

What is a allocation methodology

A defined process to Allocate costs

Cost Object

Basis of allocation – Drivers

Assignment – logic for assignment

It is important to have a Allocation Methodology before you start. Standardize the methodology as much as possible. You need internal agreement of business requirements before you start. .

Model Design

Defined as turning a logical design into a physical environment

Stages (where you are bringing data in
Drivers
Assignments

Model Design is a iterative process.

Many different approaches
Determining factor
importance and priority of “things”

Rules

Leverage the underlying Essbase database to do the heavy lifting.

One rule can do multiple allocations
Ease of development, maintenance and understanding
Consistency of applied logic
Dramatic performance improvement

Some overlooked gems.

HPCM provides staging tables where you can

import dimensions
Driver data
dimensions
Cost data
per existing model definitions

aids in assignment process. Importing to getting up running quickly. Pre-apply consistent logic to speed up allocations.

Testing

Out of the box validation reports are indispensable. Use them to validate your model. (My note, This is an important differentiator. The logging and traceability of calculations; the next bullet point,is something not easily done in Essbase or many other products)
Graphical tracing maps use as a development aid to verify your model(must load data to take advantage of graphical maps)
Dense sparse settings can be used for impacting performance but requires knowing your data.

Training

Take HPCM training early
Best training is hands on testing/ parallel testing
Focus on functionality not mechanics
Develop initial reports for end user then educate on metadata
- remember end users care more for reports than the model that produces the reports
- In the users mind good reports translate into good model
Leave enough time to test

My session on LCM is us soon, so I’ll spend a little time reviewing the slides.

I just finished my session on LCM. For those of you who missed it, I believe it will be on the interRel webcast series in October. It was a small but interested group. I had the best timeslot. Thursday after the concert, after lunch. I was actually amazed when people came to the session.

After my session, it was a dash to the airport. I really do like the Bart system. It is fairly efficient and reasonably priced. I made it with plenty of time. So I bid a fond farewell to Oracle Open World 2010. It will probably take me the full year to recover and prepare for next year.

Oracle Open World Day 3 (Part 2)

2010-09-22T12:58:00.001-07:00

Financial Reporting 11.1.2 tips, Tricks and New Features presentation.

Tip 1. Create a rolling report. Use Range and Relative member functions along with CurrentPOV. Not a new tip, but useful none the less. It uses the Range of first period to the current period for then current year and current period to last period for prior year (or offset from the current period) for something other than a 13 month rolling report

Tip 2. Use conditional formatting to suppress rows or columns.

Feature Annotations. Users have the ability to create. reply to and remove annotations. Supports administration. IT is also available from Workspace. It uses a little icon to show the annotations and similar to Linked reporting objects it pops up a dialog to create or manage them. It was also told that Cell Text from HFM and LROs from Essbase are available as read only.

I’m afraid I had to leave part way through the session to meet with a client so I can’t comment more in it.

The rest of the day was spent meeting with people. If noting else Open world is a great place to connect with people.

This is the first year I actually attended the customer appreciation event. It was really interesting. Where else would you see Berlin, Black Eyed Peas, The Steve Miller Band and Montgomery Gentry on the Same Bill. The event was held on Treasure Island with an indoor and outdoor stage. Black Eyed Peas was definitely the main attraction with SRO. While it is really not my taste in music, if was an energetic and interesting show. IT really is more a show tan a concert. In addition to the concerts there was food. I have to admit that that part was not my favorite. hot dogs are not my favorite food.

I left back to my hotel around 11:30 and it took aver an hour to get there on the shuttle bus. Traffic getting off the island was jammed. I want to go to a 9:00 session in the morning. I hope I make it!

Oracle Open World Day 2

2010-09-22T10:07:00.001-07:00

Tuesday at Open world was a blur. I don’t know if it was because I was so tired from the prior two days or worried about my presentation. I present all the time, why do I still get nervous?

I was in and out of sessions during the day and have additional information on patches. It turns out the 11.1.1.4 (11.1.1.3 ps1?) will not be out till later next year. I think they are trying to get people to upgrade to 11.1.2.

My session was one I have done before “Top 10 optimization hints to give you 99% improvement”. It was in the Develop track sessions and had nothing in it that said Hyperion. So it was difficult for people to find. Because of that I had a small group. It got smaller when I actually started talking about what I was going to talk about. The people who did stay got some good information and I had some good conversations after the session. It is amazing how much consulting I give away for free at these sessions. I had a client Stephanie Shrake from Care first who had done a great job with Edward Roske on a presentation “What is Essbase and why do I care” in the session. I’m not sure if she was there for moral support or to see if I actually knew anything. but it was nice to have her listen in.

Between sessions, I spent time meeting customers and potential customers at the interRel booth.

The evening was spent at events and meeting with people. I read that there are over 41,000 people at the conference and it infuses over $100M into the local economy. The way vendors were giving parties and wooing clients I can believe it.

Open World Day 1

2010-09-21T09:56:00.001-07:00

This morning I spend as few minutes as possible walking through one of the vendor pavilions. Like last night’s keynote, the theme for the conference seems to be Cloud computing. There are so many companies talking about clouds you would there it was a meteorologist convention. The second theme is iPad. Every vendor seems to want to get your attention by exchanging your contact information for a chance to win one. The will also give you junk to fill your suitcase to take home to give your children, decorate your cubical or throw into a drawer to never be seen again.

I did spend some time working with and for a couple of clients and spent a bit of time in the interRel Kiosk. It’s nice, the Kiosks are right outside of the Hyperion session rooms and we don’t have the pain of being on the main floor talking to people who have no interest or knowledge of Hyperion.

The first session I sat in on was “Introduction to Oracle's Hyperion Performance Management Applications” In the session the integration of different products was discussed including disclosure management and HPCM. I have to admit I did not pay as close attention as I should have as the discussion was mainly on 11.1.2 features and not the roadmap, but it was a good presentation.

Next I was sitting in the Planning features session and just heard the 11.1.2 patch will not be out until January (“December if we are lucky”) so for those of you waiting for the patch to be able to upgrade current applications. There was so much content, the speakers spoke so quickly, I could not capture much of the roadmap. But some things I heard were in the 11.1.2.1 (Now called 11.1.2 PS1) you can upgrade from 9.3.1, 9.2.0 9.3.3, 11.1.1.3, 11.1.2 but if you want to upgrade from from 9.3.0 you have to first upgrade to 9.3.1. Other features I saw were better display in text on forms, new roles, dynamic owner assignment, and more that I could not get. 11.1.2 PS2 will include an approvals dashboard. Sorry guys it was the end of the session and the speaker zoomed through the slides.

OK, today seems to be roadmap day. The last session I sat in on was features of Hyperion Applications. There is so much overlap in these roadmap sessions. I think everything I heard was talked about in prior sessions of the day. Big changes to Task management in Planning, Essbase application link for HFM to planning cubes, The ability to spin an ASO cube from Planning with text measures becoming dimensions, Oh wait, something new, HCPM micro allocation. The ability to do allocations down to a extremely low level in HCPM. The example was cost of doing business brought down to the customer/product level. I am sitting through a HPCM session later in the week. Hopefully I’ll hear more about this.

In between sessions, I worked at the interRel booth answering questions and meeting customers and people looking for clarification and answers. At the end of the day, I went to a joint client appreciation reception between interRel and our friends at Star Analytics and Dodeca. Two companies that truly have innovative products for the Hyperion community. It was nice to talk with customers I’ve met before or had the chance to meet. NExt I went to the OTN (Oracle Technical Network) appreciation event. In the crowd of people I actually met people I knew and we had an interesting discussion of the ODTUG elections that now underway. I won’t bore you with the details(oh wait, if you read this far , you are already bored) The discussion was around the real need to have a balanced board and that means getting Hyperion people on the board. This was from a non-Hyperion person. I couldn’t agree more as you can tell from prior blog posts.

After that a ride back to my hotel on the bart where I quickly fell to sleep from fatigue of all of the events from the prior two days.

Tomorrow round three.

Oracle Open world Day 0

2010-09-20T09:32:00.001-07:00

I made it to Open World 2010 and promised to let you know what is going on. You may ask why I say Day zero. Well, Sunday is typically sessions sponsored by different user groups. I started out early with the Hyperion Customer Advisory Board (CAB) meeting starting at 9:00. Since I’m too cheap to spend a lot of money on a hotel, I stayed by the airport. I was going to take the BART into the city, but surprise. It’s Sunday and they don’t start early. So I had to cab it in. (Is still save money!). The CAB meeting was interesting, but I don’t know that I’m allowed to talk about what was said. So I’ll not say anything except, I was impressed. The number of development team members at some points outnumbered the participants. They were not just sitting there either, they were actively asking questions and probing every comment and recommendation we had. I truly believe they cared about what we have to say.

I left the meeting just a little early as I had a presentation to do. My first presentation was “An introduction to Essbase Studio” MY biggest problem is trying to fit hours worth of content into 1 hour. I think I did pretty well. I started with about 125 people in the room and while there was some attrition, I would guess at the end there were about 100 left. I guess they were being nice or they were the ones still asleep. Well maybe not as there were a number of good questions. Since most of you have not heard this presentation, I will ask Danielle to get it on the list of interRel webcasts.

After my session, I walked from the Moscone Center to the Hilton to visit my Develop and Java World friends for some adult beverages (Ok you got me, you know I don’t have friends and I don’t drink much) but The appetizers were good and I stood watching one of the Keynotes. It was supposed to be Larry Ellison, but most of what I saw was a dog and pony shoe from HP. (can you tell I was not interested?) I do find it interesting that HP participated in an Oracle conference now that Oracle bought Sun and competes with them on Hardware. They talked about their Cloud Computing offerings.

Larry’s presentation started with a video of the Americas cup race. Larry started out talking about Cloud computing and what it is. Many companies are rebranding their old technology Cloud computing. He want through a list of things it might be. He touted Amazon’s Elastic Cloud as the most accurate description. A virtual environment.

Why is he talking about clouds. Because he just announced the Exalogic Elastic Cloud computer from Oracle of course. 30 Servers , 360 cores, VMs, Storage , 40 gigabit and all of the middleware all in the box the size of a refrigerator. To patch the system you download a single file. This box has no single points of failure. If I were HP I would be really mad. They spend a half hour talking about all of there great cloud offerings then Larry comes in

Well I’m off to an Oracle Ace event so so much for day 0

ODTUG News

2010-09-16T10:20:00.001-07:00

I received notification that the ODTUG board elections have opened. There are 14 candidates, 5of which are from the Hyperion community all for 4 open positions. Currently, there is no one on the board representing the Hyperion space, so in order to get our voices heard, we need you to vote for 4 of the Hyperion candidates. The 5
Hyperion candidates are:

Eduardo Quiroz

Angela Wilcox

Cameron Lackpour

Tim Tow

Juan Porter

The elections go until Nov 2, 2010. In order to vote you have to be a paid ODTUG member. What you are not a member, well have I got a deal for you. During Open World (Sep 20 –23) individual memberships have been discounted to $99 That is a $76 dollar discount off of the normal rate. (BLOG CORRECTION. I was informed by Edward that you Save $150 on Kscope registration. Even better) Just go to the website ODTUG website and sign up during that time frame. In addition to discounts on the Kscope conference (you basically get your membership fee reimbursed) , ODTUG membership offers you the technical journal, multiple conference opportunities, Webcasts and past presentations. It is well worth the price.

Make sure the Hyperion voice is heard on the board. Vote early and vote often (Ok you can only vote once).

Oracle Open World is fast approaching

2010-09-03T10:17:00.001-07:00

I just finalized my schedule for Oracle open World (OOW). I will be blogging on the sessions I attend for those of you who won’t be there. In addition to attending sessions, I’ll be doing a number of other things. On Sunday, I’ll be attending the Essbase Customer Advisory Board meeting as well as other events. I’ll make occasional appearances at the interRel Booth to help out when I’m not doing other activities. When I signed up to go to OOW, I was just going to go as a participant, but I then picked up a session to speak on. Today I found out I have two more sessions I’m speaking at. If you are going to be at OOW stop by my sessions and say hi. Below is where you will find me. (why do I always get last day late sessions? ). My schedule is quickly filling up, I think I’ll be busier that I first anticipated, It is typically a very long week.


Sunday	4:00pm-5:00pm	Introduction to the New Oracle Essbase Studio	Moscone West L2, Room 2008
Tuesday	2:30pm-3:30pm	Top 10 Oracle Essbase Optimization Tips That Give You 99% Improvement	Hotel Nikko, Bay View
Thursday	1:30pm-2:30pm	Implementing Effective Configuration Management on Oracle EPM’s Platform	Intercontinental San Francisco, Ballroom C

Kscope 11 Abstract submissions open and quiz answer

2010-08-24T14:31:00.000-07:00

I got in my email today that the kaleidoscope 11 submissions are open. (they have actually been open since the conference). This is the most awesome conference and if your submission gets accepted you can go for free. The tracks for Hyperion content have been expanded and there will be a more diverse set of topics. In the past three years the presentations have all be very technical. The conference is broadening its scope and will have some case studies and more business related topics as well. This means you don’t have to be a technical guru in order to present. In fact, the conference is looking for a lot more customer (versus consultant) presentations.

If you want to look at the topic categories to choose from you can find them at: Kscope 11 Abstract page.

If you want to submit a presentation, there is a link on that page or you can go directly to Kscope Abstract submission .

The deadline for submissions is October 26, 2010 so you don’t have a lot of time. Put on your thinking caps and come up with an interesting topic. If there is something you would like to see presented at the conference but don’t feel you are the right person to present it, send me a comment and I’ll get the abstract added to the list and help to find a presenter. Or better yet find a friend, co-worker, friendly neighborhood consultant that you like and admire (no not me) and get him/her to submit it.

A few weeks ago, I put out what I thought was a pretty easy quiz. Perhaps it was too easy as I only got three responses to it. The first response was to my faithful Man in Philadelphia Cameron Lackpour who got almost all of the quiz answers correct. At least the answers I was looking for.

The first question was list two ways to display numeric zeros in SmartView retrieves. Cameron correctly answered #NumericZero and (0) (note in System 9 just entering a zero worked in some versions. I don’t know when that changed).

The first bonus question asked “How he can take non-numeric data in Excel and show it in formulas as a zero without using an "IF" statement” The answer is to use the syntax N(cell reference) like N(A3). This takes and turns the value into a numeric. Unlike Value() which Cameron described. The N() syntax turns non-numeric data into zeros so you don’t get #values in your formulaic cells. This is not a Smartview or Add-in solution, but an Excel solution. I amaze even the most prodigious Excel users with this one when I show them it.

The second bonus question asked “How can you have the classic Add-in return numeric zeros?” The answer is the same as part of the SmartView answer using the syntax (0) will bring back real zeros from the classic add-in.

Finally the last bonus question was “Why can this be a bad thing?” In the classic add-in and some versions of SmartView, numeric zeros will be sent to the database replacing your #missing with zeros. This will bloat your database, especially BSO databases and make your calculations run longer and the database consume a lot more disk space.

Time for a quick Quiz

2010-08-12T08:41:00.000-07:00

I finished up presenting three sessions at the DOUG semminar in Dallas on Tuesday and my voice is shot.I talk way too much. I was amazed at how many people were there. I'll guess over 100 just to hear me talk. Well actually to hear Edward Roske, Tracy McMullen and me talk. The biggest draw was Tracy's track on OBIEE. It seems to be the hot ticket right now. In all I think it was a very good day and people got a lot out of it. I only stayed for a little of the Rangers/Yankee game that followed the session as I had a long drive back to Austin. Thank you interRel for hosting the event. (Is this a shameless plug for the company or me just being nice?)

It has ben a while since I have done one of my quizes so I thought I would give you an easy one.
Bob is a real zero in life, but he does not like missing anything.. He loves the little round orbs that are nothing. But when he tries in smartview 11 to have his missing values display as zeros he has problems. Can you tell Bob two ways (without doing find and replace) that he can have true numeric zeros in a smartview retrieve. As added bonuses
How he can take non-numeric data in Excel and show it in formulas as a zero without using an "IF" statement
Second Bonus, How can you have the classic Add-in return numeric zeros.
Third bonus, Why can this be a bad thing?

Persenting again

2010-08-06T11:01:00.000-07:00

This upcomming week I'll be presenting at the DOUG (Dallas Oracle User Group) meeting at the interRel office in Arlington. There will be three tacks
The Future of Reporting & Analysis , Oracle EPM 11.1.2 and Becoming an Essbase Guru

Although I am far from Guru, I will be presenting the sessions in the Essbase track. The topics include
Configure it out. , Load rules from intermediate to Advanced. (It used to be called from Basics to advanced, but I guess my basics are more intermediate to many people) and Preparing & Optimizing for 64-Bit Essbase. All really good topics.

If you want to see all of the topics in the other tracks or the schedule, you can check it out on http://memberservices.membee.com/538/irmevents.aspx?id=19 or contact Danielle White at DOUGBIEPM@GMAIL.COM

Stand alone installer for 11.1.1.3 Classic add-in

2010-07-07T14:05:00.000-07:00

Thanks to Sebatian Roux for his persistance on this. Oracle has realeased a "Service fix" for a stand alone installer for the classic add-in. It is available on Support.oracle.com. Log in, select patches and updates and type in the patch number 9147550. (note you do have to have a valid service agreement to log in)

Here is info from the readme:

This Readme file describes the defects fixed in 11.1.1.3.11, as well as the necessary requirements and instructions for installing this release.

This service fix provides a new MSI for installing Essbase Spreadsheet Add-in separately from EPM System Installer.

Thanks again Sebatian.

Kaleidoscope summary

2010-07-07T12:12:00.001-07:00

I am sitting on an airplane heading for home after a very busy 5 days at the conference and a few personal days braving a Washington D.C. heat wave. Not that you really care, but after the conference I took a couple of days off to view the sights of the city. My wife and I spent the 4th on the National Mall watching the fireworks behind the Washington Monument. It is a something everyone should experience.

About the conference. It was all the hype said it would be. Sunday started with the Oracle symposium where product managers gave us insight of things to be. Of course we are not allowed to blog about such things. Drats. Leave it to say that there was some interesting stuff.

On Monday the real conference began. In the opening session, it was announced that Edward Roske will be the conference chair for the upcoming year. The site of next year’s conference was announces as the Long Beach, Ca. Conference center, June 26th – 30th. So mark your calendars now. I understand if you register now, you get the lowest possible rate. Edward did say you can already submit abstracts for the conference. I don’t have connectivity now or I would blog you the site. Another announcement at the opening session was Hyperion SIG board member and all around nice guy Cameron Lackpour was awarded the best new speaker for his presentation last year. If you have a chance, congratulate him.

I started off the conference with my session on MDX practical examples and it seemed to go well. Over 80% of the room had little to no experience with MDX so I think the presentation was at the right level. As typical I had more content than time (even for a 90 minute presentation) so I was rushed at the end. I think I will talk to Danielle White at interRel and have it added as part of the weekly interRel broadcast series. I will either have to cut it down or do it as a two part series. I think the latter would be better. Later in the day, I did Edward Roske’s presentation on ASO optimization. It was like trying to swim through jello(umm red strawberry jello with whipped cream). He had so much great content, There was no way to get through it all. In the end, I ran out of time and did not talk about a few important topics like cache settings. Luckily for people who attended the session following mine, There was a lot of overlap of content and they were able to pick up what I missed. Monday “officially” ended with a session with the Oracle Aces and a reception following. We could have been answering questions for hours, but time was limited. There was a lot of great interaction. I did not see much of the reception as I was in the interRel booth meeting people and seeing old and new friends.

This year the conference had a room reserved for werewolf and there were games every night starting at 10:00 pm. IT worked out well. It was a full moon during the conference and the wolves came out in mass. we has multiple games going every night. Edward did not want to sleep Wed night so the game went till 4:30 am. I was lucky to make it to bed by 2 am most nights as I moderated games.

Tuesday came too early and fast. Luckily I did not have any presentations to do. I ended up doing some work for clients. I popped in and out of sessions all day long and have to say overall I was very impressed by the presenters. Most sessions had between 80 and 120 people in them (not including the labs). I did sit through Doug Pearce and George Cooper’s presentation on a case study on how GAP (The clothing company) used a combination of BSO, ASO, Varying attributes and text measures to allow users to track the uplift of promotions. (I hope I got that all correct). It was a very interesting session. I have worked with Both Doug and George in former lives and it was fun and interesting to watch their interaction. The presentation had good technical content on how they overcame problems, but I would have liked to have seen a bit more talked about on what text lists and Varying attributes are (I did mention this to Doug later).

Tuesday night was the interRel client event so I was busy. We had three (or four) Werewolf games going at once in a kill all competition. The winners of each game played in a final game for great prizes (don’t ask me what I have no clue).

Wednesday was a busy day. I did a presentation on Advanced Essbase Studio tips and tricks. There was a Essbase Studio Basics session going on a few doors down, but over three fourths of my room had no to little experience with the product. I afraid some of my presentation was over their heads. The information was good and when they start using Studio, it will help them but until then I’m sure I confused more than a few people.

I was the Ambassador for Cameron Lackpour and Markus Shipley’s (forgive me is I spelled names wrong) intro to ODI session. At Kaleidoscope, every session has an ambassador to introduce the speakers, record attendance and overall be a go-for, in case of problems. It was a 90 minute presentation and they needed every minute of it to get the information across. Cameron went into detail on what you have to do to create an integration and then Marcus gave a real world example of how his company implemented it, solving multiple problems. They went from days, literally days, of work to get data loaded into Essbase into multi-minute long automations. It was amazing to hear how much his users love the results of the project.

I later sat in on Edward Roske’s Hacking Essbase session. The room was packed with standing room only. Edward is a great and knowledgeable speaker. He could have spent a day just with this one session and answering the crowd’s questions. He promised a mush longer session next year.

Wednesday night was the Kaleidoscope event where John Heffron (the winner of last comic standing) entertained us all. I had never seen him before. I was laughing so hard, I had tears in my eyes. If you have not seen him, I highly recommend searching him out and listening to his work. Than k you interRel for co sponsoring the event so the conference could afford him. The other entertainment that night was a band that I was not impressed with. I' will not tell you their name as for me it was not worth remembering.

Oh one special note about the event. The volunteer of the year award was given out. This year it was not to a single person, but to the entire Hyperion SIG board for the amount of work they put in to make the week a success. Thank you Gary Crisci, Angelia Wilcox, Natalie Delemar, Cameron Lackpour, Doug Bliss Quinlann Eddy, Tim Tow, Jeff McAhren and Edward Roske. The executive board could not have made a better choice. (see my note below about board elections held)

At 4:43 am on Thursday, I got a text telling me, I was doing Edward’s 8:30 session on BSO and ASO internals. So I was bright eyed and not so bushy tailed very early in the morning to give it. I was really amazed that on Thursday so early, the room had a good number of participants. I guess they had not heard that Tracy or Edward was not presenting, but I was. As typical I talk too much and ran over a few minutes, but most people did not mind and I think there was a lot of good content. I got a small break then presented my load rules basics to advanced. Lots of good questions, but to a much smaller group. The conference was winding down.

Overall I think the conference was a great success. I heard multiple time from participants “That tip paid for the whole conference” during various sessions. While this year I did not get much time to see other sessions as I would have liked, The feedback I got from others was that the conference was outstanding.

On a personal note, I was re-elected to the Hyperion SIG board along with Joe Aultman (Autotrader), Brian Suter (Dell) and Alice Lawrence(LSGSKY Chefs) so if you have any suggestions for next year’s conference or have an abstract to present or something you would like the board to do, please let me know.

Kaleidoscope

2010-06-30T14:21:00.001-07:00

I’m sorry. I said I would be blogging about the conference each day to keep those of you how could not make it informed about what was going on. Frankly I’ve been so busy that I have not had the time to do it. Monday started with a genera session. During the session a couple of things were revealed. First, The location for the next Kaleidoscope. It will start June 26th in Long Beach Ca. Second, A new conference chair was announced. It is Edward Roske. The Keynote speaker was Lee Rainie from the Pew Research Center’s Internet and American Life project. I won’t go into the presentation itself, you can look at their web site to see statistics, but it was interesting as he showed a lot of technical trending.

I had the opening technical session of the conference with a session on MDX practical examples. While I think (hope) the presentation went well, I had so many good questions, I got off track at times and had to rush through the last part.

I ended up taking over one of Edward Roske’s presentations on ASO optimization so I spent an hour or so going over it the had lunch. The food was good,but crowded and a bit unorganized. First impression, not quite as good as last year.

Since I’m on so little sleep, I’ll apologize for not saying more, I’ll TRY to do a recap of what I saw after the conference is over.

Kaleidoscope Day 1

2010-06-28T05:36:00.001-07:00

Welcome to Kaleidoscope 2010. I’ll try to blog about what is going on here for those of you who could not attend. It may be limited as I’ll be pretty busy. I’ve picked up a few more presentations and I have a lot of commitments during the week. This is Sunday, Day 1. I arrived yesterday with little problem. Taking the Metro to the hotel was easy if not crowded. I got my room at the Marriott. It is bigger than a small country(I think thanks to the number of nights I spend with them).

I’m sitting in the Sunday symposium hosted by oracle. There are about 150 people in the room. I have to say, I will not be saying much if anything about it. We have been cautioned we are not allowed to say anything about what is talked about today (aside from saying Edward’s shirt says he is a tasty villager). The whole day is on Oracle products and future and will be an interesting day.

Tomorrow (Monday) I have two presentations. MDX practical examples and ASO optimization (which I picked up at the last minutes). It will be a long night

ASO Outline Compaction

2010-06-11T07:47:00.000-07:00

The other day Tim Tow on his blog talked about opening outlines and commented about how slow ASO outlines created in EIS or Essbase Studio were to open.
http://timtows-hyperion-blog.blogspot.com/2010/06/essbase-outline-performance-testing.html

I've seen that and have a number of large outlines that don't seem to compact. I have one very similar to the zzzz application Tim talks about. sitting at about 122 meg. I tried to compact it and brought it down to 120 Meg. Still big and still slow to open. This is where OTN came to my rescue. There is a extension to Esscmd (yes Esscmd not MaxL) that calls the outline API. This is used by Oracle internal Quality Engeneering to test the API. On the page they have a PDF that explains how to compact an ASO outline. I tried it and my outline went down to 8K and opened in a few seconds. Simply amazing results.

I've sent this post to Tim and hopefully he will test his instance and will report on the results.

I guess I should tell you where to get this godsend. You can download it from http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html?ssSourceSiteId=otnjp

While not a supported application ,it does have versions for all of the platforms and for versions from 6 to 11.

Never listen to what people tell you (me included)

2010-06-10T11:57:00.000-07:00

Ok, so do you believe me or not based on the title? Today I had another Homer Simson moment , you know D'oh. For years we have been talking about the config commands SSAUDIT and SSAUDITR and how they only work with the Classic Add-in. I'm sure I read it somewhere. Someone (Brian Suter) casually mentioned to me that they are using it with Smartview. That goes against what I had been told so I had to try it. Sure enough, SSAUDIT creates the files and writes to them if you do a submit from Smartview. I still don't know about from a planning form and I think I would use the transaction logging in 11X as it is most likely more robust. But it is nice to know that this actually works.

Moral - Never trust what people tell you or even what you read. Test it for yourself. I know of at least three incidents where the DBAG is just plain wrong of what it says and other places where information is omitted.

Data Export, why I never thought of it

2010-06-09T13:26:00.000-07:00

All of the posts on OTN lately about the Export command got me thinking about something I found by accident (and should have realized all along). We all know if is possible to do a parallel export using the MaxL Export statement just by supplying multiple export file names separated by comas. I always write a script to do exports because it is quicker than exporting in a single file format from EAS.

I thought it odd that EAS did not give you the option to do a parallel export. Stupid, stupid me. It does, it just does not slap you in the face being obvious to tell you. I expected a check box and a place to list each file name in it's own box. You know bells and whistles sort of stuff. No that would be too easy and intuitive. To do a parallel export from EAS you just list the export file names separated by comas (just like MaxL D'oh).

I selected export from Sample.Basic and entered sample.txt,sample1.txt and ran it. I then went to the file system and in \hyperion\products\essbase\essbaseserver\app (sheesh could they make the path longer please), I found Sample.txt with a size of 214 K and Sample1.txt with a size of 212 K. It couldn't have been easier.

The moral of the story is, if you can do it in MaxL you can most likely do it elsewhere. Get creative and try.

Kaleidoscope changes

2010-05-25T08:29:00.000-07:00

For those of you are my groupies (I guess the whole post is for Cameron) and signed up for all my presentations at Kaleidoscope, I have a change for you. Edward Roske and I switched a session. I will now be presenting "Essbase Studio Tips and tricks" on Wed at 9:45 and Edward will be presenting "Configure it out" on Thursday at 9:45. All of you Roske groupies can change your schedule to listen to his dry sense of humor. The switch made sense to us, I'm almost done putting together the presentation and I think it has some good content. A word of warning, if you read the abstract on the the Kaleidoscope website you will get a little confused. They did a good job of updating who the presenters are, but in the abstract is still says Edward is presenting.

Wow, after my initial posting, I found that I've been added to another session. This time with Edward Roske and Tracy McMullen. It is on Tuesday at 9:45 and is called "Ask interRel's gurus, More fun that a barrel of monkeys".So for this session you don't have to choose between Edward and me. Actually if it works like normal, Tracy will answer the questions and Edward and I will make snide or off topic comments. What I want to know is how fun is a barrel of monkeys? One would think they would get rather annoyed being packed together. I do think it will be interesting, we never know what will happen. I typically end up learning new things at these sessions from the audience.

Cameron on his blog has posted the sunday symposium and it looks like the topics are interesting and meaningful. I am anxiously waiting to see what is new, exciting and on the roadmap. You can find the agenda here at http://camerons-blog-for-essbase-hackers.blogspot.com/2010/05/kalidoscope-2010-symposium-agenda-is_18.html

If you are not signed up to go, what are you waiting for? If you miss this conference, you miss so many technical presentations that you will fall behind. I would say this is where all the cool kids will be, but to be more accurate, this is where all the geeks and nerds will be. (I'm proud to be a geek or is it a nerd?). All of the Oracle Aces in BI except for John Goodwin will be there presenting. I'm still hoping John comes out. If he does, he will get very drunk as a lot of us owe him drinks for help in one thing or another.

I'm not sure anyone ever notices it, but I think the coolest thing on my blog is the useless knowledge box that appears on the lower right hand side, you might need to scroll down to see it. I come to my blog just to see the trivia. Of course some people say my whole blog is useless knowledge, but that is a different story

ODTUG Hyperion SIG and Essbase Studio 11.1.2 features.

2010-05-06T08:55:00.000-07:00

Are you interesting in helping you fellow Hyperionites (I made that word up). If so, consider running for the ODTUG Hyperion SIG board. There are three openings this year. The bylaws require at least 50% of the board be from end users and not consultants. This is so end user need are met and the board does not become a consulting advertisement. This is a hard working group and if you looked at the agenda for the 2010 kaleidoscope conference, you will see they have put together a great program. They work all year to prepare for this one week. If you are interested in becoming a board member, please contact Natalie at Nadelemar@yahoo.com to submit your intent to run. After a year’s absence on the board, I have decided to run again. If you don’t plan on running, plan on voting for me.

In my last post, I was talking about issues with Essbase Studio and my hope that many things would be better in 11.1.2. With that version now out, I thought I would highlight some of the improvements that I think are significant. First, you can now install Studio as a service, why that was left out in the prior version I have no idea. You could do it yourself, but it was a pain to set up.

Next is support for ODBC DSNs and OCI connections. There were times I needed to specify a particular connection and was not able to, this resolves it.

Here is one of the big improvements. Enhanced Data source sync. You can now delete tables (as long as nothing is using them, makes sense) You can refresh them for changes in columns or data types. YEAH. I don’t know how many times I’ve had to rebuild because of the lack of this functionality. It will make me a lot more productive.

You can now create text lists from Studio. While I have not done a lot of implementations with Text lists (maybe because I couldn’t create them). This is a piece of functionality that was missing.

Another big change is in Essbase Models. With the prior version(s) if you made changes to hierarchies, you had to rebuild the Essbase model every time. Now there are a number of conditions in which you don’t have to rebuild. Things like renaming or moving members or changing the bindings or overridden bindings does not require you to rebuild. Items like adding or removing hierarchies, reordering or adding members to hierarchies still require rebuilding. At least the dev team is on the right track. I’d like to see more cases where you don’t have to rebuild, but at least some things are better.

Hoo Hoo, you can now set the order of dimensions in the Cube schema. This was a huge hole in the functionality. In at least 11.1.1.3, you could emulate this by inserting the dimensions in a particular order,but once done, you could not change them. If you did it manually in the cube, if you deleted and rebuild members, the order would go away. Such a little change can make such a big difference.

Automatic placement of stored members before shared members. This goes along with an Essbase change and is important for ASO cube especially, although it does have an impact on BSO cubes. ASO cubes will fail to build if the shared members come first. This figures that out and set the first instance to be the stored member and subsequent members to be shared.

No longer do we have to parse through CPL code to figure out what a deployment did in order to recreate it in MaxL. We now have the ability to save off the deployment as a script to use later for automation. Of course we can also edit the script to add encryption and other steps, but it makes life easier. Talking about making life easier, when you run a deployment through the studio console the console will show you the step it is performing. You could watch the Studio Server window to see the same thing; if you were connected to the server, but this is much easier.

Recursive Hierarchy drill-through. That is a mouthful. While I was able to do this with custom SQL it was a pain to do. If I did it myself, I could not set the level to allow drill-through to occur at. Since recursive hierarchies are one of the main ways dimensions are created (especially if you use products like DRM) it did not make sense to not be able to do this. I know the code must have been a bit difficult for the dev team, I’m glad they figured it out.

While there are other features added, these are the ones I think have the biggest impact.

On a related note, in my last blog entry, I talked about a bug the Smartview team owned about being able to drill-through on intersections you do not have access to . I got notification that they are working on implementing a fix for this in one of the 11.1.1.3 releases. I look forward to it.

Essbase Studio issue and work around

2010-04-20T12:40:00.000-07:00

I was working with data loads in Essbase Studio and ran into some odd behavior (OK working with Studio IS odd behavior). I had a data load that kept failing with a data item found before all members error. . I took the generated SQL and ran it through a SQL tool and it returned properly. SO what could it be. From Studio 11.1.1.1 I remembered you could not edit dimension or load rule files, so the first thing I did was try to open the data load rule (I never learn). To my surprise it opened. For fun I tried dimension build rules and they fail with a message that you can't open file created in Studio, oh well). I went and looked at the SQL in the rule and it was the same as the SQL I pulled from the Essbase Model. Since the SQL should work, I tried to retrieve it into my load rule to see what the data looked like there. Below is what I got:

Notice the two columns. It turns out these were manually created dimensions that I had set the default bindings to set. In my SQL (Oracle based) The two columns looked like:

'MTD' as View,

'Actual' as Scenario.

In one case (don't ask we why) it took MTD and only returned the "M" for Actual it returned nothing! Huh??? To fix this I got creative. remembering back to old old old problems with certain data types from SQL, I modified the code to look like

Cast('MTD' as Varchar2(10)) as View,

Cast('Actual' as Varchar2(25)) as Scenario

And it worked like a champ. There is something in how it is handling the literals that does not work right.

I have spent about two months working with Oracle support showing them the issue (Three different web conferences with three different people and sent them my code) but they have not been able to recreate the issue on the support environment. So after a lot of time, I gave up and closed the ticket. This means we won't have a fix for what I consider a bug, but support doesn't recognize, but at least now when you run into it, you know how to work around it.

There is a lot of potential in Essbase Studio and I look forward to the 11.1.2 release where a lot is supposed to be fixed. When 11.1.2 comes out, I'll look it over and let you all know all the cool stuff it will do.

One item that will not be fixed (that I know of) is with Drill-through. There is an issue I found where if you do not have access to an intersection of data from Smart View, you can still drill-through to detail. I don't know if this is a Smart View issue or a Studio Drill-through issue, but The Smart View team has taken ownership of the issue (Thank you Matt) and is looking for a fix to this. I commend the Smart View team for their consistent willingness to listen to problems and react.

Quiz 3 Answer

2010-04-06T15:45:00.000-07:00

Amarnath had the quiz answer about 99% there.
Note, I moved hie paragrapgs around a little to make it more meaningful but his reply was:

Since boolean function results in 0 or 1, if the current member has UDA as "Major Market" then the boolean function will result in 1, if not it will result in 0 and dividing by 0 results in #Missing. By this method it is not required to use an IF statement.

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales"->"Market"->"Product" = "Major Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales"->"Market"->"Product" = "Small Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX

The problem with his answer is he needs to do a fix before his existing fix to clear out the values as he is writing to an upper level intersection and the code would not be rerunnable (is that a word?). It is nice that he tried to use the last quiz's answer as part of his response, but I was not bright enough to think about having that happen. It shows he actually read my solution :)

Clementine's modified version of his code is:

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales" = "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales= "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX

AGG(PRODUCTS,MARKET);

In this case, Clementine would have to have the agg statement but gets the ability to drill into Market to see what Markets made up each of the calculated members.

If Clementine was not so picky about Fix statements she could have done a fix around the Agg to only do the Aggs in the Actualscenario, but that would have broken her rule.(And added efficiency.). If the requirement was not to use the UDA, you could have also used the attribute dimension and would not have needed to calculate at all or you could replace the @ISUDA with @ISATTRIBUTE. There are a number of other solutions available and Jared had the beginnings of one, but his solution had a few problems.But is was a really good try. A little modification and it would most likely work. If you have another solution, please post it, In Calc scripts, like many other things in Essbase, there is more than one way to accomplish things.

Only two people tried to answer the quiz. Do I need to make the easier or harder, keep them as they are, forget about them or just post them as tips instead of quizes so you don't have to think too hard?

Quiz 3

2010-03-29T14:12:00.000-07:00

In a cavern, in a canyon,
Excavating for a mine,
Dwelt a miner, forty-niner,
And his daughter Clementine.

Now Clementine in addition to being beautiful was a bit particular. She
was an Essbase Calc script wizard. As I said she was particular, this proves it.

She hated the word IF because she know if statements are slow and Clementine was a fast woman. She figured if she was going to Fix something it should be fixed the first time and not again.

She was asked; well it was highly requested or she would be swimming with the fish (and from the chorus, we all know how that turned out)
Ruby lips above the water,
Blowing bubbles soft and fine,

to create a calc script in the Sample.Basic database (BSO) that would take Sales and and add them into a one of two members.
Major Market Sales
Small Market Sales
(Members she had to create in the Accounts Dimension)
The calculation should be done on level zero of all dimensions and be based on the Market UDAs. Only one fix statement and no if statements.

Can you help Clementine?

I'm so lonely, lost without her,
Wish I'd had a fishing line,
Which I might have cast about her,
Might have saved my Clem

Note, Clementine does not refer to any real person, the song just popped into my head, so I thought it would be a fun to include it in the quiz and perhaps throw you off a bit.

OK, now for some other news, Edward Roske created yet another linked in group, but this one is just for us Essbase types. Not Hyperion, Not Oracle, Not planning, but just Essbase. If you want to be like one of the cool kids, come link it at http://www.linkedin.com/groups?gid=2905269

Kaleidoscope detailed agenda is now available

2010-03-23T08:44:00.000-07:00

They just published the detailed agenda for Kaleidoscope at Kaleidoscope Agenda . What I want to know is why all of the presentations that I want to go to are always scheduled when I am presenting. The Best Practices for Performance, Scalability, and Reliability with Oracle BI Enterprise Edition from Mike Duran on Tuesday conflicts with my "MDX Basics, practical coding examples". IT will make it hard for people to choose which to go to.

Then on Thursday Edward Roske's Integrating Essbase and OBIEE: Implementing in the Real World conflicts with my Essbase configuration settings presentation. I would go to Edward's presentation. Finally my Load Rules Basics to advanced conflicts with Matt Milella's Convert Legacy Add-in Solutions in VBA to Smart View VBA or .Net. I really wanted ot go to this one. If everyone in my session goes to Matt's session, I can skip mine and go as well. I'm sure that won't happen, so I'll be presenting. I think mine is a good presentation I just finished putting it together and it has a little bit for everyone. It truly is Basics to Advanced.

As I said before, there is too much great content at the conference and you will not be able to attend everything you want to, but for most presetations the slides will be available. For my presentations, at some point interRel consulting will allow me to present them during their tuesday and Thursday webcasts.

Quiz 2 answer

2010-03-22T15:45:00.000-07:00

I am amazed no one came up with the answer I was thinking about. Amarnath came up with an interesting idea on using a partition and ASO cube. While it might work, I have not tested it, but in my dealings with ASO cubes, partitions and calculations combined together, I have found less than satisfactory performance. Still it was thinking outside the box and he gets brownie points for that.

My solution (see code below) breaks the rules for using cross dimension operators on the left hand side of the equal sign. The trick here is to bypass all of the intermediate levels and only put data where we need it for our subsequent calculations. If necessary, we could agg the dimension later to get all the values populated. Since the number of level zero combinations is pretty small, cycling through them is very fast. The solution is always writing to the same block, so that block will most likely be kept in memory. Note, this solution will also work if one or two of the dimensions are not at top level. For example; if I had a customer dimension and wanted to get totals by customer, I would just fix on the level zero of customer. If I don’t put the customer dimension in the left side of the equation, it would do the calculation for each customer. If doing this, don’t forget to add the level zero parents in the first clear statement.

So what exactly is the solution?

In text, we clear out where we want to stick the data. This makes the code rerun able.

Then we get to the level zero of the dimensions and cycle through the members for each member we add to our total block. Since this is a dense calculation and it is only on level zero member, this calculation can be very fast.

/* Need to clear out any data that exists at the target intersection.
This really is clearing one block) */

FIX(@levmbrs("Year",0),"Actual","Product","Market")
"Sales" = #Missing;
ENDFIX

/*Now fix on level zero of the dimensions and add to the total */

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

/*Note Sales is in a dense dimension member so we are doing a dense calculation */

/*Since I'm doing a cross dimension operator on the left side of the equal sign it has to be in a block statement */

"Sales" (
"Sales"->"Market"->"Product" = "Sales"->"Market"->"Product" + "Sales";
)
ENDFIX

At a recent client, using this technique, I was able to cut 30 minutes off of multiple calculations. In total I cut 10 hours off of a calculation process since their calculations did a ton of allocations. It helped that when the calculation was done, the data was copied to an ASO cube for reporting, so I never had to really agg the cube. If I did have to agg the cube, It would have only been one time instead of multiple times. I should point out as side effect of this method was the cube size was brought down from 10 gig to 2 gig and fragmentation was reduced a lot.

Let me know what you have a different solution. If I continue on with this series of quizzes, you will soon know all I know (which is not much). So let me know if you find them interesting.

This and many more tips and tricks can be seen at the Kaleidoscope conference in Washington D.C. at the end of June. It is a worthwhile investment to attend.

Time for another quiz

2010-03-10T08:07:00.000-08:00

The last quiz I did was very easy and was answered by a number of people very quickly. In addition, it wasnot technical at all. This quiz is a little harder and makes you think a little.

The problem.

I have expanded sample.basic and now it has 10,000 products with 5 levels in the hierarchy. Alternate hierarchies have been put under a parent called Alternate_Product_Hierarchies and the primary hierarchy is under a member called "All Products". Market too has been expanded and goes to city level so there are now county and city levels in the hierarchy. There are 3141 counties and for fun lets say there are 19,355 cities.

For the actual scenario for each month, I need to get sales at the generation 1 of Product and Market so I can use it for an allocation later. Because of the size of the dimensions, I can't just make the upper levels dynamic. Also, while I could just agg up the dimensions, it is slower that I need it to be. What is the quickest way I can get my sales at the top of the dimensions. This code also has to be re-runable. I'm sure there are multiple ways to do this. I'm interested to see what you come up with.

Have fun.

Too much content at Kaleidoscope

2010-03-02T11:53:00.000-08:00

I has looked over the Hyperion sessions at Kaleidoscope and decided which ones I wanted to go to (between my presentations), but now my selections have become muddled. I was reading Mark Rittman's blog and he announced some of the sessions in the BI track that deal with OBIEE. There are a number of great session there that deal with Essbase. Like Oracle Business Intelligence Applications Essbase Integrator and Oracle® Hyperion Smart View for Office, Fusion Edition (This one is from Toufic Wakim). How Mark got him to talk in his track instead of the Hyperion track, I don't know. but I'll bet the information will be good.

You can see the details of these presentations and more at
http://www.rittmanmead.com/2010/03/02/obiee-content-at-odtug-kaleidoscope-2010/

As for my mini-quiz question, A number of people quickly got it (Google is many people's friend), The first conference was called Arbor Dimensions and it was help in 1995 at the Fairmont Hotel in San Francisco(Getting ready to be a part of the future Open World I guess). They quickly outgrew that location and the following conference was at the Westin in Santa Clarita. and soon following at the convention center.

I will have a more technicl question for the next quiz. Thanks to those who responded

A mini- Quiz for you old timers

2010-03-01T10:20:00.000-08:00

OK, I'm stealing from Mark Rittman's Blog and doing a little quiz or puzzle from time to time. This one is to see if any of the old time developers have any memory left. We all know that the best conference to go to for Essbase content is Kaleidoscope (Yes this is a plug for the conference). Prior to it, was the Sales pitch intensive Solutions conferences. But what was the first Essbase conference called and in what year did it start? For Bonus points where was it held. And where was the next year's conference held. Yes it was so successful they did it again.

Kaleidoscope is coming too quickly

2010-02-16T12:17:00.000-08:00

It's already feburary 16th and Kaleidoscope is less than 4 months away (June 26 -Jul 1, 2010) and I have too many presentations to prepare. I have 4 sessions that I have to create presentations for and one or two more where I show up and make everyone else look more handsome(anyone next to me always looks better. Is that why pretty girls always have an ugly friend to hang with? I know I'll get comments on this. It is meant in fun don't slam me for it).

Anyway my first session (and first session of the conference) is "MDX basics a practical approach". It goes over some of the basics about coding in MDX and then I'll show working examples of coding them and how they work. Then I have a joint presentation sometime with Gary Crisci and Cameron Lackpout on the Best of OTN posts. We share some of the more interesting threads from ONT (and perhaps Network 54). My final two presentations are the last two presentations on Thursday. First and last, I get to open and close the conference. The sessions are "Load rules basics to Advanced" and "Configure it out" which deals with the multitude of configuration settings in the Essbase.cfg file.

I've gotten a good start on all of the presentations, but if there is something special you feel would be something you want to know about or have dealt with and think I should share, please let me know.

As you all know the kaleidoscope conference is the Best conference for Hyperion Technical presentations. It IS your conference, so share your ideas. I know I am speaking to the choir here as most people who read my blog are technical and know the value of the conference, but for those of you how stumble onto my site, I will reapeat what you hear all over. This conference is better than any single training session you can go to. It has more technical content and more knowledgable speakers than ANY other conference I have been to. Even after doing Essbase for about 15 years, I pick up good information in every session I attend. (Even my own, where someone from the audience will tell me something I didn't know) Yes I am talking about you Cameron, and Gary :)

If you read my blog or are on OTN or Network 54, look me up at the conference and say hi. It is nice to put faces with the handles I see on the web.

Book Review - Oracle Essbase & Oracle OLAP The guide to Oracle’s Multidimensional Solution

2010-01-14T15:51:00.000-08:00

First my typical disclaimer.
1. I am an Essbase developer and know little about Oracle Olap
2. I work for interRel Consulting which has a competing book out on Essbase (Well not really as you will read)
3. I am a pessimist by nature.
4. I have irritable bowel syndrome (Not really, but it was a fun thing to say)

OK now with those thoughts in mind, on to the review.

Mid December I got a package in the mail. Oh Boy, another Chanukah gift and In a way it was. I opened the package to find my copy of the book “Oracle Essbase & Oracle OLAP The guide to Oracle’s Multidimensional Solution” that was promised to me for review. This book was written by Michael Schrader, Dan Vlamis, Mike Nadar, Dave Collins and a host of others. I will refer to them collectively in the review as the Authors. Being the holiday season, I sat down and quickly put the book aside to look at later. I have finally gotten around to reading it (I spend too much time on planes and this gave me a lot of reading time). I can say, this is a good, worthwhile book.

I am brutally honest in my reviews and hated the last book I read. This one is very good. I will say it is not the detailed walk you through everything you need to know book, but it a good overview. The discussions of similarities and differences between Oracle OLAP and Essbase are good. The book discusses Oracle Olap and Essbase compares the two and contrasts them pretty well. I don’t agree with everything the authors say, but we are entitled to differing opinions. I even learned a little about Oracle OLAP so they must have done something right.

First I will talk about the good, and there was a lot of good about this book.

The book is well thought out and put together. It flows in a logical and concise manner. The discussions are well thought out. Even if you know nothing about Oracle OLAP and Essbase, you would walk away with a general understanding of the products.

I liked the first couple of chapters which discussed OLAP in general, the strengths of each product, The product suites and more.

I really like the section on reporting. It walks through considerations when determining a reporting solution and is a must read for any reporting development project even if Essbase and/or Oracle OLAP are not part of the solution. It covers the basics of what one should consider when designing a reporting solution then delves into product specific solutions to meet those requirements. I thought interesting (and brave) for the authors to include non-Oracle solutions in the discussion. For example, For Essbase reporting and analysis, they discussed Dodeca from Applied Olap which I believe is one of the finest reporting tools available!

While I think the Authors cheated (See my rants below), I did like the approach for Essbase of showing how to build a cube using Essbase studio. This tool is the direction Oracle is heading for all administrative aspects of Essbase. While the chapter concentrates on Studio, The authors do tell you there are some things you still have to do in EAS and walk through some of them. I’m sure for a long while it will still be necessary to know EAS, knowing how to do things in Studio will help you a lot, especially if you plan to use OBIEE as a data source for Essbase cubes.

Unlike the last book I read, this book had no real misinformation. Remember I’m an Essbase developer, so if they lied to me about Oracle Olap, I would not know. For Essbase, I say no real misinformation because I don’t agree with a few things said. I would call them differences of option instead . The examples were good and easy to follow. The screen shots and illustrations were applicable and added value to the text.

Now for the bad about the book, (remember my disclaimer – point 3 to be exact). I find it easier to find fault and complain about things than I do to relate the good. For the record, I don’t think I can really call the items below bad just personal observations.

I think the authors cheated a little as pretty much two of the main chapters were lifted from existing work (at least a good percentage). The chapters that demo building Oracle OLAP and Essbase cubes are from existing documents. I know a good part of the Essbase demo build is taken from the “Dare to Olap” series and The Oracle OLAP references where the tables and additional documentation can be found. I would guess the Authors originally helped put these modules together so I don’t think there is anything wrong. I also think most of the readers of this book will never have seen these demos before so it is probably not a real bad thing.

The book was very redundant. I would read about something only to have parts repeated in the next paragraph or in the next chanpter. Did I say the book was redundant? well it is.

The third thing I did not like is the way the pigeonholed the products. IT supported implementations should use Oracle OLAP, User supported applications use Essbase. While in general (and historically) this might be true. I have seen a great many projects where The Essbase has been initiated and supported by IT ( I can’t speak for Oracle Olap, but would imagine that they would all be IT supported). Now that Essbase is owned by Oracle, IT has gained a respect for what Essbase can do and is embracing it. I think a better generalization would be “If all your data is in Oracle, use Oracle Olap, if you have multiple sources or need complex calculations use Essbase”. Of course if you want the additional functionality like Planning or write-back, use Essbase as well. Damn, I guess even my generalizations are bad as I can think of a number of exceptions to my statement.

Finally, this is a VERY dry read. Yes it provides all the information you need, but with my A.D.D., I really had to concentrate to get through it.

While I don’t think you could become a developer from just reading the book, I do believe it is a good read for an understanding of both products, their ancillary products and the various use cases for the products. I think after reading the book, you would still need a class on the
product(s) to be even somewhat proficient, but I don’t think the authors’ intent was to create a how to book. In order to do either product justice and cover the topics in detail would require a book that the airlines would charge me overweight fees on. I would suggest, reading this book as an overview and then if you are developing for Essbase read the Look Smarter than you are with Essbase 11 book to give you more details (I would make the same statement even if I did not work for interRel). I don’t have the knowledge to recommend an Oracle OLAP centric book as a follow up. Of course it would be even better to take a course to get the expertise of the instructors.

In conclusion, I would like to thank the authors for spending the time and energy to put together this book for us. I know the amount of energy, blood, sweat, tears, late nights, drunken fits and blood curdling screams that go into writing a book like this. This was a great job. I know I don't have the skill, time or patience to write a book like thig. To all the authors, Thank you from the OLAP community for sharing your expertise with us.

FREE Oracle SoCal Roadtrip Conference Jan 26th

2010-01-10T12:28:00.000-08:00

For any of you in the So Ca area, On Tuesday Jan 26th, I will be joining Oracle and other speakers on a one day mini-conference in LA. It will be a great event and you won't want to miss. it.
I'll be talking about Essbase optimization tips. Other sessions include:
Best practices in consolidation
Hot Topics in Financial Reporting
Secrets of Customizing Hyperion Planning
Creating an Oracle EPM center of excelence
Taking control of your hierarchies with DRM.

The keynote will be from Tobin Gilman on the EPM roadmap for 2010 and beyond. It is a free event that you should not miss. The complete agenda is available at Agenda

and to sign up go to Register

For more information you can contact Danielle White at dwhite@interrel.com

Did I mention that it is free and you could win door prizes?

as a teaser, I have my review of the new book "Oracle Essbase & Oracle OLAP, The guide to Oracle's Multidimensional Solution" almost done. I'll be posting it in a few days. Check back, I'm sure you'll enjoy it.

Miscellaneous Ramblings

2009-11-24T12:27:00.000-08:00

Just a few musings to show you I still exist since its been a while since I last posted. I don't know how some people (John Goodwin) have the time or energy to post as often as they do with such good information.

First, as mentioned on Edward Roske's blog, the ODTUG board elections were announced and Essbase's good friend Tim Tow was not reelected, while we have Mark Rittman on the board, Tim is/was the Hyperion advocate on the board and I'm sure he will be missed. We owe a big thank you to Tim for all his hard work to get Hyperion included in the Kaleidoscope conference. It is truly the best home the products have had.

Second, in conjunction with item 1, the Kaleidoscope conference dates and location have been announced. It will be held in Washington D.C. on June 27th to July 1st(2010). While I am not on the Hyperion SIG board this year, I did get a peek at the list of submitted abstracts and they look awesome. I don't know what the final selections will be, but it looks like there will be some great topics. I recommend doing whatever you can to go to that conference. You will not be disappointed. From what I understand, there might even be a track with more intoductory topics and case studies for people who want to start learning more about the product.

Finally, I am anxiously awaiting my copy of the new book on Essbase and BI. While I don't remember the name, I know some of the authors and I'm guessing it will be a good read. As soon as I read through it, I'll put a review here.

Open World 2009 Day 2

2009-10-12T17:22:00.000-07:00

Oracle Open World Day 2.
This has been a hectic morning with my needing to deal with some issues at clients so I missed this mornings Keynote. As I looked at the schedule as I did not preregister for any sessions (which is a mistake), I noticed that all of the Hyperion related sessions are in the Intercontinental Hotel on the 5th floor. Interestingly all of the Hyperion Vendor Kiosks were outside the rooms. What a great idea. Not huge booths, but a place to talk to vendors associated with the products you are looking at. Aside from the interRel Kiosk, there was one for Applied OLAP (and their Dodeca product) and Star analytics who has a couple of great products as well. The other booths were competitors, so I guess I won’t mention them. In total I think there were about 8 Kiosks.
I wanted to go to the session on EPM Roadmap, but when I looked on the board, I saw it was overbooked, so I figured I would have no chance to get in. There were people standing off to the side who had been shuffled there by the door wardens ( A thankless job). I figured what the hell, so I got into line expecting to be pushed aside. I got to the door warden, he scanned my card and let me in. I figured out afterwards, that my Blogger pass is worth something. Thank you Oracle.
The session was led by Bill Guilmart and Al Marciante. Like ALL of the sessions here, the first slide was a disclaimer saying nothing they say (or for that fact, my reporting of it) is reality and may never be delivered. So take what I say with a grain of salt or a pound and rub it into your open wounds.
The session first talked about the existing releases since Open World last year and since that is all old news, I won’t repeat any of it here. As they got to talking about new features, there was a these I have seen yesterday OK. I get it, the theme for the conference seems to be “Complete Open Integrated” and the real theme for the session was Unify intelligence from Transactional , BI and Management systems.. Toward that end, the topics would be
· Complete Integrated Close
· Extending the Planning Platform
· Expanded ERP integration
· Ease of use enhancements
· Integration
· Portfolio wide improvements
What does all this mean? I’ll go through what I was able to jot down while actually trying to listen at the same time (because of this my notes are not complete)
Financial Close Management is a four piece enhance,ment
1. A financial Calendar to let everyone know the schedule and task deadlines
2. Process monitoring – A dashboard to show how the close is going
3. Task Management. Lots of improvement including integration with Oulook task lists and calendar
4. Account Reconciliation This includes
a. Internal/ External reporting
b. Disclosure management (10Q, XBRL, SEC filings)
It will work with MS office and other products to provide consistent reporting and accountability

Planning
· It too has enhanced process management including reassigning of tasks(delegation)
· A New Form designer
· Composite forms
· Web form enhancements including
o Conditional formatting
o Ad Hoc Data entry forms
o Formatting options (freeze frame, sorting, filtering,, etc)
o Better member selection
o And Built in validation rules
Public sector Planning. This section went too fast for me to get the info, but it looked good

Smart View
· Enhanced Excel experience
· Task lists
· Composite forms from Planning
· Context sensitive ribbon bars
· Enhanced 2007 look and feel
· Better Log in
· For HFM
o Smart slices
o Report designer
o Cascading reports
· For Essbase
o Drill through to ERPI
Shared Service.
Alas, I was not able to get everything down they talked about, but good stuff is coming including
· New security deployment configuration
· 2 way SSL
· SSL offloading
· (I missed the rest of the points sorry)
EPMA
It looks like they are doing a lot of work on EPMA to make it really usable, this is a good thing.
· Enhanced Essbase support
o ASO and BSO
· HCPM Validations
· Smart mapping of Planning lists to ASO cubes. OK, This means Planning will create ASO reporting cubes. It sounds like it turns smart lists into dimensions (attribute???) as Al talked about 25 dimension cubes for reporting
· Batch updates
· The ability to purge transaction log files
· HFM copy application ability
Calculation Manager
While 11.1.1.3 enabled calculation manage to work oncubes outside of EPMA, it is getting better
· Procedural calculations on ASO cubes (Allocation and others)
· Template ability. Create one and use anywhere
· Parameter passing
Data Relationship manager
· Browser based
· Unicode support (Multilanguage)
· Role based
· Multiple applications per server
· Validations in real time and in batch
· Improved Nnavigation
ERP Integrator (Part of FDQM)
In general improvements in functionality
HFM
· Starter Kits – IFRS, Japan, ….
· Enterprise extracts in HFM format
· Integration with Government/Risk
o Segmentation of duty
o GRC manager
· Integration with OBIEE Answers
FR
· Related content enhancement
· Grid level and POV passing enabled for snapshots and books
· MS word integration as Word tables
· LCM support to migrate
· Diagnostics and logging
IOP
This is a fairly new product and was not integrated into the suite. The major work being done will make it similar to the other products including SSL, Diagnostics and logging

HPCM (HCPM) I’ve heard it both ways.
· Expanded Driver functions
· Standard Cost drivers
· Sequence dependent drivers
· Expense assignment functionality
· Exp Model navigation
· POV management
· Improved performance
o 10-20 times better for direct drivers
o 3 times for genealogy
o 140 times from Allocations
And last but not least Essbase
· Improved EPMA deployment
· Improvements in LCM
o Outline compare
o Project naming
· Access ability(they put the number 508 in parens after it, I don’t know what that means)
· Functional enhancements
o Username/character length allowability
o ASO Formula editor
· Web services
· Studio Usability
· Diagnostics and logging
They covered a lot in 1 hour and could not go into a lot of depth, but as you can see, the products look like they are going in the right direction. It’s nice to know that Oracle thinks they are worth all the work.

OpenWorld 2009 Sunday

2009-10-12T13:57:00.000-07:00

After an on and off and on again scenario, I actually made it to Open World. I got asked to sit on the Customer Advisory board for Essbase (CAB). So if there is anything on your wish list for Essbase or one of its related products (studio, PBIEE integration, etc) let me know and I’ll see if I can get your voice heard.
After a long day in CAB listening to things I’m not allowed to talk about, I’m sitting here with thousands of my closest friends waiting for the opening keynote address. One nice thing, they have set up an area for press and bloggers with tables so I don’t have to try to type in my lap. I hope there is some amazing tidbit about the Hyperion products I can tell you, but I’m not hopeful.
The session is starting with Scott McNeally from Sun Microsystems. His theme is innovation. He started of with a top 10 list of Engineers gone wild. His list is cute, but not worth repeating. (although I did like his sushi USB drives and Nobel prize for Gas Mask Bra ,no more funny than other Nobel prizes. ) He then went into the top 10 innovations from Sun (not a funny list, but interesting). The list includes the following,
Sparc, Solaris, Opensource, Java, Open Storage, Blackbox Datacenters( in containers for 3rd world countries), different chips, multithreading, etc.
I won’t go into them, but Scott talked about them all. And more.
Scott Fowler talked about Sun Systems, integrated systems, Java. Security.. He announces that Sun SPARC/Solaris is the number 1 in all the commercial Benchmarks including Hyperion.. He talked about the Sun Oracle Database machine (Didn’t I see a similar presentation from Oracle and HP last year?). $00 gig of flash on a card that won’t wear out like old flash and a nore Flash array. A single rack equates to 1000s of disk with 4X the throughout .
It was a real love fest from Sun about Oracle.
Larry Ellison then came out and the love fest continued with him talking about why He supports the Sun products and will not get rid of parts. He talked about his commitment to beat IBM. Expect to see ads why Oracle and Sun have a 25% better processing than IBM being 6 times more green.

After the keynote, I headed off to the Oracle Ace dinner. I met some old friends and met some new ones and had a nice mean (Thanks Oracle). While I was at the dinner, The rest of the crew from interRel was at the Partner awards presentation. interRel won the EPM Titan award for a second year in a row. Congratulations to Edward, Eduardo and everyone who was associated with the project

New Essbase book Review - Oracle Essbase 9 Implementation Guide

2009-08-28T07:08:00.000-07:00

Recently, I was asked by Packit Publishing to review a new book on Essbase by Joe Gomez and Sarma Anantapantula titled Oracle Essbase 9 Implementation Guide. I jumped at the chance. There are so few resources available out there for someone new to Essbase, it’s a shame. I understand it’s a small audience, but since the Oracle acquisition it is growing. I would like to thank the authors for spending their time, energy, sweat and tears to create the book. I know it is a difficult task, for I have a hard enough time just updating my blog from time to time. So my hat is off to them for their undertaking.

Before my review, I feel it only proper that I give a couple of disclaimers before I review the book.
1. I am not the intended audience for it as they say it is for the IT professional who wants to start working with Essbase. I’ve been doing the way too long, about 14 years now (I think)
2. The company I work for puts out a competing book. Look smarter than you are with Essbase.(I am not the author nor an editor or reviewer of it).
3. I realize this is the first printing of the book and a tried to overlook errors like typos, obvious misstatements and wrong graphics for the text. I tried to concentrate on the subject matter.

With that said, I came into the assignment with an unbiased and open mind. I attempted to read the book from a new IT professional’s point of view while using my knowledge to insure the material was accurate.

I was excited when I got the book last week and using time I don’t have, began to go through it. I have to say, I really wanted to like the book as another source of information would be invaluable to the Essbase community. I am sorry to say I feel this book falls very short of being a good guide or reference. There were some good points, but the problems out weighted those few glimmers of insight. There are a numbers of troubles I had with the book.

First the good. As bright notes, the book talks about Cube Preview in EAS. It is a useful tool for administrators that no one ever talks about. In addition, they spend time talking about report scripts and even go through some of the syntax. Everyone seems to ignore this topic thinking report scripts are dead. I thank the authors for reminding us that they can still be useful. Even though the classic add-in is dying, they spend a bit of time on Query designer. This feature can be very useful and they mention some of the high points of it. I also enjoyed the introduction to data warehousing. It was interesting, although there was not really applicable to the subject of the book.

What did I not like about the book, I’m afraid more than I liked. I won’t go through everything, but give you a number or items I had difficulty with.

First, the book was hard to follow. While in the middle of a subject, the authors would veer off to talk about something related, but minor. In other cases it appeared pages might be missing or thought processes were incomplete. I would get into a subject and it would just end.. As an example of being hard to follow, when talking about dimension building, they went into a detailed discussion of MaxL. With Data loads, they did it again and then for Calc scripts veered off to Esscmd, this while having a whole chapter on automation and not really adding value to the topic. Another item that made it hard to follow was extreme detail would be presented on what buttons and options meant of various screens, but important functionality would be glossed over. For example, they described the load screen extremely well, but gloss over, the two different types of joins, selection and rejection criteria, adding text, etc, giving no examples at all. Finally, the examples were not cohesive. I never really got the overall understanding of what the database they were building looked like. It would morph into different dimensionality without explanation.

Second, there was not a cohesive flow to the examples. It would have been nice to have examples that built on each other. There was talk about the dimensions of the outline, and one example of how to build it, but I would have liked to build on prior steps. Perhaps first create the dimensions manually and build the dimension members manually, then exercises to add the members of other dimensions through load rules. Once flaw here (and with the Look Smarter book line) is there should be a source for sample files used to do dimension builds, data loads, and results from calc scripts that would allow the reader to easily follow the examples.

Third, unknown or inaccurate terminology was used. For instance, I’ve never heard of a parent dimension. Does this imply that there are child dimensions? Throughout the book the terminology was inconsistent. Dimensions were called all sorts of things. I know this next one is trifling, but in the Calculations section, they call the set commands functions. They are not functions but commands. There is a difference and if you are writing you need to be accurate with your information.

Fourth, this is a book that is supposed to be based on System 9. In the install section you get the most basic of installs, Essbase, EAS and the Excel Add-in. No mention of Provider servers, external authentication, FR etc. They talk about Esscmd (a lot). As a dying interface, why spend the time on it. I have to admit the authors do warn you in the automation chapter that Esscmd is being phased out. Get new users using MaxL, since it is the direction of the future. Yes you can mention that Esscmd exists, but don’t waste the readers time teaching it. I have the same comment about the classic Excel Add-in. While I love the add-in, new readers should be learning Smartview. I guess that would have required the author to discuss how to install and configure APS which would have made that section longer and more difficult.

Finally, I could live with all of the above and still be relatively happy, but there are too many inaccuracies in the book that would either confuse or mislead readers. In some cases the book would give completely wrong information. Here are a very few of the many examples I found.

Did you know that Smartview costs extra? I didn’t but according to the authors it does.

When talking about attribute dimensions, they use color as an example and put it on their product dimension. Their product dimension level zero members are Car models (like Sedan). This makes the reader believe you could have different colors (attribute dimension members) for the same model. No where do they tell you that a base member can only have one Attribute from an attribute dimension associated with it. Attributes on dense dimensions? I don’t know they never tell you if you can, but allude that it’s possible.

A second example is their discussion of Two Pass. It is stated that Two pass is only allowed on the Accounts dimension and only on Dynamic calc or Dynamic calc and store members. While only on the accounts dimension was applicable on version 3 of the software, it has not been the case ever since Dynamic calc came into effect. “Only on Dynamic calc (and store)”, If this were the case, what is there a command called Calc TwoPass. Further in the section on Two Pass, they give an example and lead the reader to believe that if you perform aggregations on the database in different orders of dimensions, your results will be different. The example shows data that is added up to parents. The last time I checked addition and subtraction were commutative. I surely hope that adding up my database in different orders will not affect the results. Had they used the typical example of a ratio supplying different results if you add the sums or sum the adds, I could understand, but this example makes me scratch my head.

A third example of misinformation is in the section on Calc scripts. They give you the following calc script in an example
Fix(@IDescendants(“Calendar Periods”))
“Gross Sales” = “Sales” – “Discounts”;
Endifix
While this calculation is not incorrect, It is no different than just running the calculation on the entire database. In the discussion on Fix, it is stated that items left out of a fix statement exclude them, in truth, dimensions left out of a fix statement are all included at all levels. Like the above, a lot of examples are not well thought out. For Sumrange, the use @Descendants(Products) Based on the outline example they give, you would be summing together multiple levels, parents and children. I doubt that would give the answer one would want. I thought it interesting that they showed examples of calculations, but never showed the starting point or the result. The book claims that after reading the chapter on calculations, you would not need to take a calculation course. I believe the opposite is the truth. After reading their explanations I would need a course more than ever. If I knew nothing about calc scripts, I would have come out of that chapter more confused than when I went in it.

I don’t remember a section on security and I was not impressed with the section on optimization. As you can tell by now, I was rather disappointed with the book. As I said earlier, I applaud the authors’ intentions but I think this book falls much too short to be useful.

Upcoming Webinar

2009-08-13T06:29:00.000-07:00

On Tuesday August 18th (2009) I'll be giving the weekly interRel webinar. It will be on little known features of Essbase. IT is basically the same presentation as one I gave at Kaleidoscope in June. If you missed it then, try to attend this week. You can register for it at www.interrel.com
I was scheduled to repeat the webcast on Thurs Aug 20th, but I'll be unavailable, so my co-worker and friend Cameron Lackpour will be giving it then. Heck sign up for both and see who does it better. At any rate, I think it a worthwhile topic and everyone I know that has seen it has picked up a few tidbits (or more)

4th addendum to Data Export

2009-08-10T09:33:00.000-07:00

Well, I guess the team at Oracle was listening to someone. I just looked at the release notes for EPM Fusion Edition 11.1.1.3 and there plain as day is the bug fix

Data Extraction. While exporting data to a relational database, DATAEXPORT does not create thedelimiters if there are one or more missing values in the last column. [8507606]

Another problem I was getting at a client was when I would remote onto a server using the consloe option, when I logged of the Essbase application would hang. That apparently has been fixed as well

Agent. When Essbase is running as a Windows service and the domain user logs off the machine,
the Essbase applications hang, which in turn causes Essbase Server to hang. [8279377, 8464004]

While I worked around both of these problems, Its nice to know I won't have to in the future

3rd addendum to DataExport

2009-07-13T08:23:00.000-07:00

My last blog post talks about dropping columns when exporting to relational using the dataexport command.
I found the answer looking through the knowledgebase. Turns out you have to set
DEXPSQLROWSIZE 1 to get it to work. I don't know if I like tihs answer as it requires you to basically turn off bulk insersion, but at least it works and I can continue with my development withput having to asdd jexport to the mix.

2nd Addendum to Data Export

2009-07-10T12:04:00.000-07:00

OK, I'm starting to see why people don't like Data Export. I'm trying to export a particular set of data to a relational table and for some reason, one of the dimensions is not showing up in the table. If I change the export to write to a file, the column is there. It is interesting that the order of the row members is different in the flat file than in the relational export. What appears to be happening is it is taking one of my two dense dimensions which shows up as the last row member before my data vailes in my flat file and is moving(or perhaps overlaying) what is my second row member in the flat file. I know that is clear as mud so to show you an example a row my flat file export looks like:

Actual,r1,aaa1,bbb1,ccc1,ddd1,eee1,Tier1,123456.789

In the relational it looks like:
Actual,Tier1,aaa1,bbb1,ccc1,ddd1,eee1,blank,123456.789

so it is shifting or overwriting the R1 with Tier1 and where tier1 should be is blank.

If anyone has figured a workaround for this other than to export to a file and load it into relational, let me know. In the meantime I'm opening a SR with Oracle and se eif they have a fix

Addendum to Data Export post

2009-07-07T19:07:00.000-07:00

As an addendum to my post on Data export, John Goodwin reminded me of a work around I had to do. There is a problem when you are exporting to a relational table and your columns dimension does not have values in the trailing members (for example you have Jan-Dec in columns and you only have data in Jan –Mar) the data export will fail because the record columns don’t match the table columns. . In order to get around it, I set :
DataExportDynamicCalc On
And
DataExportLevel ALL

Then in my fix statement, I had to make sure that the last column of the load would always have data. Testing it by sending it to a flat file confirmed that if the last column had data, the intermediate columns would have something as well. In Sample Basic if Years is your column dimension, in your fix statement you could fix on Jan:Dec and Year. Since Year is a dynamic calc member if any month has data so will Year. Note, this could be be a problem if you have time balance accounts without skip missing turned on). Because you need to set all levels, you also have to make sure you fix on the level zero members of your other dimensions as well. I set the other dimensions to @relative (dimension name, level zero). Since the last column had data it would load to the end.

In talking with others, they have had problems with Data export, So far, I’ve not found anything I could not work around.

Dataexport is great

2009-07-06T09:18:00.000-07:00

I’ve had the need to work with the newish dataExport command (I did it in 11.1.1.2) recently and thought I would share some things I’ve found with it and with Sql interface in load rules. I think you will find my musings interesting.

First, I was using data export to export date to a flat file to act as an audit report for some users. It worked like a charm. Some of the things I found are if you specify to export level zero, no matter what you put in your fix statement it will only look at level zero blocks. Using DataExportDynamicCalc on allowed me to export dynamically calculated members as well. For my 20K rows it did not seem to slow the export down, ut I don’t know the impact on a big data set. I could also specify DataExportDimHeader ON to get column names. Using DataExportColHeader "Dense dimension name" I could specify the dense dimension I wanted as the column. It would have been nice if I could put a single member of a sparse dimension there, but I understand why I can't.

Next I needed to back up some of the static information from my cube. Static in that it is user input for my testing and I didn’t have an easy source to reload from. I set up a fix statement and used the binfile option (DATAEXPORT "Binfile" "fileName"). It created a file that I could reload to my db. I can see the usefulness of this on a production database where you need to save off something like budget, clear the database and reload it with source actual and the budget. It’s much easier than the old export and reload and much quicker. In addition, you can be selective of what you export instead of exporting everything.

Finally, I needed to load data populate a Sql database with data from Essbase, modify it and load it back. Yes there are some things that Sql can do better than Essbase. In this case, It was to take two disassociated sets of data and merge them together. It needed to join them on the one common dimension member and basically turn the two 3k row tables into about 1.5 million records that get loaded back into Essbase. I set up the ODBC driver with no problem and exported the data into flat files to see their structure. I then created tables that matched the structures. I will say that there is where I had minor difficulty. If the columns don’t match exactly, the export fails with little information (Just the typical messages in the log that tell you “you screwed up”). I played around with the table figuring out that I miscounted the columns and fixed it and it worked fine. I defined the amount columns as float and found that for #missing values Essbase stuck -2e-15 in the columns that were once #missing in Essbase. A quick stored procedure and I converted them to null.

Oh but wait, how could I run the Stored procedure. I could run an OSQL but the instance I was working on the tools are not working right. I could get into Sql Server, but could not run OSQL or Bulk insert. So thinking swiftly, I thought of load rules. A load rule is just supposed to take Sql commands, so how could I get it to run a stored procedure. I know I can put statements like a union in a Sql statement, So I tried something like:

Getdate()
Exec myStoredprocedure.

I clicked ok/retrieve, entered my id and password, and lo and behold, I got the system date back into my load rule. I checked my tables and the bad characters were converted to nulls. Wow it worked. Who would have thought? I figured I could use the load date to update the alias of a member to set the last time the calculation was run. Another suggest I had was to use rejection criteria to reject the row and load nothing. I used this technique to run another stored procedure that truncated and populated a table from the tables I loaded, so my next step was to create a load rule and bring the data back in. Everything was done without having to resort to anything but MaxL statements.

I’ve since added a custom defined function that will run the SQL statement directly from the calc script. I got this from Touifc Walkim the development manager for Smartview, a very nice guy and CDF guru. Some clients don’t like the idea of CDFs so I have my origina method available when necessary. IF this works, you can get the CDF here

FileDropper Free File Hosting

Adding DATAEXPORTENABLEBATCHINSERT TRUE to my config file made the process run faster as it allows the dataexport to use a batch insert method(when the ODBC driver allows it).

As I use Dataexport more, I’ll comment on my findings, but I have to say I’m impressed so far. I have asked for enhancements to the command, and they have been added to the enhancement list. I’m interested to see if or when they are implemented. I was very happy when I was told they were added to the list since it appears that Oracle is open to hear what people recommend. Some of the things I recommended were:
Add an append option to the file export so you could have multiple fix statements write to the same file
Add a debug option to the SQL export to see the SQL statement generated to make it easier to debug when you have problems
Allow aliases to be exported instead of file names.

Let’s see if these get implemented.

Kaleidoscope wrap up

2009-06-26T12:01:00.000-07:00

Well I just got back from Kaleidoscope and am still on a sleep deprived high. I don’t think I got to sleep before 2 am any night thanks to the special midnight madness (ask the expert panel) and getting sucked into games of Werewolf. On Wed night we had over 70 people playing or watching multiple games. But the conference was not about game playing, it was about knowledge.

I presented at two sessions, 10 Optimization tips you (probably) never heard before and Little used features of Essbase. My first session was a 60 minute session and I went 75 minutes and hardly anyone left the room. (sorry guys, I didn’t realize I was running so long). For my next session(back to back with the first(arg), I had someone with cue cards telling me how much time I had left. This was a 90 minute session and I never made it to the last two topics reference cubes and Data mining. I put these as the last topics because I think they will be the least used, but many were disappointed that I didn’t talk about data mining. Frankly, I think this is really better done in relational with larger sets of detailed data anyway.

The pre-conference started on Sunday with a symposium put on by the Oracle Project managers. They discussed the road map, new products and solicited feedback. A lot of what was in the presentations we were asked to not blog about. For me the best presentation of the day was from Toufic Walkim the new Smart view product manager. Toufic has been around a long time and has helped me with numerous things (I believe he wrote the original jExport cdf). He talked about changing the Data connection manager (as he said, trying to get it right for the 4th time) The direction they are going looks like the right way. He also talked about a bunch of new functionality like context specific menus (so if you are in Essbase you only have Essbase options) and a lot of other things. In the Middle of the day Robert Gersten talked at a high level where things are going. It looks like they are putting a lot of R&D into the products.

Sunday night was a reception for all attendees, it was well attended and the food was good. I was in the interRel booth most of the time, so afterwards we went and grabbed a bite of food on our own. I’m not sure if I can call the interRel booth a booth. There were couches and comfy chairs in front of a TV playing the best movies (Princess Bride, Goonies, Lord of the Rings and space balls were some of the offerings during the week). There were also video games and Rock Band going most of the time.

Monday started with a keynote from John Kopcke. It was an interesting discussion on using EPM and management excellence. I think it might have been lost on some of the apex and other developer tracks in the group, but it was a good talk sprinkled with humor. We then went into sessions. The first session I was in was Calc scripts for mere mortals by Gary Crisi and Edward Roske. I was the ambassador for the session so I introduced them. I have heard the presentation before from Edward, but it is always good to get a refresher. I always seem to come away with something new or that I had forgotten about. At the same time there were two other presentations going on and a hands on lab. I think at the lab, people almost came to blows trying to get in. You had to pre-signup for it and it filled very quickly. People tried to crash the party, but when there is no room there is no room. We have talked about trying to expand the labs next year, but as a reminder next year sign up as soon as the schedule is open.

Next I sat in on a presentation from Matt Milella on top 5 Essbase CDFs. It was a good presentation which he said he was putting on the Essbaselabs Blog. They have a library of over 300 cdfs that they are trying to find a home for. If you need something, ask them and they have probably already created it. Matt did a very good job and I can see a lot of use for his CDFs.
After that were vendor sponsored presentations. They were the only sponsored sessions of the week. I heard good things about all of them. They were less Ad like than one would expect and all offered technical content. I was asked to sit in on the one interRel offered on implementing HFM in less than 6 months. I’m not an HFM guy but Tracy McMullen did a good job. It was really more on managing this type of project to get it done than the product itself. But this is what is so important in this type of project.

For the last session of the day, I sat in on Driver based forecasting by Ron Moore. He had good content, but I was pulled away by some work issues and did not get to see the entire presentation. bummer

The day ended late with a special caffeine and chocolate 10:00 to midnight aske the experts session. It was better attended than I expected with over 60 people there asking a panel various questions. I don’t think the panel got stumped at all. A rare occurrence. After the panel, some people got together to play werewolf. I’ll not tell you much about it, but it’s a chance to kill off your friends, coworkers, etc. For more info go to http://www.eblong.com/zarf/werewolf.html
All I can say about Tuesday is ASO, ASO, ASO. I sat through two presentations from Rudy Zuca on Designing your way out of BSO with ASO and ASO Fundimentals (These two were out of order due to an oversight), then Querying ASO with MDX from Mike Nadar and Gary Crisi, followed by I knew how it do it in BSO, how do I do it in ASO. This was followed by Optimizing MDX in ASO and Optimizing ASO both given by Steve Libermensch. All I can say is wow. While there was some overlap in content, if you didn’t know ASO before the sessions, by the end of the day you were an quasi-expert. The best thing I learned in the day was from Steve. If an ASO formula says in the log it is calculating in cell mode, it means it is too complicated for Essbase to handle efficiently. Create new members in the cube and have intermediate calculations that your formula calls. Even though these still have to be evaluated dynamically, it is more efficient for the ASO formula optimizer.

Tuesday evening was a sundown session with the Oracle Ace directors, Time Tow, Edward Roske, Tracy McMullen and Mark Rittman. There was interesting discussion during the presentation, but not any one thing that sticks in my mind to talk about. After the session, was a reception, then I was off to the Oracle Ace dinner and afterwards, an appearance at the Hyperion SIG mixer, then more werewolf.

I was very busy Wednesday as I was asked to sit in on an optimization session for moral support from Scot Marin from cash America. Scot did a fine job and I just talked a little on some more arcane questions. After that were my two sessions. As I stated before I think they went pretty well. I missed the session on How Essbase thinks. I really wanted to go to that as Tom Tortolani and Edward Roske were speaking. Tom helped create Essbase long ago. I got called and had to do real work. Bummer. I was able to attend the 64 bit optimization session. It reminded me, I have to rethink and retest when going to 64 bit as the rules change a lot. In some cases you throw away the optimizations you did for 32 bit. For my last session of the day I was involved in an optimization round table. There were a ton of good questions. Wednesday night was the Kaleidoscope event, dinner and a comedian. Both were very good. Kaleidoscope does not skimp on its food budget. There was variety at every meal and all was tasty. After the comedian, there was a DJ for dancing. Since I have two left feet, I went with a group for the last werewolf game. It was a lot of fun.

Thursday morning came very early and fast. The first session I was going to attend, through some sort of mix-up, the speaker did not show up, so I have conversations with a number of others in the room. Then I listened to Cameron Lackpour talk about MaxL using variables, error handling encryption and finally putting it all together to do something that Planning currently can’t do, use metaread filter. It was a very good presentation. I watched the first half of automating with Perl by Angie Wilcox, but had to leave early and hit the road for a long drive home.

In about 90 days the presentations will be on the ODTUG website. If you were not able to make the conference, look for them there. I would recommend start putting a bug in your boss’s ears for next year. I’m sorry for those who could not attend, you missed a lot.

Oh I forgot to mention the new ODTUG Hyperion Sig board was announced. Elected were (in random order)
Edward Roske
Angie Wilcox
Natalie DelemarJeff McAhren
Doug Bliss
Gary Crisi
Cameron Lackpour
Quinlan Eddy
John Weimar

Please give them your support and offer assistance. You don’t have to be on the board to help.

Ask the experts at Kaleidoscope

2009-06-05T16:35:00.000-07:00

On the Tuesday night of this year's (2009) Kaleidoscope conference will be an Ask the Experts panel. We ae soliciting questions for that session. If you have a burning desire to know something, now is your time to ask. Send your questions to Edward Roske eroske@interrel.com (or you can send them to me) or you can even give them to us at the conference.

From what I heat, this special "Midnight" session hosted by the hyperion sig, will feature lots of chocolate.

If you are unable to attend the conference this year, don't fret. I know Edward is planning on blogging on it and if I can get time, I'll post my thoughts on the sessions as well.

If you are at the conference, please say hello.

Kaleidoscope revisited

2009-05-28T07:54:00.000-07:00

With Kaleidoscope less than a month away, I sent in my presentation on little used features of Essbase. I think there is some cool stuff in it. At least I was impressed with some of the things I found. As a preview, I talk a lot about load rules and things I never used in them, the expanded role of substitution variables in newer versions, Triggers, Query logging and much more. I'm excited to give the presentation as I think it has information anyone can use. I think, I may end up doing a second presentation for a presenter that can't attend, but that is still in flux right now, so I won't comment on it more. After Kaleidoscope, I'll post sections of my presentations here for those who could not attend. But it's not the same as hearing it in person. For one, you don't get my dry humor (ok, attempt at humor) or my enthusiasm

Speaking of attendance, if you are still on the fence about going, I would recommend it (as I have in the past). I've seen some of the presentations and they are awesome. They are technical with things that developers and advanced users need. For a couple of more days, you can use the code IRC (interRel consulting) to get the early discount. But I think it expires on June 2nd. Last year I paid for the conference myself and it was worth it. Take vacation if you have to in order to attend. It's better than any single training you can go to.

I hope to see you there. If you are introduce yourself or if I know you and you are not avoiding me, say hi.

Random thoughts

2009-03-24T11:04:00.000-07:00

It's been a while since I entered anything on my blog, so I thought I would share some random items.

First, I spend a week at Oracle at an event called Xmonth which is a partner seminar. The 15 or so people in the seminar were given an overview of things to come and had a chance to play with some of the features of products. While Non-disclosure does not allow me to talk about it a lot, I will say that most of the Hyperion products are stategic in Oracle's future.

Second, I finally got out of the 18th century and joined linked-in. I did it to join the ODTUG Kaleidoscope group at http://www.linkedin.com/groups?gid=1796201 I'm not sold on it. Its a fad like PCs and fire, but I look forward to seeing what it does for me.

Third, as I've mentioned before, I'm presenting at the Kaleidoscope conference on a session called little used features of Essbase. If there are particular features that you would like more detail on or think others would get benefit from knowing about, let me know, I'm putting together the full content now. I've spent enough time talking about the merits of this conference so I won't bore you more with it. If you want more information, look at the agenda at http://www.odtugkaleidoscope.com/

Finally, a little technical content.
I had a client create a small cube for some really quick analytics. It was a small cube, under 100 meg. I got a call that when they tried to export data out if it, it gave them an error. In sending me the log, it was actually throwing an exception and crashing. I looked through the log and quickly found the problem. They had defined all of the dimensions as sparse. I replicated the problem on my system and sure enough, it crashed also. So it appears in order to export data, you have to have at least one dense dimension. We talk about tiny blocks if you make everything sparse, well, here is a real reason not to.

I've also ben playing with Smartview in EPM Fsuion Edtion 11.1.1 and while I have been an Essbase Add-in bigot, I have to say the enhancements they have made with the later versions makes me want to use it more. If they could get substitution variables, the ability to have both member names and alias and for it to allow the use of multiple alias tables at once, I would probably completely convert to smartview. Oh, I would like HFM to be part of the common provider. I believe that is coming. There are some really cool things like multiple retrievals on one sheet, the ability to create slices and even more use the slices to crate reports with grids, tables and graphs makes smartview my choice for a lot of quick reporting. I also like that you can pull in Planning forms and use them. The next version will have more functionality, but you will have to wait until Oracle tells you about it. But just wait it's cool

Update on report script commands and more on ODTUG

2009-02-05T09:37:00.000-08:00

I was looking through the tech reference the other day (9X) for something and noticed a couple of report writer commands that I had not seen before. They deal with using aliases or member names. AS you might remember we used to use {OUTMBRALT}, {OUTALTMBR}, {OUTALT}, {OUTALTNAMES}, OR {OUTMBRNAMES} sometimes with little success. You had to place one of these commands before the dimension you were defining in the report and change it before the next command if you wanted something different for the next dimension.

When they added duplicate member names, they created new commands
<REPMBR , <REPALIAS, <REPMBRALIAS, and <REPALIASMBR
They offer more flexabilitiy than the old commands. You can dfine multipe dimensions at once that share the same attribute.

For example if you want aliases for Product and Market you would specify
%lt;RepAlias "Product" "Market"

It should be noted you can't use these commands mixed with the old commands (exception bing to select the alias table <OutAltSelect) and you can't use them if you are using rename functions. There are additional commands to get fully qualified names, but by looking the above up in the tech reference you should be able to find them.

You may nave noticed I've been posting a lot about the ODTUG conference. I had a comment about me being crazy(well not as nicely put as that) to suggest that people pay for it themselves if their company won't. I really believe this conference adds so much value and knowledge that it is worth it. I paid my own way last year and would again if my boss were not so supportive. (an amazing compliment. Edward, don't get used to it)
If you need rational to present to your boss to compel him/her to let you go, I suggest looking at Gary Crisi's blog entry. http://garycris.blogspot.com/2009/02/some-tips-for-kaleidoscope-2009.html He has great tips on how to get this conference approved in these tight times.

Exciting news about the ODTUG Kaleidoscope Conference

2009-02-03T14:28:00.000-08:00

I just got word that the ODTUG Kelidoscope has confirmed John Kopke for it's Keynote address and Robert Gersten for the Sunday Hyperion Symposium Keynote. In addition, there are hands on sessions planned for OBIEE, ODI, Essbase Studio and Creating your first Java Essbase application. (Standard disclaimer, this is all subject to change). I believe there will also be a "Midnight Madness" for old people (it starts at 8:00 pm.) where you can bring your tough questions to the Essbase Aces and get them answered.

While I realize budgets have been cut this year in many organizations, this is such a good conference with so much in-depth information, it is something you should not miss even if you pay for it yourself. All the cool nerds will be there including all of the Oracle Ace and Ace Directors in the BI space. Last year we were a minority of the coference with one track, this year there will be four tracks including the hands on. You could not pay and get this much training in one week.

While this might sound like an ad, and is some ways it is, if I did not believe this is the best technical Hyperion based conference to go to, I would not be talking about it.

Blog Rant

2009-01-27T13:06:00.000-08:00

I apologize in advance if this entry into my blog offends anyone, but today I want to talk about a pet peeve of mine. As most of you know I am a frequent contributor to the Oracle and other forums. There have been multiple threads with a common theme.” I know nothing about Essbase, how do I get certified?” In addition, I’ve looked at web casts and other web sites that help you with the certification questions. One went so far to say “We know our questions and answers are good, we buy them from the internet”. They are willing to teach you, not how to use Hyperion products, but how to pass the test. I will admit they have a disclaimer that passing the test is no substitute for experience. If you don't know anything about the product you should not be certified.

Certification means nothing without experience, so what that I can memorize the formula for computing block size, unless I know how to apply it and the implications of the block size on performance, it means nothing for my clients. Diluting the pool of certified professionals just cheapens the value of certification. I have interviewed (and worked with) certified people who could not answer the most basic questions and have wasted so much client time and money. In some cases, people have gotten jobs (permanent or consulting) from being certified, Did they last long? In most cases NO. So they got paid for a short time, don’t have a reference and leave behind unhappy and upset clients. It makes it harder to get the next job.

I realize that some people get certified because their company wants them to. I’ll admit, that is why I got my latest certification, but I have the experience and knowledge to back it up. Can you say the same thing?

Don’t get certified for the wrong reasons, get it because you know and understand the product(s) and can truly be a help to the client(s) you serve.

Please, I’m interested in your comments, Tell me why I’m wrong!

Varied topics

2008-12-19T12:24:00.000-08:00

It’s been a little while since I’ve written anything; I’ve just gotten bogged down in actually doing work. That is what happens when you start a new job and go to a new client. There are a few things I'd like to discuss.

First, I would like to congratulate Tracy McMullen from interRel on becoming an Oracle Ace Director. It requires a lot of work and she deserves it. For those of you who don’t know, There are two types of Oracle Aces. The standard Ace and Ace Directors. Oracle Aces are nominated for their work helping others and willingness to share their knowledge. Oracle Ace Directors are those people who go beyond what an Ace does and truly evangelize Oracle products. Being an Ace director also requires additional work doing presentations, attending events and many more things. While you don’t have to do much as an ACE, you have to commit time (actually sign an agreement) to what you will do as a Director. Tracy jumped over the Oracle Ace level and was honored as an Ace Director for all of her past and future work. I’m sure Tracy is up to the challenge, so I am glad she was bestowed this honor.

Second, I’ve been on the committee putting together the ODTUG Kaleidoscope conference this year. It will be bigger and better than last year. In addition to Essbase topics, we will have sessions on other Hyperion applications, reporting tool and even hands on labs for things like Essbase Studio, OBIEE+ and Java. While there might be changes(standard disclaimer) you can see the schedule on Tim Tow’s Essbase Blog http://timtows-hyperion-blog.blogspot.com/2008/12/tentative-odtug-hyperion-schedule.html If you attend no other conference this year, be sure to attend this one. It is completely technical in nature and you are constantly learning. I learned a lot last year and expect to get a lot out of this year’s conference as well. It's much cheaper than training classes and you learn so much more than the basics you get from a class.

Third, when I started my blog this last spring, there were not a lot of other blogs out there dealing with Essbase topics, but I’ve been looking around the web community and am amazed at the number of new Essbase related blogs that have popped up. There are a lot of people putting out quality information. I’ve read about ODI, OBIEE, EPM Fusion Edition 11.1.1 (.1), tuning tips, MDX formulas and so much more. There are a few links to other blogs here on my site, and each of these blogs has links to other great blogs. Spend a few minutes and you’ll be amazed at how much you can find.

Now onto some more technical stuff. I recently have been working with a couple of other skilled consultants and we were running into a problem with a dimension build (Parent/Child) coming out very funky. At first we were doing it from SQL and it seemed to work fine, but when we BCP’ed the dimension data to a flat file and tried to run it, we got the outline not building the dimension properly. It took a while to figure it out. Turns out the alias column for one member had nothing but a space in it. This caused problems. In our case it was easy to change the view we were pulling from to substitute the member name where the alias was a space, but it was odd that from SQL it was ok and from a flat file it wasn’t. As another workaround, I guess in the rules file we could have replaced space with a character selecting all occurrences, then replace the character with nothing matching whole word only and finally replacing the character with a space for all occurrences. It sounds like a lot of work sometimes is necessary. A similar thing can be done to replace a null column with a default value.

A new beginning

2008-10-21T07:30:00.000-07:00

Although most of my entries have been technical in nature, I depart a bit in this one to give some personal news. I have accepted a new position as a consultant with interRel consulting and start Friday Oct 24th (2008 for those of you like to look through really old blogs). While I enjoyed working with those that became really good friends at Narratus Solutions; after all I only worked with them for 17 years, it was time for a change. I am excited about my new position and a bit apprehensive as interRel has some of the brightest and most knowledgeable Oracle BI (Hyperion BI) consultants around. This is really good for all of you because I can share new things I learn. The one thing I have learned over the years working with Essbase is no matter how much you know, there are always things you don’t know. As I go forward in my career, I find that the more I learn, the less I know. It really is amazing how much they have packed into the applications now.

While I don’t answer technical questions sent to my email as I prefer you post your questions to the Oracle Forum http://forums.oracle.com/forums/category.jspa?categoryID=145, if you need to contact me about business or personal matters, I can now be emailed at Gschwartzberg@interrel.com

I’ve been told, I should to blog( I was encouraged to increase my blogging) and am even allowed to flame Edward Roske once in a while (Relax Edward, I won’t do it more than once an entry). Some of the interRel people have even offered me bonuses if I do. I think I’m going to like it here.

Let the journey begin

Open World Tuesday

2008-09-23T23:04:00.000-07:00

OK, I’ll start out with a couple of observations about the conference. First, I find it interesting that the scheduler builder web site went down Sunday night and was not available Monday most of the day when I tried. Also getting to the agenda did not work most of Monday. Perhaps Oracle should talk to a software company to help them have software to support the load. Not a good showing Oracle. Second, I don’t understand why snacks come out at the end of breaks and right before the next session is starting. In addition, why are coffee and tea limited to certain breaks. It would be nice to have it available between sessions. With all the money Oracle is spending on the conference, I’m sure this could not add much to the cost or running the conference. It would also keep more of us awake on these long days. Finally, why do we need an hour dedicated to the exhibition hall? I realize the vendors paid a whole lot to be there, I’m sure most people have been though the exhibition halls multiple times already. We don’t need to cram everyone into the feeding frenzy at one time.

My first session of the day was “Management Excellence, Performance Leadership” by Frank Butterendijk (Hope I spelled that right). Although this session was listed as Hyperion, It wasn’t. Frank is an interesting speaker and related Performance management to cultural Analysis. He did this only using one power point in the presentation. He wrote a book on the subject and although I have not read it, if it is anything like the way he talks, it should be a good read. Frank talked about compensation being related to revenue which is his opinion is wrong, He says it should be more related to contribution. I agree with this. It is wrong due to two reasons, thoughtlessness and laziness. It’s easier to do things the way then always have than to come up with better ways. Some other things Frank said that I thought interesting were? Performance indicators need to be assigned to individuals and not shared. Shared responsibility is no responsibility. He had some interesting cases which the audience helped him evaluate (His powerpoint slide). Overall he was informative and enjoyable, but I left a little empty thinking it should have been related to how to define key metrics or how to evaluate them or something related to software that would provide tangible results.

Most of the rest of the day was a bust. I had to skip a couple of sessions I planned to attend to provide my own little un-conference for a client that had created a cube and had performance issues with it, I spent a couple of hours teaching them about dense/sparse settings, outline organization, cache settings, Load rule optimization and ASO cubes. In the end I think they got a lot out of it.

I did find what I think the best deal of the conference is.(At least to me). While I didn’t pick up any of the junk any of the vendors were offering. I found if I had lunch at the Yerba Buena gardens, they gave me a cool oracle blanket to sit on in the grass. I got to keep it. Ok so I picked up one think I don’t need, but really like.

I finished off the day listening to Ray Roccaforte and John Kopke on Oracle Essbase in the middle tier and Oracle OLAP in the database tier. I thought Ray was a bit boring, just re-reading what was on his power points. John was much more polished and more enjoyable to listen to. While I though this session was going to explain to me how to integrate the two technologies together, it really was two distinct presentations. The first describing Oracle Olap and the second describing Essbase.
Is short the grid they provided showed the differences. They went though it quickly and hopefully I got everything correct in it. Some of it is summarized.

I was supposed to go to a party hosted by Applied OLAP and a couple other companies, but I got out the session late and didn’t want to figure out how to get to the hotel. Instead I want to the Oracle Ace dinner and had a nice time talking to a number of other Aces and Ace Directors.

I’m afraid, this will be my last post on the conference. I have to go back to the real world tomorrow and meet with a client.

Open World Monday

2008-09-23T06:35:00.000-07:00

Well, I was going to do my write-up last night but I've been getting over the flu and after a looooong day bed sounded better than writing.

I started the day off with some personal business so I missed the keynote. I'm not really heartbroken over that. When I got to the conference, It was in time for about 30 minutes of looking at booths. Very impressive, but I didn't sign up for anything, didn't get any of the junk they were giving away and didn't play any of the games they were enticing me with. I guess I'm getting old.

Although I'm an Oracle Ace, I decided to attend the "So you want to be an Oracle Ace" presentation at 11:30. I wanted to figure out what I'm actually supposed to be doing as one. Due to some mix-up on my part, I was late and didn't hear the first part of the presentation. I'm sorry for the presenters as the room was almost empty with about 20 people there. When I walked in. there on the screen was a quote attributed to me in a survey the presenter had sent out to all the ACES. Wow, I'm being quoted now. Too bad there were so few in the room to see it. The main theme of the presentation is that Oracle Aces don't need to know a ton, but have to have the attitude of wanting to help others. Doing this through Blogs, forum, articles are all ways to get noticed. They said that most Aces didn't try to become Aces, they just helped others and one day someone called them and told them they were an Ace.

After this session was lunch. They had a few choices, I ended up with a chicken caesar salad. Nothing to write home (or blog about) but it filled the void.

The next session I attended was the Oracle BI Roadmap and Strategy presented by Paul Rodwick. While many things he said echoed what I had heard from John Kopke on Sunday, he did go over other things. Some things I found interesting. The next release of OBIEE 10.1.3.4 will include a sample apps section that has 20 dashboards which are what Oracle considers the best practices of what a company should be looking at. There is Iphone support including approvals which is write back from the phone to an application.
He talked about what are strategic applications, these included Hyperion Planning, HFM, Oracle Integrated operational planning (combines financial and operational plans), predictive reporting (crystal ball)and Essbase.
He talked about fully functional prepackaged BI applications that will be fully functional out of the box .Trying to put me out of work I guess, but good for companies who can’t afford custom solutions.
He then talked about the next release 11G which has a list of over 140 enhancements, A few of the things he covered were a New OBIEE start page that is personalized, Answers + which is optimized for Essbase but still fully functional for an array of products. Paul talked about OBIEE having write back ability thus turning reports into forms. He talked about a lit more I forgot to write down. As he wound down, he went over a quick list of other products he had not talked about before and then said that none of them are going away. Oracle will support them and as you want to migrate from one product to another, there will be migration tools available (but you don’t have to migrate unless you want to).

I’ll be a little less verbose about the next two sessions. Both were on Planning The first was what’s new and coming. The highlights from that session were:
Attaching documents to forms, Member formulas on forms, Calendar based date selection. Run time prompt improvement (yeah), hiding and showing missing/zero rows. Administrative changes include: security to folders, support for all attribute dimension types, and a wizard for data sources.
The presenter (Guillaume Armaud) went over changes to associated products. For example, Calculation Manager which will replace HBR can turn existing calc scripts more graphical and is web based.
EPMA 11.1.1 which will work with 9.3.1 is fully functional now.
In Planning 11.1.2 they are including Public Sector Budgeting, Sources to EBS and SAP, Excel and Office based planning, Web UI and data form improvements and even more improvements to Calc Manager and Life cycle management. There was some talk of ASO with Planning as well.

Next was Planning Tips and Tricks. By Stephane Tibault. He is very knowledgeable about Planning, but was VERY hard to understand. Because of it and being very tired I have to admit, I nodded off during the presentation. While I was coherent, He talked about Essbase optimizations, Dense and sparse settings, switching outline order between calculations and retrievals. After that, rather than talk about tips, he seemed to go off more on talking about features of the new release and demo how to turn them on. I guess those are tips, but I what I was thinking of.

With the sessions over for me for the day, I had weaseled and invitation to a reception hosted by interRel, Star Analytics and Applied Olap. I’m grateful for them allowing me to drink their booze and eat their food. Look up these companies if you need help, they are all good companies with nice people. I hate to say it, but they had a drawing and I won a Ipod shuffle. I think they would have preferred it to go to a client. Sorry guys. Edward talks more about the reception on his blog, so I won’t bore you with the details, But I had a nice time talking to old friends I saw there.

Well I’m off to OW for another exciting day (alas my last as some of us have to work for a living) but I’ll let you know how it goes

Sunday at Open World

2008-09-21T21:51:00.000-07:00

Well, I flew up early this morning to San Francisco for Oracle Open World. and got back to my hotel around 9:30 pm. It makes for a long day. I do have to say I was happy with the first day's events. After registering( A fairly painless and fast process) I went to the ODTUG symposium on Hyperion. Four sessions back to back.

The first session was by Edward Raske of interRel. Edward has a very dry wit. Think of him as a dry martini. You know the kind. Gin with a picture of vermouth. He is an acquired taste, but quite knowledgeable. His session on "How Essbase works" was very good. Edward has an interesting approach to sessions. He creates about 200 powerpoint slides and tells you how good the ones he is not showing you are. He went over some basic info on block storage cubes(BSO), nothing really new if you have been around, but very good for most of the audience. He then switched to ASO tried to show the differences. Not an easy task, but interesting never the less.

The second session was on Hyperion reporting tools by Sean Bernhoit. While the information was ok, there were a few errors, like saying the Excel add-in and smartview can't exist together. This is not true, you can have both, just not on the same worksheet. Plus you have to turn on the Add-in option for mouse actions on connected sheets only. While most of the information from this session was good, I found the presentation to be very redundant and a bit boring(sorry Sean). If you didn't know anything about the different reporting options, the info was ok. The reporting options he went over included:
Financial Reports
Interactive Reporting (which I think is actually called production reporting)
Web analysis
Smart view

Sean could have included Smartspace gadgets, Visual Explorer and a few other tools, but what he covered was ok. He also mentioned OBIEE which was covered in the next session.
Maybe I'm just bitter because I was originally asked to do this presentation and didn't because I was not sure if I could attend.

Between this and the next session was a break. I was disappointed as nothing was ready at the prescribed time and when stuff came out it was only soda, coffee, tea, apples, oranges and bananas. I was starving as I didn’t have lunch and this did not help my blood sugar much.

The third session was from Mark Rittman. Mark explained to the group how OBIEE and Essbase can work together. I don’t think today was Mark’s best hardware day. He upgraded his VMware before the session and was struggling to make sure it all worked right at the last minute (most did, but there were a few issues). Then the microphones would not work well for him. He struggled through, being the professional that he is and I think it went well in spite of the problems. This was a good session for me as I really have not looked at OBIEE. Mark gave a good detailed account on why to use it. In essence, it's so the data warehouse guys can get Essbase data with their relational data. Mark gave a really good walk through on:
1. How OBIEE is organized (structure, server components, logical components)
2.How to connect Essbase into OBIEE
3. How to bring in a cube to OBIEE (I found it interesting that OBIEE turns the cube into a relational model)
4. Problems with the import
5. How Essbase studio can be used to use OBIEE as a source for Essbase databases.

Most of what he talked about is version 11.1.1 specific, but was very interesting.

Mark posted on his blog the directions for connecting Essbase to OBIEE http://www.rittmanmead.com/2008/09/20/loading-obiee-data-into-essbase-using-essbase-studio-111/

The final session of the day was John Kopke. It was an interesting talk on the direction of EPM at Oracle. Basically, The Hyperion products are here to stay and are the direction for a lot of what goes on. Edward Roske’s blog did a good job of talking about . Rather than repeat everything here, look at his blog http://looksmarter.blogspot.com/.

I went to the keynote and although I’m fairly apolitical, I enjoyed the discussion of the presidential campaign by Carville and Matalin.

After the keynote was the opening reception, various drink, food and LOUD music. The food was ok (remember I was still starving). I’m sure it is difficult to server so many people at once, but the lines were long and the food just ok. (I really liked the pork eggrolls). Well, it’s getting late and I have to be ready for more tomorrow. I’ll try to post something tomorrow night. Unfortunately, I’m only going to be at the conference through Tuesday. I’ll post what I can. I even gave away my wristband to the party on Wednesday and it’s going to be a great party!

Partitions

2008-08-12T18:32:00.000-07:00

As you have seen, the first entries in my blog have been a three part series on report scripts. This was initiated by a comment in Edward Roske and Tracy McMullen’s (Best co-author ever according to Ed) book “Look smarter than you are with Essbase”. In the book, and I really paraphrase, they said, “Report scripts are useless”. I had to prove them wrong!

I don’t know if I succeeded or not, but I had fun trying. It was actually much more work than I though it would be since I’m not a writer. Doing these types of articles takes a lot more work than I thought it would… God why did I ever start a blog? Because of this and because I’m swamped at clients, I’m going to take a different approach for the next few entries. I’m going to give short comments of things I’ve had problems with or found or tidbits of old obscure knowledge. As I have time, I’ll create more articles. If you have any requests for topics, please let me know.

The other day I ran into a problem that I never expected. I built a partition and every time I saved it, it showed as orphaned. Interestingly enough, it still worked, don’t know why, but it did. After trying different things to fix it, different ID, using MaxL to define it, trying it on different cubes, simplifying it, etc, I had to go to support for an answer. They gave an answer that I didn’t expect. I had to change the Essbase server names from friendly network names to the IP addresses. Seems it had problems understanding the friendly name. While I don’t like this because the client I was working with “swings” servers and the friendly name switches to a new IP, it did fix my problem. When they swing, there is now an additional need to change the partition, where the friendly name would follow directly.

Talking about partitions, I’ve had questions about mapping a single member to different members in between a source and target cube. The instance was the same accounts for an entity needed to be mapped to different accounts than the same accounts for a different entity. For a visual
For Entity 1 here is the mapping:
Revenue is mapped to internal revenue (But not the IRS)
Sales is mapped to Internal Sales
Expenses is mapped to “Total Expenses”

For Entity 2
Revenue is mapped to Revenue
Sales is mapped to Sales
Expenses is mapped to “Total Expenses”

They way to do this is to is to have two areas each with their mappings specific to their area. In general a snippet of the partition definition in Maxl would look like:

Create or replace partition ……..
Area “Entity 1”, “Revenue”, ”Sales”, “Expenses” SourceArea1
Area “Entity 2” ”, “Revenue”, ”Sales”, “Expenses” SourceArea2
Area “Entity 1”, “Internal Revenue”, “Internal Sales”, “Total Expenses” TargetArea1
Area “Entity 2”, “Revenue”, “Sales”, “Total Expenses” TargetArea2
Mapped TargetArea1 (“Revenue”) to (“Internal Revenue”)
Mapped TargetArea1 (“Sales”) to (“Internal Sales”)
Mapped Globally (“Expenses”) to (“Total Expenses”)
Outline direct

You will notice I can map Expenses globally since it is common between the Areas, but Since the names are different in Area 1 I have to map them individually. Since there are no specific mappings for Area 2 I don’t need to specify anything.

Finally, as I was going through a partition the other day, I was banging my head against the wall for a few minutes. The partition did not appear to work correctly. I had one mapping “Actuals” loaded to “Loaded Actuals”, which was a change as it originally “Loaded Actuals” to Actuals. Turns out, I had to recreate the partition and when I did, I set both the source and target Areas to Actuals. Even though I had the mapping in place, because I did not include the member I really wanted to map to, it was smart enough to find the Actuals member in the target and loaded the data there. I hate being stupid like that, but it’s the little things that will bite you in development. I was looking for big problems and it was a little one.

Wow this was longer than I thought it would be. Once I get started it’s hard to stop!

The Power of Report Scripts (Part 3)

2008-07-10T08:27:00.000-07:00

I returned from the ODTUG Kaleidoscope conference 2008 tired but refreshed. It was fantastic to spend 5 days in the same room as 150 other Essbase geeks and gods and nerds. If you were there, decide which category you fit into. Needless to say I learned a lot and got to meet many whose names I’ve seen on the discussion groups and was able to put faces to them. It was definitely worth attending. It was the best technical conference I have ever attended. Plan on attending next year you will not be disappointed (If you like technical stuff like me). I’ve heard the Hyperion tracks are being expanded to include other products besides Essbase. We were the minority this year; I can see us taking over the conference next year. Because of spending a full week away from work I get very behind and my blog took a back seat because of it.

In the first two parts of my blog, I concentrated on how to use report scripts to help automate and retrieve data from Excel, in this part, I will go over some of the ways to use report scripts stand alone and offer a few optimization tips. Here is one tip that is applicable to both the Excel and standalone versions to start with. There is an undocumented command called <Preview, use it is like using “Navigate without data” in Excel. It brings back the format of what you want with no data(well it brings back zeros for the data). This is wonderful if you need to get lists of members but do not get bogged down trying to get the data. It’s much quicker!

There are a few reasons people use report scripts, but I’ll admit they are not used as much any more as they used to be and almost never in the way they were originally developed, as end user reports. People typically create report scripts to extract a subset of data to load to other systems or other cubes. There are more options available now to get data out of Essbase than ever before (depending on the version you are running). You can export the database, use the Dataexport commands in calc scripts (System 9), use the API, use replicated partitions to move data to another cube, use Java custom functions and there are even third party tools available to help you export data. With all that, if you still want or need to use report scripts to get out data, I’ll discuss some of the tips I’ve run across to make your reports run faster.

The first tip seems so simple; I almost hate to mention it. In Essbase class you were taught the most effective way to load data was to put the data in the order of dimension starting with the sparse and ending with the dense dimensions. You do remember that don’t you? If data goes in better this way, it is logical that it comes out better this way. (who would have thought!!!) Have dense dimensions as column members and sparse members as row members and page members in the order they appear in the outline. This allows Essbase to process each block once and does not have to revisit them. If you have to have a dense dimension in the rows, make it the last row member.

Second, let’s talk about the data you extract. Is there really a reason you need rows upon rows that have missing data or zeros? Turn on {SUPEMPTYROWS} to shrink your output, most of time by many factors. While talking about missing data, don’t you just love the #missing you get for missing data? I know I do, but that is 8 characters plus the delimiter and spaces getting returned for every missing entry that I don’t need. Change it to something real short like a space or zero or how about nothing. That is the same as I get paid to tell you all my secrets. Use the command {MISSINGTEXT ""} to do this.

Third, there is a command to stay away from, its DimBottom. Personally I like a well lit bottom, but that’s another story. Like @CURRMBR in calc scripts, DimBottom is one of those commands that is very slow. If you use Query designer to create a report, it uses a better way. It uses the link command. In sample.basic to get all level 0 products it uses
<Link ((<LEV("Product", 0)) AND ( <DESC("Product")))

Here are a few other things you can do to make sure you get the results you want in a report script. Turn on <ACCON. This command accumulates member names even if they are intermixed. If I have
<Row(Product,Market)
“100-10”
“200-10”
“East”
“300-10”
“400-10”
And don’t include ACCON, it would only give you east and “100-10” and “400-10”. With it on, you will get all the products.
Turn on <SYM symmetrical reports are quicker than asymmetrical.
Avoid calculating columns and rows. Pulling the data directly is faster than having it perform calculations.
Reduce the number of decimals returned. Use the {Decimal N} command to do this. It will reduce the amount of data returned.
Here is a sample header I use when I’m setting up data to be transferred to another system:
{ DECIMAL 2 // set decimals to 2 positions
SUPALL // suppress pages, headers, commas, brackets
MISSINGTEXT "" // set the missing text to nothing
NOINDENTGEN // turn off any indentation of member names
ROWREPEAT // repeat the row information
TABDELIMIT // Delimit the columns with tabs
}
Notice all of the commands are within one set of brackets. If you prefer you can bracket each one, but I like grouping them together.

There have been a couple of questions about report scripts on the different forums I frequent.

The first is “Can I get column heading for the row members”? The short answer is no. You might be able to simulate it by using custom headers, but since most people want these headers for creating column names when importing data into relational databases, my preferred method is to have a static file with the headers. After creating the report extract file, I concatenate my header file with my data file.

The second is “How can I export the entire database (or a subset)? Doing it is so slow”. Depending on the size of the database, a report script can be somewhat slow, but using the hints I gave before, can speed it up. Give my suggestions a try and see how it works for you.

The Power of Report Scripts (Part 2)

2008-05-08T07:59:00.000-07:00

Only a little later than I had hoped, here is the second installment of “the power of report scripts”. Before I get started, I am excited to mention that I’ll be presenting at Kaleidoscope, the upcoming ODTUG conference. I am confidant that it will be one of the best technical conferences for Essbase developers in quite some time. It’s the week of Jun 14th -18th 2008 in New Orleans. Plan on attending, I’m sure you won’t be sorry. I also have to thank my friend and super Essbase developer Doug Pearce from Analysis Team for reviewing this entry. Thanks to him it is more coherent and easier to read.

In the first installment of my blog, I talked about simple ways to use Essbase Report Scripts (in Excel) to retrieve members and data. I spoke of creating lists to use in drop downs as opposed to static lists. A friend of mine read the blog and related a real life story to me. He had a user that spent hours zooming in on a database that contained millions of possible product/customer combinations to get to the data she wanted. . By creating a report script she was able to get the few thousand intersections she needed and her task went down to a couple of minutes. “Normal” Add-In queries are limited by the row limits imposed by the server and Excel before the suppression of missing rows can be applied. Since the report script is run on the server, the limits are applied to “real” intersections of customer and product (not the hypothetical maximum).

In this Article, I’ll expand on Report scripts in Excel more and offer a few little tips to make the reporting more robust.

In part 1, we used some simple report script language, but what if we want to get more complicated. In my prior examples, everything was in a single cell. This is great, especially if you want to copy the script from one sheet to another. Often in my projects, I’ll have a hidden sheet with all my report commands - each in its own cell. I give each cell a named range. I also have a single cell as a named range on my retrieval sheet. Instead of copying the script to the retrieval sheet, I just set the retrieval sheet range equal to the reports sheet range. I’ve found copying is much slower than setting them equal.

You can have a whole set of report scripts connected to buttons, click on the button and it clears the retrieval sheet, sets the appropriate report script range to the retrieval range and does the retrieval. You now have an almost instant reporting system.

As you get more complex, you can have the report script in multiple cells. It makes it easier to read and much easier if you want to substitute values. An added bonus is it is easier to figure out how to do asymmetrical reports. Talking about asymmetrical reports, here is where some settings make a difference. From the Tech reference “ [&lt Asym] prints an asymmetric report (with BLOCKHEADERS) only when all column dimensions include the same number of selected members and all members from each column dimension are on the same line. Otherwise, a symmetric report (with PYRAMIDHEADERS) is produced.” What does this mean? Well if I have the columns of time and scenario and I want Actuals for Jan and Budget for Feb and Mar, let’s see what different options give us. If I do a symmetrical report like:I’ll get a report that looks like the following.You will see that I get Jan having Actual and Budget and Feb having Actual and budget. Each month has the same number of items under it. If I change the Sym to Asym to turn it into the following asymmetrical report .I get a different report where Jan is only for Actual and Feb only for Budget.
Just like in the first example, if I do a symmetrical report of three month and Actual and budget.
I get the report shown below:
But If I try to change it to an asymmetrical report

I get the following error
This is because I have to have an equal number of members for both dimensions otherwise the report generator does not know how to process it. A simple change
I’ve said report scripts can be faster than excel retrievals. Here is a good example. Suppose I want to get a member of the product dimension and its attributes. I could set up a sheet like:
Zoom in on the different attributes (turning on suppress missing). In an Excel add-hoc retrieval, it is a slow process - zooming in on each of the attributes. To switch to another product, I have to start all over. An easier solution is to set up a report script like:
When I want to change product, I simply replace the product and retrieve again. I have a cube with 16 attributes and can get the attributes for a product almost immediately. I then use them in a dashboard report. If I try to do this with zooming in, it’s a bunch of code and much slower. In one case the difference was sub-second vs. 10 minutes.

To make the above script even more productive, I set up a selection page with a list of products. I replaced the line in the script that says “100-10” with a formula to get my the selected product value from the selection page and now have a flexible retrieve.

I don’t know about you, but in Excel, I love to record macros to see what they will do and steal the code and modify it to do what I need. Well you can do the same thing with report scripts, not with recording macros, but by using the Query designer. Here is another tool that people rarely use but is really useful. You can create a more complex query using query designer and save it as a report script on the server (Go into EAS and open the report script section and open the report). Bring the code back into your Excel file and use it. The code above was created in query designer. It can also help you learn report script syntax. A small word of warning, in some cases the Query Designer adds extraneous information. For instance, I really don’t need all the {OUTMBRNAMES} in the script, but in this case they don’t hurt anything. In reality, I probably would not have used the restrict statement on the last line, but would have set suppress missing on my sheet options. I’m not sure which is faster, test it and let me know.

In the next installment, I plan on talking about standard report scripts, some commands to use and a few ways to optimize them. stay tuned.

Report Scripts the unloved part of Essbase

2008-03-27T10:36:00.000-07:00

When I took my first Essbase class long ago in the dark ages (V3.11), the instructor talked about report scripts. I can repeat all he told me. He said “There is something called a report script you can use, but we don’t go over it in the class”. As I went through projects, I heard the same line over and over “You can use report scripts, but they are slow and unless you really need to extract data from another system, no one uses them”. Interesting, no one used them, but Both Analyzer and Reports used reports as a basis for their queries and performance was not real bad. So I started investigating how and when to use them. I have to say, I didn’t know what I was missing. There are some pretty good uses for report scripts. This article will so you how to use report scripts in Excel to simplify some tasks, some commands that help format reports and others to avoid.

Although I have no proof or evidence to back my claim, it is my belief that report scripts were the first way to extract data from Essbase into Excel. Why else would that functionality be build in? You might ask, why would you want to use a report script in Excel when the interface works so well to retrieve Essbase data? There are lots of reasons, automation, ease of change, speed of retrieval; yes I said speed of retrieval. In some cases, you can get much quicker retrieval using as report script than by drilling in. So let’s get started.

If you want to use report scripts in Essbase, you first have to make a few changes to settings. Open Excel and log on to Sample.Basic. You don’t want to retrieve data. Go to the Essbase menu, select options and select the Mode tab. Change the retrieval type from Advanced Interpretation to Free Form. If you do not plan to do any other zooms on the sheet, you can leave Template Retrieve checked. If you think you might want to zoom in after the report has been created, uncheck this option.

Click OK to set these options. When you want to do a regular Ad-hoc retrieval, you will have to switch this back to Advanced Interpretation.

For one of the easiest reports, on your worksheet, type <children “Product” in a cell and retrieve the data. (If you have mouse buttons turned on, double click, otherwise, go to the Essbase menu and select Retrieve). If your database has been loaded and calculated, you will see the following:

How simple was that?

This is great if you want to get a list of all product groups for a drop down selection list. By changing the command to <IDESCENDANTS "Product"

you could get the entire product list. Turn on or off “Suppress Missing Rows” to toggle between getting all rows or rows with data. If you want all rows, you could make it even faster by turning on “Navigate without Data”

The problem with this is you get the shared member rollups included twice (see rows 3 and 19 for example. By changing it a little, you can exclude what you don’t want. Try <LINK(<DESCENDANTS( "Product") and not(<DESCENDANTS("Diet Drinks")))

It removes all of the Diet Drink rollup. I don’t like diet drinks anyway.

OK, this is nice, but what is the point, one zoom in and I have it in a regular report. How about creating two drop down lists? One to give you the children of Product and the second one to give you the level zero members of what you select in the first list. Have your first list created and populating a dropdown box using <Children “Product”, then when you select an item from the first list, create the second report script command. So for instance, if you select “Root Beer” from the first list <LINK( <DESCENDANTS( "Root Beer") and <LEV("Product",0) ), you would get just the level zero of Root Beer.

Not very helpful in this example, but what if you had 50 product groups and 4 levels between it and the level zero members.

You are not limited to simple queries like I have been showing. You can use formatting and selection settings. Give it a try.

In The next installment of the blog, I’ll go over some more ways to use report scripts in excel and also some suggestions on how to improve report script speed