Business Intelligence – Oracle

Archive for January, 2008

Oracle BI EE 10.1.3.3/2 – Help URL in Title View

Posted by Venkatakrishnan J on January 22, 2008

I had an user today emailing me about the non-working of Help URLs in Title Views. I thought i would take a look and the results were pretty strange. First to give you a background, Help URLs are those URLs to specifc documents that an end user can refer to while looking at a report. They occur as Question Marks on the top of a title view. When one clicks on the question mark it would take you to the document specified in the help URL. Now lets start with entering some simple URL like http://oracle.com in the Help URL text area and see what happens.

      

Well, in fact nothing happens. Then lets try entering just fmap: in the Help URL and see what happens. Now we get the “?” in the preview. But if you hover over this question mark you would notice that the URL would be something like this http://localhost:9704/analytics/Missing_”. Very strange. So far, i am not sure why the Missing_ is appended to this. Maybe because of OC4J.

      

      

I confirmed this is the same behaviour even in 10.1.3.3.2 and 10.1.3.2. Now, the question is how do we get this to work. In order to make this work, go to {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics and create a folder called Missing_.

      

Once this is done copy all your html help documents into this folder. For example, lets copy a html document AboutPrompts.html from {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\olh\l_en to {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\Missing_. Now restart OC4J. Enter fmap:\AboutPrompts.htm in the Help URL text area. Now you can see that you would be able to access this html page directly by clicking on the Question Mark. So, all you need to do is copy and paste all the necessary documents to {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\Missing_ and restart OC4J.

      

      

Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3.2 – Connectivity to Essbase – First Impressions

Posted by Venkatakrishnan J on January 21, 2008

I had some time today to quickly install Essbase and BI EE 10.1.3.3.2 to test out the newly added feature of Essbase connectivity. Following are the list of bare minimum components that you would need to get this up and running.

1.   BI EE 10.1.3.3.2 – If you are doing an upgrade from an earlier release ensure that you have added ESSBASE = nqsdbgatewayessbasecapi; to your NQSConfig.ini
2.   Hyperion Essbase Server
3.   Hyperion Essbase Client (This is very important for the connectivity to work. If you already have an Essbase server somewhere then you would just need 1 and 3).
4.   Hyperion Administration Services (This is for starting up various Essbase applications).

You can download the Essbase components from http://edelivery.oracle.com. Just an observation from my end. Ensure that you are restarting your machine once all the components have been installed (After installing Essbase, it might not ask you to restart. But BI EE would not be able to identify Essbase immediately since the Essbase client APIs need some Environment variables that get populated only after a restart). Once this is done, go to the admin tool and click on Import from Multi-Dimensional.

      

      

So far so good.

      

As you see, you can import databases from multiple applications simultaneously. The next step is to create a Business Model and Presentation Layer. For this we do not have to design BM and PL. All we need to do is drag and drop the folders directly into BM and PL. For example, when you drag the Sample database, you can see that all the hierarchies and joins would automatically be imported from Essbase.

      

      

Even the final reports seem to be very fast. Of course, the sample applications do not have a large dataset. Lets see how the connectivity works on a larger data set in a future blog entry.

      

P.S: Came to know about the existence of a new Oracle OLAP blog via Mark’s blog here. Any users who want to know about Oracle OLAP, this blog is where you should go since this is maintained by 3 well known OLAP gurus, if i may call them that. Having grown to reading Kevin Lancaster’s long educative replies in our internal mailing lists, i have been waiting for him to be part of a blog so that others can also benefit. Looks like the wait is over :-). Also, the other 2 authors, Keith and Jameson are well known within Oracle OLAP circle. Both Keith and Jameson are former Product Managers. If you do not know where to start you should start with their Workshop series.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3/2 – Dynamically varying Colums in Sub-Prompts – Passing Presentation Variables to Dashboard Prompts

Posted by Venkatakrishnan J on January 21, 2008

We just had another interesting question today in one of our internal forums. The question was how do we dynamically vary the columns in a Dashboard Prompt based on a selection in another dashboard prompt. Let me explain a bit further. For example, lets assume that we have 2 dashboard prompts. One has values like Region Sales and Brand Sales (these are hard coded values, same as what we saw in the previous blog entry). Now the requirement was, whenever Region Sales is chosen in one prompt the values in the other prompt should change as to show the values of only category names (CATEGORY_NAME). Whenever Brand Sales is chosen in the 1st prompt the 2nd prompt should show only the product names (PRODUCT_NAME). I can envision a similar requirement in many scenarios where one might want to have a single page wise dashboard prompt that would filter all the other prompts within the dashboard whenever a selection is made. In order to achieve this, lets start with the first dashboard prompt where we shall print dummy values ‘Region Sales’ and ‘Brand Sales’. Make this dashboard prompt to set a presentation variable say Report. For more details on how to achieve this refer my blog entry here.

      

Now lets start creating the sub-prompt whose reference column would change based on what is chosen in the first prompt. In order to do this, lets include a dummy column (whatever we choose in the dashboard prompt will not affect any columns. In order to use this prompt we can only use presentation variables) by entering the below case statement in the formula tab.

CASE WHEN 1=0 THEN PRODUCTS.CATEGORY_NAME ELSE ‘Dummy unused Column’ END

      

Now in the dashboard prompt, convert the results to show only SQL Results and enter the below SQL.

SELECT CASE WHEN ‘@{Report}’=’Region Sales’ THEN PRODUCTS.CATEGORY_NAME
WHEN ‘@{Report}’=’Brand Sales’ THEN PRODUCTS.PRODUCT_NAME ELSE ” END FROM SH2

      

So basically, we are passing the presentation variable that we used in the first prompt to the second prompt. Lets now look at the final output after including both the prompts in a dashboard.

      
Lets first choose Region Sales and see what happens to the second dashboard prompt.

      

Similarly, lets choose Brand Sales and see what happens.

      

The only disadvantage of this approach is that the users would have to hit the GO button to make the dashboard prompts to change. But again this can be very helpful in situations where you want a single dashboard prompt to control the output of multiple dashboard prompts.

Posted in All Posts, OBI EE Plus | 7 Comments »

Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables – Part 2

Posted by Venkatakrishnan J on January 18, 2008

Well, this is an extension of what we saw earlier the other day here. I was told by one user that though they were able to mimic the report in terms of look and feel, there still was one part of the report that did not look the same when compared with the new features guide. For example, look at the screenshot below (Thanks Monika for providing this screenshot).

      

Basically, we have a repeating Total Fixed Assets and Total Investment which user did not want to have. So, lets go back to the report that we created and start modifying it from where we left off. One of the major problems with sub-totals is that there is no way we can filter them so that they appear only for certain rows. So, the first step is to go and remove the sub-total that we created in BALANCEGROUP3 column so that it removes the inner sub-group.

      

Once this is done, we need to create 2 sub-totals. One for Cash and one for Other Assets. So, go to ACCOUNTDESC and click on create new Calculated Item. In the Calculated Item sum all the values that are part of Cash metric. Then go to Format of this Calculated Item. Make it bold and give a left padding of 50.

      

      

      

This would create our Cash sub total. Similarly create the sub-total for Other Assets.

      

      

Now we have our final report with non-repeating sub-totals.

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI 10.1.3.3/2 – Pagination in Pivot Tables

Posted by Venkatakrishnan J on January 17, 2008

Another common request that usually comes up is how do we enable Pagination in Pivot Tables. Though this is not available out of the box, lets look at one approach today to have pagination in pivot tables. Lets start with a simple pivot table report as shown below containing 2 columns, DIMENSION_KEY and SALES ( ihave taken this report just as an example).

      

Now our aim is to add pagination to this report. In order to do this lets go back to the criteria tab and add one more column to this report(any column). Go to the formula window of this column and enter the formula shown below

CASE WHEN RCOUNT(1) < 11 THEN ‘1-10′
WHEN RCOUNT(1) < 21 THEN ’11-20′
WHEN RCOUNT(1) < 31 THEN ’21-30′
ELSE ’30+’ END

So, what the above code basically does it creates repeating rows 1-10, 11-20 etc. You can modify the above to suit your needs.

      

      

Once this is done, drag and drop this column into the Pages section of your pivot table. Now you can paginate through your pivot table report.

      

Very simple but can be pretty handy in a lot of situations.

Posted in All Posts, OBI EE Plus | 7 Comments »

Oracle BI 10.1.3.3/2 – Selecting Reports from Dashboard Prompts and Guided Navigation Sections

Posted by Venkatakrishnan J on January 17, 2008

While i was away on vacation, a set of pretty good questions came up as comments in the Contact Me page of this blog. Though i never expected this page to get any comments at all, it looks like i was wrong. So, apologies to everyone who have been waiting for a reply from my end. I plan to make a reply as soon as a comment is made from now on. Among those comments, one user had a question on how to go about creating a dashboard prompt to list down specific report names and while selecting the report names the user wanted to show those corresponding reports within the dashboard. I have had this question asked before. Though it is not pretty straight forward to achieve this, lets look at one of the possible approaches today. In order to do this, lets start with a very simple example. You can modify this to suit your needs. In our example, our aim is to make the dashboard prompt to show two report names ‘Brand Sales’ and ‘Region Sales’. Based on what we select in this dashboard prompt we would be showing either the Brand Sales Report or the Region Sales report in the dashboard. Lets start with creating the dashboard prompt. For now we shall be hardcoding the values in the dashboard prompt.

So, create a dashboard prompt on a column (though this column will not be actually used. This is just to trick the BI Server) and in the formula enter the below case statement.

      CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END

As you see the idea is not to filter on the actual column but instead on a dummy value of Brand Sales.

      

Once this is done, go to the Show drop down and go to SQL Results. In the SQL Results enter the below SQL. Also, make the prompt to set a presentation variable say Report

      SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Region Sales’ END FROM SH2 UNION ALL SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END FROM SH2

      

As you see above, what we are basically doing is making the dashboard prompt to print our Report Names and then we are making the presentation variable to accept these report name values. Now the next step is to create 2 reports that each will return rows for one of the above selections and zero rows for the other selection so that we can use this as a source for our guided navigation section.

      

      

      

What we are basically doing above is creating a report that just has a column with a dummy value Region Sales. And a filter is applied on this report with the presentation variable Report so that this report will return non-zero rows when we choose Region Sales value in the dashboard prompt. Similarly this report will return zero rows if we choose Brand Sales value in the dashboard prompt. Create another report as shown above but in this case make it to return non-zero rows when Brand Sales is chosen.

Now go to the dashboard and include the dashboard prompt that we created in a normal section. Now included 2 other sections below this dashboard prompt and each of these sections would be a guided navigation section. Include the region sales report in GN section and Brand Sales report in the other GN section. Go to the GN section properties containing the Region Sales Report and in the source, reference the report that we created above and make it to show only when it returns some rows. Similarly, for the GN section containing the brand sales report, reference the second report that we created above and make it to show only when it returns non-zero rows.

      

      

Now if you go choose Brand Sales in the Dashboard prompt, you would see that the Brand Sales report would be listed. Similarly if you choose Region Sales, then the Region Sales report would be shown.

      

      

The idea is pretty straight-forward. But this can be used especially if you want to allow your users to choose multiple reports.

Posted in All Posts, OBI EE Plus | 8 Comments »

Its a day of Acquisitions – Oracle to Acquire BEA and SUN to Acquire MySQL

Posted by Venkatakrishnan J on January 16, 2008

What more does one need after a nice little vacation. News of acquisitions of course :-). Well it looks like a day of acquisitions. I am not sure how many of these is true. Use your own discretion to validate these since i am just pointing you to some market news of which i have absolute no knowledge before hand or even now for that matter. If it is indeed true wonderful days ahead for the entire Java, SOA and BI community. We would have the best of breed of everything interacting with each other seamlessly. I am thinking far too ahead of course but again i am entitled for free thoughts aint i? :-). Coming to the actual acquisitions themselves following are some news articles.

Oracle to Acquire BEA: http://www.bloomberg.com/apps/news?pid=20601087&sid=aNvBPJQ_cvio&refer=home

Sun to Acquire the Open Source Database Vendor MySQL: http://ap.google.com/article/ALeqM5gBphtwc5ZhkRHmUBQAj_6yJmvLpAD8U70OOO1

Posted in Acquisitions, All Posts, General | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Input box data validation using Guided Navigation Sections

Posted by Venkatakrishnan J on January 12, 2008

Couple of days back we had seen how to go about achieving input box data validation using custom Java scripts here. One of the drawbacks as mentioned there was that it applies the validation to all your edit prompts. Though you can still further modify the java script to make it work only for your dashboard prompt but again that would entail lot of custom coding from our end which sometimes is a overkill for such a small feature. So, lets look at another approach today which would use Guided Navigation sections. Lets start with an example wherein we have a dashboard prompt on AMOUNT_SOLD column (using between). Below the dashboard prompt we have a report containing PRODUCT_NAME and AMOUNT_SOLD(this contains a filter on AMOUNT_SOLD with is prompted clause). So, first lets check whether the filter works by entering proper values.

      

The problem with this prompt is if we enter some char values you would find that this results in an error which many users would not like.

      

So our aim is to hide this report whenever wrong data is entered rather than this error. In order to achieve this go to the dashboard edit page and ensure that you have both the prompt and the report in different sections (if you have more reports this report alone should have a seperate section). Now go to the section properties(the section that contains the report) and click on Guided Navigation. In the Guided Navigation properties, reference the source request and reference the same report that we have used above. So, basically our aim is to hide this section when it does not return any data or any error.

      

      

Lets test it out by giving some character data.

      

As you would see this would not show the report at all if we enter any wrong data which is better than the error message.

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables

Posted by Venkatakrishnan J on January 10, 2008

I had a couple of emails referring to a particular new features guide here. It has been said in this new features guide that one can achieve highly formatted financial layouts and also an example is given in the form of screenshots. A couple of users wanted to know how to go about achieving the same. Now lets start with exactly the same pivot table as shown in the guide above and try to achieve the same.

      

In the final layout, we only need the BalanceGroup1, AccountDesc and Amount columns. So lets hide the remaining 2 i.e BalanceGroup2 and BalanceGroup3. We are not removing these columns since we need sub group sums.

      

Once this is done, if you look at the final layout required, there is a line break between BalanceGroup1 and Account Desc. So, we would have to move the BalanceGroup1 column to the Sections area.

      

If you notice, we still have to position the Assets to the left and then remove the tabular headings. In order to achieve this, go to the Section Properties and insert Page Break on the BalanceGroup1 column. After that go to the Measure Labels section and hide the headers.

      

      

Now, we need to include to the sub-totals for both BalanceGroup2 and BalanceGroup3.

      

Next is to left Align the Assets value. Go to the Section Properties and change the Horizontal Cell Alignment to Left.

      

Next we need to change the label names to something Total Assets etc. In order to do this we shall use “Total @”. @ will bring the value of the column.

      

Now the next step is to add indentation to all the Totals and subtotals. Go to the hidden BalanceGroup2 total Label properties and add a left indentation of 25. Similarly add the left indentation for BalanceGroup2 to 50 and AccountDesc values to 75.

      

      

Our result so far will be like this.

      

Now the next step is to remove all the border lines. In order to do this go to all the Columns and Section properties and change the Border Position to None. For example go to AccountDesc Format Values and change the position to None as shown below.

      

      

The following pic shows all the places that you need to change the Border Position to None.

      

The next step is to change the background color of the subtotals and the AccountDesc to #FFFF (that will give a white background). And lastly if you want total values to have 2 horizontal bars on the top just go to border position of the sub total and include the top two borders.

      

And this should give you the desired Financial Template Layout.

      

I know some of the above is pretty trivial. But this should give you an idea of how to achieve very good formatting with pivot tables.

Posted in All Posts, OBI EE Plus | 16 Comments »

Oracle BI EE 10.1.3.3/2 – Dashboard Prompt Edit box input validation

Posted by Venkatakrishnan J on January 8, 2008

Just a Word of Caution: The example that we are going to try today would affect all your edit box prompts. Just use caution and modify the below example according to your needs.

The example that we are going to try today is to add some simple validation criterion to the edit box in the Dashboard Prompt i.e. allow users to enter only numeric values. If they enter anything else then pop up an error message asking them to try again (without refreshing the whole page). This example deals with modifying the underlying Java Scripts. The steps are as follows

1.   Go to {OracleBI}\web\app\res\b_mozilla\prompts. There you would find a file called as globalfilterprompt.js. All the methods that make the dashboard prompts work are present in this file.

2.   Now open this file in a text editor and search for GFPBuildFilter(). This is the function that builds the filter for Dashboard Prompt. Replace this function with the one below.

function GFPBuildFilter()
{
var tExpr = XUICreateElement(saw.xml.kSawxNamespace, ‘expr’);

var tArgs = GFPBuildFilter.arguments;
var tF = new GFPQueryFilter(tArgs[0],tArgs[1]);

var tSQLExpr = XUICreateElement(saw.xml.kSawxNamespace, ‘expr’);
tSQLExpr.setAttribute(“xsi:type”, “sawx:sqlExpression”);
XUISetElementText(tSQLExpr, tArgs[0]);
tExpr.appendChild(tSQLExpr);

var bNoVal = true;
var sNewOp = tF.sOp;

// MBH6 shows some errors where GFP still has eq for some reason. Most surgical fix
// is to uncomment this check.
if (sNewOp == “eq”)
sNewOp = “in”;

if (sNewOp == “bet”)
sNewOp = “between”;

var sType = tArgs[2];

var tValues = new Array();
if (sType == “multi”)
tValues = GFPParseArgs(tArgs)
else
{
for (var i = 5; i < tArgs.length; ++i)
{
tValues[tValues.length] = GFPRightTrim(tArgs[i]);
}
}

// is a variable to be set?
var sSetVariable = tArgs[4];

//convert datetime; sTimeZoneOffset is ‘0’ if data type is not datetime or type is ‘drop’
var sTimeZoneOffset = tArgs[3];
var nOffset = parseInt(sTimeZoneOffset)
if (nOffset)
{
var tDTP = new DateTimeParser();
for (var i = 0; i < tValues.length; ++i)
{
tValues[i] = saw.tz.parseAndAdjustTimeZoneOffset(tValues[i], nOffset);
}
}

// if more arguments, then values
for (var i = 0; i < tValues.length; ++i)
{
var sVal = tValues[i];

if ((sVal == ksDropDownNone || !sVal) && sNewOp != “between”) //if sVal is empty, it is consider the value is not specified. We should not change the operator to NULL in this case.
return;
else if ((sVal == ksDropDownNone || !sVal) && sNewOp == “between”)
continue;
else if ((sVal == ksDropDownAllChoices || sVal == ksEditBoxAllChoices) && sNewOp == “between”)
{
//treat all choices in between op as blank but always apply the filter
bNoVal = false;
continue;
}

if (sVal == “(All Choices)”)
sVal = ksDropDownAllChoices;

// check for null or just whitespace
/*if (sVal == ksDropDownUnspecified)
{
sNewOp = “ignore”;
bNoVal = false;
}*/

if (sType == “edit” && sVal != “”)
{
var validation = /^[0-9]+$/;
if (!sVal.match(validation))
{
alert(“Only Numbers Allowed Here”);
return;
}
}

if (sType == “edit” && sVal == “”)
return;

// if all choices
else if ((sVal == ksDropDownAllChoices) || (sVal == ksEditBoxAllChoices))
{
sNewOp = “prompted”;
bNoVal = false;
break;
}

// if null
else if (sVal == ksDropDownNull || !sVal)
{
sNewOp = “null”;
bNoVal = false;
}

// if they specified a value
else if (sVal != null && (sVal.search(/^\s*$/) == -1))
{
bNoVal = false;
// check for operators we allow user to override
if (sType == “edit” && ((tF.sOp == “in”) || (tF.sOp ==”like”)))
{
// Check for operator
if (sVal.search(/^=/) != -1)
{
sNewOp = “in”
sVal = sVal.replace(/^=\s*/,””);
}
else if (sVal.search(/^/) != -1)
{
sNewOp = “notIn”;
sVal = sVal.replace(/^\s*/,””);
}
else if (sVal.search(/^<=/) != -1)
{
sNewOp = “lessOrEqual”;
sVal = sVal.replace(/^=/) != -1)
{
sNewOp = “greaterOrEqual”;
sVal = sVal.replace(/^>=\s*/,””);
}
else if (sVal.search(/^</) != -1)
{
sNewOp = “less”;
sVal = sVal.replace(/^/) != -1)
{
sNewOp = “greater”;
sVal = sVal.replace(/^>\s*/,””);
}
}
tF.AddPredArg(tExpr, sVal);

}
}

if (bNoVal)
{
// JPR 8/05 – this check is causing problems with between since it was forcing to prompted.
// We can’t determine why this sType != “drop” was ever necessary, so we’re returning in
// all cases when there is no value
// if (sType != “drop”)
return;

// JPR 8/05 – the comment below makes no sense to us, assuming old logic and returning above now
// Change to prompt as ignore prevents users from blanking out an entry.
//tF.sOp = “prompted”;
//sNewOp = “prompted”;
}

/* if (tF.sOp == “in” && tF.vValues.length == 1)
sNewOp = “equal”;
*/
if (tF.sOp == “cany”)
sNewOp = “containsAny”;
if (tF.sOp == “call”)
sNewOp = “containsAll”;
if (tF.sOp == “bwith”)
sNewOp = “beginsWith”;
if (tF.sOp == “ewith”)
sNewOp = “endsWith”;

// between must have two values
if (sNewOp == “between”)
{
var sVal = tValues[0];
var sVal2 = tValues[1];

// var bVal = (sVal && (sVal != ksDropDownAllChoices) && (sVal != ksEditBoxAllChoices));
// var bVal = (sVal2 && (sVal2 != ksDropDownAllChoices) && (sVal2 != ksEditBoxAllChoices));
if (sVal == ksDropDownAllChoices || sVal == ksEditBoxAllChoices || sVal == ksDropDownNone)
sVal = “”;

if (sVal2 == ksDropDownAllChoices || sVal2 == ksEditBoxAllChoices || sVal2 == ksDropDownNone)
sVal2 = “”;

if (!(sVal && sVal2))
{
if (!sVal && sVal2)
tF.sOp = “lessOrEqual”;
else if (sVal && !sVal2)
tF.sOp = “greaterOrEqual”;
else
tF.sOp = “prompted”;
}
}
else
tF.sOp = sNewOp;

GFPGetOpType(tExpr, tF.sOp);
//tExpr.setAttribute(“xsi:type”, sType);
tExpr.setAttribute(“op”, tF.sOp);

if (null != sSetVariable && sSetVariable != “”)
XUISetAttributeString(tExpr, “setVariable”, sSetVariable);

return tExpr;
}

The code that we have added extra to the actual function is the one below

if (sType == “edit” && sVal != “”)
{
var validation = /^[0-9]+$/;
if (!sVal.match(validation))
{
alert(“Only Numbers Allowed Here”);
return;
}
}

What this does is, it checks whether we are calling this from the Edit box. If yes, then we make the validation of “Only Numbers are Allowed here”. Then save this file.

3.   Copy this file to {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts i.e we need to overwrite the globalfilterprompt.js that is present in this directory so that both the java scripts are in sync.

4.   Restart the presentation services and OC4J. Now if you enter some characters in the dashboard prompt it will throw an error.

      

      

      

Remember this will affect all your dashboard prompts. You would have to add more conditions to narrow it down to a single edit box in a particular page. This would give you an idea of how to modify the underlying Java Scripts.

Posted in All Posts, OBI EE Plus | 6 Comments »