Every now an then one needs to pad a column value with a character or symbol in order to conform the resulting value to some standard. For example, a company may represent their stores by a maximum of 4 digits and each character in that 4-digit store number must contain a value as to not be confused with any other values..  In other words, store #99 would be represented as 0099 instead of just 99.

This is not uncommon practice. Often times, similar functionality is required when pulling data from the mainframe where “super keys” are paramount.  Our good friends at MS have set up SQL Server so that this is universally simple.  The code snippet in its basic form looks like this:

RIGHT('0000' +  [COLUMN NAME], 4)

This is straightforward.  Use the RIGHT function by concatenating the first parameter to a set number of padding characters and the column name in your table. Be sure to CAST that column as a string data type if pulling a numeric field.  Then, for the RIGHT functions second parameter, make its value the same number of character spaces required for your final value.

Oracle just had to be difficult.  In order to conduct the same operation using PL/SQL you must use a function, which I am still not sure how it completely works behind the scenes.  The syntax is as follows:

SELECT to_char([COLUMN NAME],'FM0000') AS result FROM table;

Update
Recently a reader posted the use of the Oracle function LPAD, which is much cleaner than the use of the FM function and works better than the SQL Server function. The syntax would be

SELECT LPAD('Test', 8, '0') AS result FROM dual;

This LPAD function has been relevant since 8i.

Conclusion

Each of the code snippets above will provide a conformed look at a column value.  SQL Server’s method seems to be a little more cut n’ dry than PL/SQL’s but they both get the job done.

Another great source for Oracle functions can be found at http://www.techonthenet.com/oracle/functions/lpad.php

ASO does not provide an out-of-the-box version of dynamic-time-series (DTS) like its partner in crime, BSO. However, one straightforward approach to achieving this functionality is to implement a time aggregation dimension and leverage MDX functionality.  In this example we will house the Time Periods/Months (i.e.:  Mar, Apr, May, etc.) and the Years dimensions into two separate dimensions.  We then add an additional dimension called “Time Series” to the outline to support our ASO version of DTS.

Read the full story

By now every knows that the release of Hyperion 11x has provided us with the latest and greatest Essbase modeling application, Essbase Studio.  A lot of us are familiar with Essbase Integration Services and have been looking at Essbase Studio like a two-headed dog.  They either are scared and don’t want to go near it or they are so used to their purse-dog, i.e: EIS, that they stick with what they know best and haven’t unwrapped Essbase Studio yet.

This blog post should put and end to any reluctancy.  I introduce to you the first Essbase Studio tutorial on the web. 

You can download the PDF here. (I’ll eventually move this to a CDN if the downloads get to big, so please link to this post and not the document itself.)

Hyperion Essbase Studio Tutorial by Example (PDF)

I am also breaking the tutorial into a series with this being part 1.  There are a lot of cool new functions with Essbase Studio and you will immediately see the benefits it has over EIS. Soon, you’ll see other blog post here on Essbase Studio tutorials part 2, 3, etc. so be sure to check back often or subscribe to the RSS.

If this series is good, bad, or indifferent please let me know.  I would like to keep these up and with your feedback I can revise and make the documents that much more informative everyone.

Cheers.

Update 8/30/2009

- This should have been posted to the live site last week. I am in the middle of switching hosting providers for my blog and uploaded this post to my test site instead of this one. Sorry for the delay.

I covered in a previous blog post the new addition of Annotations in HFR/FR in the System 11x EPM release.  When you create or reply to an annotation one has the option to taxonomize the annotation using the category dropdown.  This blog quickly steps you through how to add/remove a category so that the default categories can be augmented or offset.  Please note that you may cause referential damange to your HFR/FR system if you modify a category that is already being used in an existing annotation entry. Be careful or you may cause errors in FR and the annotation report viewer resulting from your modification.

When a user creates or replies to an HFR annotation, out-of-the-box the default category list appears in the drop-down with the options seen below:

In order to modify this list (add/change/remove) on must access the “annotations.properties” configuration file located in [%Hyperion_Home%]\products\Foundation\workspace\lib\. This file also stores logging information related to the annotations but we are concerned only with the categories at this point.

Open the file and location the section for “Categories”. It should be above the logging configuration section. There is a comma separated list of values which are set by default. At this point you can add, delete, or modify this list. For testing purposes and the sake of this tutorial I simply appended a new value called “Testing” to the end of the list. Pay attention to follow the structure currently in place before making your change. Notice that there are no spaces between values and that the last value does not contain a trailing comma.

Save the annotations.properties file and close it. At this point your modifications have taken place but they will not be available in FR or anywhere else in the system for use until you restart the Hyperion Annotation Server windows service. Restart the windows service and re-open your HFR/FR report. You will now see that your change has been picked up properly.

Conclusion

In this tutorial we quickly located our annotations config file, made a modification, restarted services, and saw the change reflected as we desired. Just as a reminder this operation should only be done by a high-level administrator. Extra care should be taken to run the annotation report from Workspace first to ensure that no existing annotations are using categories that you seek to modify. Doing so may have undesired results and cause headaches. Enjoy.

Google Maps is very popular and when someone first mentioned to me that an integration between OBIEE and the Google Maps API was possible I was totally stoked. For a sales, marketing, supply chain and many other subject area reporting analytics this is awesome.  You get the picture. 

Recently a colleague mentioned they were having problems getting the integration to work and I just had to chime in.  They had done several things right, such as getting the API key from google (one should always register it for http://localhost/ unless domain restrictions don’t otherwise allow for full blown testing using this URL) , they had built a simple report with city, state, and zip, and they downloaded a code snippet from another OBIEE blogger who has also been playing with the integration. That blogger had them off to a decent start but they immediately noticed problems with the logic. They were unable to view the map properly when editing, etc.  The other OBIEE Google map bloggers have probably never done any serious web development integration before and that’s where the problems start. I’ll seek to set you on the correct path in this blog series.  I’ll start you with a simple example of an Answers Google Map integration and the code that goes along with it.  So, if you want your report to look similar to the one below, without any glitches, then continue reading and follow along.

Read the full story