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!

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.

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

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

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.

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.

Open World 2009 Day 2

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

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

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

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

New Essbase book Review - Oracle Essbase 9 Implementation Guide

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3rd addendum to DataExport

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

Addendum to Data Export post

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

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

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

Random thoughts

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

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

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

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

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

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

Blog Rant

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

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

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

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

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

Partitions

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

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

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

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

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

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

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

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

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

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

The Power of Report Scripts (Part 3)

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

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

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

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

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

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

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

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

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

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

Report Scripts the unloved part of Essbase

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

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

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





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

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

How simple was that?


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

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

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

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

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

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

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

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