Thursday, January 24, 2013

Basic Embedding of Endeca Information Discovery

I wanted to test embedding of Endeca Information Discovery (EID) in basic iframe.

So I took the URL of specific dashboard page (http://localhost:8080/web/quickstart/products) and created the following sample1.html file:

<html>
<body>
<iframe style="width:1200;height:500" src=
"http://localhost:8080/web/quickstart/products"></iframe>
</html>

Now I wanted to do the same with parameters. To do it in EID you can use deeplink. It is documented in the Studio Users Guide, chapter 16.

The following simple deeplink worked fine for me: 
 (On data source "quickstart", set filter Color=Black). 

When I tried to run this URL in iframe I got an error:
Deep Link Error Malformed deeplink parameter value: must be a valid JSONArray


Here I got a tip from a colleague: replace the double quotes sign with its HTML code &#34.
This solved my problem and the following works as expected:

<html>
<body>
<iframe style="width:1200;height:500" src="http://localhost:8080/web/quickstart/products?deeplink=[{&#34quickstart&#34:{queryFunctions:[{&#34class&#34:&#34RecordFilter&#34,&#34recordFilter&#34:&#34Color:Black&#34}]}}]"></iframe>
</html>

Friday, January 18, 2013

OBIEE 11.1.1.5.5 is available

Patch 11.1.1.5.5 is available from Oracle support site (support.oracle.com) as Patch 15887317). 
It's the most updated / latest version (actually bundle patch) of OBIEE, installed on top of 11.1.1.5 versions.

At the moment the patch is available for Windows 64, Linux 64 and Linux 32.

See the readme file for installation and OPatch version instructions.

The readme file describes the following bugs fixed:


** New fixes included in this patch (11.1.1.5.5)

10634778:QA USER WITH NO ACCESS OVER A FOLDER IS ABLE TO RUN ANALYSIS REPORT CONTAINED
11850704:OBIEE 11G CONFORMING DIMENSION SETTINGS FROM 10G NOT WORKING SIMILARLY IN 11G
13482511:UNION ALL TERADATA ISSUE WITH HIERARCHIES
13511930:NEW COLUMN CREATED BASED ON TWO DIFFERENT DIMENSIONS RETURNS INCORRECT RESUTLS
13639557:EXPORT TO EXCEL LOST CURRENCY FORMAT (NEGATIVE VALUES IN RED)
13990203:AFTER UPGRADE FROM 10G CONDITIONAL FORMATTING NOT WORKING FOR CHARTS
13991011:UNNECESSARY WHERE CLAUSE IS GENERATED BY PHYSICAL SQL WHEN USING FILTER.
14113844:INVALID JSON PAYLOAD
14147801:MEASURES ARE NOT AGGREATED BY THE DIMENSION COLUMNS WITH CASE STATEMENT
14276169:EXPORT TO EXCEL OR PDF IS NOT WORKING IN IE8 ON FIRST CLICK OF THE EXPORT LINK.
14313177:DRAG AND DROP CREATES INCONSISTENT SUBJECT AREA FROM A CONSISTENT BMM
14324595:BISERVERXMLEXEC FAILS TO CREATE LTS FOLDER FOR LOGICAL TABLES
14496280:OBIEE 11.1.1.5.0 CREATING HUGE CORE DUMP FILES
14543914:PRESENTATION SERVER AND JAVAHOST ARE SHUTTING DOWN AFTER CONSOLE LOGOFF EVENT
14562676:CSV AND TAB LIMITED DOWNLOAD IS NOT PICKING THE COLUMN SELECTED USING COLUMN SEL
14611639:EXTRA UNION BLOCK IS ADDED WHEN MEASURE IS ON EDGE AND TOTAL IS ON
14665812:SAWSERVER CRASH IN SQLBASE GETMAPPEDGROUPBYFORMULAFROMKEY
14689090:QUERY LIMITS TIME OF DAY RESTRICTION IS NOT TAKING EFFECT IN APPROLE HIERARCHY
14723047:NLS EXPORT REPORT TO CSV, DATA IS MISALIGNED AND SHOWN IN DIFFERENT COLUMN
14848265:NAVIGATE FROM PIVOTED LINE GRAPH AND TARGET REQUEST IS NOT FILTERED.
15848643:CHANGE PHYSICAL LAYER NOT CORRECT COLUMN LENGTH

** Fixes included in 11.1.1.5.0BP4 and also included in this patch

14133796:ERROR Q4NU7XSN OCCURS IN AN ANALYSIS
14013626:LARGE PIVOT TABLE SHOWS WRONG DATA IN COLUMNS WHEN EXPORTED TO EXCEL
13062842:HORIZONTAL SCROLL BAR IS NOT DISPLAYED ON EDITING DATABASE DIRECT REQUEST
13994110:VARIABLES CANNOT BE USED IN COLUMN SELECTOR
14228857:SAWSERVER CRASH IN FINDPROPERTYINCONTEXTANDFINDSCOPEINNERLOOP
13973660:CONTAIN OPERATOR AND VARIABLE ASSERTION FAILURE !LASTEXPRESSIONPOINTLESS()
14181830:UNABLE TO SHOW THE VALUES AS PERCENTAGE FOR A COLUMN, ERRORS OUT.
13688304:REPORTS NOT RENDERED IN HTML AFTER APPLYING PATCH 12667254
13685763:NQSERROR59001 OCCURS WHEN USING SUBSTRING, POSITION AND LENGTH AT SAME TIME
14220560:ALL BI SERVICES CRASHING WHEN EXECUTING A SPECIFIC QUERY
13775242:FREQUENT COREDUMP OF NQSERVER
13442285:EXPORT TO EXCEL WITH REGIONAL SETTINGS TO ENU AND BI LOCALE ITALIAN
13688051:CONTEXT VALUES ARE NOT BEING PASSED WHEN USING GO URL IF &FORMAT=PDF IS SET
13511145:OBIEE 11.1.1.5 MSSAS MEMBER_VALUE COLUMN ERRO
13803440:PROVIDE FIX TO BUG 13586829 ON 11.1.1.5.0
13590740:REPORT PROMPT ON ANSWERS REPORT WITH UNION QUERY DOES NOT WORK
13541432:PIVOT SORT BUTTON DISAPPEARS WHEN SELECT AFTER FROM THE TOTAL OF COLUMNS
13727327:SEARCHING USERS BY DISPLAYNAME DOES NOT WORK
14337710:NQS SERVER CRASH (STACK TRACE ATTACHED)
13808813:PLEASE PROVIDE FIX TO 12870901 TO 11.1.1.5.0
13982971:PERMISSIONS ON WEB CATALOG OBJECTS NOT APPLIED IMMEDIATELY
12989039:MOST RECENTLY USED LIST COMPUTATION CAN CAUSE CORRUPTION AND CRASH
13683564:TABLE PROMPT NOT WORKING WHEN SOURCED FROM COLUMN BINS
13983360:OBIEE SERVER DIES AFTER CONSOLE LOGOFF EVENT
13921437:SUBTOTAL CALCULATION IMPACTS PERFORMANCE IN OBIEE 11.1.1.6 UPON UPG FROM 10G
12787619:EXPORTED EXCEL THE 11TH COLUMN HEADING'S FONTS INCORRECT AND LINES ARE MISSING
14344399:OBIEE SERVER GENERATE WRONG SYNTAX FOR SQLSERVER WHEN IT'S A DUAL TABLE QUERY
13024366:SOLARIS CRASH WHILE MODIFYING GRAPH IN ANALYTIC REPORTS
13926291:OBIEE 11G RENAME BUTTON FOR BIP REPORT LINK IN DASHBOARD IS MISSING
14461064:INCORRECT WRITEBACK TEMPLATES USED WHEN DASHBOARD HAS MULTIPLE REPORTS
14080249:CUSTOM CSS STYLE DOES NOT WORK ON COLUMN HEADING.
14087030:REPORTS COMING FROM FOLDER OBJECT DOESN'T SHOW DASHBOARD LINKS

** Fixes included in 11.1.1.5.0BP3 and also included in this patch

13779997:QA: CHART NO DISPLAY -VALUE HIER CHILDREN IF CRITERIA CONDIT FORMAT MEAS
13774635:OBIPS THROWING ASSERTION WHILE RUNNING THE CACHE SEEDING AGENT
13739082:QA: ASSERTION ERROR IF VALUE HIER WITH CALC ITEM MOVE TO PAGE/PROMPTS EDGE
13791266:CUSTOM GROUP SELECTED IN VALUE HIERARHCY PAGE EDGE DOES NOT WORK
13631676:BISERVER ASSERTION ERROR AFTER APPLYING 13562882 PATCH
13516292:REPOSITORY INIT BLOCKS LOSE CONNECTION TO DATABASE AND NEVER REGAIN IT
14321778:BI7BH2: SQLUTILS::NORMALIZEEXPR HANGS ON COMPLEX CASE STATEMENT
13100667:PSR:FUNC:BI OBIS CRASH IN RQEXPRPHYSICALSUBQUERY ON RUP1 ST1 ENV.
13448188:DOUBLE COLUMN FILTERS DOES NOT RENDER CORRECTLY FOR LARGE VOLUME DATA
13868067:LEADING HINTS BASED ON TABLE IDENTIFIERS USELESS MIGRATING TO DEV, STAGE, PROD
14075766:BUNDLE PATCH 2 CAUSES BAD PERFORMANCE ISSUE WITH MSSAS DATA SOURCE.
14080494:DELIVERS NOT ABLE TO SEND CONTENT USING DX-MAIL AS SMTP SERVER
14158517:BIPS CRASHING AT VIEWLAYOUTBUILDERUTIL::XML2DRILLSTEP+0X8109
14159610:ADDING REPORT TO BRIEFING BOOK WITH COLUMN SELECTOR RESULTS IN SAWSERVER CRASH
14245163:BP2: BIPS CRASH @ REPORTLAYOUTMODELIMPL::SETPAGESTATE(SAW::VIEWLAYOUT*)
12530897:DEFAULT THREAD STACK SIZE FOR 64-BIT PORT IN SUGTHREAD.CPP SHOULD BE 1 MB
13388423:CHART NOT SHOWN IN IE ON CHECKING (USE 1000'S SEPARATOR) CHECK BOX
14115678:DXE COMPILER ERROR WHEN HAVING CUSTOM GROUP WITH MEASURE SORT
14146950:DECIMAL SIGN IN ITALIAN IS WRONG IN TAB DELIMITED DOWNLOAD AFTER PATCH 13986088
14137805:BI SERVER CRASH WHEN REPORT TOTAL HAVING DUP CONST IN BY
14120190:OBIEE EXPORT EXCEL DOESN'T SHOW GRAPHS AFTER INSTALLED PATCH 13442285
14082430:DEFAULT SELECTION FOR A COLUMN IN DASHBOARD PROMPT DOES NOT APPEAR AFTER BP2
14075002:PROMPT WITH MULTIPLE PAGES DOES NOT KEEP VALUES OF PREVIOUS PAGES
14037198:QA: ERROR IF 2 CALC ITEMS/GROUPS MOVED TO PAGE/PROMPT EDGE OF PIVOT
13974012:PIVOT TABLE CALC ITEM W/ HIDE DETAILS HAS INCONSISTENT TOTALS
13969710:QUERY REFRESH DOES NOT SHOW COLUMN DRAGGED FROM EXCLUDED SECTION AFTER BP2 PATCH
13966291:QA: ERROR IF SUM CALC ITEM MOVED TO PAGE/PROMPT EDGE OF PIVOT
13960499:BIPS CRASHING IN REPORTLAYOUTMODELIMPL::REBUILDDEFAULTMEASURESORTS
13959839:AFTER APPLYING BP2, REQUEST RETURNS ERROR GETTING CURSOR IN GENERATEHEAD
13958976:ENCOUNTER DXE COMPILER ERROR WHEN REPORT HAS COMBINE WITH SIMILAR RESULTS
13941525:ADD ABILITY TO SCALE SMA BLOCK ALLOCATION SIZES
13936284:OBIEE GENERATES INCORRECT QUERY FOR A MULTIPLE-HIERARCHY DIMENSION
13922366:SEARCH A MEMBER IS NOT WORKING FOR HIER COL PROMPTS WITH SPECIFIC COLUMN VALUES
13906706:PIVOT TABLE LAYOUT ISSUE WITH SORTED HIERARCHICAL VALUES
13899403:WHEN ADD A CONDITION USING SWEDISH CHARACTERS THROWS NO RESULTS
13895504:SORT ORDER UNEXPECTED IN 11G
13852931:INTEGRATIONS WITH MS ACCESS TO BI SERVER FAILS THROUGH ODBC
13852705:OBIEE 11.1.1.6 - COLUMNS ORDER AFFECT RESULTS IN ERROR
13846010:'[NQSERROR: 46036] INTERNAL ASSERTION:CONDITION' ON SOLARIS RUNS FINE ON WINDOWS
13795240:LOOKUP OF BI SERVER CONFIG MAY CAUSE THREAD STARVATION (STRESS ISSUE)
13794350:PIVOT TABLE CALCULATED ITEMS GRAND TOTAL IS NOT CORRECT
13723775:RECIEVING ERROR WHEN ADDING FIELD INTO ANALYSIS AFTER ADDING CUSTOM CLASSES TO T
13709093:RENAMING A DASHBOARD FOLDER W PRESERVING REFERENCES TO OLD NAME CAUSES DUPLICATE
13708781:ORACLE OLAP PARENT-CHILD QUERY PERFORMANCE ISSUE - LIMIT TO BOTTOMDESCENDANTS
13704274:SECURITY FILTERS ARE INCORRECTLY APPLIED TO LEVEL-BASED MEASURES
13641963:BACKSPACE BUTTON AFFECTS THE MAIN WINDOW INSTEAD OF POP-UP WINDOW
13641669:EMPTY DASHBOARD PAGE WHEN USER HAS FULL ACCESS
13635238:SASEEDQUERY FUNCTION CAUSES 'NQSERROR: 26002' FOR LONG QUERIES
13627902:MAPS ARE PRINTED IN GREY WHEN LOG IN USING ITALIAN LANGUAGE
13599283:BRIEFING BOOK DOESNT SHOW SAVED CUSTOMIZATIONS VIA A LINK.
13585714:USING 'FORMAT TITLE VIEW' IN ANSWERS- RESULTS - TITLE THROWS 'INVALID XML' ERROR
13564003:SAVING AN IBOT(AGENT) GIVES ORACLE BI SCHEDULER ERROR:[NQSERROR: 76016]
13552972:GRAND TOTAL IS PASSED IN A NAVIGATION FROM A PIVOT PROMPT TO A DASHBOARD PROMPT
13542145:UNION ERROR IN WHEN FILTERING A COLUMN THAT HAS A DESCRIPTOR ID COLUMN
13522246:DASHBOARD EMBEDDED IN IFRAME MODIFIES PORTALPATH OF SINGLETON SESSIONINFO
13517856:IPAD PAGE BEHAVIOR IS NOT THE SAME AS SEEN IN THE BROWSER
13502763:DRILL DOWN ON REGULAR COLUMN WITH CUSTOM DATE FORMAT
13482602:BI SERVER NOT HONORING ODBC QUERY TIMEOUT FOR SECURITY QUERIES
13479613:CANNOT MODIFY REPORT/DASHBOARD WHEN USER HAS READ-WRITE BUT NO DELETE PERMISSION
13476700:SERVICES FAIL TO COME UP ON RESTART DUE TO INIT BLOCK CRASH
13458667:HIDDEN OPTION NOT AVAILABLE FOR DATE COLUMNS IN TABLE LAYOUT
13408540:STRESS: STUCK THREAD DURING VIEWING REPORTS WITH OBIEE SERVER AS SOURCE
13403713:ADMINTOOL CRASHES DURING MUD CHECKOUT
13398245:PROMPT VALUE IS NOT PASSED ON NAVIGATION IF THE PROMPT SECTION IS COLLAPSED
13396591:NLS:REG ADF UI HINTS FAIL TO REFRESH WHEN LOG IN WITH ANOTHER LANGUAGE
13386728:OBIEE 11G EVALUATE FORMULA FAILS IF PARAMETERS HAS % OR /
13105251:PSR:FUNC:BI OBIS CRASH IN METADATACONVERTER::VISIT ON RUP1 ST1 ENV.
13068446:IPAD: ERROR ON DRILLING ON MAP VIEW
13040934:UNABLE TO DENY ACCESS TO A PRESENTATION COLUMN TO AN APPLICATION ROLE
12977923:ADMINTOOL CRASHES WHEN CHANGING COLUMN TYPE FOR ESSBASE CUBE COLUMN IN PHY LAYER
12918041:EXPORT TO CSV WHEN USING COLUMN SELECTOR DOWNLOADS ALL DATA
12870857:MAP VIEW INFO BOX DISPLAYS WRONG RESULTS
12865221:CHART SLIDER SHOWS '<BIDVTGRAPH><GRA' IN 11.1.1.5G
12820639:UPGRADE CATALOG FAILED
12807186:OPTION TO NOT SUPPRESS POPUP DOES NOT WORK WITH COLUMN SELECTOR ACTIONS
12766521:UNION REPORT W/ ACTION LINK ENABLED CONDITIONALLY FAILS
12664185:PSRHOT:PERF:BI OBIS HANGING AT NQSFMWIDMGMTAPIS::GETUSERS
12641049:DENIED PRIVILEGES FOR THE CATALOG GROUP NOT WORKING WHEN APPLICATION ROLES EXIST
12633058:SECURITY PREDICATES IN SELECT STATEMENT FOR DERIVE METRICS WITH FILTER FUNCTION
12628700:INCORRECT DISPLAY IN DASHBOARD PROMPT FOR RAGGED HIERARCHY
12618767:TABLE DOESN'T WORK AFTER ADDING A FILTER THAT CHANGES NUMBER OF PAGES IN REPORT
12586806:NOT POSSIBLE TO OVERRIDE THE COLUMN DATA FORMAT IN A DIRECT DATABSE REQUEST
12427458:PROMPT VARIABLE SHOWS '.&AMP;#39;' WHEN SELECTION CONTAINS APOSTROPHE ()
12423761:UA DOES NOT MIGRATE USER FULL NAME AND DESCRIPTION
12418724:FILTEREXISTS FUNCTION IS NOT WORKING FOR QUERY BLOCKING FEATURE
12367261:REMOVE DUPLICATE CALC ITEMS
12329191:COMBINED REQ SELECTION STEP BUTTON INACTIVE-- UNABLE TO DELETE CALCULATED ITEMS
11940308:SAVING CUSTOM DATE FORMAT FOR DATETIME COLUMN CAUSES INVALID XML ERROR
11898882:COLLAPSE NOT WORKING ON TABLE VIEW WITH H-COL AND MEASURE TO THE LEFT
11778125:PIVOT TABLE FILTER ISSUE WHEN THE ACTION LINK GOES TO DIFFERENT SUBJECT AREA
11667221:ENABLE POPULATION OF ROLES FROM SQL WHERE USER AUTHENTICATED VIA FMW
10141104:QA: ACT AS - PROXY - CAN'T RETRIEVE THE LIST OF TARGET USERS

** Fixes included in 11.1.1.5.0BP2 and 11.1.1.5.0PS1 and also included in this patch

7265385:SQL MULTI SELECT PROMPT LOSES CONDITION WHEN SEARCH
10210633:ERR CODE: EIRWWH9E - EMPTY STRING ERROR CREATING CALCULATION IF AFIELD IS NULL
10232312:SORT ICONS NOT SELECTABLE WITH FIREFOX WTIH LARGE NUMBER OF COLUMNS
10295403:EXCEL DOWNLOAD TOTAL RULE LINES ARE MISSING
10310311:ODBC CRASHES WHEN A DSN IS CREATED USING BISERVER ODBC DRIVER ONWINDOWS 2008
10316972:Y AXIS SCALE IS REPEATING.
10633330:'DO NOT DISPLAY IN A POPUP' OPTION ACTION LINK INCORRECT BEHAVIOR
10647655:COLUMN DATA FORMAT IS IGNORED ON WRITEBACK
11700314:REPORT NOT EXPORTED FULLY INTO EXCEL WHEN DOWNLOADING FROM PAGESOTHER THAN 1
11717987:INCORRECT ESSBASE MDX GENERATED FOR COMPLEX FILTERS WITH AND/OR
11772408:XML ERROR IN A PIVOT TABLE WITH GRAND TOTAL WHEN TRYING TO FORMATTINGLABEL
11793832:MULTIPLE VALUES IN A DASHBOARD PROMPT DOESN'T WORK WITH BI PUBLISHERINTEGRATED
11802400:COLUMN HEADERS SHOW INCORRECT FOLDER WHEN HIDING COLUMNS
11810367:CONSISTENCY CHECK - OPTION TO TURN OFF (AND ON) CERTAIN WARNINGS
11815935:NLS THE SETTING FIRSTDAYOFWEEK IN LOCALEDEFINITIONS.XML DON'T AFFECTCALENDAR UI
11823765:SIGNIFCANT PERFORMANCE DIFFERENCE BETWEEN PIVOT AND TABULAR VIEWS
11837656:NLS THE EXPORTED CSV WOULD BECOME MISALIGNED WHEN TITLE OR VALUECONTAIN COMMAS
11845571:QA: NO POINTER IS SHOWING UP FOR GAUGES
11882495:DRILL DOWN BREAKS ON PRODUCT IN SAMPLE APPS ON ESSBASE
11890139:PARENT CHILD HIEARCHY THROWING ERROR WHEN USING FRAGMENTATION INSOURCES
11897206:RENAME OF FOLDER IS NOT ATOMIC AND LEAVES TWO CHUNKS OF ORIGINAL
11924932:PERFORMANCE ISSUES IN 11G
12315036:PSR ST3B16 NO TIMEOUT  BETWEEN OBIS AND WLS SECURITY AUTHENTICATION
12344432:CARA:AMAZON:XML VALIDATIONS: QUERYITEM1 -- 8
12344465:CARE:AMAZON:XML VALIDATION:CODASHBOARDSELECTIONS1 --2
12352010:RESTRICTED COLUMNS SHOULD NOT SHOW IN SUBJECT AREA.
12373034:SEARCH DIALOG BOX IS SLOW WHEN THERE IS AN EXISTING FILTER
12376825:SSAS USING SUB-SELECTION IN MDX CODE GENERATION.
12377693:ASENG: ERROR MESSAGE WHEN USING FORMULAS IN FIELDS
12391045:FUNCTION REPORT_AGGREGATE REQUIRES AT LEAST ONE MEASURE COLUMN IN ITSFIRST ARGU
12393459:WRONG WEBCATALOG PERMISSION GIVEN TO LOGIN USER  (security issue)
12394949:ASENG:RC3: EXPORT TO EXCEL DOES NOT HONOR 'DECIMAL PLACES' COLUMNPROPEPERTY
12396205:MISSING REPORT LINKS FOR REPORTS IN A CATALOG FOLDER ON A DASHBOARD
12399693:LD1 B17.1: INV VAL AND GROSS MARGIN REPORT TABLE - INVALID PATH ERROR
12399899:PERFORMANCE IMPROVEMENT - REMOVE CENTER QUERIES FOR PAGE SLICES NOTDISPLAYED
12407600:STRESS NQSSERVER CRASHED IN THE ODBC LOGICAL QUERIES 360 STRESS TEST
12411977:TERADATA RANK() IS NOT ALLOWED IN THE SUBQUERY
12534231:CRMQA_RRF, ST3B17.1: SPE TRAINING JOB FAILS WITH NULLPOINTEREXCEPTION
12535513:CONDITIONAL SELECTION STEP WHEN SPECIFYING MATCH SESSION VARIABLE NOTWORKING
12544465:BIPS STATEPOOL DOES NOT UPDATE LAST ACCESSED TIME - INVALID STATEIDENTIFIER
12546862:PIVOT TABLE 'PERCENT OF COLUMN' DATA DISPLAY BADLY WHEN LOGGED INTURKISH
12550336:RC2-HOT-BIAPPS GUIDED NAVIGATION NOT SHOWING ON DASHBOARD PAGES
12561330:DRILL DOWN REPORTS WITH SQL FILTER ARE  ERRORING OUT  IN SYSTEM TESTENV
12562702:PERIODICALLY CALL MACOMPACT()
12565506:BIP REPORT USING PRES VAR IN FILTER FUNCTION IN COLUMN FORMULA NOTFILTERING
12577234:ISSUE WITH PRESENTATION VARIABLE EFFECTING PROMPT
12578133:DATE LITERALS NOT SUPPORTED FOR MSFT ANALYSIS SERVICES
12583085:500 INTERNAL SERVER ERROR FOR A FEW REQUEST AFTER UPGRADE FROM 10G TO 11.1.1.3
12583341:PREFIX IS LOST WHEN NAVIGATING TO CRITERIA OR RESULTS TAB
12587004:EXPORT TO EXCEL CORRUPT VALUES THAT ARE PART OF A % IN TURKISH
12587722:AFTER UPGRADE TO 11.1.1.5 CANNOT FUNCTION SHIP ERROR FOR ESSBASE EVALFUNCTIONS
12594007:AGGREGATED COLUMNS THAT CONTAIN NULL RESULT IN NO DATA WHEN GRANDTOTAL ADDED
12609751:AFTER UPGRADE, PIVOT TABLE CALC ITEM W/ HIDE DETAILS HAS INCONSISTENTTOTALS
12609868:INTERNAL SERVER ERROR WHILE BROWSING DASHBOARDS USING CATALOGNAVIGATION PANE
12612904:TOTAL PICKS VALUE IN FIRST ROW AND GIVES NOT A NUMBER WHEN AVERAGINGNULL
12617883:VARCHAR DATATYPE NOT SUPPORTED FOR FILTER METRICS USING PARAMETERSOVER JDBC
12628039:SUPPORT MORE THAN ONE HIERARCHY FROM THE SAME DIMENSION FOR MSANALYSIS SERVICES
12632151:REQUEST VARIABLES ARE NOT PASSED FOR A LONG RUNNING QUERY
12632611:ERROR WHILE EXPANDING HIERARCHY COLUMN WITH CACHE ENABLED
12633881:WEBCATALOG GETS SEVERELY CORRUPTED AFTER MIGRATION/UPGRADE FROM 10GTO 11.1.1.5
12638186:EXCEL EXPORT GENERATES A SPACE BEHIND ANY MEASURE VALUE WHEN USINGACTION LINK
12643093:WHEN EXPORT TO EXCEL, ZERO VALUES APPEAR TO BE BLANK
12656913:OBI 11G ONDEMAND - DASHBOARD MENU ITEMS DISAPPEAR FROM MENU
12661967:PRES VAR 'YEAR-1' DISPLAYS @{YEAR -1} IN COLUMN HEADING AND NOT VALUE
12662417:CALCULATED ITEM DISAPPEARS FROM REPORTS (AFTER APPLYING BUG 12587870PATCH)
12667254:LDAP ATTRIBUTES NOT APPEARING IN SYSTEM PROFILE SO NOT AVAILABLE TODELIVERS
12669280:'HIDE DETAILS' OPTION NOT DISPLAYED FOR NEW CALC ITEM IN COMBINEDREQUEST
12669338:IBOT AGENT CONDITION NOT WORKING WITH DIRECT DATABASE REQUESTS
12675796:QA: MLR:REG ASSERTION ERR AFTER CHANGE LAYOUT, VALUE HIER NO DRILL,FILTER DRILL
12676102:DIVISION BETWEEN INTEGERS ARE NOT CONSISTENT BETWEEN WINDOWS ANDLINUX.
12679383:PROTECTED FILTER SHOULD NOT PICK UP PROMPT VALUES
12687823:CORE DUMP FROM LARGE PLAN CACHE
12687949:ACT AS FUNCTIONALITY IN 11.1.1.5 DIRECT TO HOME, NOT USER DEFAULTDASHBOARD
12693390:UPGRADING TO 11.1.1.5 FROM 10.1.3.4.1 AGGR ALONG ESSBASE DIMENSIONCAUSES ERROR
12696084:FORMAT HEADINGS &gt; HORIZONTAL ALIGNMENT IS NOT WORKING IN TABLE ORPIVOT VIEW
12701383:11G DOWNLOAD TO EXCEL INCLUDES A BLANK TEXT ROW BELOW THE LAST ROW OFDATA
12701483:BAD PERFORMANCE IN A PIVOT TABLE WITH ESSBASE AND UNCHECK'USEUNQUALIFIED MEMBER
12708184:ANONYMOUS BLOCK IN CONNECTION SCRIPT CAUSES ORA-6550 ERROR ON ROWCOUNT
12710524:ORA-1704 ERROR OCCURS WITH QUERY_BLOB CLOUMN OF USAGE TRACKING
12715581:PRESENTATION VARIABLE VALUES NOT PASSED CORRECTLY IF VALUE CONTAINSQUOTES
12715885:HORIZONTAL ALIGNMENT WON'T WORK FOR NARRATIVE, STATIC TEXT VIEW IFEXPORT TO PDF
12716399:TOTAL_TIME_SEC VALUES INFLATED WHEN REQUEST HAS 'DISPLAY MAXIMUM'BUTTON
12717149:PERFORMANCE ISSUE IN VALUE HIERARCHY DRILL ISSUE - WITH ESSBASE ALIASCOLUMNS
12720664:VARIABLE NOT WORKING AS EXPECTED AFTER NAVIGATING TO A PAGE WITH LONGQUERY
12721517:EXPORTED TITLE DISPLAYS INCORRRECT VALUE WHEN PRES VAR IS SET AS ALLCHOICES
12725139:VARIABLE VALUES NOT RESOLVED IN TITLE/LEGEND OF CHART, SHOWS FORMULA
12727616:ACTION LINK FROM PARENT W/ SUBQUERY TO CHILD REPORT LOSES FILTERPROMPTS
12729332:PAGE EDGE QUERY OPTION FOR ALL PAGES OR SEPARATE QUERY
12732606:HIERARCHICAL PROMPT WITHOUT SELECTIONS APPLIED INCONSISTENTLY
12734649:PSR:OBIPS CRASHED/CORE DURING DOWN TIME IN LD1B18 ENV
12739309:PSR:PERF:BI OBIS RESPONSE TIME GOES UP TO 500SECONDS+ WHEN RUNNINGSCOTIA RPD
12741534:OBIEE 11G 11.1.1.5 MSSAS DOUBLE COLUMN CAPTION DISPLAY
12741655:'BYPASS ORACLE BI PRESENTATION SERVICES CACHE' NOT USABLE WITHHIERARCHICAL COLS
12756905:PRESENTATION VARIABLE FILTER ERRORS IF DEFAULT VALUE IS %
12758061:AFTER UPGRADE, ISSUE WITH 'TREAT AS ATTRIBUTE' PROPERTY
12761316:NESTED 2+ SAVED FILTERS IN A DASHBOARD PROMPT DOES NOT WORK IN 11.1.1.5
12761355:UNABLE TO PRESS OK ON CHART PROPERTIES FOR GAUGES.
12761619:SUBQRY CONTAINS TOO MANY VALUES FOR THE IN PREDICATE ERR
12764435:DASHBOARD SECTION CONDITIONS NOT UPDATED WHEN REFRESHING DASHBOARD
12766173:HEADER DOESN'T WORK PROPERLY AFTER UPGRADE TO 11G
12769254:OBIEE 11.1.1.5 - NOETIX 10G CATALOG UPGRADE FAILS
12776974:SUPPORT FOR 2 MODES IN PAGE QUERY: 1. FROM CUBE OR 2. SEPARATE AXISQUERY
12777172:POSITIONAL CALCULATE ITEMS NOT CALCULATED CORRECTLY IF HIERARCHY INOTHER EDGES
12779543:DASHBOARD ERROR CATALOG OBJECT SCHEMA VALIDATION FAILEDAGFIXBO2:EIRWWH9E
12791684:ERROR IN THE QUERY GENERATED BY OBIEE WHILE THE PROMPT DEFAULT SQLFAILED.
12796744:UI OPTION FOR ALL PAGES OR SEPARATE QUERY WHEN POPULATING VIEW PAGEPROMPTS
12800814:PSR:PERF:BI THERE ARE SOME MEMORY LEAKS IN SAS(OBIS)
12806276:IT IS NOT POSSIBLE TO DRAG A DIM COLUMN TO A PIVOT TABLE PROMPT IN 11.1.1.5.0
12806562:PRESENTATION VARIABLES KEEPING VALUES WHEN THEY SHOULD BE NULL
12810401:PIVOT TABLE SECTIONS SHOW ZEROS (0) FOR SOME CALC ITEMS
12810496:BIPS NAVIGATION SPINS DUPLICATE LOGICAL QUERIES (WITH BYPASS CACHEOPTION).
12821662:SEVERE PERFORMANCE DEGRADATION WITH PIVOT TABLE PROMPT OVER OLAPSOURCE
12822192:INCORRECT RESULTS AFTER DOWNLOADING TO EXCEL (LOCALE DIFFERENT THANENGLISH)
12825306:EDIT FORMULA: SHARED FILTER GIVE 'OBJECT EXPECTED' JAVASCRIPT ERROR
12831483:INTERNAL ASSERTION: CONDITION BYTECONVERTED &lt;= FROMBUFFERSIZEINBYTES
12834582:REG: CANNOT SET BIPUBLISHER REPORT TIME ZONE FROM 'MY ACCOUNT' DIALOG
12836077:PERFORMANCE DIFFERENCE AFTER UPGRADING TO 11.1.1.5
12844774:SHOW WRONG COLUMN FORMULA OF UNION ANALYSIS
12850289:COLUMNS ARE NOT IMPORTED FROM SYBASE IQ 64BIT WHEN THE TABLE NAME HASUNDERSCORE
12853576:HIERARCHY IN PROMPT DOESN'T DISPLAY VALUES IN IE7 UNTIL THE DIALOG ISMOVED
12860823:SUM(MEASURE BY ENTITY) ROW ADDED -&gt; SHOW AS PERCENT OF COLUMN DOESN'TWORK
12870926:NO ABILITY TO CONFIGURE SMA COMPACT FREQUENCY
12874242:PATCH FOR BUG 12736571 INTRODUCES UNDESIRED BEHAVIOR
12877995:FIREFOX 10: BROWSERS WITH VERSION GREATER THAN 10 REPORTED AS NOTSUPPORTED
12879300:DRILL THROWS [NQSERROR: 59137] FILTER LEVEL XXX IS BELOW YYY
12888983:FIELDSTORAGE ON 64-BIT NEEDS 8K BUCKETS ADDED TO SMA
12897439:PRESENTATION SERVICES CRASHES WITH CERTAIN QUERIES
12900745:NARRATIVE VIEW IS FETCHING ALL RECORDS EVEN WHEN ROWCOUNT ISSPECIFIED
12908818:EDIT COLUMN FORMULA NOT WORKING IN FIREFOX
12909000:QA: REG:UI: SELECTION STEPS PANE - LOCATION OF EDIT/DELETE ICON
12909840:ERROR : [46036] INTERNAL ASSERTION AFTER SAVING ONLINE RPD CHANGES
12911049:EXPORT (PDF, EXCEL, ETC.) OPTION IS NOT EXPORTING ALL ROWS OF AREPORT
12914177:UI CHANGES TO RESPOND TO IS ISUSERSECURITYINVISIBLE() FALSE
12921728:DXE MAKING UNNECESSARY COPY OF DATA FOR SINGLE AXIS LAYOUTS
12921997:DONT CREATE COLUMN NODE IN COLUMNS PICKER WHEN COLUMN IS NOTACCESSIBLE
12922186:REQUEST NAME IS APPEARING IN TABLE PROMPT IF THE REQUEST HAS A PROMPT
12926133:EXPAND \ COLLAPSE NOT WORKING FOR PARENT CHILD DIM AFTER PATCH 12899699
12934869:PUBLISH PAGE TO DASHBOARD DOES NOT CREATE OR USE NEW OBJECTS
12937182:AFTER UPGRADE, COLUMN PROPERTIES ARE NOT WORKING IN COLUMN SELECTOR
12937512:BI SERVER CRASHES AND RESTARTS STD BAD_ALLOC ORA-24550 ERROR
12939713:MAX TIME QUERY LIMIT GOVERNOR DOES NOT CANCEL THE DATABASE QUERY
12940837:IE DISPLAYS MULTIPLE X , Y AXIS BROWSER SCOLL BAR AFTER DRILLING INDASHBOARD
12950704:MEASURE BASED SORT DOESN'T WORK WHEN VIEW IS IN DETAIL GRAIN
12960177:ADDING CALCULATION TO THE HIERARCHY COLUMN IN SELECTION STEPS RESULTSIN ERROR
12965257:NQSERROR 42039 OCCURS WHEN USING DATA FILTER
12969300:GETTING ERROR OAMP2OPY:U5V5TIAH WITH SOME REPORTS AFTER APPLYINGPATCH 12899699
12976029:IN IE, DASHBOARD PROMPT'S EDIT WINDOW GOES OUT OF BROWSER BOUNDARY
12978196:COLUMN SELECTOR DOES NOT PASS VALUES FROM SUMMARY REPORT TO DETAILEDREPORT
12981896:MAPVIEWER HOVER TOOLTIPS SHOW WRONG VALUES
10298197:1 ROW OF EMPTY RESULTS WHEN MAX(RCOUNT(1)) IS IN PROJECTION.
12982344:MAPVIEWER TOOLTIP DOES NOT SHOW VALUE FOR MULTI-COLUMN BI-KEY.
12992697:PAGE BREAK WITH COLUMN BREAK DOES NOT WORK WHEN USE PRINTABLE HTML
12993928:LIFETIMEGUARD NOT THREADSAFE
13004557:DRILL ON A MEMBER IS NOT WORKING
13008675:LARGE IN LIST OF LITERALS ISSUE
13026233:USAGE TRACKING INSERT MISSING A COMMA
13030883:IN A VIEW SELECTOR, IF DRILL IN PIVOT TABLE AND RETURN, GOES TO THEDEFAULT VIEW
13031147:CANNOT DELETE A HIDDEN MEASURE EVEN IF ANOTHER MEASURE EXISTS IN APIVOT TABLE
13031395:GRAND TOTAL SHOWN INCORRECT RESULTS
13031827:XSLFO PROCESSING CONSUMES TOO MUCH MEMORY
13034629:EIRWWH9E ERROR HAPPENED WHEN SETTING PAGING CONTROLS HIDE VALUE
13040363:BUG 12790703 -&gt; BASE BUG 12609751 STILL REPRODUCES AFTER PATCH 12609751
13043745:PIVOT TABLE'S LAYOUT COLLAPSES WHEN HIDDEN ROW AND TOTALS BEFORE ISUSED
13054445:REPLACING NULL VALUES WITH '0' IN AN OBIEE 11G PIVOT TABLE IS NOTWORKING
13056782:SAWSERVER.EXE CRASH
13063155:FATAL:INVALID STATE IDENTIFIER -1006880667 ERROR
13067844:RED 'WRITE BACK ERROR' MESSAGE IN 11.1.1.5 - REGRESSION FROM 11.1.1.3
13070272:READING XSL-FO XML MEMORY CONSUMPTION CAN BE REDUCED BY 50%
13071711:CIRCULAR JOIN ERROR WHEN RUNNING ANALYSIS USING UPGRADED REPOSITORY
13072653:NAVIGATING FROM PORTAL TO OBIEE DASHBOARD THROWS A LOGIN PAGEOCCASIONALLY
13083074:PROMPTS CREATING UNSPECIFIED ERROR WHEN SELECTING MULTIPLECONSTRAINED CRITERIA
13083732:IN PARENT CHILD HIERARCHY EXCESS MEMORY SPIKE
13084799:QUERIES AGAINST BISERVER NOT LEVERAGING FORWARD ONLY OPTION
13087113:UPGRADED 10G DASHBOARD FAILS WITH ASSERTION FAILURE:(*IBASEFORMULA).GETNAME() =
13087545:CONDITIONAL FORMATS IN TABLE VIEW CAUSE EXTRA COMPUTATIONS
13088771:DRILL DOWN CAUSES PIVOT TO SHOW ROWS THAT DEFAULT TO HIDDEN IN 10G
13096133:ERROR OCCURS WHEN DISPLAYING A RUNNING SUM IN PIVOT TABLE IN DIRECTDB REQUEST
13098323:AN ARITHMETIC OPERATION IS BEING CARRIED OUT ON A NON-NUMERIC TYPE
13102881:DATALAYOUT METADATA  IMPLEMENTATION HEAP INTENSIVE
13107569:INCORRECT TOTALS IN GRAPH PIVOT TABLE VIEW
13111799:GRAPHING ENGINE IS NOT RESPONDING ERROR HAPPENED.
13249783:AFTER APPLY PATCH 129725592 OBIEE 11G THROWS BAD XML INSTANCE
13251234:SMALL STRINGS ON 64-BIT RESERVE EXTRA CAPACITY
13253545:ADDING A NEW GROUP SHOWS EMPTY COLUMN
13258962:BITHIRDPARTY ATOMIC OPERATION CAN CORRUPT NEXT 32-BIT WORD INLINUX-64
13262149:IBOT SUBSCRIPTION CODE CAN CRASH ACCESSING USER GUID
13263142:HC SELECTION NOT SHOWING TEXT WITH SPECIAL CHARACTER (SINGLE QUOTE)WHEN APPLY
13263694:CATALOG MANAGER OFFLINE MODE DISPLAYS INIT BLOCK USERS AS INVALID
13321507:ITEM STALE ERROR WITH NETAPP ON WRITING THE SAME .ATR FILE BACK TOBACK
13324965:SAWSERVER CRASH IN IBOTGETMETADATA
13332501:ASSERTION FAILURE: !SFORMULA.EMPTY() .. RESULTS IN NQSSERVER COREDUMP
13336101:UNNECESSARY COPY OF DATA IN CACHINGTABLE
13350175:ADDING FILTER TO DIFFERENT LEVELS OF A HIERARCHY GETS IGNORED IN MDX
13355961:STRESS BI NQ SERVER CRASHES DUE TO ANAATOMICDECREMENT CORRUPTION
13359653:ANALYSIS QUERY IS NOT CANCELED PROPERLY.
13360538:MAPVIEWER DISPLAYS THE MEASURE VALUE WITHOUT MEASURE NAME
13362302:CHANGES MADE TO ONLINE BI SERVER USING BISERVERXMLCLI IS NOT UPDATEDIN RPD FILE
13366675:INAPPROPRIATE PRE-RESERVES WITH STRINGBUFFER
13370386:'THE CURRENT XML IS INVALID WITH THE FOLLOWING ERRORS: BAD XMLINSTANCE!' OCCURS
13377442:EXPORT LINKS SHOULD WARN / ERROR WHEN ALREADY EXPORTING
13383217:QDR ISCUSTOMGROUPUNIQUEID DOES UNNECESSARY STRING CONCATENATION
13383976:PERFORMANCE REGRESSION IN CLIENT DATA SCROLLING IN BIPS
13384157:LARGE SMA BUCKET SIZES NEED TO BE ALIGNED TO POWERS OF 2
13393602:RSUM NOT CALCULATING CORRECTLY WHEN THERE ARE EXCLUDED COLUMNS
13400140:MULTI-STEP PROMT MISSING THE DASHBOARD PROMPT WHICH 'LIMIT VALUES BY'IS SET
13401386:AUTO PREVIEW ARE NOT PREVENTED EXCEPT PIVOT VIEW
13403816:AFTER APPLIED MLR 13110245 STILL HAVING THE HIERARCHICAL PROMPT ISSUE
13403822:LIMIT BY ALL PROMPTS NOT WORKING WHEN MORE THAN THREE PROMPTS USED.
13404742:REDUCE DL MEMORY FOOTPRINT FOR 64-BIT BY CHANGING INST MEMBER DATATYPES
13405110:OPENING REPORT CRASHES BI PRESENTATION SERVICES
13409646:CALCULATED ITEMS CAUSE MANY INTERNAL CUBES  WITHIN DXE
13416870:RACE CONDITION IN PRESORTED CUBE
13418590:EXPORT TO EXCEL STILL NOT WORKING PROPERLY AFTER PATCH 12394949(LINUX 64)
13425631:CONDITIONAL FORMAT ON NUMERIC LITERAL COLUMN GIVES ASSERTION ERROR
13437652:QA:CLICKED BACK BUTTON,FACING'FATAL:UNABLE TO FIND THE REQUESTEDSTATE IDENTIFER
13442873:REMOVE QDR CACHE IN DLITEMINSTANCE
13444157:ERROR WHEN USE 'IS IN TOP' OPERATOR IN FILTER WITH PRESENTATIONVARIABLE
13449747:ACCESS: PIVOT TABLE CONTEXT MENU NOT WORKING WITH KEYBOARD
13455402:DXE UNNECESSARLY STORING MULTIPLE COPIES OF VARCHARS WITH MANYCOLUMNS
13455408:ENHANCE DXE FIELDSTORAGE TO GROUP SMALLER ALLOCATIONS
13455412:DXE MAKING EXTRA COPIES OF DATA
13455456:CERTAIN DXE SORTS ARE BEING DONE IN MEMORY INSTEAD OF ON DISK
13470294:BI PRESENTATION SERVICE FAILS TO START IN PARALLEL WITH OTHER BICOMPONENTS
13474382:MEMORY LEAK IN COLUMNORDER / MEASUREORDER OBJECT HANDLING
13494385:THE SCROLL BAR OF RESULT PREVIEW WINDOW.
13498910:BI APPS DEBT TO EQUITY REPORT RETURNS SQXNQAGGRBREAKPROCESSOR ERRORIN 11.1.1.5
13501903:PATCH FOR BUG 11837656 DOES NOT FIX THE ISSUE WITH DOUBLE QUOTES
13517767:POSITIONAL CALC CAN CAUSE XMLWRITER EMPTY DOCUMENT ERROR
13522060:RPD SESSION VARIABLE UNABLE TO IDENTIFY THE DYNAMIC SCHEMA NAMES FOROLAP
13522214:VALUE-BASED HIER PROMPT SEARCH RESULTS IN ODBC ERROR
13523548:ERROR OUT WHEN USING MULTIPLE DOUBLE COLUMNS IN SAVED FILTERS
13526747:PATCH 13342140 DOES NOT CORRECT PIVOT CHART LEGEND ISSUES.
13529441:MULTI DASHBOARD PROMPT DOESN'T WORK WITH BI PUBLISHER INTEGRATED
13535937:NEGATIVE VALUE IN CACHE MANAGER
13542805:BAD CACHE ENTRY BEING GENERATED
13546009:SELECT PIVOT TABLE IN VIEW SELECTOR CAUSES COLUMN TO BE EXCLUDED WHENDRILLING
13567100:CASE WHEN USED IN MEASURE IN PIVOT IS NOT WORKING AS IN 10G
13610379:UNABLE TO CHANGE TIME ZONE IN USER PREFERENCES/GLOBALLY IN OBIEE 11GFOR BIP
13616593:AUTO PREVIEW ARE NOT PREVENTED FOR GRAPH
13625435:RUP2-ST-CDRM:FORMMATING AND ACTION LINK ISSUE AFTER PS5 UPGRADE
13626098:RUP2-ST-CDRM: DRILL DOWN REPORT NOT RETURNING OUTPUT.
13647309:OBIEE GRAND TOTAL FUNCTIONALITY INCORRECT AND INCONSISTENT WITH OLAPCUBES
13680310:ERROR DISPLAYING VIEW WITH NESTED AND SAVED CALC ITEMS
13682411:CALC ITEM NOT DISPLAYED IN A PIVOT TABLE AFTER PATCH 13544352

Thursday, January 10, 2013

OBIEE and Linear Regression with Oracle DB

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). (See here 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 by Y=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: