“If it looks too good to be true, then it probably is.” That is the first thing that came to my mind the very first time I saw a demo converting a traditional Essbase cube to this new-fangled ASo storage kernel. “There has to be a catch,” I kept thinking. “It simply cannot be this easy.” So, for more years than I can justify, I tenaciously held to my tra-ditional cubes, failing to endorse and embrace the new world of ASo. I was wrong. It was that easy.
For many of you, the history of Essbase may be well known, but you may not be as familiar with the timeline with respect to the introduction of the ASo storage kernel. to keep the facts straight, let me provide a little refresher:
• 1992: Arbor Software shipped the first release of Essbase.
• 1998: Arbor was acquired and merged with hyperion.
Contents
5.1 Why Would I Convert to using ASo Cubes? 143
5.2 When Should I not try to use ASo Cubes? 147
5.3 What Is the Easiest Way to Convert a BSo Cube to an ASo Cube? 149
5.3.1 using the Wizard 149
5.3.2 Converting Calculations 153
5.3.3 Determining Solve order 157
5.3.4 Converting Dimension Build rules and Dimension tags 158 5.3.5 Converting report Scripts and Calculation Scripts 158
5.4 What about reporting Cubes? 160
5.5 What great Things Can I only Do with ASo Cubes? 161 5.5.1 making a Cube Smarter through use: training a Cube 161
5.5.2 Working with Slices 165
5.6 So What great Things Can I Do with Both ASo and BSo cubes? 176
5.6.1 Extreme Partitioning 176
5.6.2 typed measures: text and Date measures 180
references 183
• 1998: IBm started shipping Essbase under the name of “DB2 oLAP Server,”
a porting of the software to their platforms for widespread release with their oS that continued until 2005.
• 2005: ASo was introduced with version 7.
• 2005: Essbase was declared one of the most Influential technologies of the last 10 years by Information Age magazine.
• 2007: hyperion (who was in the middle of renaming Essbase for some unknown and undisclosed reason) was acquired by oracle.
• 2009: oracle renamed the product officially to “oracle Essbase” (loss of the Essbase name had not been treated happily in most technical and even business realms and everyone jumped for joy at its return).
That traditional Essbase cube first developed in 1992 and still developed today now has several names. often, you can tell how long someone has been working with this oLAP (online analytical processing) technology by which names they use when they refer to Essbase. you may see within oracle materials the references to Essbase BSo (Block Storage option), BSo cubes, or Essbase Analytics. It is one of the two storage ker-nel options now available to the developer when creating a cube from scratch. Selecting this option allows you to create cubes as they have been created for the past 20 years, taking considerable time to determine (among other things) which dimensions should be Dense versus Sparse. time also will be spent considering whether the resulting block size will be optimal, or if the system will run out of memory because the block size is too big. on and on goes the list of worries and angst that exist when we build traditional Essbase BSo cubes. The entire debate becomes so complex that it has always been essen-tial to have a good consultant at your beck and call to help you determine how to best configure your cube.
This is contrasted to the newer technology option that also has several names in the lit-erature and marketing materials. Essbase ASo (Aggregate Storage option), ASo cubes, and Enterprise Analytics are all references that can be found and are valid. With ASo cubes, many of the things we worry about with regard to BSo cubes are gone. you can just build your dimensions, add member calcs using mDx (multidimensional expres-sions), load your data, and you are ready to go. no more Dense versus Sparse debates.
no more spreadsheets to evaluate optimal block size. The dimensionality can increase in ways you never dreamed (depth and breadth). you also can store a much greater volume of data with ASo. The disk footprint is incredibly smaller, and many times the batch load and calc times drop so dramatically that it seems like magic. Little did anyone realize that the simplicity this brought to many cube designs would be a trait as well, which caused many developers (including myself) to shun it for years. The belief that “it just cannot be this easy” was pervasive and rampant among my peers. I was not alone.
Erring on the side of caution, I built BSo cube after BSo cube, despite the growing amount of white papers and conference presentations on how great ASo cubes were.
I would listen to talk after talk on how they could be built faster, loaded faster, aggre-gated faster, and how you could have dozens of dimensions and millions of members in a dimension if you wanted it. That was unheard of and I continued to savor my own skepticism, knowing that some undefined pitfall was lurking nearby that would surely catch the ASo cube users unaware and I alone would be saved.
The turning point for me was the day I was asked to reduce the amount of disk space my 80 BSo cubes were taking up. Since these were well built, streamlined, fully
optimized BSo cubes, this was not a simple directive to fulfill. I could not just go chop off some fluff inside the cube and provide the requested disk reduction. “Well,” I said to myself, “I guess I have no choice except to try this ASo thing. It probably will not work, and my terrifically designed cubes will really not run well, but I need to be able to tell them I tried and to go ahead and just purchase more disk space; after all, disk is cheap.” So, I set off and started converting cube after cube. The results were staggering.
hours on hours were knocked off of our load and calc each night and weekend. our disk footprint was reduced to a mere 20% of the original amount of disk. In most instances, although not all, the users’ reports were faster. From that day forward, I was a convert and I have never looked back or regretted that move. I only wish I had jumped on the band wagon sooner and begun embracing the ASo technology immediately instead of waiting so long. to quote an old idiom—better late than never. At least I had not missed the entire party, and now neither will you.
one of the simpler methods to learn what an ASo cube does is to compare it to something you are intimately familiar with: the BSo cube. The easiest way to review a summary of the functionality or capabilities available in an ASo cube versus a BSo cube is in a table format. Drawn together from a great many sources, the following is a high-level summary comparison to assist you in working through your decision process. not every BSo cube is an appropriate candidate to become an ASo cube. The decision on whether to convert it or not should be a purposeful move. table 5.1 is not considered to be an all-inclusive table of every function or capability available within the two complementary technologies. Some of these facts may surprise even seasoned developers.
This type of list was first produced by consultants in the early adoption of ASo tech-nology. I wish I had some of my earliest comparison papers and lists; the comments I wrote in the margins would be very interesting to read (and laugh about) now. typically, a new version of the software would be released. We would download and install it and then take it for a test run with our favorite “difficult” application (because everyone knew that all releases look good when you run Sample or Demo Basic) and our favorite front-end tool. After we had it properly installed and fully loaded, we would do our checkbox comparison. With each new release what we found to be happening is that there were fewer blanks or negatives on the ASo column, and more blanks or nega-tives on the BSo column. Slowly but surely, ASo started having a greater number of advantages than disadvantages. Finally, I think we can openly declare that there is a new development standard. The “new norm” among many respected developers is that you always design using ASo cubes, falling back to BSo cubes only when absolutely neces-sary (and mostly in context with Planning Applications as well as homegrown budget-ing/forecasting applications).
“Should I immediately start converting all my BSo cubes to ASo cubes?” no, defi-nitely not. There are some key questions and considerations to think about before launch-ing on a large or small conversion project. Determinlaunch-ing if your BSo cubes are good candidates for conversion is not necessarily a straightforward task and there are a lot of things to consider. hopefully, the next few pages will help with that task. Approaching it analytically with an open mind will allow you to accurately determine the best course of action for existing cubes. Just as important in this process, though, is the need to start thinking through which technology should be used for new implementations, and why.
Don’t be like I was and stay with BSo cubes only because it is what you know, and more importantly, what makes you comfortable. use this resource guide to empower you to
Table 5.1 Functional Comparison of Essbase Storage Kernels
BSO ASO Functionality Description/Notes
y y Supports user queries with oLAP enabled reporting tools
y y Supports write-back applications (ASo can only write back to level-0) y y Supports partitioning (ASo has no outline synchronization)
y y Supports Development of member Specific Calculations
y y Supports Creation of Calculations at all member levels (ASo has some limitations) y n Supports data loads at all levels of the cube
n y Allows the developer to determine the order in which a member calculation occurs (BSo can only control scripted calculations, not member calculations that follow native rules)
y y Supports use of Calc Scripts in batch processing (new ASo feature and there are significant limitations to functionality)
n y Supports greater than 12 dimensions in a design with ease
n y Supports dimensions with hundreds of thousands to millions of members y n Supports multiple Databases per Application (while supported, it is not
recommended anymore for BSo)
y y Supports use of Attribute Dimensions and Attribute Calculations (ASo: Sum only) y n Supports Association of Attribute Dimensions with the Dimension tagged Accounts
y y Supports ragged hierarchies
y y Supports Alternate hierarchies
y y Supports user Defined Attributes (uDA)
n y Supports multiple slices of data that can be merged on request
n y Supports replacement of the entire contents of a database, or just an incremental slice n y Supports automatic update of aggregations after a load (ASo: If aggregates exist, they
are updated; BSo all required calc scripts must be run)
y y Supports variance reporting
y y Supports y-t-D reporting
y n Supports all four unary types (+, −, /, *)
n y Supports Date/time Dimension type (text metrics) y y Supports report Writer Functionality
y y Supports Spreadsheet add-in
y y Supports Export Functionality (ASo Level-0 only)
y y Supports API
y y Supports mDx Queries
y y Supports use of alternate Alias tables
y n Supports Currency Conversion module
y n Supports Data mining module
y n Supports Linked reporting objects (Lro) y y Supports hybrid Analysis (ASo some exceptions)
y y Supports time Balance Accounting and reporting (ASo some exceptions)
learn and stretch your skills and start utilizing the full oLAP platform and both storage kernels available to you. having options and knowing you have options is a key turning point in your education.