4th addendum to Data Export

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

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

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

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


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

3rd addendum to DataExport

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

2nd Addendum to Data Export

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

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

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

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

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

Addendum to Data Export post

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

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

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

Dataexport is great

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

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

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

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

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

Getdate()
Exec myStoredprocedure.

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

I’ve since added a custom defined function that will run the SQL statement directly from the calc script. I got this from Touifc Walkim the development manager for Smartview, a very nice guy and CDF guru. Some clients don’t like the idea of CDFs so I have my origina method available when necessary. IF this works, you can get the CDF here. Note, I had to remove the link to the CDF as after 15 years blogger has decided it is is some sort of spam or virus instead of something to help other developers.



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

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

Let’s see if these get implemented.

Kaleidoscope wrap up

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ask the experts at Kaleidoscope

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

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

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

If you are at the conference, please say hello.