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

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

I am about to show you a great sql trick that only a few SQL Server T-SQL masters actually know.  It will save  you a decent amount of coding and make you the envy of your Sql Server colleagues.

Even in the Microsoft documentation on the CASE statement does not list that the CASE … WHEN…THEN…END statement can actuall not only check for logical boolean expressions but via operators but it can also handle an IN() statement similar to a fullblown SQL (or SQL Sub-Query statement, if so inclinded) function in the where clause. The code is quite simple.  I have not found any restrictions when using constants (the hard-coded text that does not change) or immediate variables that are defined within the execution frame of the CASE IN() statment such as CASE WHEN something IN(@supertest).  That is where @supertest = ‘A, B, C’.

 Here is an decent example so enjoy and share with your friends. Be sure to comment on this if you like it. We are trying to gain a rep here at ArtOfBi.com!

SELECT
accountID,
accountSubID,
CASE [debitOrCredit]
WHEN 'C' THEN
CASE WHEN accountTypeID IN(1, 4, 7) THEN
[amount] * -1
ELSE
[amount]
END
WHEN 'D' THEN
CASE WHEN accountTypeID IN(2, 3, 6) THEN
[amount] * -1
ELSE
[amount]
END
ELSE
[amount]
END
AS amount
,
CASE dataElement1 IN('asset', 'liability') THEN
'Budget Account'
ELSE
'Income Account'
END AS something
FROM
FactDataTable

I have not seen any performance issues with the use of this case statement. It works like a charm for me on tables with 10,000+ records. As a side note, using the CASE IN() statement requires only two rigid assumptions:

1. The IN statment must be immediately next to the parenthesis (i.e. no space between the two) ex: IN(’yes’, ‘no’) and not IN (’yes’, ‘no’)
2. The values must be seperated by a comma. If the value is a string it must be contained as a string in single-tick quotes.

This is just another post that I’ve been meaning to put up for a while but it just became relevant again on an OBIEE project I am working on.  In Essbase I am generally a fan of loading data in using Parent/Child builds rules but as we know with OBIEE when looking at the relational datasets a parent/child table can prove to be an annoyance.  And, thus the need to flatten a parent-child table into a more OBIEE-friendly horizontal table. Basically how do we get a table schema like:

CREATE TABLE olap.vDimAccounts(
parent nvarchar(50)
, child nvarchar(50)
)

to look like

CREATE TABLE olap.ParentChildFlattened(
level1 nvarchar(50)
,level2 nvarchar(50)
,level3 nvarchar(50)
,level4 nvarchar(50)
,level5 nvarchar(50)
,level6 nvarchar(50)
...
)

This can of course be accomplished using several different techniques.  I am also contemplating writing a simple C# console application to handle this in a more robust command-line interface manner but that can wait.  On this project I have an Accounts dimension. It  has ragged hierarchies, of course.  Here’s my solution:

USE MyDataBaseName
GO

SELECT
	lev01.child level_01,
	lev02.child level_02,
	lev03.child level_03,
	lev04.child level_04,
	lev05.child level_05,
	lev06.child level_06,
	lev07.child level_07,
	lev08.child level_08,
	lev09.child level_09,
	lev10.child level_10,
	lev11.child level_11,
	lev12.child level_12
INTO
	olap.dimAccountsFlattened
FROM
	olap.vDimAccounts lev01
	LEFT OUTER JOIN olap.vDimAccounts lev02 ON lev01.child = lev02.parent
	LEFT OUTER JOIN olap.vDimAccounts lev03 ON lev02.child = lev03.parent
	LEFT OUTER JOIN olap.vDimAccounts lev04 ON lev03.child = lev04.parent
	LEFT OUTER JOIN olap.vDimAccounts lev05 ON lev04.child = lev05.parent
	LEFT OUTER JOIN olap.vDimAccounts lev06 ON lev05.child = lev06.parent
	LEFT OUTER JOIN olap.vDimAccounts lev07 ON lev06.child = lev07.parent
	LEFT OUTER JOIN olap.vDimAccounts lev08 ON lev07.child = lev08.parent
	LEFT OUTER JOIN olap.vDimAccounts lev09 ON lev08.child = lev09.parent
	LEFT OUTER JOIN olap.vDimAccounts lev10 ON lev09.child = lev10.parent
	LEFT OUTER JOIN olap.vDimAccounts lev11 ON lev10.child = lev11.parent
	LEFT OUTER JOIN olap.vDimAccounts lev12 ON lev11.child = lev12.parent
WHERE
	lev01.parent = 'Accounts'-- IS NULL

How does it work?
I can tell from essbase (or an import of my essbase cube into OBIEE) that I have 12 generations so I know that I should have at least 11 columns in my final horizontally flattened table.
Next I create a left outer join on the same table or view object containing my parent-child hierarchy each time referencing the previous instance of said table so that I capture the true parent/child relationship.

The final where clause really just tries to determine where to kick this puppy off. In most parent/child hierarchy tables the first parent is Null to indicate that the first records child does not have a parent. I would do that for a parent/child table when building a MS Analysis Services cube or just a relational report but in Essbase we typically fill that first parent with the name of the dimension if doing a full build so the load rule knows where to start the hieararchial build.

What other techniques exist?
Well this topic is actually somewhat obfuscated since few implementations require it and it is somewhat of a technical work-around. Here are few worthy links on twists on this adaptation:

• Ryan Farley on .Net…
• StackOverflow.com Forum
• Janne Pyykkö

Props On This Knowledge
I have to give mad props to Janne P. on this one because originally his post a year or so ago really helped me out. I thought I’d share the knowledge.