Fragmentation in ASO Cubes

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.

1. During the year, the client deletes some data out of the cube by deleting the members (old years)
2. The cube contains no slices
3. The cube gets an incremental update multiple times a month by clearing out the current Month/Year and reloading the data
4. 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

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

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!

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.

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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.