OBIEE11g - Stopping and Starting Services from Command Line

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

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

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

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

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

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

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

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

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

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?)

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.