OBIEE 11g - How to get proper date sorting in graphs

When using OBIEE, I've run into situations with graphs and tables where, if I want to show a date format of just month (Jan, Feb, etc.), using the function MONTHNAME ends up converting the date to a character, and then the sort ends up being by alphabetical order, rather than in date order.  Below is an example of this:

One way people get around this is by using the date number.  I'm not a fan of that, as it makes the reporting look bad.  Even worse, is when you have dates that cross years.  You then have to add the year to the axis, which looks bad as well, since in order to sort it correctly, it needs to show year first.  Report users aren't used to this, and don't like it.  I think you'll agree it doesn't present well.

Fortunately, there is a nice solution to get around this.  In my example, I also have the complication of having multiple days per month in my date field.  Thus, just using the 'Data Format' options in 'Column Properties' doesn't really work by itself.  Even if I switch it to be 'MMM yyyy' it will still show a separate data point for each date, showing many duplicates of the same month/year along the X axis.  The way around this is to:

1.  Convert your date to the first of the month.  To do this, you'll need to use the following OBIEE function where my date field is "Time Detail"."Time Date":    

TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH("Time Detail"."Time Date") * -1) + 1, "Time Detail"."Time Date")

2.  Use the 'data format' option to show the date in the right format (e.g. MMM yyyy).

In the below table, the first column is the raw date.  The second column is the manipulated date using the function shown in #1 above, and the 3rd column is the same as the 2nd, but using the date format as shown in #2 above. 

Now, when you graph this, your sorting ends up as expected, and there are no issues crossing over years. 

Even if I remove the year from my format, the graph is still 'smart' enough to show in the correct order.

 

OBIEE 11g - 'Act As' functionality

When setting up security for new users, it is often useful to have a test ID to work with.  However, OBIEE has the ability to allow specific users the ability to 'Act As' another user.  This is nice functionality when setting up security or troubleshooting access issues and/or setting someone's preferences for them before giving them access to the tool.  I borrowed much of this from the following wiki (http://gerardnico.com/wiki/dat/obiee/actas), but it wasn't as straight forward as I needed it (I'm probably not as deep as Nicolas Gerard on the ins and outs of OBIEE), and it didn't have correct SQL for the PROXYLEVEL variable (which, in all fairness, is incorrect in the Oracle documentation).  I'll go through the process of setting this up in a way that makes more sense to me.  Hopefully, this will be helpful to others as well. 

1.  Create a table (I used the OBIEE_PROXY name as mentioned in Nico Gerard's wiki)

CREATE TABLE 
  OBIEE_PROXY  
  ( 
    PROXY_USER_ID VARCHAR2(30 BYTE) NOT NULL 
  , PROXY_TARGET_ID VARCHAR2(30 BYTE) NOT NULL  
  , PROXY_LEVEL VARCHAR2(10 BYTE) NOT NULL  
  , CONSTRAINT OBIEE_PROXY_PK PRIMARY KEY ( PROXY_USER_ID , PROXY_TARGET_ID ) 
ENABLE 

  ) 

2.  Populate the table.  I populated it with my ID as the PROXY_USER_ID, added all users in the PROXY_TARGET_ID and put the PROXY_LEVEL value as 'full'.  The choices on the proxy level are 'full' or 'restricted' (all lower case).  The table looks something like this.

 

3. Bring in the table into the Physical Layer in the RPD.  Of course, you'll need to create an alias so you can have two tables to create a data model.   I didn't bring this forward to the Business or Presentation layers, as I don't want to necessarily report on it.


4. Create Session Variables.  You'll need to create two session variables.  One called PROXY and one called PROXYLEVEL.  I created a separate connection pool for use with the Initialization block.  NOTE:  The SQL given in the Oracle documentation for the PROXYLEVEL variable is wrong.  You will see below that I have crossed out the original SQL statement and udpated it with the correct statement.  If you use the corrected SQL in the PROXYLEVEL variable, it will allow the PROXYLEVEL to be retrieved from table.  This is documented in Oracle Problem ID 1418227.1.  The Default Initialization Strings for the variables are as follows:

PROXY:

SELECT PROXY_TARGET_ID 

  FROM OBIEE_PROXY 

 WHERE UPPER(PROXY_USER_ID) = UPPER(':USER')

   AND UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')

PROXYLEVEL (Note:  Do not use the SQL in the Oracle documentation, which is shown crossed out below.  It will not work.  Use the SQL as shown in yellow below to allow the PROXYLEVEL variable to work):

SELECT PROXY_LEVEL
  FROM OBIEE_PROXY
 WHERE UPPER(PROXY_USER_ID) = UPPER(':USER')
   AND UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')

SELECT PROXY_LEVEL
FROM OBIEE_PROXY
WHERE UPPER(PROXY_TARGET_ID) = UPPER('VALUEOF(NQ_SESSION.RUNAS)')
  AND  UPPER(PROXY_USER_ID) = UPPER('VALUEOF(NQ_SESSION.RUNASORIGUSER)')

When setting my variables, I set the PROXYLEVEL default to 'restricted'.   

5. Create a Custom Message Template.   You will need to create this template in the following location (the 'xxxx' will depend on the folder name you used to do your install):

 \xxxx\OracleBI1\bifoundation\web\msgdb\messages\

I named my template LogonParamSQLTemplate.xml (as suggested in Nico Gerard's wiki).  You will have to create this, as this template does not exist otherwise.  I made a copy of one of the existing files in the messages folder, saved it off as LogonParamSQLTemplate and then pasted the following (change "physical_dbname"."conn_pool_name" to the connection pool name used in the session variable setup in step 4):

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
 <WebMessageTable system="SecurityTemplates" table="Messages">
   <WebMessage name="LogonParamSQLTemplate">
      <XML>
       <logonParam name="RUNAS">
       <!-- for EXECUTE PHYSICAL CONNECTION POOL, SQL_Paint.SQL_Paint =  -->
       <!-- SAS Repository physical_dbname.conn_pool_name -->
         <getValues>EXECUTE PHYSICAL CONNECTION POOL "physical_dbname"."conn_pool_name"
                  select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}'
         </getValues>
         <verifyValue> EXECUTE PHYSICAL CONNECTION POOL "physical_dbname"."conn_pool_name"
                  select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}' and PROXY_TARGET_ID='@{VALUE}'
         </verifyValue>
         <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL "physical_dbname"."conn_pool_name"
                  select PROXY_USER_ID, PROXY_LEVEL from OBIEE_PROXY where PROXY_TARGET_ID='@{USERID}'
         </getDelegateUsers>
       </logonParam>
    </XML>
  </WebMessage>
 </WebMessageTable>
</WebMessageTables>

6. Update the instanceconfig.xml file to include the below elements.  The file should be located at:

[OBIEE_HOME]\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\

Be sure to back up this file before making updates.  For mine I added it before the </ServerInstance> tag at the end.  See picture below.

<LogonParam>
  <TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
  <MaxValues>100</MaxValues>
</LogonParam>

(See picture below for placement)



 7. Check OBIEE Security settings to make sure you've provisioned access to proxy for the appropriate role.  If you click on Administration, and select 'Manage Privileges'.

 

You should see the option of providing access to 'proxy'.




7. Restart services.  I didn't test whether or not this will work without restarting services, but I doubt it would work without it.    

Now, when you click on your name, and select 'Act As', it should give you a small window with a list of the users you inserted into the table in Step 2. 

OBIEE 11g - Using a Dashboard Prompt as a Column Selector

Yesterday, I was looking for a solution for creating a column selector that would work for an entire dashboard page, without the objects on the page being part of the same compound view.  I found this link on Business Intelligence Consulting Group site that looked promising ( BI Consulting Group ).  However, with some Prompt functionality in OBIEE, I was able to do it using a simpler method that does not require any updates to the RPD.  

Create Variable Prompt

1.  First, instead of using the method from the link above where they start by creating a member in the RPD, I decided to bypass this by using the standard functionality within a Variable Prompt.  I decided to populate the variable 'SELECT_COLUMNS' with a variable prompt, and added custom values that will correspond to Presentation Column names in the RPD.  

Add your Presentation Column names in the 'Choice List Values', just as they are in the RPD

Example of Presentation Columns in RPD that match the custom
values in the Variable Prompt

2.  Now, I update my OBIEE query to have the Presentation Variable as part of the column name.  Now, when I put these together in a dashboard, the Presentation Variable allows me to select the column that I want to show in the dashboard.  Like I said, a column selector will do the same thing if you only have one query/view.  However, if you want to update more than one compound view, a column selector isn't really going to allow you to update all views with the same column selector. This approach allows you to do this without any RPD development.

Now, I add my Presentation Variable 'SELECT_COLUMNS' in the column formula to allow for the Variable Prompt to drive the Presentation Column that will be displayed in my dashboard.