Installing OBIEE on XP Home Edition

OBIEE installation on XP Home Edition or even on some dell laptops has been a mystery.However after browsing through seven skies and taking a religious trip to the mountains bare foot ,God finally answered my request and I was able to install OBIEE on my DELL inspiron 600m which runs on a windows XP Home Edition.Follow the steps below and one will be able to install OBIEE on their machine running XP Home Edition
1)First find a machine with Windows XP Professional most of the corporate machines and laptops have it,Also have a USB handy.
2)Now browse to the windows folder of that machine and look for the System32 folder.
3)Plug the USB in the system and grab the SYSTEMINFO.EXE from the XX:\WINDOWS\System32 folder and copy this file into the USB.
4)Unplug the USB and plug into your machine that is running XP Home Edition.
5)Browse to the Windows folder find the System32 folder and copy the SYSTEMINFO.EXE from the USB in the System32 folder.
Once the file has been copied into the folder restart the computer and try to run the OBIEE installation and it should work like a charm.

Happy installation!
If you have any further questions please do email me at mfarhanalam@gmail.com

RTC-5150 OWB Error resolution

Problem Scenario
While working at client on an OBISE1 project ,the size of the DB started to grow at an enormous rate.After doing some initial digging around we found that the actual data was only 2GB but somehow the DB was taking 33GB.Contacted the DBA at client ,we went to look into Extent map through OEM and found that there were some blocks towards the end of the tablespace that were sitting between an area of blocks being used and some not used and hence Oracle thought of all that area as being used.Long story short the tablespace was not being reorganized so we moved those tables from the user tablespace to example tablespace and back into user tablespace.The tables that were moved were OWB managed tables (again application managed tables are a little different in there key structures)anyways the three tables that were moved where WB_RT_XXXX tables basically warehouse builder run time tables .So after the DB was resized and the tables were moved back I wanted to log back into OWB and wanted to use the OWB just to make sure things A-OK ,much to my surprise I was able to log into the design center ,but when I tried to access the control center I got a RTC-5150 error ,please refer to the figure above for how the beast looks like , it is an undocumented error.

Solution
Next morning me and DBA started to look into all the possible routes to figure out the cause and we started by looking into all the invalid objects in the databases starting with indexes ,packages and other object ,but the real resolution came back after we had re built the indexes.What happens is each index is ref by the leaf and rowid within a table. So when we move a table ,it becomes our responsibility to have to rebuild all indexes.Luckily indexes can be build online .
Use the following command
Query to see which indexes need to be re-built
"Alter index <> rebuild online";

PIVOT AND UNPIVOT IN 11g

Pivot

The Pivot clause is a new feature that has been introduced with the introduction of 11g.The simple logic behind the Pivot clause is that it enables the user to rotate rows into columns in the output of a query and at the same time enable the user to run aggregate functions on the data.

Unpivot

Unpivot clause is also a new builtin Analytical function that is introduced with the release of 11g,unpivot clause lets the user rotate columns into rows in the output from a query.


Examples to illustrate the use of Pivot clause

If you can log in to the Scott schema of the database then skip step 2 and 3 to execute the query directly

However , If you want to create the table in your database and then run the query please follow steps 1 through 3

1.Create table using the Script below

CREATE TABLE emp
( empno    NUMBER(4,0),
  ename    VARCHAR2(10 BYTE),
  job      VARCHAR2(9 BYTE),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0)
); 

2.Insert Script

The script below can be used to insert data into the table created in step 1.

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7369','SMITH','CLERK','7902',TO_DATE('17-Dec-80','DD-MON-RR'),'800','0','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7499','ALLEN','SALESMAN','7698',TO_DATE('20-Feb-81','DD-MON-RR'),'1600','300','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7525','JACK','SALESMAN','7690',TO_DATE('26-Sep-81','DD-MON-RR'),'1550','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7527','JONES','CLERK','7622',TO_DATE('22-Feb-81','DD-MON-RR'),'1000','300','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','KANYE','PRESIDENT','',TO_DATE('28-Feb-81','DD-MON-RR'),'5000','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7500','SAM','SALESMAN','7689',TO_DATE('23-Feb-81','DD-MON-RR'),'1257','500','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7400','JOE','MANAGER','7607',TO_DATE('22-Feb-81','DD-MON-RR'),'1540','300','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','10');
COMMIT;
/

3.Pivot Query

SELECT *
FROM (SELECT deptno,sal
      FROM emp
      WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT FOR (deptno) IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS));

/*
General Form of query using PIVOT clause
SELECT * FROM (INNER QUERY)
PIVOT (
AGGREGATE_FUNCTION
FOR (COLUMN TO BE PIVOTED) IN (LIST OF VALUES));
*/

A single Pivot clause also support multiple Aggregate Functions.
PIVOT CLAUSE provides great support to businesses which would like to do a detailed analysis on their Sales methodology and make future decisions on their sales and marketing strategies.

Using the UNPIVOT clause


The UNPIVOT clause rotates columns into rows.UNPIVOT clause is useful when a user has to execute a query that returns many columns , using the UNPIVOT clause the user can view those columns as rows.

Lets Illustrate the concept discussed above

1.Create a table pivot_emp_data using the query in step 3 as

CREATE TABLE pivot_emp_data AS
SELECT *
FROM (SELECT deptno,sal
              FROM emp
              WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT
FOR (deptno)
IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS);

2.Once the table has been created we can run the query below

SELECT *
  FROM pivot_emp_data
  UNPIVOT
(sal FOR deptno
IN(ACCOUNTING_SUM_AMOUNT,RESEARCH_SUM_AMOUNT,SALES_SUM_AMOUNT,OPERATIONS_SUM_AMOUNT))

The Query rotates the pivoted Data .Also , do realize that If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

Business Intelligence - Future

Few simple questions about the future of Business Intelligence.
1.What can truly change the implementation of Business Intelligence?
2.How fast can we adopt automated Decision support system?
3.How soon can we have real time data available in our datawarehouses?
4.Which methods can be implemented to stream line data across various Business Processes within an Enterprise?
5.Best set of tools that can be used to implement a sound BI solution.

These are just some questions that raced my mind today as I thought about the future of BI solutions.