Hitesh's BI Blog

OBIEE11g - Stopping and Starting Services from Command Line

2010-11-25T02:40:00.000-08:00

In the previous post I showed how to stop/start OBIEE 11g services using Fusion Middleware Control. This post shows how to achieve same using command line.

OBIEE 11g installer creates opmnctl.bat file for each instance. To manage services start command prompt and go to the bin folder of the instance \{MWH}\instances\instance1\bin

Run following command to check the status of all the services running under the instance.

opmnctl status

You can use ias-component name in the output to stop/start individual service.

For example, following command stops presentation service.

opmnctl stopproc ias-component=coreapplication_obips1

And following command starts presentation service

opmnctl startproc ias-component=coreapplication_obips1

Following commands can be used to stop/start all services in the instance.

opmnctl stopall
 
opmnctl startall

OBIEE11g - Stopping and Starting Services using Fusion Middleware Control

2010-11-25T01:47:00.000-08:00

In OBIEE 10g all five services could be stopped and started from the windows services management console.

However, things have changed with OBIEE 11g because of the change in the architecture, specifically related to scalability. All the services are maintained through Oracle Process Manager and Notification (OPMN) server. Stopping/Starting this service will stop/start all BI services for that instance.

Most of the administration activities are performed using Fusion Middleware (FMW)
Control in OBIEE 11g. You can stop and start services from FMW Control too.

Logon to FMW Control using URL http://hostname:7001/em

Select coreapplication under Business Intelligence in the left tree in FMW Control. The "Overview" tab on the right will show number of services running (Generally 5, but may be different based on your scalability settings). You can Stop, Start or Restart all services using buttons provided.

To stop and start individual services, navigate to "Availability" tab under "Capacity Management" tab. You can stop/start all or selective services here.

OBIEE - Purge BI Server Cache from Analytic Web Manually

2010-11-12T21:12:00.000-08:00

All of us knows how to purge BI Server cache manually from Analytic Administration Tool i.e. Go to Manage > Cache. Select cache entries, right click and purge.

But what if you are working on reports remotely using Analytic Web and need to clear the BI Server cache? Here's the trick.

Go to Settings > Administration

In the Administration dialog select "Issue SQL"

In the SQL Statement textbox in the "Issue SQL Directly" dialog, enter the following statement and click "Issue SQL" button.

Call SAPurgeAllCache();

Allow some time for BI server to clear the cache. The page will refresh with results message at the bottom saying operation is successful.

You can also use other cache purge functions based on your need.

OBIEE11g - Table Prompts and Sections

2010-11-04T21:17:00.000-07:00

Table/Pivot Table Prompts and Sections are very nice and handy features in the OBIEE11g. This post shows different ways to create it.

Here's a simple pivot table with Company, Year, Brand and few measures.

Edit pivot table view and drag the columns to the Prompt and Section area as shown below.

Back to result view. Company is now a dropdown prompt and Brand is section header

Alternate way: Right click column in result view. Select "Move" menu. Select either "To prompts" or "To sections".

Another alternate way: Hover the column and drag the small tab that appears over it to the empty area above the table.

Pivot Table Prompt area will appear automatically. Drop the column there.

Similarly drag and drop columns to Sections area.

To bring back columns to table, hover the Prompt or Section header. Small tab will appear on left. Drag and drop it to the table back.

OBIEE - Aggregation Rules

2010-09-28T22:41:00.000-07:00

Talking of aggregation rules in OBIEE, people uses Sum, Average and Count most commonly. Some of them are not even aware in what scenario the other aggregation rules can be used. In this post I am going to show a real-life business scenario for use of some of the other aggregation rules.

Take a look at following accounting data. There's a fact holding Opening Balance, Debit Amount, Credit Amount and Closing balance for accounts on daily basis. We have a Time dimension with hierarchy Year-Month-Week-Day.

What aggregation rule will apply to "Opening Balance" measure? Ideally if I look at "Wk2", it should give me value $2,359 i.e. opening balance of first day of "Wk2". Similary, if I look at Jan 2010, it should give me value $1,000 i.e opening balance of the month. This cannot be achieved with commonly used Sum or Average aggregation rules.

Some people suggests, we can create Year, Month and Week level aggregate tables and design aggregate navigation. Technically this is right, but what if a new level (Quarter) gets introduce at later point. You will require changes at many places.

Now let see how we can solve this scenario using aggregation rules. On the aggregation tab select "Based on dimnsions" checkbox.

A list will show up with "Other" dimension with "Sum" aggregation for it. Leave this as is. Cilck on "New" button at the bottom of the dailog.

In the dimension list select Time dimension.

In the formula dropdown select formula with aggregation rule of "FIRST".

Defining "FIRST" aggregation formula on Time dimension ensures that, for the non-granular members (such as Year, Month, Week), value of its first granular member is returned.

Why "Sum" aggregation needed for "Other" dimension? The example data above just shows information of one account, but ideally we will have more such accounts and we want to sum up value of opening balance of all account to know the total opening balance at a particular time.

Finally, we can handle closing balance too in similar way by choosing "LAST" aggregation formula.

OBIEE - Schedule Purge and Re-build of cache programmatically

2010-08-25T23:18:00.000-07:00

In a standard OBIEE implementation, we generally want to purge and re-build the cache after every ETL run. This post deals with how to programmatically purge and re-build cache and also schedule it.

Purging Cache
OBIEE provides ODBC-extension functions that can be used to purge cache programmatically. These functions can be called using NQCMD commandline tool. There are four different functions that can be used to purge cache.

SAPurgeAllCache - Purges all cache
SAPurgeCacheByDatabase - Purges all cache for a specific physical database
SAPurgeCacheByTable - Purges cache for a specific physical table
SAPurgeCacheByQuery - Purges cache for a specific query

Here's how to use the command in windows environment.

Create a text file using notepad with name "PurgeCache.txt" and add following line to it. Save the file to local disk (Let's assume to drive C:\)

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

Since we used SAPurgeAllCache function, it will clear all cache on the data source . Refer this link for syntax of other functions.

Re-building Cache
Once the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

Scheduling Purge and Re-build
You may not want to run purge/rebuild cache manually after each ETL run, so you can create a batch file using notepad, "Caching.bat" and add the two command that we ran manually to it. Save the file.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt
NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

We can schedule this file to run automatically using windows scheduling using following steps.

Go to Control Panel and double click "Schduled Tasks"

In the Schduled Tasks window, double click "Add Scheduled Task".

The Scheduled Task Wizard screen appears.
Click Next to continue.

Click Browse button on next screen. Navigate to the C:\Caching.bat and click Open button.
Next, you give the task a name and choose when to run the task.

Name the scheduled task.
Pick a name that will make sense to you later when you want to check the list or make any changes.

Choose when to run the scheduled task.
There are a number of choices as to when you would like to run the task:

Daily – once a day
Weekly – once a week
Monthly – once a month
One time only
When computer starts
When I log in

Click "Next" to continue.
Specify the day and time to run the scheduled task.

Click "Next" to continue once you have made your time selections.
Enter the name and password of the user who is authorized to run the task. Generally Administrator.

Click Next to continue.

Click Finish to complete scheduling the task.

Squarified Treemap for OBIEE Dashboard

2010-06-13T00:02:00.000-07:00

In one of my earlier post (here), I showed how to show OBIEE data on calendar. In this post I am going to show yet another way to use power of OBBIE Narrative Views to visualize OBIEE data - Squarified Treemap.

Squarified treemap is one of the most effective way to visualize tree structured data, be it a finance or any other domain. I will be using javascript toolkit from http://thejit.org/ to create the squarified treemap. I will suggest you download the toolkit and go over the example code that comes along.

Here's tree structured report which shows the sales data of a store by Product Class, Product Subclass, Product Item. It has a "Rank" column which ranks product based on its sales value and % Share show its share in the total sales value. We will be using Rank to determine color and % Share to determine size of the rectangle in the squarified treemap.

Create a folder name "jit" on OBIEE server under Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\ folder and copy jit.js and Treemap.css from the toolkit to this folder.

Next we create narrative view and write javascript to use treemap control and pass the report data do it. Note: When I was designing it, preview under narrative was showing a black rectangle. However, I could save the report and view treemap in the result / dashboard view.

Here's the script from the narrative view.
Prefix:



<!-- JIT Library File -->

<script language="javascript" type="text/javascript" src="./res/jit/jit.js"></script>

<!-- JIT Stylesheet File -->

<link type="text/css" href="./res/jit/Treemap.css" rel="stylesheet" />

<script language="javascript" type="text/javascript">

      function init() {
            function itemdata() { this.rank = ""; this.share = ""; }
            var rdata = [];

In Prefix section we include jit.js and Treemap.css files. Note the path for the files is ./res/jit. The init function will be called on load event of document body. It will build treemap using report data. The multidimensional object rdata will be used to capture report data. We will use instance of itemdata to store Rank and % Shares .

Narrative:

            if(typeof(rdata["@1"]) == "undefined")

            rdata["@1"]=[];

            if(typeof(rdata["@1"]["@2"]) == "undefined")
            rdata["@1"]["@2"]=[];

            if(typeof(rdata["@1"]["@2"]["@3"]) == "undefined")
            rdata["@1"]["@2"]["@3"]=new itemdata();

            rdata["@1"]["@2"]["@3"].rank=@4;
            rdata["@1"]["@2"]["@3"].share=@5;

Here we store report data to rdata. Being tree structure, there could be multiple rows for the same Product Class or Sub Class, so we first do a check if the element with the name is defined or not and then add it. At Product item level we use instance of itemdata and store Rank and % Share of product.

Postfix:



            function nonleafclass() {
                this.children = new Array();
                this.data = new function() { this.$area = "" };
                this.id = "";
                this.name = "";
            }

            function leafclass() {
                this.children = new Array();
                this.data = new function() { this.$area = ""; this.$color = ""; };
                this.id = "";
                this.name = "";
            }

            json = new nonleafclass();
            json.name = "Product Performance";
            json.id = "Product Performance";
            json.data.$area = 100;
            var i = 0;


            for (var cls in rdata) {
                json.children[i] = new nonleafclass();
                json.children[i].name = cls;
                json.children[i].id = cls;
                var j = 0;
                var iarea = 0;

                for (var subcls in rdata[cls]) {
                    json.children[i].children[j] = new nonleafclass();
                    json.children[i].children[j].name = subcls;
                    json.children[i].children[j].id = subcls;
                    var k = 0;
                    var jarea = 0;

                    for (var item in rdata[cls][subcls]) {
                        json.children[i].children[j].children[k] = new leafclass();
                        json.children[i].children[j].children[k].name = item;
                        json.children[i].children[j].children[k].id = item;
                        json.children[i].children[j].children[k].data.$area = rdata[cls][subcls][item].share;
                        json.children[i].children[j].children[k].data.$color = rdata[cls][subcls][item].rank;
                        jarea = jarea + rdata[cls][subcls][item].share;
                        k++
                    }

                    json.children[i].children[j].data.$area = jarea;
                    iarea = iarea + jarea;
                    j++

                }
                json.children[i].data.$area = iarea;
                i++;
            }


            var infovis = document.getElementById('infovis');
            var w = infovis.offsetWidth, h = infovis.offsetHeight;
            infovis.style.width = w + 'px';
            infovis.style.height = h + 'px';

The jit toolkit requires the data in a specific object format. In their example code they have used json syntax to define the data. In postfix section we create same object in bit different way. Note use of Rank and % Share for $color and $area properties. For nonleaf level we calculate area by summing up area of children (i.e. item).

Postfix Continue..

            //init tm

                var tm = new TM.Squarified({
                //Where to inject the treemap.
                rootId: 'infovis',

                //Add click handlers for
                //zooming the Treemap in and out
                addLeftClickHandler: true,
                addRightClickHandler: true,

                //When hovering a node highlight the nodes
                //between the root node and the hovered node. This
                //is done by adding the 'in-path' CSS class to each node.
                selectPathOnHover: true,

                Color: {
                    //Allow coloring
                    allow: true,
                    //Set min value and max value constraints
                    //for the *$color* property value.
                    //Default's to -100 and 100.
                    minValue: 1,
                    maxValue: 29,
                    //Set color range. Default's to reddish and greenish.
                    //It takes an array of three
                    //integers as R, G and B values.
                    minColorValue: [0, 255, 50],
                    maxColorValue: [255, 0, 50]
                },

                //Allow tips
                Tips: {
                    allow: true,
                    //add positioning offsets
                    offsetX: 20,
                    offsetY: 20,
                    //implement the onShow method to
                    //add content to the tooltip when a node
                    //is hovered
                    onShow: function(tip, node, isLeaf, domElement) {
                        tip.innerHTML = "<div class=\"tip-title\">" + node.name + "</div>" +
                "<div class=\"tip-text\">" + this.makeHTMLFromData(node.data) + "</div>";
                    },

                    //Build the tooltip inner html by taking each node data property
                    makeHTMLFromData: function(data) {
                        var html = '';
                        html += "Share in Sales" + ': ' + data.$area + '%<br />';
                        if ("$color" in data)
                            html += "Rank" + ': ' + data.$color + '<br />';
                        return html;
                    }
                },

                //Remove all element events before destroying it.
                onDestroyElement: function(content, tree, isLeaf, leaf) {
                    if (leaf.clearAttributes) leaf.clearAttributes();
                }
            });
            //load JSON and plot
            tm.loadJSON(json);
            //end 
        }    //init

        document.body.onload=function() { init() };
</script>
      
<div id="center-container" style="width:700px;background-color:#1a1a1a;color:#ccc;height:500px;">
     <div id="infovis" style="width:700px;height:500px;margin:auto;overflow:hidden;position:relative;"></div>
</div>
<div id="log">
</div>

The above code is picked from the example. It initializes the squrified treemap from the toolkit and setup necessary property. The name of DIV tag where treemap will be rendered is infovis. I changed maxvalue property of color to 29, since I have 29 rows on my report. You can even calculate this value dynamically if required. Also, I modified makeHTMLFromData to use information from the report. This will be shown as tooltip on the treemap. The line tm.loadJSON(json) passes the data we prepared to treemap control. As mentioned earlier we bind call to init function on load event of the page. Finally, we create required DIV tags.

Don't miss-out to select "Contains HTML Markup" checkbox. Here is how report looks once saved and placed on the dashboard. When you hover treemap, it show tooltip for specific item. Note the color change based on rank from bright green (1) to bright red (29). Also, note the size of rectangle is according to % Share in sales value.
You can drilldown to any level in the treemap to analyze specific area using left mouse click. For example, I drilled-down here to leaf level member that has rank of 26.

Right mouse click will drill one level up. When I, right clicked above I get following.

And then one more level up.

You can also jump to any specific level in the tree. For example, below I can click on Product Class - Cereal and it will present the data under it.

I hope this post will be helpful for those who are looking to implement squarified treemap visualization over OBIEE dashboard.

The jit toolkit has many other visualization controls. I will try to cover them in my future post.

Accessing OBIEE Analytical Data from Windows C# Forms Application

2010-05-20T05:55:00.000-07:00

OBIEE analytic repositories are accessible to the third party applications through the ODBC connectivity. In this post I am going to show a sample C#.NET windows application that connects to OBIEE server using ODBC to fetch analytical data and display it on a grid.

First of all, we will need to install the ODBC driver for Oracle BI Server on the machine from where we are trying to access it. Refer the guide Installing Oracle BI Open Intelligence Interface to do this. Next we need to create ODBC DSN. I explained the steps to do this in one of my earlier post Switch OBIEE Repository and Catalog. Now you are ready to code your windows application.

Launch the Microsoft Visual Studio and create new C# Windows Forms Application.

From the tools pane drag-n-drop DataGridView control over the Form and align it as needed. Visual Studio will name it dataGridView1 by default.

In the code-behind of the form, add using directive for System.Data.Odbc namespace.

Now lets add code to execute query over BI Server and get the output. In the form's load event include following code.

In the connection string, DSN name should match the name of ODBC DSN you created. Provide appropriate user id and password.

string constring = "DSN=AnalyticServer;UID=Administrator;Pwd=Administrator";

Create ODBC connection using connection string.

OdbcConnection con = new OdbcConnection(constring);

The query should contain select statment that you intend to execute against BI Server.

string query = "SELECT statement";

Create OdbcDataAdapter using query and connection. Create a DataSet to hold results. Execute query using Fill method.

OdbcDataAdapter da = new OdbcDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds);

The dataset returned will contain a Table with results. Bind this table to the dataGridView1.
dataGridView1.DataSource = ds.Tables[0];

Now build and run the project and you should see output of the query in the grid.

Oracle BI ODBC Client executes queries in the similar way. However, it is not a .NET application.

Realtime access to OBIEE analysis over iPhone

2010-05-17T04:01:00.000-07:00

Oracle Business Indicator is a business application designed to provide mobile business users realtime access to pre-defined OBIEE analytics from the iPhone device.

Business user can browse folders on OBIEE server and view any pre-created reports stored under it.

The client for iPhone device is available for download from http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewSoftware?id=284793704&mt=8

There's only one configuration change needed on server side. Since the iPhone device does not support Flash components, we need to configure the BI Server to render charts in the PNG format. To make this change just add following configuration under section of the instanceconfig.xml file:

<charts>
<defaultimagetype>PNG</defaultimagetype>
</charts>

iPhone Client will require setting server URL, user and password etc. For more details about configuring Oralce Business Indicator client, refer configuration guide.

Creating custom styles for OBIEE Dashboard

2010-05-10T07:17:00.000-07:00

The default look and feel of OBIEE dashboard can be modified by creating custom style. To create custom style, one should know Casecading Style Sheets (CSS) and have basic knowledge of graphic design.

Here are steps to apply custom style to OBIEE dashboard.

1. Stop OC4J

2. Go to folder Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res and make copy of folder s_oracle10 with some different name. For instance, s_mycompany.

3. Modify CSS and images in the subfolder b_mozilla_4 under s_mycompany, as per the requirement.

4. Copy s_mycompany folder to Drive:\OracleBI\web\app\res

5. Start OC4j and Log on to analytics web application.

6. Go to dashboard properties and select newly created style "mycompany" in the style dropdown.

The screen below shows default OBIEE banner area.

Here I made changes to banner images and styles to change the looks.

You can write to me if you want files for above style.

OBIEE Aggregate Navigation (not hitting?)

2010-04-23T02:24:00.000-07:00

Often you will find people complaining that they have setup aggregate navigation, but when they run report, OBIEE is not hitting the right fact table.

Common suggestions you will find to resolve this are:

Go to Content tab of the Logical Table Source and check if Logical Level are set correctly set.
Go to General tab of Dimension Level and check the value in textbox "Number of elements at this level"

In this post, I am just going to show that even after following above, the query may not hit aggregate table if you are not following modeling practice right.

Let's take following data as example. There are two sales fact tables and a time dimension. DAILY_SALES has day level sales data. WEEKLY_SALES has sales data pre-aggregated at week level.

Here's repository created on top of above physical data. The Logical Level is set correctly for WEEKLY_SALES.

Even the number of elements are set correctly for Week and Day level of Time dimension.

Let's execute following query which fetches sales by week. Since we have setup aggregate navigation, it is expected that the query should fetch data from WEEKLY_SALES table.

SELECT "Time"."WEEK_DESC", "Sales"."SALES_AMT" FROM SALESCATALOG

Now have a look at NQQuery.log. The query didn't hit WEEKLY_SALES table, in fact it is grouping data form DAILY_SALES table instead.

So what went wrong?
Our query has two columns WEEK_DESC and SALES_AMT. If you see the earlier repository screen carefully, you will see that WEEK_DESC attribute is not associated with any level of the Time dimension, so OBIEE assumes it to be most granular level attribute i.e. day level. When we query data by WEEK_DESC, OBIEE goes to fact table with day level granularity (i.e. DAILY_SALES) and fetches the data.

Let's add WEEK_DESC to Week level of the time dimension as shown below and execute the query again.

Now let's see NQQuery.log again. Bingo! it just hit the table we wanted it to.

Alternative approach with Snow-flake:
In the example I have given, the Time dimension is snow-flake, so it is also possible to set the logical level for invidual source table to make aggregate navigation work. Setting logical level "Time Week" for source table TIME_WEEK and "Time Detail" for source table TIME_DAY will do the work.

I hope this post will give a different perspective to your troubleshooting effort when you face that aggregate navigation problem again.

Switch OBIEE Repository and Catalog

2010-04-18T23:27:00.000-07:00

At times, for development you may need to setup another OBIEE Repository and Catalog. In this post I will show how to do this.

First, stop Oracle BI and Presentation Services from Control Panel > Administrative Tools > Services. Also stop OC4J.

Configure Repository
Copy new repository to Drive:\OrabeBI\Server\Repository folder.

Open NQSConfig.INI file from Drive:\OrabeBI\Server\Repository folder. Under [ Repository ] section, add new repository alias. See the screen below. I added Star2 = MyProject.rpd.

Create ODBC DSN for Repository
Goto Control Panel > Administrative Tools and open Data Source (ODBC). Under System DSN tab click Add. Follow the steps in the wizard as shown below:

Step1
Select "Oracle BI Server" from the drivers list.

Step2
Provide a name for the ODBC DSN. I used "AnalyticServer"
Specify BI Server name.

Step3
Specify alias of the repository you want to connect to. We added alias in NQSConfig.INI.
Not needed if you want to connect to default repository.

Step4
Click Finish.

Configure Presentation Service
Copy new catalog under Drive:\OracleBIData\web\catalog\ folder. In case you want to create new catalog just add an empty folder under it.

Open instanceconfig.xml from Drive:\OracleBIData\web\config folder. Change the DSN name to the one we just created (AnalyticServer). Change the CatalogPath to point to path for the new catalog.

Finally, start OC4J, Oracle BI and Presentation Services.

Falling Back
To fall back to original repository and catalog

Stop OC4J, Oracle BI and Presentation Services.
Revert the DSN and CatalogPath in the instanceconfig.xml
Start OC4J, Oracle BI and Presentation Services.

OBIEE Showing Data on a Calendar

2010-04-13T04:33:00.000-07:00

Sometimes there's need for the business data related to promotional events, holidays etc. to be presented on a calendar to improve readability. In this post I will explain how to achieve this in OBIEE using mooTools and javscript calendar (http://dansnetwork.com/mootools/events-calendar).

Here's Sales report of an ogranization by day. Wouldn't it be great if we can plot this on a calendar and higlight the day when it's holiday.

Prerequisites:
To render the calendar we will need mooTools Core, mooTools More and mooTools events calendar. Here are URLs for download: (I used No Compression for all downloads)

MooTools Core: http://mootools.net/download
MooTools More: http://mootools.net/more
Select Date, Scroller, Tips while downloading mooTools More.
Events Calendar: http://dansnetwork.com/mootools/events-calendar/download/

Here's the list of all files you should have after download:

mooECal.css
mooECal.js
mooECalLarge.css
mooECalSmall.css
mootools-1.2.4-core-nc.js
mootools-1.2.4.4-more.js

Create a folder name mooTools on OBIEE server under Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\ folder and copy all the files to it.

Narrative View to render Calendar
I added Narrative View to the report I showed earlier.

Added following information to each textbox and selected "Contains HTML Markup" checkbox.

Prefix:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECal.css">
<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>
<div id="calBody"></div>
<script language="javascript">

function getDiv(holFlag)
{
if(holFlag=='Y')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div>';
}
}

new Calendar({calContainer:'calBody', newDate:'1/21/2010',
cEvents:new Array(

Narrative:
{
title: getDiv('@3') + '@4</div>',
start: '@2',
end: '@2',
location: ''
}

Row Separator:
,

Postfix:
)
}); </script>

Please note:

The path to .js and .css files is ./res/mooTools.
getDiv function returns div tag with red background when it's holiday. You can do other event specific handling if required.
Calendar instance is passed an array of events. In our case this is sales information.
Sales value is passed in the title. Start and End date are sale date.
The comma (,) in Row separator will separate event array element.
You can change mooECal.css in prefix section to mooECalSmall.css or mooECalLarge.css if you want smaller or bigger calendar.

Here's data rendered on calendar. Note, background color for holidays.

Please read licensing terms of mooTools and event calendar before making commercial use.

Common OBIEE Errors and Resolutions

2010-04-08T03:30:00.000-07:00

In this post I am going to cover some common errors faced during OBIEE implementation and its resolution, in the windows environment. I am hoping this will be handy to anyone who is new to OBIEE.

Unable to Log In
Error connecting to the Oracle BI Server: Could not connect to the Oracle BI Server because it is not running or is inaccessible. Please contact your system administrator.
Error Codes: WH4KCFW6:OPR4ONWY:U9IM8TAC

Common cause(s):

"Oracle BI Server" windows service is not running. Starting the service could resolve the issue.
In case the BI Server service is running and still the error occurrs, check the port the service is listening to is matching with the port specified in the ODBC DSN for the analytic repository. In NQSConfig.ini file look for RPC_SERVICE_OR_PORT. The default value is 9703. The ODBC DSN name can be found in instanceconfig.xml

"Oracle BI Server" windows service is not starting.
Common cause(s):

The RPD file specfied in the NQSConfig.ini doesn't exist in the repository folder.
RPD file may be inconsistent. Open it using Admin tool in offline mode and fix the consistency errors.

Error Generating Chart
Error Codes: ETI2U8FA

Common cause(s):

"Oracle BI Java Host" windows service is not running. Starting the service could resolve the issue.

Access denied for user to path /users/username/_portal.
Error Codes: O9XNZMXB

Common cause(s):

The specified user doesn't have permission to read/write to the his/her catalog folder. To resolve the issue, login as Administrator and grant permission to the user to his/her catalog folder using catalog manager.

OBI EE Data Visualization Trick

2010-03-27T03:51:00.000-07:00

Visualization plays very vital role in the data analysis by communicating information clearly and effectively through graphical means.

The below chart from Google finance website very effectively tells which sectors are performing well. It shows % of companies trading below -2% of its previous close using dark red bar, % of companies trading within -2% of its previous close using pink bar, % of companies trading within 2% of its previous close using light green bar, % of companies trading above 2% of its previous close using dark green bar.

OBIEE - BI Answers has limited set of data visualization capabilities. We may see some radical improvements in this area in the coming releases of OBI EE. However, there's a feature in BI Answers which is seldom used - "Narrative View". The power of Narrative View comes from it's capability to render output as HTML (and javascript). This opens up ample of opportunities to create custom visualization of data.

Above we created a report using BI Answers which tells the % of companies trading below -2% of its previous close and so on so forth. There's no built in capability in BI Answers to visualize the data like the Goolge Finance chart. However, using Narrative View we can create the same very easily with little HTML code. Note: Narraive view denotes columns using numbers. E.g. @1 for first column.

In the BI Answers result tab, navigate to Other View > Narrative as shown below.

Narrative View UI has four main textboxes. The text/HTML inside prefix textbox is rendered first (only once). the text/HTML inside Narrative textbox is rendered for each row. The text/HTML inside Postfix textbox is rendered in the end (only once).

In the Prefix textbox add following HTML. This will create title for our chart. We also open a table tag for our chart HTML

<table cellspacing=0 cellpadding=20 style="border:1px solid #999999">
<tr>
<td style="font-family:Tahoma,Arial; font-size:14px;" valign="middle" align="left">Sectoral Performance</td>
</tr>

<tr><td>
<table cellspacing=0 cellpadding=0>

In the Narrative textbox add following HTML. This HTML will be rendered for each row in the report. Note: Use of % values in report for the width property and background color of the DIV tag.

In the Postfix textbox we will close the table tag for chart. It also has additional markup to show chart legends.

</table>

</td></tr>
<tr><td align="left">

<table border=0 cellspacing="0" cellpadding="5">
<tr>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">
<div style="background-color:#ED1A3B;width:8px;height:8px;font-size:1px;"></div></td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">Less than -2%</td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">
<div style="background-color:#C5DE89;width:8px;height:8px;font-size:1px;"></div></td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">Between 0% and 2%</td>

</tr>

<tr>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">
<div style="background-color:#F69788;width:8px;height:8px;font-size:1px;"></div></td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">Between 0% and -2%</td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">
<div style="background-color:#A6CE39;width:8px;height:8px;font-size:1px;"></div></td>
<td style="font-family:Tahoma,Arial; font-size:12px;" valign="middle">More than 2%</td>
</tr>
</table>

</td></tr>

</table>

Finally, don't forget to select the checkbox "Contains HTML Markup". Save the report and place it on the dashboard. This is how it should look. Tabular view can be removed if not needed.

OLAP desiging using ADAPT

2010-03-24T23:04:00.000-07:00

I recently came across this wonderful designing technology for OLAP databases named ADAPT by Symmetry Corporation (www.symcorp.com)

I am sure most of us limit the OLAP modeling aspect to star/snowflake schema using the traditional design methodologies such as entity-relationship (ER) diagramming.

Using ADAPT designing, you can create visual representation of almost all objects in the OLAP database (even relationships). See the sample Visio diagram below:

It helps in creating blue-print of OLAP model and validate it against the business requirements before actually jumping on to implementation, hence saves time on implementation iterations.

Definitely worth giving a try if you are not using it already.

DISCLAIMER:

A NONEXCLUSIVE COPYRIGHT LICENSE IS GRANTED TO USE ALL PROGRAMMING CODE EXAMPLES IN THIS SAMPLE FROM WHICH YOU CAN GENERATE SIMILAR FUNCTION TAILORED TO YOUR OWN SPECIFIC NEEDS. THE SAMPLE CODE PROVIDED IS FOR ILLUSTRATIVE PURPOSES ONLY. THESE EXAMPLES HAVE NOT BEEN THOROUGHLY TESTED UNDER ALL CONDITIONS. I, THEREFORE, CANNOT GUARANTEE OR IMPLY RELIABILITY, SERVICEABILITY, OR FUNCTION OF THESE PROGRAMS.ALL PROGRAMS AND INFORMATION CONTAINED HEREIN ARE PROVIDED TO YOU "AS IS" WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES OF ANY KIND. THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. I SHALL NOT BE LIABLE FOR ANY COST OR DAMAGE ARISING EITHER DIRECTLY OR INDIRECTLY FROM THE INFORMATION IN THE ARTICLE OR USE OF THE SAMPLE CODE. IT IS SOLELY YOUR RESPONSIBILITY TO EVALUATE THE ACCURACY, COMPLETENESS AND USEFULNESS OF ALL OPINIONS, ADVICE, SAMPLE CODE AND OTHER INFORMATION PROVIDED HERE. I DO NOT WARRANT THAT ANY INFORMATION OR SAMPLE CODE IS ERROR-FREE. ANY TRADEMARK OR LOGO USED IN THE ARTICLE ARE PROPERTY OF THEIR RESPECTIVE OWNER.