Oracle Bi Solutions

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Thursday, 10 January 2013

OBIEE and Linear Regression with Oracle DB

Posted on 22:58 by Unknown

I needed to create some basic prediction with OBIEE and decided to use Linear Regression. Something like this:
 but with substantial data.
I wanted to check Oracle DB regression functions in OBIEE for some time, so now I have an excuse.

2 disclaimers:
1. To do prediction with Oracle DB, you should prefer Oracle Advanced Analytic (Data Mining). (Seehere for additional information).
2. If you are looking for general Linear Regression with OBIEE that is not DB dependent, you should read Kurt Wolff Statistical Analysis Using Linear Regression in OBIEE. 

If you are still with me...Lets start working.

In Oracle DB there is a set of Linear Regression Functions (here in 11g documentation, you'll find them in10g as well ). We will focus on 2 of them regr_slope and regr_intercept. They do exactly what their names implies. 
Basic version of both expect 2 numeric parameters and finds the line that can be described byY=a*X+b.
Unfortunately regr_slope(X,Y) is actually 1/a and regr_intercept(X,Y) is not b but rather the point on X where Y=0 (a*regr_intercept+b=0). So if you do some basic math you will find out that for Y=a*X+b using these 2 functions you get:
Y=(X-regr_intercept)/regr_slope. (regr_slope=1/a and regr_intercept = -b/a)
   
To use this sort of functions in a OBIEE Analysis we will use EVALUATE_AGGR function in OBIEE. Unlike regular EVALUATE function, that allows us to run DB functions in OBIEE. The EVALUATE_AGGR does it for aggregation functions. It usually forced the DB to run the SQL query without it, and then run the aggregation function on top of the result.

Lets start with a basic Analysis. Mine will run on top of SH schema in sample Oracle DB.
We will complicate things, step by step.

So if we start with 2 columns Year (a number) and Amount_sold, the relevant functions are:
REGR_SLOPE(Year, Amount_Sold)
REGR_INTERCEPT(Year, Amount_sold)

and the relevant formulas in OBIEE (I removed the folder names for clarity):
EVALUATE_AGGR('regr_slope(%1,%2)' as DOUBLE, "Year", "Amount_Sold")
EVALUATE_AGGR('regr_intercept(%1,%2)' as DOUBLE, "Year", "Amount_Sold")

Change the Data Format in Column Properties for regr_slope so you can see several places after the Decimal Point or you will see a 0.
The formula that combines it all is (remember Y=(X-regr_intercept)/regr_slope):
("Year"-EVALUATE_AGGR('regr_intercept(%1,%2)' as DOUBLE, "Year", "Amount_Sold"))
/EVALUATE_AGGR('regr_slope(%1,%2)' as DOUBLE, "Year", "Amount_Sold") .

What do I do when I want "prediction" as well. The prediction is actually extending the regression line to future dates. So all I have to do is change the Business Model Diagram and make the join Outer Join (instead of Inner).
And the result is:



Now lets complicate things:
A. Add Calendar month
B. Add Chanel Class and require separate regression for each.

What is the problem with adding Calendar Month? We can't just add it to the Analysis, we have to add the Month to the regression functions. Since they are only 2 parameters we will add it to the Year. We need uniform spread of the month during the year. I prefer to add the following calculation for Year+Month calculation:
Year+(Month-1)/12.
There is one more problem. Month is an integer, we want to divide it by 12. To make it really work we need to turn it to dual, so the actual date value will be:
Year+cast(month-1 as double)/12

The regr_slope for example is now:
EVALUATE_AGGR('regr_slope(%1,%2)' as DOUBLE, "YEAR"+cast("MONTH_No"-1 as double)/12, "Amount_Sold")

And the complete function is the next 6 lines:
("YEAR"+cast("MONTH_No"-1 as double)/12-EVALUATE_AGGR('regr_intercept(%1,%2)' as DOUBLE, "YEAR"+cast("MONTH_No"-1 as double)/12, "Amount_Sold"))
/
EVALUATE_AGGR('regr_slope(%1,%2)' as DOUBLE, "YEAR"+cast("MONTH_No"-1 as double)/12, "Amount_Sold")


It's not that terrible. For clarity, lets replace "YEAR"+cast("MONTH_No"-1 as double)/12 with XX:
(XX-EVALUATE_AGGR('regr_intercept(%1,%2)' as DOUBLE, XX, "Amount_Sold"))
/
EVALUATE_AGGR('regr_slope(%1,%2)' as DOUBLE, XX, "Amount_Sold")

 So we have now:


Or in the Outer Join Version:
 


And now the last part, adding Channel Class. This is actually the most problematic part. Why? Because of the way EVALUATE_AGGR works.
When we add Channel Class to the Analysis, we actually want a separate line for each Class. So the relevant solution is running the regression functions with "over (partition by CLASS)" extension. Unfortunately when doing it with our EVALUATE_AGGR we get a Cartesian Product of the result sets. In plain English - OBIEE creates an SQL that doesn't know how to relate the regression result to the relevant Class and for each class returns all the combinations. I tried everything I could (including this

http://gerardnico.com/wiki/dat/obiee/vertical_fragmentation_sql), but in vain. If you can do better, please let me know.

Luckily for me, in this case the regular Evaluate works as well.
So I created the following calculation:
("Year"-EVALUATE('regr_intercept(%1,%2) over (partition by %3)' as DOUBLE, "Year", "Amount","CHANNEL_CLASS"))
/EVALUATE('regr_slope(%1,%2) over (partition by %3)' as DOUBLE, "Year", "Amount","CHANNEL_CLASS")

And it worked:



Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in OBIEE | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Upper Function
    In Oracle/PLSQL, the  upper function  converts all letters in the specified string to uppercase. If there are characters in the string that ...
  • [OBIEE11g] - OBIEE Dashboard for Informatica Metadata Reporting
    The metadata that Informatica Power Center 8 retains in its repository can be exposed via OBIEE reports and dashboards. This metadata includ...
  • OBIEE 11g Hide/Show Sections based on Dashboard Prompt
    allow a user’s interaction to hide/show certain sections of a dashboard. In this particular case the user wanted to choose either ‘Quarterly...
  • OBIEE 11g not showing new dashboard in the drop down menu
    When creating New dashboard in  OBIEE 11g, I have faced with issue that dashboard name did not show up in drop down dashboard menu. 1. When ...
  • [ODI] - Frequently Asked Questions (FAQ)
    Here is a list of FAQs about Oracle Data Integrator 1) What is Oracle Data Integrator (ODI)? 2) What is E-LT? 3) What components make up Ora...
  • Data Modeling: Schema Generation Issue with ERwin Data Modeler 7.3
    We are using Computer Associate’s ERwin Data Modeler 7.3 for data modeling. In one of our engagements, we are pushing data model changes to ...
  • Installation Informatica Powercenter 9.1 on Oracle Enterprise Linux 5.6
    Ingredients: Program Version Filename Oracle Express 11G 11.2.0 oracle-xe-11.2.0-0.5.x86_64.rpm SQL Developer 3.0 sqldeveloper-3.0.04.34-1.n...
  • [OBIEE11g] - Creating Dashboard Traversing Through Graph
    The general requirement asked for by customers is that they want to Click on the Main Dashboard Page’s Graph and be transferred to the other...
  • [OBIEE11g] - Dashboard Prompt - "Prompt User"
    Oracle BI 11g which we hadn't seen before, the " Prompt User " operator on a dashboard prompt. I'm not sure exactly when t...
  • OBIEE 11g - Query Limit
    Query limit and number of minutes a query can run per physical layer database connection, follow the below steps. > Login to Repository u...

Categories

  • BI Publisher
  • DAC
  • DataWarehouse
  • Hyperion
  • Informatica
  • OBIEE
  • ODI
  • Oracle Applications EBS 12.1.3
  • Oracle Database
  • PL/SQL
  • SQL
  • Unix/Linux

Blog Archive

  • ▼  2013 (500)
    • ►  November (8)
    • ►  October (1)
    • ►  July (4)
    • ►  June (9)
    • ►  May (15)
    • ►  April (24)
    • ►  March (43)
    • ►  February (73)
    • ▼  January (323)
      • Uninstalling Obiee 11g instance on a linux red hat
      • OBIEE 11g not showing new dashboard in the drop d...
      • OBIEE11g Installation
      • Starting OBIEE 11g Services on Linux
      • OBIEE11g Timestamp differencess
      • DAC11g Installation on Windows Server 2008R2.
      • BI Apps 7.9.6.4 Installation in widows server 2008R2
      • [OBIEE11g] - Eventually succeeded, but encountered...
      • [OBIEE11g] - Blue Screen Error While Login With Bi...
      • [OBIEE11g] - No Log Found Error
      • [OBIEE11g] - Stream Closed Error when Click on cor...
      • OBIA 7.9.6.4 RPD And Catalog Shared
      • [OBIEE11g] - Destination Path too Long error while...
      • [OBIEE11G] - Lookup table is a new feature in obie...
      • [OBIEE11g] - Create Veriable in OBIEE11g.
      • [OBIEE11g] - Configuring LDAP Server to provide OB...
      • [OBIEE11g] - Authentication Failure in OBIEE 11g
      • [OBIEE11g] - Bing Map Integration with OBIEE 11g
      • [OBIEE11g] - OBIEE Dashboard for Informatica Metad...
      • Informatica PowerCenter Upgrading from Version 8.6...
      • Data Modeling: Schema Generation Issue with ERwin ...
      • [OBIEE11g] - DAC Reporting in OBIEE11g
      • [OBIEE11g] - Publisher 11g – Performance Monitorin...
      • [OBIEE11g] - Auto Start OBIEE 11g using Windows Se...
      • [OBIEE11g] - Upgrade OBIEE 11.1.1.5 To Latest Vers...
      • OBIEE11g - User Right Click Interaction Control w...
      • [OBIEE11g] - Customizing Prompts ‘All Column Value...
      • [OBIEE11g] - Choosing the Right OBIEE Visualization
      • OBIEE11g - 11.1.1.6 New Features
      • [OBIEE11g] - Certification with Siebel Marketing f...
      • [OBIEE11g] - Creating a Stacked Bar Chart.
      • [BI EE11g] – Managing Host Name Changes
      • [DAC] - Multi Source Loads With OBIA
      • [Informatica] - ERROR CODES: [CNX_53021 ],[DOM_100...
      • [Informatica] - Informatica PowerCenter Repository...
      • [Informatica] - Processing UNICODE Characters in I...
      • [Linux] - Unix/Linix Commands
      • [DAC] - Full Load Vs Incremental Load
      • [Informatica] - Installation of Informatica 9.0.1 ...
      • [Informatica] - SF_34004- Service initialization ...
      • [Oracle Database] - Linux OS and Oracle database S...
      • [Oracle Database] - Installion Oracle database11g ...
      • [Informatica] - RR_4053 : Row error occurred while...
      • [OBIEE11g] - Change the placement of currency name
      • [OBIEE11g] - Exception Occuring During OBIEE 11.1....
      • What is Indexing in a Database
      • [OBIEE11g] - Setting up OBIEE11g Admin Tool for OD...
      • [OBIEE11g] - Getting Top-N Sales Reps Using the TO...
      • [OBIEE11g] - Getting Top-N Sales Reps Using Result...
      • [OBIEE11g] - Getting Top-N Sales Reps for Year and...
      • [OBIEE11g] - Analyzing Sales for “N Years Top-10 S...
      • [OBIEE11g] - Drill Down to Sub Reports Passing Mul...
      • [OBIEE11g[ - Configuring BI Scheduler for iBots on...
      • [OBIEE 11g] - How Application Roles, Groups and Us...
      • [OBIEE11g] - Setting up Access Permissions to Repo...
      • [OBIEE11g] - Fixing Weblogic and bi_server1 startu...
      • [OBIEE11g] - Deleting and Re-Creating Users in We...
      • [OBIEE 11g] - Backup and Restore of OBIEE Filesyst...
      • [OBIEE11g] - Creating Effective Bar Graphs
      • [OBIEE] - Useful SQL statements in Business Intell...
      • [OBIEE11g] - Creating Dashboard Traversing Throug...
      • [OBIEE11g] - Database Connection Failure while cr...
      • [DAC] - Admin password recovery
      • [Oracle 11g] - Oracle Database 11g installation on...
      • [OBIEE11g] - Variables in Oracle OBIEE 11g
      • [OBIEE11g] - Installing OBIEE 11g on Linux Fedora 17
      • [OBIEE11g] - Table view Date Column controlled by...
      • [OBIEE11g] - Adding Tooltips and conditional colo...
      • [OBIEE11g] - Show top-N Sales Persons in BI Publi...
      • [OBIEE11g] - Creating Scrolling Ticker Views
      • [OBIEE11g] - Authentication first with LDAP then ...
      • [OBIEE11g] - Relocation of OBIEE MetaData Reposit...
      • [OBIEE11g] - Hierarchical Roll-Up and Individual T...
      • [OBIEE11g] - Creation of Sales Reps Hierarchy wit...
      • [OBIEE11g] - Using external table to Filter BI Ans...
      • [OBIEE11g] - Configuring of RPD deployed on Linux...
      • [OBIEE11g] - Configuring an ODBC DSN for the Oracl...
      • [ODI] - Frequently Asked Questions (FAQ)
      • [OBIA] - Oracle BI Applications - Frequently Asked...
      • [OBIEE 11g] - Maps - Frequently Asked Questions (FAQ)
      • [OBIEE11g] - The 11g Features You Maybe Didn't Know!
      • [OBIEE11g] - New Features with OBIEE 11.1.1.6
      • [OBIEE11g] - Dashboard Prompt - "Prompt User"
      • [OBIEE11g] - [46153] The configuration file (O:\us...
      • [Informatica] - Multiple Chart of Accounts Configu...
      • [OBIEE11g] - Customizing Pivot Table Error
      • [OBIEE11g] - How to get Month Start Date and Month...
      • [OBIEE11g] - How to get Week Start Date and Week E...
      • [OBIEE11g] - How to rename My Dashboard
      • Table Organization in OBAW (Oracle Business Analyt...
      • [OBIEE11g] Uninstall OBIEE 11g
      • [OBIEE11g] - Command Line Merging in OBIEE 10g/11g
      • BI Publisher report is showing incorrect date(Show...
      • [OBIEE11g] - Connectivity issue from OBIEE (in Sol...
      • [OBIEE 11g] - Installation on Red Hat Linux
      • [OBIEE11g] - Different ToolTip for different rows ...
      • [OBIEE11g] - Integrating OBIEE 11g with EPM worksp...
      • [DAC] Fail to create indices during DAC execution ...
      • [DAC] Oracle DAC issue in 64 Bit Machine
      • [OBIEE11g] Connection Pool Select Button is Disabl...
Powered by Blogger.

About Me

Unknown
View my complete profile