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.