OBIEE 11g - Sorting on the Total in a Pivot

Pivots in OBIEE are a great option of displaying data, especially monthly data.  However, I often run into a situation where the customer wants me to show trailing 12 months of data, sorted descending by the total for those 12 months.  Unfortunately, OBIEE doesn't have a sort arrow on the Total.  And, creating a column with a total doesn't get me what I need, since it ends up being another metric in the pivot.  For example, a customer might want to see this:

Unfortunately, with the limitation of not having a sort button on the total, this is not as simple as one would like.  However, there is a way to do this.  What I did in the above example, was create a sorting column.  I started out by trying to add a calculation to give myself a SUM of the metric/fact by the Dimension ('Utility').  However, if I do this, it shows up as a fact/metric, and it won't let me sort on it.  So, I then tried to check the option 'Treat as an attribute column'.  However, this caused performance issues that were unacceptable.  So, I finally came to a solution where I created a Rank column, by ranking the sum of the metric by the 'Utility' field, and then converted it to an integer (if you convert to a character, it will sort a 10 before a 2).  I set the option to treat the field as an attribute, and now it works.  Below is the example of what I did.

This formula gives me the ability to have a Rank column that I can now put in my pivot and sort on it.  If I want to, I can also hide this field, but the downfall of this is that once hidden, none of the other sorts will work, since the first sort is on a hidden field.  Below is the formula and a picture of the final result:

CAST(RANK(SUM("Fact Table"."Field" BY "Dimension Table"."Utility")) AS INTEGER)

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.

OBIEE 11g: Calculated % always shows up as 0%

While upgrading some of my dashboards from OBIEE 10.1.3.4 to 11.1.1.5, I noticed that some of my calculations in Answers were showing zeros instead of the calculated amount.  The calculation I was trying to perform was to calculate a match rate % for some data based on a field that showed 'MATCH' or 'NO MATCH'.  The original calculation was:

(SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(BuyPoint.Match ))*100

However, this was returning 0% as the result.  This happens when you have an integer in a calculation.  A very easy fix to this is to multiply by 1.0 (don't use 1, as this will not work.  Use 1.0).  The updated calculation below works and provides a meaningful result.

((1.0*SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(BuyPoint.Match )))*100

OBIEE 11g: "OBIEE content cannot be displayed in an IFrame"

OK, so I started this blog because of the issue of not being able to render an OBIEE page within a Hyperion Web Analysis HTML window.  I needed to do this for some additional functionality required prior to converting the entire Web Analysis dashboard over to OBIEE (still looking at Essbase connectivity with OBIEE to see if it is a viable solution).  I found half the solution on someone's blog, but half the solution doesn't help.  Below are both sides of the solution.  


1)  Locate the instanceconfig.xml file at the location:
[OBIEE_HOME]\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\


Update the instanceconfig.xml file to add the highlighted entry within the "security" tag (possible values are 'allow', 'prohibit' and 'sameDomainOnly'):

<Security>
<InIFrameRenderingMode>allow</InIFrameRenderingMode>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<ClientSessionExpireMinutes>210</ClientSessionExpireMinutes>
</Security>

Now, this will only get you half way.  You'll still run into the error, which really doesn't help you any. The 2nd piece is as follows:

2)  Edit the 'web.xml' file at the following location:

[OBIEE_HOME]\oracleBI1\bifoundation\web\app\WEB-INF\

Add the following highlighted entry to the file.  The syntax is the opposite of what the syntax for instanceconfig.xml is, since the 'never' means to never block the rendering within IFrame (you can choose 'never' to always allow rendering in the IFrame, or 'differentDomain' to only allow rendering if it is in the same domain).  

 <servlet-mapping>

      <servlet-name>SAWBridge</servlet-name>

      <url-pattern>/saw.dll/*</url-pattern>

   </servlet-mapping>

   

   <servlet-mapping>

      <servlet-name>RelatedContent</servlet-name>

      <url-pattern>/RelatedContent</url-pattern>

   </servlet-mapping>

   <context-param>

   <param-name>oracle.adf.view.rich.security.FRAME_BUSTING</param-name>

   <param-value>never</param-value>

   </context-param>

   <login-config>

    <auth-method>CLIENT-CERT</auth-method>

   </login-config>

3) Now that you've completed both steps, restart the OBIEE System Components using opmnctl (Stop opmnctl and Start opmnctl).  If you need to find this, you'll find it in your start menu on the server under the 'Oracle Business Intelligence' folder, called 'Stop BI Services' and 'Start BI Services' respectively.  

4) Before testing, be sure to clear your browser cache.