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.