Saturday, May 31, 2014

Wednesday, May 28, 2014

OBIEE BUNDLE PATCH 11.1.1.7.140527 AKA 11.1.1.7.7 (?) is available

Please note OBIEE Bundle Patch 11.1.1.7.151020 is available. You might prefer it. 

OBIEE 11.1.1.7 new general bundle patch (number 7?), 11.1.1.7.140527 is available in Patch 18507268 it can be installed on any previous 11.1.1.7 release.

This patch release supports IE 11 !

I had no problems downloading the patch this time. If you have problems, try an add-on to Firefox "DownloadThemAll".

The Patch is platform specific and is available for:
  • HP-UX Itanium
  • IBM AIX on POWER Systems (64-bit)
  • Linux x86
  • Linux x86-64
  • MS Windows (32-bit)
  • MS Windows (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86-64 (64-bit)
You can read the readme file here.

Unlike 11.1.1.6 patches, this patchset is downloaded in a single big file+one bonus patch. So just for the record (since you don't need to download them separately):
Patch Abstract
16913445 Oracle Business Intelligence Installer (BIINST) - same as previous patches
18507640 Oracle Business Intelligence Publisher (BIP)
18657616 Enterprise Performance Management Components (BIFNDNEPM)
18507802 Oracle Business Intelligence Server (BISERVER)
18507778 Oracle Business Intelligence Presentation Services (BIPS)
17300045 Oracle Real-Time Decisions (RTD) - same as previous patches
16997936 Oracle Business Intelligence ADF Components (BIADFCOMPS) - same as previous patches
18507823 Oracle Business Intelligence Platform Client Installers and MapViewer

As usual there is a bonus patch: Patch 16569379  (1111770)- Dynamic Monitoring Service patch. It is platform dependent. If you installed it with 11.1.1.7.1 or later, that's enough. It's the same.

Oracle BI EE Suite Bundle Patch 11.1.1.7.140527 contains no new features (but apparently supports IE11 ).
In the ReadMe here there are special instructions for upgrading from 11.1.1.6.X to 11.1.1.7.140527 at  "Section 3.1: For Customers Moving From 11.1.1.6.x to 11.1.1.7.140527". DON'T upgrade before you read and understand it.



Bugs fixes 

BI Publisher

 
New bug fixes in this patch are shown in the following list:

15979745: THE LAYOUT GRIDS CELLS IN THE REPEATING SECTION HAVE BEE MERGED WHEN EXPORT TO E

16011433: BI PUBLISHER REPORT DOES NOT WORK WHEN PARAMETER IS HIDDEN

16523273: DIFFERENTIATE BETWEEN INT/LONG IN BUSINESS VIEW

16770946: REMOVE SELECTED ELEMENTS DOES NOT REMOVE THE ELEMENT IN DATASET

16961054: REPORTSERVICE - UPDATEREPORTDEFINITION DOES NOT CHANGE DATAMODELURL

17168007: PERFORMANCE ISSUE IN BI PUBLISHER 11G CATALOG

17234210: BIPUBLISHER_11.1.1 GENERATING HUGE TEMP FILES : POD -ATS

17638010: CAN'T SEARCH PARAMETER WHEN MULTIPLE VALUES ARE SELECTED ON DEPENDENT PARAMETER

17645617: INSERTED BLANK CELL WITH XSLX OUTPUT IN BIP

17911239: TOO MANY ERRORS AND WARNINGS IN BI PUBLISHER LOGS

18006813: CANNOT QUERY UP A BIP REPORT JOB IN ORDER TO DELETE IT.

18069303: BIP SCHEDULE TRIGGERS GENERATE MANY BI SESSIONS WHICH STAY OPEN

18167350: BIP 11 EXCEL TEMPLATE: EXCEPTION FROM FOREACH

18281134: QA- IE11 - UNABLE TO ACCESS DATASOURCES WHEN CREATING REPORT

18316757: DATE PICKER DISPLAYS WRONG TIME COMPONENT AND DOES NOT REFLECT WHAT'S IN THE FIE

18405607: BIP 11.1.1.7.140 REPORT LOV PARAMETER NOT LOADING WHEN USING IE8 BROWSER

18410617: QA- IE11 - FAILED TO SAVE ERROR WHEN SAVING NEW DATA MODEL

18410979: QA: IE11 :OLB - DOUBLE CLICKING ON TEXT ITEM MAKES THE WIDTH VERY SMALL

18477237: NOT ABLE TO SEARCH VALUES IN BIP REPORT LOV.

Enterprise Performance Managment

 
New bug fixes in this patch are shown in the following list:

14191566: ROUNDING DIFFERENCES BETWEEN ESSBASE V7 (32 BIT) AND V11 (64 BIT)

16272408: UNICODE CHARACTERS WERE NOT DISPLAYED ON EAS SQL INTERFACE USING OCI ROUTE

16520342: SUBMIT DATA WITH MEMBER OPTION "MEMBER & ALIAS" CRASHES APPLICATION

16574357: PSR:ESSBASE:COREDUMP ON REL7 ST2 CDRM ENV FOR ESSSVR

17187207: ESSSVR CRASHES DUE TO MULTIPLE THREADS TRYING TO FREE THE SAME BUFFER

17215424: ASO-ASO TRANSPARENT PARTITION RETURNS INCORRECT VALUES ON TARGET

17222088: ATTRIBUTE VB_NAME = "MODULE1" IN ESB32.BAS FILE CAUSE COMPILE ERROR IN VB

17326375: CANNOT OPEN NEW SHEET IN SMARTVIEW FOR LINKED PARTITION.

17503192: CREATEBLOCK NOT CREATING A BLOCK IF IT IS USED IN IF STATEMENT

17505060: JAPI BUG FOR ESSBASE BUG 17350699 - EMPTY ROWS GETTING INSERTED IN SMART VIEW RE

17563682: ASO APP CRASHES WHEN USERS PERFORM SMART VIEW REFRESH (REALFREE() ON SOLARIS)

17581433: DATA POINTS IN POWERPOINT DO NOT WORK SEV 1 24X7 SR

17782718: MDX QUERY PERFORMANCE SEVERELY DEGRADED

17814289: VALUES INSERTED INTO HASH TABLE RANDOMLY CAUSES SPURIOUS DIFFS

17829319: UNABLE TO CREATE CONNECTION TO ESSBASE BSO APP W/ADDITIONAL VOLUME CONFIGURED

17927729: DATAEXPORTDRYRUN FAST OPTION DOES NOT GIVE CORRECT ESTIMATE FOR DYNAMIC MEMBERS

17997117: <REQ_QUERYMEMBERS> APS WRITES OUT EMPTY ATTRIBUTES EVEN WHEN SVC IS NOT ASKING

18041741: FA:REL9 & REL9+ ONLY: ESSBASE TO CALL THE NEW PROCEDURE IN GL_JOURNAL_IMPORT_PKG

18185805: ESSSVR CORE DUMPS OBSERVED ON REL9 MAT3 DIT ENV

18243719: MDX LEAVES FN MAY GIVE A WRONG RESULT WITH A LABEL-ONLY OR IMPD SHARE DIMN MBR

18302916: OCI DATA LOADING FAILED WITH XCP ON 32- BIT PLATFORMS

18340803: NLS:REGN:DATA LOAD FAILS FOR DATA IN UNICODE WHEN USING OCI DRIVER

18421146: CAN NOT LOGIN TO ESSBASE SERVER (TIMING OUT ERROR)

18463622: APP CRASHING INTERMITTENTLY WHILE RUNNING ACCXMAIN.SH 

18468783: ASO DEFAULT TABLESPACE GROWS TREMENDOUSLY WHEN RUNNING LIGHTWEIGHT DATALOAD

18498167: ASO SHOULD LOG MESSAGE TO LOG WHENEVER THE SLICE MERGE 

 

BISERVER

New bug fixes in this patch are shown in the following list:


17556109: RANK OF A MEASURE IN PIVOT VIEW ERROR WHEN "TREAT AS AN ATTRIBUTE COLUMN" USED

17707118: PSR:PERF:BI QUERY RETURNING LARGE NUMBER OF UNEXPECTED BYTES

17857998: UPGRADED REPORT WITH REPORT_AGGREGATE FUNCTION FAILS WITH NQSERROR: 23004

18076980: YAF:PRODUCTION: NOT ABLE TO RUN ANY BIP REPORTS

18301329: SUBTOTAL GROUP BY CLAUSE CANNOT CONTAIN AGGREGATE ERROR IF SUBTOTAL ON SCALAR


Presentation Services

New bug fixes in this patch are shown in the following list:

14279471: EXPORTED EXCEL FILE NAME IS DIFFERENT FROM LOCALIZED NAME WHEN LOCALE=DEUTSCH

14381076: ACTION LINK NOT WORKING CORRECTLY WITH CALCULATED ITEMS AND GROUPS

14618928: TIME RUN AND LOGO IMAGE DISPLAYED NOT SAME AS IN ANSWERS AFTER EXPORT TO EXCEL

15906090: THE INCORRECT VALUES ARE EXPORTED TO EXCEL BEFORE PUSHING "APPLY" BOTTON.

16393953: REPORT REFRESH AND SLIDER SELECTION CAUSES BROWSER HANGIN MASTER DETAIL REPORT

16721351: PROBLEM WITH QUOTES ESCAPE SEQUENCE IN CASE STATEMENT

16743740: RUN DASHBOARD PROMPT CHANGED DEFAULT SELECTION WITH DASHBOARD SCOPE

16987088: WARNING POPS WHEN WRITEBACK-'TOGGLE TABLE MODE' OFF ON IE8

17254844: BIPS TO USE VERSION-BASED FILTERED SERIALIZATION ON SERVER

17308210: REPORTEDITORSVS.INITIALIZE(REPORTDEFN, REPORTEDITORSVSOPTS) FAILS

17390179: DATAMODELSVS.GETDATAMODELSFORREPORTXML THROWS EXCEPTION

17504416: FORMATTING ISSUES WHEN CLICKED ANY DASHBOARD SECTION

17612183: QA:IE11: ADD BIPS SUPPORT FOR IE11 USERAGENT STRING

17624367: BI 5% OF CPU WAS SPENT ON SAWSTL::VECTOR<>::PUSH_BACK

17742658: DASHBOARD CONDITIONS NOT WORKING WHEN USING REPORTS BASED ON PRESENTATION VARIAB

17847398: OBIPS NEED TO OPTIMIZE LAYER FORMATTING FOR EXPORTING REPORTS

17875975: IE11: UNABLE TO ADD FILTERS ON CRITERIA TAB OR FRM MAIN MENU IN ANSWERS

17896330: AGENT REPLACING "SPECIFIED BY USER" FIELD

17924548: QABLK: IE11: CHART IS NOT RENDERING AND PERFORMANCE ISSUES IN DASHBOARDS

18068186: MAP VIEW BI DATA LAYER SCALE VALUE NOT DISPLAYED APPROPRIATELY

18070780: MHT FORMAT OF BRIEFING BOOK IS NOT CORRECT ANYMORE

18115154: NOT ABLE TO SORT DATA WITH PERCENTAGE COLUMN

18126951: LOV ON CHAINED 3+ CONSTRAINED PROMPT IS EMPTY AFTER CHANGING HIGHER PROMPT

18143637: REQUEST VARIABLE INITIALIZED WITH WRONG VALUE WHEN DEFINED BY A SQL RESULT

18170766: PROVIDE API TO SORT ON SHARE OF GRAIN COLUMNS IN QM

18201501: OVERRIDE DATAVIEW OPTION NOT WORKING IF SETTING TRUE IN INSTANCECONFIG

18219689: PROCESS FILES THAT ARE EXPLICTLY LISTED IN THE DIFF FILE.

18224096: QABLK: IE11: TRELLIS/TABLE/PIVOT VIEW EDITORS NO WORKIN IN IE11 BROWSER

18224732: QA: IE11: > SHUTTLE BUTTON NO EFFECT EDIT COL FORMULA DIALOG, NEW CALC MEAS DIA

18225942: QA: IE11: DONE/REVERT BUTTON NO SHOW IN EDITOR PREVIEW TRELLIS/TABLE/PIVOT

18225991: QA: IE11: STATIC TXT,NARRATIVE VIW EDITOR FONTSTYLE NO APPLY BOLD ITALIC UNDRLIN

18276265: BUG 17857300 DID NOT FIX SCORECARD ISSUE

18282484: BIP REPORT IN BB FAILS TO PRINT TO PDF RESULTING IN NULLPOINTEREXCEPTION

18303025: ONE LINE APPEARED AFTER THE 'GET MORE ROWS'

18303739: ACTION LINK IS NOT PASSING FILTER VALUES TO DASHBOARD PROMPT

18386603: REQUEST VARIABLE IS NOT UPDATED FROM THE PROMPT VALUE FOR LONG RUNNING QUERY

18411651: QA:IE11: COPY LINK DISAPPEARED AFTER CLICK OF REFRESH WITHIN THE DB PAGE.

18429016: QABLK:IE11: UNABLE TO IMPORT LAYERS OR BACKGROUND MAP IN MAP ADMIN PAGE

18733369: TABBING NOT VISIBLE IN AGENT EDIT PAGE

Remember to upgrade the BI Administration Clients with the 18507823 patch content that comes in the bundle patch (not relevant for Windows servers).

You might want to look at note OBIEE 11g: How To Improve the Functionality of Exporting Data from Analyses, Dashboards, and other Oracle BI Presentation Catalog Objects into Microsoft Excel with OBIEE 11.1.1.7.131017 and Later (Doc ID 1595671.1)

Tuesday, May 27, 2014

OBIEE with Microsoft Analysis Services - The no parameter Evaluate

In this post (done on OBIEE 11.1.1.7.xxx) I will describe a trick of using the OBIEE Evaluate function without parameters with Analysis Services. In OLAP databases (MS Analysis Services and Essbase it's critical).

Evaluate is a function that enables the OBIEE to call native database functions. You can find an Oracle white paper about it here (and it is described, with all other OBIEE functions, in appendix C of the Repository Developers Guide). 

From that guide:

Syntax of Evaluate:

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:
db_function is any valid database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result.
Use this parameter whenever the return data type cannot be reliably predicted from
the input arguments. However, do not use this parameter for type casting; if the
function needs to return a particular data type, add an explicit cast. You can typically
omit this parameter when the database-specific function has a return type not
supported by the Oracle BI Server, but is used to generate an intermediate result that
does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.
Example of Evaluate:

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Example of Evaluate_AGGR (used for aggregation functions):

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic

From some point in OBIEE, Evaluate can work without parameters as well, for example to get the system date from oracle DB, the following would work EVALUATE('sysdate') .


We will use the regular Evaluate and Evaluate_Aggr here.

When working with Microsoft Analysis Services (and Essbase) I have to use the format of the column the way they described in the Physical Layer.
For example:
  • For dimension "My Dim"  with hierarchy "hier1", I have to address the hierarchy as [My Dim].[hier1]
  • Based on that Hierarchy lets assume I have a folder "Customer" with column "Customer". It's addressed as usual "Customer"."Customer"

You can see some nice example of Essbase  Evaluate samples here at gerardnico.com and here at essbaseobiee.blogspot.com (the Microsoft Analysis Services cases are very similar).


In Microsoft Analysis Services (and Essbase), there are many cases we want to produce an MDX without any parameters.
Example:
I want to see the name of a column one level above my current customer (Parent), I can issue the following MDX:
[My Dim].[hier1].CurrentMember.Parent.Name.
In most cases if I try to get the same functionality in regular code  created by OBIEE or other BI tools, I'll will have a join between levels of hierarchy, so I would rather call the MDX from EVALUATE function.

Unfortunately running  
Evaluate('[My Dim].[hier1].CurrentMember.Parent.Name' AS CHARACTER(40)) will not work.
I will get something like:



View Display Error

Odbc driver returned an error (SQLExecDirectW).


Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 22001] Target data source does not support the Complex Expression operation. (HY000)


I need to pass as parameter a value from that hierarchy level, I working on. On the other hand I don't really need a parameter here. We solve the problem by setting the parameter as remark:
Lets return to my example with   "Customer"."Customer", I have to issue the following code:
Evaluate('/*%1*/ [My Dim].[hier1].CurrentMember.Parent.Name' AS CHARACTER(40), "Customer"."Customer")

 So the actual MDX would be /*"Customer"."Customer"*/ [My Dim].[hier1].CurrentMember.Parent.Name that is the wanted MDX, with a remark. 

This option works well.

So should similar code such as /*%1*/ [My Dim].[hier1].CurrentMember.Properties("THEProperyName")




Another example with EVALUAT_ AGGR that returns the Revenue of the previous 6 month (thank you Dan Wities from Matomy for the example):


Evaluate_AGGR('/*%1*/sum({[Month Set].Item(0).LastChild.Lag(7):[Month Set].Item(0).LastChild.Lag(1)},[Measures].[Revenue])' as DOUBLE, "Customer"."Customer")


It returns the Revenue of the last value from first tuple ( item(0) ) of month set (in that case, the current month) -7,  till the same -1 month, meaning the previous 6 month. (this is specific for that data structure).



P.S. 1: In case some OBIEE Essbase expert have a better solution, I'll be happy to hear.
P.S. 2: There is a similar idea, done on Essbase and OBIEE 10, from everythingoracle.com here

 

Monday, May 19, 2014

OBIEE with Microsoft Analysis Services (and Tabular) - Real Life Guide

If you search the net, you might find lots of posts about setting OBIEE to work with Microsoft Analysis Services as data source (here, here, here... The first one, by Suman Busireddy from KPI Partners is the most recent and very nice) . Unfortunately most of them seem to be based on small cases or personal trials of the writer. When you actually work with it, there are few additional things to be aware of. This is the main focus of this post.

The first part is to set up the IIS to work with your relevant version of Microsoft Analysis Services. You need to follow the Microsoft document Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0 (the default version at the moment is SQL Server 2014, you might need the ""Other Versions" option for 2012 or 2008). The above mentioned posts did something very similar, but in real life you need the local System Administrator, to be involved, since in different companies, you will find various approaches to security. (Most of the problems are with the "Identity" definition and the "Connect By" option, that sometimes requires a specific user defined). 
Once you are done with the IIS setup you have a MSMDPUMP.INI file, you edited in Step 4 of that document. NEVER leave it as Microsoft recommends in the note. The default is to support very small amount of parallel connections. As a result, with any Dashboard with over 4 analysis you will see random errors.

The solution is to make additional modifications to the ini file. This is how I like it:

instead of Microsoft recommendation:
<ConfigurationSettings>
    <ServerName>localhost</ServerName>
    <SessionTimeout>3600</SessionTimeout>
    <ConnectionPoolSize>100</ConnectionPoolSize>
</ConfigurationSettings>
I prefer:
<ConfigurationSettings>
    <ServerName>SQL_SERVER_2012</ServerName>
    <SessionTimeout>3600</SessionTimeout>
    <ConnectionPoolSize>200</ConnectionPoolSize>
    <MinThreadPoolSize>200</MinThreadPoolSize>
    <MaxThreadPoolSize>500</MaxThreadPoolSize>
    <MaxThreadsPerClient>100</MaxThreadsPerClient>
</ConfigurationSettings>

The server name might not be localhost. That is why the specific server name.It depends on the location of the DB and the IIS.

 It is best to test the connection ( http://localhost/OLAP/msmdpump.dll   or http://YOUR_Server_NAME/OLAP/msmdpump.dll) with SQL Server Management Studio, as advised by Microsoft.

Now you can import in OBIEE Administration the Metadata of the connection type XMLA with the relevant URL  (http://localhost/OLAP/msmdpump.dll   or http://YOUR_Server_NAME/OLAP/msmdpump.dll).

Few additional Points

After the import, it is very common to see many similar hierarchies in the each Dimension.
You have can follow the advise of Suman Busireddy from KPI Partners here and delete some. Since every Hierarchy in Microsoft creates columns in OBIEE you might have several columns that seems like the same one (day, day#1...) It is very important to use the one that is your main Hierarchy in Database, otherwise you might create unnecessary "joins" in the query (If you use day from hierarchy A and Month from Hierarchy B from the same dimension, for example).



You might also see many KEYxx and MEMBER_VALUExx redundant columns, you might want to remove them from the presentation layer. There are rare case you might want to use them.  


In Physical layer the default definition of Measures is External Aggregation. There are few very rare cases you might want to change. Remember it will always have performance price, since the OBIEE server will be forced to make the calculations locally.
 


In Physical Layer, you might want to modify date columns do be defined as Date and not the typial Varchar(10).



If you create a cube from scratch, it might be a good idea to create unique names in different columns and hierarchies.


Most columns in OBIEE have descriptor ID and as a result use it rather than the value you see in the column. To see the actual descriptor id, you can always create a filter on a value in analysis and select "convert to SQL".
After you discover the actual format, you can often mimic it when you try to navigate from cube to relational data.


One last but critical topic (that is relevant to Essbase users as well): In Connection Pool, mark the "use session" option. If you don't, when you cancel a request, the cancellation is not passed to the database. Since we usually cancel the most problematic queries, we are tired waiting for. It is important to cancel those queries on database as well.





Next time I will talk about specific use cases of Evaluate, to improve performance.