Tag Archives: Hyperion
Hyperion Essbase 11.1.1.2 – Varying Attributes
Another excellent feature that was introduced as part of the EPM 11 release was the support for varying attributes. Varying attributes help in providing different perspectives of multiple attributes of a dimension over time. One can visualize this as SCD 2 in a relational world. For example, lets take the Sample –> Basic cube and look at the various attributes that have been defined.
As you see, this cube has 5 different attributes defined on Product and Market dimensions. As of 9.3.1 release of Essbase one can have only static attributes defined. If varying attributes had to be defined, they had to be modeled as separate individual dimensions. Static attributes are those attributes that remain constant for a specific member. For example, lets assume that a product X has a static weight of Y ounces when it was introduced. If the manufacturing company decides to repackage the product X with a weight of Z ounces, then as of 9.3.1 release all the sales data mapped to Y ounces would switch over to Z ounces(similar to SCD-1 in a relational DW world). But in most cases, we want to see the sales data corresponding to their weights so that one can analyze the sales drop or increase due to the new product packaging. This is called as varying attributes over time. Sometimes, the same product X can be packaged with Y and Z ounces depending on Market. In this case, the weight varies over Time as well as Market.
With the advent of EPM 11.1.1.0, one can enable tracking of metrics over varying attributes as well. In the above outline, lets take the example of Pkg Type attribute. This has 2 values
1. Bottle
2. Can
This packaging can vary for products across multiple Markets. Also the packaging can vary for a product in a specific market over time. To enable varying attributes, one would have to first enable this feature while creating an outline or later.
In order enable the attribute Pkg Type on the product dimension, one would have to choose the attribute and the set of independent dimensions. Independent dimensions are those dimensions over which the attribute varies like Market and Time.
Continuous dimensions(in the screenshot above) are typically those dimensions like Time where there is a chronological order and where we can specify a range. After this has been enabled, while setting the attribute for each product, the Market and Time would have to be chosen as well. For continuous attributes, one can specify a range.
For each product set the Time range over which it is planned to be sold and also the market. In the above example, the product 100-10 is sold in New York Market from Jan till Dec in Bottle Type and in the Massachusetts Market from Jan to Dec in Can type. The same kind of association would have to be done for each and every Market for the corresponding Pkg Type.
The above basically provides a relational visualization in a multi-dimensional cube. This also provides analysis of data in multiple perspectives. For example, we might be needing a report wherein we would like to analyze the sales for the list of products that were sold as Bottles in New York from Jan-Dec with a perspective of the attribute setting that we had in July. These kind of queries can be answered pretty easily by Varying attributes. In a future blog entry we shall see how these varying attributes can be leveraged from BI EE. As of the current release in BI EE, one can only use the default query context/perspective. One cannot alter the perspectives due to the limitation of modifying certain parts of the MDX query in BI EE. Having said that, it is possible to leverage the varying attributes using the default perspective. I will cover this in the coming weeks.
Hyperion Essbase 11.1.1.2 – Altering Measure Formats – Format Strings and Text Measures
If you had read my blog entry here, i would have shown how to go about creating textual measures in Essbase. Though that was a new feature introduced as part of the EPM 11 release, one related feature that i did not cover was the introduction of Format Strings. This probably is one of the very important features from an end user reporting standpoint. In a couple of implementations that i was involved in on 9.3.1, i faced a situation wherein we had to control the format of the data within Essbase itself. Unfortunately, since that was not possible in 9.3 release, custom formatting had to be applied in each and every downstream tools like Excel Add-in, HFR, BI EE etc.
Format strings help in controlling the output format of the measure values. For example, if we have an Expense account and a revenue account, the normal reporting requirement is to show the variance of the current quarter Actuals with the last quarter Actuals for both the type of accounts. For Revenue accounts, when current quarter is more than last quarter, then the variance would be positive else it would be negative. For Expense accounts, when the current quarter is less that last quarter, then the variance would be positive else it would be negative. Also, all negative values would have to be shown within braces and not as negative values.
Normally to implement the above requirement, we would have to write a custom formula to change the variance formula based on the Expense/Revenue UDA in an ASO cube(BSO cube has Expense/Revenue based reporting properties. But it is very limited in its usage). Also, adding braces to negative values would have to be done in the front end. Lets see how Format Strings can help us in achieving this requirement. Lets take the example of the Sample – Basic BSO cube. The strange aspect to the Format Strings is the fact that the implementation is done using MDX even in a BSO cube (should give a hint about the strategic direction 
).
Lets first look at the outline. There are 2 measures Sales and COGS. Sales is a revenue measure and COGS is an expense measure.
Now lets create another member called Variance as shown below in the Scenario dimension.
This variance member is actually updated through a calculation script shown below (Format strings would not work on members having a member formula in BSO cubes)
SET CACHE HIGH;SET LOCKBLOCK HIGH;
CLEARDATA "Variance";
CALC DIM ("Year");
"Variance" = "Actual"->&CurrQtr - "Actual"->&LastQtr;
CALC DIM ("Market","Product");
Run the calc script after the data load. Following would be the variance values in Excel add-in.
As you see, since there was no formatting applied on the Variance values, both COGS and Sales are getting treated the same way i.e shown as positive numbers. Now, in order to apply format strings on this, lets first convert the outline into Text measure compatible outline.
After that, go to the member properties of the Variance member and add the MDX shown below
MdxFormat(IIF(Is([Measures].dimension.CurrentMember, [COGS]),
CASE WHEN cellvalue() > 0 then Concat(Concat("(", numtostr(CellValue())), ")" ) else numtostr(-cellvalue()) end,
CASE WHEN cellvalue() < 0 then Concat(Concat("(", numtostr(-CellValue())), ")" ) else numtostr(cellvalue()) end
))
Now if you go to Excel add-in, and retrieve the same data, you would notice that the accounting format would have got applied automatically.
This is an excellent feature. In fact using this alone, one can mimic textual measures that i explained before. In a future blog entry i would show how this can be leveraged from other downstream tools like HFR, BI EE etc.
Oracle BI EE – Bulk Write backs to Essbase – Using JAPI, Global Temporary Tables and UTL_HTTP – Part 2
I had covered an approach here to do write backs into Essbase from BI EE. In some cases that approach would be sufficient. But in most of the cases, end users would want the capability to do a bulk write back similar to the default write back option provided by BI EE(like in relational sources). For example, if you look at the screenshot below, we have an Essbase report containing Q1, Eastern Market sales.
If you use the last blog entry’s approach you can write back to Essbase one row at a time. But what if our requirement is to write all the modified rows in a single shot to Essbase. In order to achieve that, the architecture for write back would be slightly different from what we saw before. The high level architecture is given below
As you see, in order to achieve the writebacks we would be using the usual Write back template provided by BI EE. The main difference between this and the relational write back is the fact that we would be writing into a global temporary table. We are using a Global Temporary table as it has the feature of truncating itself at the end of a transaction or a session. This Global Temporary table is used just for dummy purpose. During the insert into the temporary table, we would be calling a function which would in turn pass down the parameters to the UTL_HTTP function to call the custom JSP that we created last time. So let us first start with the JSP page that we had created before. For the sake of completeness, i am pasting the code again here.
<%@ page contentType="text/html;charset=windows-1252"%><%@ page import="java.io.*" %><%@ page import="java.util.Map" %><%@ page import="java.util.Map.Entry" %><%@ page import="java.util.jar.Attributes" %><%@ page import="java.util.Iterator" %><%@ page import="com.essbase.api.base.*" %><%@ page import="com.essbase.api.dataquery.*" %>
<%@ page import="com.essbase.api.session.*" %>
<%@ page import="com.essbase.api.datasource.*" %>
<%@ page import="com.essbase.api.domain.*" %>
<%@ page import="com.essbase.api.metadata.*" %> <%="WriteBack Started" %><%
String s_userName = "admin";
String s_password = "password";
String s_olapSvrName = "localhost";
String s_provider = "http://localhost:13080/aps/JAPI";
try
{
IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
IEssCubeView cv = dom.openCubeView("Data Update Example",s_olapSvrName, "Demo", "Basic");
String v_Market = request.getParameter("p_Market");
String v_Product = request.getParameter("p_Product");
String v_Accounts = request.getParameter("p_Accounts");
String v_Scenario = request.getParameter("p_Scenario");
String v_Year = request.getParameter("p_Year");
String v_Value = request.getParameter("p_Value");
IEssGridView grid = cv.getGridView();
grid.setSize(2, 5);
grid.setValue(0, 1, v_Market);
grid.setValue(0, 2, v_Product);
grid.setValue(0, 3, v_Accounts); ;
grid.setValue(0, 4, v_Scenario);
grid.setValue(1, 0, v_Year);
cv.performOperation(cv.createIEssOpRetrieve());
System.out.println("\nData Cell at 2nd-row, 2nd-column: " + grid.getValue(1,1).toString());
System.out.println ("Market: "+v_Market+" Product: "+v_Product+" Accounts: "+v_Accounts+" Scenario: "+v_Scenario+" Year: "+v_Year+" Value: "+v_Value);
int row = 1, col = 1;
if (grid.getCellContentType(row, col) ==
IEssGridView.CELL_CONTENT_TYPE_DOUBLE) {
IEssValueAny val = grid.getValue(row, col);
double dblVal = val.getDouble();
grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());
} else if (grid.getCellContentType(row, col) ==
IEssGridView.CELL_CONTENT_TYPE_MISSING) {
grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());
}
IEssOpUpdate opUpd = cv.createIEssOpUpdate();
cv.performOperation(opUpd);
}catch (EssException x){
System.out.println("ERROR: " + x.getMessage());
}
%>
<%="WriteBack Ended" %>
<%="WriteBack Ended" %>
<%
//response.sendRedirect("http://localhost:9704/analytics");
%>
After compiling the above jsp, deploy this to any Java Application server as an EAR file. The main reason for doing this is to have a URL that we can call using the UTL_HTTP database package. If more security is needed, one can code them directly into the JSP so that no one else is able to access the jsp outside of the UTL_HTTP package.
The next step is to create the Global Temporary Table. The structure of the Global temporary table can contain any number of columns. But we would need a column which we would be inserting into through the template via a function. In my case the Global Temporary table contains all the write back columns as well as an extra column that indicates whether the write back was successful or not.
create global temporary table EssbaseWriteBack_GTT(Year varchar2(100),Market varchar2(100),Product varchar2(100), Scenario varchar2(100), Sales Number, WriteBackStatus Number ) ON COMMIT DELETE ROWS;
Once the global temporary table has been created, we need to create a function which will basically pass the input parameters to the JSP. The JSP is basically called through the UTL_HTTP package and the parameters are passed using the GET method. If you need more security, POST method can also be used.The idea is for every row insert, the row attributes are passed into this function which would basically write back into Essbase.
create or replaceFUNCTION WriteBack_GTT(p_Year varchar2,p_Market varchar2,p_Product varchar2,p_Scenario varchar2, p_value NUMBER ) RETURN NUMBER IS req Utl_Http.Req; resp Utl_Http.Resp; v_msg varchar2(80); v_url varchar2(32767) := 'http://localhost:9704/AllocEssbase-AllocEssbase-context-root/EssbaseWriteback.jsp?'; begin v_url := 'http://localhost:9704/AllocEssbase-AllocEssbase-context-root/EssbaseWriteback.jsp?'; v_url := v_url||'p_Market='||p_Market||'&p_Year='||p_Year||'&p_Accounts=Sales&p_Scenario='||p_Scenario||'&p_Product='||p_Product||'&p_Value='||to_char(p_Value); Utl_Http.Set_Response_Error_Check ( enable => true ); Utl_Http.Set_Detailed_Excp_Support ( enable => true ); req := Utl_Http.Begin_Request ( url => v_url, method => 'GET' ); Utl_Http.Set_Header ( r => req, name => 'User-Agent', value => 'Mozilla/4.0' ); resp := Utl_Http.Get_Response ( r => req ); Dbms_Output.Put_Line ( 'Status code: ' || resp.status_code ); Dbms_Output.Put_Line ( 'Reason phrase: ' || resp.reason_phrase ); Utl_Http.End_Response ( r => resp ); RETURN 1; exception when Utl_Http.Request_Failed then Dbms_Output.Put_Line ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm ); RETURN 0; when Utl_Http.Http_Server_Error then Dbms_Output.Put_Line ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm ); RETURN 0; when Utl_Http.Http_Client_Error then Dbms_Output.Put_Line ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm ); RETURN 0; when others then Dbms_Output.Put_Line (SQLERRM); RETURN 0; end;
The next step is to create the Write back template as shown below.
<?xml version="1.0" encoding="utf-8" ?><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="WriteBack" table="Messages"><WebMessage name="WriteBack"><XML>
<writeBack connectionPool="SH">
<insert> </insert>
<update>INSERT INTO EssbaseWriteBack_GTT(YEAR,MARKET,PRODUCT,SCENARIO,Sales,WRITEBACKSTATUS)
VALUES ('@{c0}','@{c1}','@{c2}','@{c3}', @{c4}, WriteBack_GTT('@{c0}','@{c1}','@{c2}','@{c3}', @{c4})) </update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
Place the write back template in the {OracleBI}\web\msgdb\customMessages folder. Restart the presentation services. Now navigate to the report and enable write back on the Sales column.
Once the write back is enabled, you should be able to update multiple rows back to Essbase simultaneously.
The above will work if you are on a Block storage cube(BSO). If you are on an Aggregate Storage(ASO) cube, you need to ensure that you are writing back only to level-0 intersections. Else the write back will fail. The other option for ASO cubes is to setup the write back partition with a BSO cube. The above architecture can even be expanded further to update cell comments, dimension members etc.
Fun and Games with OBIEE Connection Pools and Essbase Logins
When you bring in an Essbase database into the Oracle BI EE 10.1.3.4 and higher semantic model, you typically import it using the “admin” Essbase user ID. This is a standard account that comes with the default installation of Essbase and provides full access to all of the applications and databases in your Essbase server. After the Essbase outline import has completed, if you take a look at the connection pool that’s been set up it uses this “admin” account as the Essbase login.
This is all great, and every user that logs into Answers that has access to this data source will connect as the Essbase “admin” user. The problem then comes if you want to make use of Essbase security, to take advantage of filters or to restrict access to Essbase databases based on Essbase users and groups. To do this, you would typically substitute the values :USER and :PASSWORD in the connection pool connection properties section, like this:
This tells the BI Server to substitute the logged in users’s User ID and password for these two variables, so that they can be passed through to Essbase. If you then create corresponding user accounts in either the BI Server repository, or in a connected LDAP server, this will then work as long as the user account names and passwords match those used by the Essbase server. If however, you plug the Shared Services directory into the Oracle BI Server, via the Hyperion Custom Authenticator, and try and log into Answers using a Shared Services login that also has access to the Essbase cube, you get this error when accessing the cube.
So what’s going on here then? Why is Essbase rejecting our login, especially as it’s come in via Shared Services in the first place? Well a clue cam be found when you view the user’s account details in the “My Account” page of the dashboard. Notice how “@Native Directory” has been appended to the “admin” user name?
For some reason, the Custom Authenticator used by the BI Server to authenticate against Shared Services is appending the Shared Services directory name to the :USER variable. Although you might think, looking at the above screenshot, that you could use Display Name instead, in most cases this is the first name and last name of the user which again would fail as a valid login for Essbase. So what can we do?
Well Venkat and I were discussing this yesterday evening, and a suggestion that he came up with was to capture the login, as entered by the user into the dashboard login page, save it into another session variable and then use this instead of the :USER variable which is then subsequently “corrupted” by the Hyperion Custom Authenticator. To put this idea in place, I first define a new BI Server session variable, and call it INITUSER, and then I configure the Init Block so that it copies the :USER value into it.
I then configure the Init Block that runs that Hyperion Custom Authenticator to run my new Init Block before it, so that I can capture the value of :USER before It corrupts it.
Finally I configure the Essbase connection pool to use this new session variable for the User ID, using VALUEOF(NQ_SESSION.INITUSER), like this:
Now if I save my repository and then log back in to Answers, I can run my report and access Essbase without any problems, as the BI Server is now connecting via my alternative user ID variable.
There is one final twist to the story though. So far, we’ve been talking about users logging in to Answers directly through the standard OBIEE dashboard interface, but in reality, they may wish to log into Answers via EPM Workspace, which has single sign-on between the EPM products and OBIEE. The problem here though is that whilst the BI Presentation Server will SSO you into Answers using your Shared Services login, directly from the Workspace UI, I’m told that the SSO process doesn’t pass across the user’s password into the :PASSWORD session variable used by the BI Server, and therefore this password can’t then be used to connect you into Essbase via the connection pool. Using our new session variable. if we try and connect via EPM Workspace into Answers we get this error, which is showing the CSS token as the User ID it’s trying to log in as:
whereas if we revert back to :USER and :PASSWORD, it’s failing now both because of the @Native Directory issue, and because the :PASSWORD variable isn’t now being populated.
So from what we can tell, firstly this appending of @Directory Name to the HSS user ID is causing Essbase connection pool logins to fail, but we can work around it using this approach of an additional custom Init Block, but so far we’ve not found a way around the EPM Workspace SSO issue, which means that if we want HSS users to be able to log into OBIEE and then access Essbase cubes using their own Essbase login, we have to restrict them to directly logging in to the OBIEE web interface rather than going through EPM Workspace. If anyone’s got a solution to this last issue, add a comment to this posting.
