jason w. jones

Kscope13 Day 1 is in the books, or at least, almost in the books. I am positively beat after attending sessions, eating great food, networking, tweeting, and so on. My style for these Kscope recaps has been to focus less on summarizing the the content of them and instead try to think about the content with respect to how I see it fitting into a broader context.

But before all that I just want to shill for thank and recognize ODTUG for a moment. This is actually only my third ODTUG: I came in 2008 (also in New Orleans). I have been absent for several of the intervening ODTUGs, owing to various reasons – but it feels very, very good to be back. These people seriously know how to put on a conference and they just simply get better each year. This conference is incredibly well organized, well executed, and relevant. If you’ve ever been to an event where everything felt like a cluster, you didn’t know where to go, and there was mass confusion, then imagine the opposite of that.

Additionally, ODTUG has a well earned and well deserved reputation for having good food. We are only one day in and I am not disappointed in the least. I am not much of a sweets person but I couldn’t resist the giant, soft, delicious cookies that showed up after lunch. I am going to have to find some time to workout so I don’t come back heavier than when I left. My only complaint about the whole trip so far is that I couldn’t find a direct flight here from Seattle.

The Hyperion/Essbase content at Kscope has grown tremendously. What started out as a sidetrack of sessions that felt bolted on has blossomed into a vibrant and sizable content track. Hyperion is also quite well represented in the exhibitors hall.

I have, surprisingly, bumped into a fair number of people that admit to reading my blog. My typical retort is “So, you’re the one!” What started out as me just wanting to document some of my own issues (for my own future reference, and others if Google turned it up for them) has turned into a somewhat whimsical and almost therapeutic outlet. I seem to be a little feast or famine with the articles, depending on how heads down I am on mobile development and other goodies, but I’m on a good run right now and having some fun with it. So hang around and you might read something useful one of these days. *wink*

Kscope is off to an awesome start. Check back shortly for my thoughts on the actual content.

 

As a programmer, I find it imperative to document code as part of the coding process itself. I have learned to treat the construction of good code as a process that includes the documentation as part of the code. One of the things I love about ODI is the ability to add a description to many objects such as interfaces. Even within a single interface you can document individual elements. This is incredibly useful to provide context around why something is designed the way it is designed, to remind yourself of something, or for the next person to use/edit the code (which could be many years in the future as well as after you have moved on).

Essbase is no different. One of the most important things to document and that quite frequently get good documentation are calc scripts. We have the ability to write documentation in them, as much or as little as we want (hopefully we write as much is as needed and no more). We can add documentation to report scripts (of course, while still useful these seem to have fallen quite out of favor, given all of the tools that can move Essbase data around). We can add comments to individual members in an outline. We can add comments to our batch files and MaxL scripts. We can add notes to databases (did you know that? You can set a note on a database… I have used these quite successfully but they seem to be a quite seldom used feature despite how useful they can be, or were at least).

I wish we had more though. I’d like to be able to at least have a Description field that I could fill in for applications, databases, load rules, outlines, calc scripts (beyond the inline documentation), and any other object. I could see a lot of uses for this. Making notes on temporary databases or other temporary files, explaining the purpose of a particular load rule, quirks in an outline (notes to a future admin), and so on. Of course, it’s possible to document this in a separate document, but as we all know, these get stale and go out of date. Furthermore, they frequently neglect to document everything. So new features or files pop up and they are not included in the documentation.

Come to think of it, more metadata than even just Description would be useful: last person to edit, create time, edit time, basically all the usual stuff. As a bonus feature, the ability to associate some arbitrary files with a server or application would be nice so that we could upload a Word doc or PDF or something to its associated cube/server and have it available as a quick reference.

Anyone else think enhanced metadata and associated functionality would be useful?

Here’s a papercut I’d like to present in the context of my thoughts on papercuts in the Essbase ecosystem. I’ve recently been doing a bit more work with FDM. After an FDM data load you need to calc data (related to what you just loaded, although I suppose you could just calc the whole thing if you wanted to) related to the intersections you just loaded. In other words, if you are loading data for a certain time period or location or whatever, you’ll want to roll that data up. Nothing special there. So you have a normal calc script except it has been parameterized for FDM – it searches for tokens in the script, such as in FIX statements, then replaces a template variable with the real variable. So it’s like [T.Location] gets replaced with the actual location. But guess what, when you go to validate the calc script now (and you do always validate your calc scripts, right?), it doesn’t validate.

Hmm.

So, I’m not an FDM expert. Maybe there’s an option to work around this that I don’t know about. Maybe you can stuff these tokenized names into a dummy alias table so that you can at least validate. But it seems like the “right” way to handle this would be to find a solution where you can still validate the calc. I guess one straightforward way to do this might be an option to ignore values with brackets around them that are enclosed in quotes. But it feels wrong that using FDM and tokenizing your calc script leads you down this path. If you have worked with this and have a solution that I don’t know because I’m not an FDM expert and you are, please let me know. But right now it’s just one of those quirky little less than ideal issues that I consider an Essbase Papercut.

I am a software developer who regards the output of the software development process as skin to the woodworker making a fine jewelry box, the master chef perfecting a plate, or the musician crafting a song. I believe solutions should be elegant, robust, deceivingly simple, and polished. I take the same approach to code with my various open source Essbase related Java projects, GitHub projects, and perhaps more importantly, the critical filter through which I see and use Hyperion, Essbase, and related software.

To that end and just for my own amusement, I will be henceforth be writing about various “Essbase and Hyperion papercuts” that I see and perceive. This is in similar spirit to the Ubuntu Papercuts project that occurred some time ago. The idea was that in the aggregate, a lot of little issues start to become troublesome and lead to a worse user experience. As with many pieces of software, Essbase in general has this problem. It’s an awesome piece of software, but then has all these little warts and quirks. It’s like NASA puts a man on the moon (this is the scientific equivalent of Essbase at its heart) and then when stepping out of the lander on the surface of the moon, trips on a faulty stair leading to the surface (this is like any number of little Essbase quirks I run into).

That being said, if there is some small, seemingly trivial and inconsequential Essbase/Hyperion (or Planning, or FDM, or ODI, or…) issue that just really grinds your gears, let me know about it! This blog gets a few hits from Oracle headquarters, so maybe, just maybe, someone will see it and we can all make the world’s best EPM software just a little bit cooler.

 

I follow a plethora (you like that vocab word for the day?) of Essbase and Hyperion blogs, in addition to other technical blogs I love to follow such as on ODI, cloud computing, big data, and iOS development. I think I have a pretty solid list of blogs but there are always new ones popping up that I want to know about! Do you have an Essbase, Hyperion, EPM or other related topic blog? Please email or tweet it to me!

Also, some of my favorite Essbase related blogs are linked at the footer of this website so check them out. And similarly if you like this blog then please consider adding it your blogroll or list of links so we can all share the Essbase blog lovin’.

Thanks!

One of the truly awesome and powerful features of Oracle Data Integrator is to develop interfaces against a logical schema, then let ODI choose the physical schema at run time. In other words, we have the same logical job that should work in development, quality assurance, and production environments – and this works without having to create and maintain multiple copies of the same job.

Some environments, however, are less than ideal and don’t have a servers for testing (!). ODI really, really benefits from extensive testing in development, though. So what to do? Create a separate schema on the production server. We can still map out the test context for ODI to this separate schema and more or less approximate having a true testing environment.

What if we need to write an ODI Procedure, though? Obviously our first choice is to create an Interface,  but we need to drop down to procedures here and there for various reasons. Normally a procedure would work just fine in different physical servers because the code being executed, including any schema references, would be the same. This is not true if we have a faux test schema on our production server. We might have CUSTOMERS and our newly created CUSTOMERS_DEV or something similar. If an ODI procedure wants to do an update on a table, for instance, it might look like this:

UPDATE CUSTOMERS.INFO SET fav_color = 'Red'

Our procedure is tied to the particular schema. Again, if we had the same schema name but on different physical servers, this wouldn’t be an issue. But we don’t in this scenario. Let’s update the procedure to pull out the proper schema name from the ODI context so that the procedure works in our quasi-dev and production enviroments:

UPDATE <%=odiRef.getSchemaName()%>.INFO SET fav_color = 'Red'

Tada! In the development context, the schema CUSTOMERS_DEV will be used, and in production, CUSTOMERS will be used. Of course, the ideal solution is to have a full test and production environment, but in a pinch this will allow us to do testing on the same server and ensure that our ODI implementation is well-tested, correct, and robust.

Editing knowledge modules is not for the faint of heart. KMs translate your interfaces and other fun business things into executable code. In short, they convert ODIs mostly declarative what definition of jobs into executable units by taking care of the how. There are numerous options available to interfaces and these largely affect the code that is generated for your job.

I ran into a problem the other week where my integration into an Oracle schema by way of the Oracle Incremental Update (MERGE) IKM from a journalized data source using an aggregate function was not working.

Thankfully, someone else had run into this issue and Google turned up a quite helpful OTN post that solved 99% of the problem for me. But the post is from a little while ago and didn’t fully work for me.

Here’s the relevant fix code from the brave and helpful OTN poster:

<%=odiRef.getGrpBy()%>
<%if ((odiRef.getGrpBy().length() > 0) && (odiRef.getPop("HAS_JRN").equals("1"))) {%>
,JRN_FLAG,JRN_SUBSCRIBER,JRN_DATE
<%}%>
asdffd

This doesn’t quite work, owing to a relatively new ODI feature: multiple data sets in the source of an interface. Multiple data set are a really, really cool feature that were added a bit ago. They basically let you take the UNION of different data sources and integrate them to the same target. This is useful in contrast to needing to interfaces for the same target. You can also do the intersection and minus out rows too but I haven’t quite had occasion to use that just yet.

As it pertains to us, though, there’s a parameter on much of the ODI generated code now that is used to specify which of the data sets to perform an action on. Here’s the updated updated (yes, that’s two updated’s in a row).

<%=odiRef.getGrpBy(i)%>
<% if ((odiRef.getGrpBy(i).length() > 0) && (odiRef.getPop("HAS_JRN").equals("1"))) {%>
,JRN_FLAG,JRN_SUBSCRIBER,JRN_DATE
<%}%>

Note the addition of the (i) in two places which is how the dataset is specified. Many thanks to the original poster for helping out in the first place and helping me fix my issue.

This isn’t one you will run into too often, if ever, but seems to be a small issue. I was working on a load rule the other day (yay load rules!) and everything validated just fine. There’s nothing better than the smell of an “everything validated correctly!” dialog in the morning. I am on a project refactoring a cube and one of the small changes made was that the dimension name has changed for one of the dimensions from Accounts to Account. One of the core load rules for this cube uses the “sign flip on UDA” feature. Of course, for this feature to work (which I have used many times before and love [but not as much as I love EIS…]) it needs to specify the dimension and the UDA of the members in that dimension to flip the sign for. Well, sign flipping wasn’t working and even though the load rule validates just fine, the problem was that non-existant dimension in the sign flip configuration. So, although there could be a reason to not want to validate this, it seems somewhat reasonable (if nothing else, for the sake of completeness) that the load rule verification should include verifying that if the dimension name for sign flipping does exist. It would have saved me a little bit of time.

A little while ago I presented a success story using Oracle Data Integrator. The webinar goes over ODI functionality at a high-level, giving a quick tour of the benefits of major functional areas and how they were used to greatly improve and build out the ETL and automation at a high profile client. If you are interested in using ODI in your organization or aren’t sure (but want to know more!) then I strongly encourage you to check it out. ODI is one of my specialization areas now where I have been doing a tremendous amount of work, so I look forward to becoming an expert in this technology and sharing this expertise with others.

Note that this webinar, while technical, is not a deep drive into implementation details. I am planning a followup webinar to very comprehensively look at using ODI as an ETL/automation solution and implementing it with best practices. This will be the webinar to look for if you are more technical and really want to see the ins and outs of how things work and can be architected for robustness, maintainability, and efficiency.

I setup a solution awhile ago where ODI facilitates data movement from an ODI error table into another data store. As you may know, ODI has a naming convention where its temporary and work tables are prefixed with some combination of a letter (or two), a dollar sign, and perhaps an underscore. So if my original table in a relational data store is CUSTOMERS, then the error table for this table would be E$_CUSTOMERS.

So of course I fire up my RKM to build out the model metadata for these new tables, just like I normally would. Everything goes fine but when I go to build the interface and run it, things don’t work so well. A dollar sign ($) is a very common character for scripts and programming languages to indicate that variable interpolation should occur (in other words, to fill in a placeholder). For example, if the variable $color = “orange” and you interpolate a string such as echo “My favorite color is $color!” then the result is “My favorite color is orange!” Nothing too fancy here.

That being said, sometimes a dollar sign can wreak havoc when used where some code doesn’t expect it. I’m not sure if it ever got fixed, but I developed at a place once that would dole out Active Directory IDs based on letters from the first and last name – three letters from each, to be precise. But people with very short last names would have dollar signs appended to them. There was a bug in Hyperion where you couldn’t login with one of these user names, likely because it just never got tested (oops). I’ll assume that’s fixed now.

In any case, back to ODI. In a given ODI datastore, you have the name, resource name, and alias name. Name is typically how you see it in the ODI design context. Resource name is the actual resource in the schema/database. Alias is used by ODI in interfaces as, well, an alias. I didn’t record the exact error message but given that my interface was failing and the output in Operator was a bit odd, I immediately suspected the dollar sign throwing things off either for ODI’s generated code, on the target system (and Oracle database server), or both.

My quick solution? Just replace the dollar sign with an underscore in the alias in the datastore. You can delete the dollar sign entirely but I decided to just go with an underscore. By making the change in the datastore, subsequent interfaces leveraging this datastore will also benefit from the fix, as opposed to trying to adjust just the one interface. Of course, leave the resource name alone (and the name) since they don’t need to be changed (and in the case of the resource name, shouldn’t be changed).

Another approach to all of this would be to change the physical topologie’s naming convention so that error tables don’t have a dollar sign (or other entities that ODI names) but I quite like ODI’s preferred naming strategy (for the most part).