Thursday, January 21, 2016

OBIEE 12.2.1 controlling VA - data visualization

With the release of OBIEE 12.2.1, we have a new component named Data Visualization (or Visual Analyzer - that what the VA in URL stands for). I will call it VA in this post.  Naturally we want to control who can use it, or disable it. Yet, the control is not at the expected place (Administration/Manage Privileges). By default it is assigned to BI Author Application role and is controlled by the Application policy oracle.bi.tech.visualanalyzer.generalAccess.

If all you want is to enable / disable it, there is a script and guidelines at blogs.oracle.com, by Amarpreet Nagra: How to Revoke/Grant Data Visualizations Permissions using WLST

Since that is covered, there is one more question: How to change the access to VA. In this post I will describe how to allow access to VA by BI Composer Role users. I'm not sure it's a good idea since VA is very much equivalent to BI Author.

Update: To control the VA I advise to follow the guidelines at Oracle support document 2102444.1: How To Remove Or Add Specific OBIEE 12c Data Visualization | Visual Analyzer Permissions and not the described bellow.




--------------------------------------------------------------




In Enterprise Manager, under biinstance / security we see the Application Policies and Roles (the same is available under WeblogicDomain - security).


In the list of policies, under BIContentAuthor we see the oracle.bi.tech.visualanalyzer.generalAccess policy.

Our task is to add that policy to BIConsumer Role. 
I Edit that role.
Press the plus sign of Add Permission.

Option1

Search in the third Permission Class (oracle.security.jps.ResourcePermission) for relevant Resource name, in the bellow example Resource Name that includes the string visual.


I select the oracle.bi.tech.visualanalyzer.generalAccess policy and press continue. In the next screen I mark all and press select.


Option 2

Absolutely equivalent is to search for Resource Type  oracle.bi.visualanalyzer.permission.

 


After I OK the change in Application Role. This is updated.
I had to restart the server for the change to work.


Accessing the VA with unprivileged users returns rather ugly Error 401- Unauthorized:
 After the change I could connect with BIConsumer user.


As BIConsumer Role I can open existing VA projects.
If I try to create a new one, I can't see the subject areas but capable of uploading new files as data source.  


OBIEE Bundle Patch 11.1.1.7.160119 is available

OBIEE 11.1.1.7 bundle patch is available:
Patch 22225110: BI BUNDLE PATCH 11.1.1.7.160119 is available from Oracle.

This patch is also recommended for security reasons.

It is available for:
  • Linux x86
  • Linux x86-64
  • Microsoft Windows 32-bit
  • Microsoft Windows 64-bit
  • HP-UX Itanium
  • IBM AIX on POWER system (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86-64 (64-bit)

You can see the readme file here. It includes the installation instructions as well.

The patch includes the following components:
Patch
Abstract
16913445
Oracle Business Intelligence Installer (BIINST)
22225969
Oracle Business Intelligence Publisher (BIP)
19825503
Enterprise Performance Management Components Installed from BI Installer 11.1.1.7.0 (BIFNDNEPM)
22225023
Oracle Business Intelligence Server (BISERVER)
22224995
Oracle Business Intelligence Presentation Services (BIPS) (BIFNDN)
19823874
Oracle Real-Time Decisions (RTD)
16997936
Oracle Business Intelligence ADF Components (BIADFCOMPS)
22225040
Oracle Business Intelligence Platform Client Installers and MapViewer
21235729
Oracle Business Intelligence Third Party

The patch includes the following bug fixes:

Oracle Business Intelligence Publisher (BIP)
19511269 - Fix for Bug 19511269
20884793 - Fix for Bug 20884793
21766173 - Fix for Bug 21766173
21766306 - Fix for Bug 21766306
21766708 - Fix for Bug 21766708

21766161- Fix for Bug 21766161
21911881 - Fix for Bug 21911881

Section 8.9: Oracle Business Intelligence Third Party
20919700 - Fix for Bug 20919700.




OBIEE 11.1.1.9.160119 bundle patch is available

OBIEE 11.1.1.9 bundle patch is available:
Patch 22393988: BI BUNDLE PATCH 11.1.1.9.160119 is available from Oracle.

This patch is also recommended for security reasons.

It is available for:
  • Linux x86
  • Linux x86-64
  • Microsoft Windows 64bit
  • HP-UX Itanium
  • IBM AIX on POWER system (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86-64 (64-bit)

You can see the readme file here. It includes the installation instructions as well.

The patch includes the following components:
Patch Abstract
22382217 Oracle Business Intelligence Publisher (BIP)
22322354 Oracle BI Mobile App Designer (BIMAD)
22475251 Oracle Business Intelligence Server (BISERVER)
22472013 Oracle Business Intelligence Presentation Services (BIPS)
22220714 Oracle Enterprise Performance Management Components Installed from Oracle BI Installer 11.1.1.9.0 (BIFNDNEPM)
21350584 Oracle Business Intelligence ADF Components (BIADFCOMPS)
22322838 Oracle Business Intelligence Platform Client Installers and MapViewer
21517672 Oracle Business Intelligence Third Party



Bugs fixed by this parch:

Oracle Business Intelligence Publisher (BIP)

New bug fixes in this patch are shown in the following list:
22364786 : ENABLE SQL PRUNING CAUSES INVALID SYNTAX ERROR WHEN DATASET NAME CONTAINS HYPHON
21108401 : CLASS CAST EXCEPTION WHEN USING LOCAL XML FILES AS DATA SOURCE
20354279 : DM EDITOR UI CHANGES REQUIRED TO GENERATE EXPLAIN PLAN OUTPUT
20354219 : BIP SCHEDULER CHANGES REQUIRED FOR EXPLAIN PLAN OUTPUT
21124839 : SECURITY ISSUES RAISED BY FORTIFY (CORE)
21766708 : Fix for this bug
20239697 : SAAS FA USING BIP CUSTOMERS NEED ACCESS TO EXPLAIN PLAN OUTPUT
21766306 : Fix for this bug
21766173 : Fix for this bug
21843699 : FLATTENXML CSV OUTPUT CANCEL FEATURE
20533206 : NEED A NEW PROPERTY TO SET MAX ROWS FOR CSV OUTPUT (FLATTENXML)
20429563 : ALTA:QA:FF - OLB - REPEATING SECTION DROP DOWN ICON MISSING
19597688 : ESS: THE ORIGINAL SCHEDULE JOB INFO SHOULD BE DISPLAYED AFTER CLICKING "EDIT"
21765871 : SCHEDULING A JOB FOR EXTERNAL FTP SERVERS FAIL WITH BROKEN PIPE
18942127 : BI PUBLISHER MANAGE JOB HISTORY PERFORMANCE IS SLOW
20760566 : ER: POSITION A TABLE AT THE BOTTOM OF THE PAGE IN AN RTF TEMPLATE
20323675 : PS: SECURE BY DEFAULT - RESTRICT ACCESS TO DTD VALIDATIONS IN XML PARSING
20124391 : PS: BIP 11.1.1.9.0 TAGS HTML2FO ARE CAUSING THE TEXT TO DISPLAY IN ARIAL
20704727 : FAREL11: VIEW DATA ONLY SHOWS EFFVO COLS WHEN DM CONTAINS REGULAR AND EFFVO BOTH
20737378 : DATE PARAMETERS, WHEN PROFILE IS NOT ENGLISH, DO NOT PRODUCE RESULTS.
21549197 : SFTP TRANSMISSION FAILURE
21484614 : QA: ALTA DATAMODEL PRIVATE DATASOURCE WINDOW SHOWING OLD THEME
21697453 : SQL PRUNING: MULTIPLE TEMPLATE OUTPUTS IN SCHEDULE PAGE RESULTS IN AN ERROR
20912569 : DATES BEYOND 12/31/2099 ARE SHOWN WRONG WHEN EXPORTED TO EXCEL
20887301 : NLS:RGRN:ERROR OCCURRED IF OPENING THE DM UNDER THE FOLDERNAME WHICH CONTAINS '.
16636721 : QA: QUERY BUILDER ISSUES RELATED TO JOIN AND TABLES SELECTED
20713159 - BI PUBLISHER REPORT EXPORT TO EXCEL SHOWS NUMBER FIELDS AS TEXT
21051661 - POLISH NUMBER SPELLOUT IS INCORRECT
21305192 - IE11 -QA:OLB -LAYOUT EDITOR LEFT PANEL HAS UNDERLYING TEXT UNDER PROPERTIES PANE

Oracle BI Mobile App Designer (BIMAD)

New bug fixes in this patch are shown in the following list:
20747276 - NO ADMIN HEADER AREA IN ALTA
21969983 - PATCH FOR BUG#21323932 DOES NOT FIX FILTER WITH COMMA VALUE ISSUE ON PRODUCTION
19812568 - 11.1.1.9.0:MAD:AFTER SIGN OUT IN APP STORE,LOG IN AGAIN ERROR SHOWS
21266156 - APPLYING_ONLINE_BI_METADATA_AND_CONFIGURATION DUE TO RO OH DEPENDENCIES
20540869 - MAD PLUGINS SHOULD NOT SHOW UP IN BIP

Oracle Business Intelligence Server (BISERVER)

New bug fixes in this patch are shown in the following list:
22278353 - TRACKING BUG FOR DELETING OPENSSL 1.0.0 FROM BITHIRDPARTY 7X LABEL
20919700 - Fix for this bug
22295419 - ADMINTOOL CRASHED DURING IMPORT THROUGH EXISTING CONNECTION POOL - ORACLE ADF
22257019 - BI BUG 16843663 CHANGES CAUSE REGRESSION FOR CSA REPUBLISH
22262062 - TRACKING BUG FOR BISERVER CHANGES FOR 7X RELEASE FOR BASE BUG 20919700
22119513 - CSAS DISAPPEARED AFTER UPGRADE
21092272 - ENHANCE "VIEW_OBJECT_ATTRIBUTE" MODE ADF QUERIES TO RETURN 3 NEW COLUMNS
21319889 - BIEXTENDER PILLAR OPTIONS SENT FROM ESS JOB UI LIST NEED TO BE MODIFIED
20526494 - PSR:PERF:BI:EXTENDER OPTIMIZE THE OBIEEBROKER CALL TO INCREASE THE PERFORMANCE
19848582 - EFF EXTENDED COLUMNS NOT VISIBLE IN ANSWERS
20602739 - ALL QUERIES VIA LOGIN IDS WITH ACCENTED CHARACTERS (DIACRITICS) FAIL WITH 401
21440982 - BISERVER_11.1.1.11.0BICS:CRASH IN NQSSERVER
21845112 - BI EXTENDER NEED TO SET ESSBASE LTS PRIORITY AS 3 TO GIVE PREFERENCE TO VO LTS
21026282 - VERTICAL CLONING THROUGH NQCMD PROC & BISERVERAPPLYVERTICLRULES AREN'T IDENTICAL
21194895 - REL11 VERTICALS: OBIEE CRASHES ON ADMINSERVICE SYNC
21625836 - ESSBASE RULES XML COPY TO SIMULATION FILE FOLDER FAILED ON INCREMENTAL RUN
19292255 - MUD PUBLISH INTRODUCES CONS ERROR BUT NOT THE ORDINARY ACTION(MOVING A COLUMN)
20841820 - INCLUDE NULL VALUES NOT WORKING WHEN USING GRAND TOTAL LEVEL-BASED MEASURE
21382932 - TREE COLUMNS FOR ESSBASE SEGMENTS ARE NOT EXPOSED IN INCREMENTAL BI EXT RUN
21226555 - REPORT DIAGNOSTICS: FACT PROBE QUERY SENDS DUPLICATE QUERIES
20619762 - REPORT DIAGNOSTICS: SUPPORT SECURED VO DIAGNOSTICS IN BI SERVER
20331988 - YBF: OTBI ELIGIBLE FOR SALARY REVIEW REPORT THROWS ERROR???
19682591 - ADD SUPPORT TO EXTRACTPROJECT TO TRIM THE PROJECT FURTHER BY DATA SOURCES
20315587 - R8 : INCORRECT ESSBASE SERVER CONNECTION POOL HOST SETTING IN RPD
19932728 - RPD TRIMMING: ADD CLEAN UP CODE- TO REMOVE PRES TABLE W/O COLUMNS
20784901 - EXTEND RPD PATCHING UTILITY TO HANDLE VERTICAL RULES ALONG WITH BI-EXTENDER
19943711 - BI-EXTENDER SIMULATION FILES TO BE GENERATED AT BISHARED
20085045 - RUNTIME EXCEPTION/CRASH ON SWITCHING PATCH TO FULL MERGE
20261156 - ADMINTOOL CRASHES DURING XML IMPORT WITH A VALID HTML FILE
20004254 - VERTICAL: SERVICE TO PUBLISH RULES FILE TO RPD
20744137 - CLOB COLUMN HANDLING AND SETTING PRESENTATION COLUMN CUSTOM DISPLAY PROPERTIES
19956799 - MASTERS REPOSITORY'S OBJECT PERMISSION IS NOT RETAINED IN APP ROLE AFTER PUBLISH
19974382 - INTERNAL DEV AUTOMATION COMMAND ISSUE - EXECUTEQUERY NOT WORKING
21436960 - SUPPORT HOT PATCHING OF BISERVER MEATADATA IN REL-10

Enterprise Performance Management Components (BIFNDNEPM)

New bug fixes in patch 22220714 are shown in the following list:
21661362 - SAVING REPORT WITH TWO DATASOURCES ON ONE GRID RESULTS IN ERROR TO LOG FILE
21924132 - FA REL10: NOT ABLE TO OPEN RELATED CONTENT ON FR REPORTS
21697989 - DEFAULT TO SMTP.ORACLEOUTSOURCING.COM:587 IN FA CLOUD IF HIT REGISTRY IS EMPTY
21185703 - FA:SCHEDULING THE FR BATCH GIVES GATEWAY TIMEOUT ERROR
21811585 - REPORT RUN W/POV RETURNING NO DATA FAILS TO DISPLAY RESULTS AFTER CHANGING POV
20664393 - SCHEDULE BATCH FOR PREFORM ONCE FREQUENCY IS NOT SCHEDULED AS PER THE SCH TIME
20894866 - OPENING A FIN REPORT IN EXCEL FAILS WITH: FILE IS CORRUPT AND CANNOT BE OPENED
20963365 - FA:FUSION REPORTS STUDIO REPORT VALUES HAVE ' MARKS INSTEAD NUMBER FORMATS
21524001 - FR EXPORT EXCEL AS TEXT WITH AUTO CALC
21076003 - FUSION FINS EXPORT TO EXCEL LOSES FORMAT
22092328 - MAKE FR DISCOVERABLE AS A CUSTOM TARGET IN EM
20900631 - FA: NOT ABLE TO DELETE THE OLD BATCH JOB SCHEDULED
21744128 - FR ESS CLIENT NEEDS TO BE ENHANCED TO RETRY FOR A FAILURE DUE TO BI MAINTENANCE
21924059 - FA - BOOKS DO NOT RUN AT ALL
21744367 - FR EXPORT TO EXCEL MISSING DATA
21682557 - FA:SUPPORT DMS EXECUTION METRIX
21450368 - FA:REL8: TWO COLUMNS FR REPORT GETTING MERGED AFTER EXPORT TO EXCEL
21116199 - 1 GRID W '2ND DB CONN ON COLUMN'-RUNS AT FIRST THEN CLOSE STUDIO, RE-OPEN ERRORS
22011436 - ENHANCEMENTS TO DMS EXECUTION METRICS

Section 7.6: Oracle Business Intelligence Presentation Services (BIPS)

New bug fixes are shown in the following list:
22262037 - TRACKING BUG FOR BIFNDN CHANGES FOR 7X RELEASE FOR BASE BUG 20919700
20564809 - CANNOT DRAG "TERRITORY" FIELD TO GRAPH PROMPTS OF THIS REPORTS
21911881 - Fix for this bug
21766161 - Fix for this bug
21888129 - OTBIE V3 BI_SERVER STARTS UP WITH XML PARSIN EXCEPTION IN BI_SERVER1.OUT
20425541 - QA:FIREFOX: DOWN ARROW SHOULD DISPLAY FOR THE DROP DOWN LIST
20814803 - BI METADATA RELATED REL 11 UPGRADE FAILURE
20860040 - REL11: DROP DOWN MENU ARROW IS NOT INLINE WITH THE FIELD
20564854 - CHANGING DASHBOARD PROMPT'S VALUES DOESN'T AFFECT THE ANALYSIS
21745479 - QA:RGRN: FILTER VIEWS ARE GETTING WRAPPED WHEN EXPORTED TO PDF IN 9.3
21811332 - TURN CONTENT SECURITY POLICY HEADER OFF BY DEFAULT FOR REL10
20055522 - Fix for this bug


Wednesday, January 20, 2016

OBIEE with Amazon Redshift

Moshe Romano is a great guy, working at Perion. I remember him lecturing few years ago about his implementation of OBIEE aggregation awareness with details at Oracle DB and aggregation with Microsoft OLAP cube. Later they wanted to work with OBIEE and Amazon Redshift database.  
Redshift can use ODBC  but you don’t want to limit the functionality to the general ODBC.
Perion used postgresql ODBC driver.

Since Redshift is not supported by Oracle as data source, Moshe had a long process of trial and error, to determine the correct database features for Redshift.
Don’t treat the following as any recommendation, maybe, just a first step. It should not be viewed as advice. Just description of what he did.

Moshe Romano advised:
  • Disable connection pooling.
  • Enforce schema name.
  • Don't be shy to use Evaluate functions.
  • Configure the DB features properly.

When you start with default 1.1.1.7 / 11.1.1.9 configuration of ODBC 2.0 at Administration, what are the DB features he changed?
This is the default, basic ODBC:



The following were changed:

Checked:

RIGHT_OUTER_JOIN_SUPPORTED, FULL_OUTER_JOIN_SUPPORTED, NESTED_OUTER_JOIN_SUPPORTED, UNION_SUPPORTED, UNION_ALL_SUPPORTED, FIRST_SUPPORTED, LAST_SUPPORTED, RANK_SUPPORTED, TOPN_SUPPORTED, BOTTOMN_SUPPORTED, DATE_LITERAL_SUPPORTED, TIME_LITERAL_SUPPORTED, DATE_TIME_LITERAL_SUPPORTED, CALENDAR_EXTRACT_WEEK_OF_MONTH_SUPPORTED, CALENDAR_EXTRACT_SHORT_NAME_OF_MONTH_SUPPORTED, CHAR_LENGTH_SUPPORTED, POSITION_SUPPORTED, LOCATE_SUPPORTED, MOD_SUPPORTED, SINH_SUPPORTED, COSH_SUPPORTED, TANH_SUPPORTED, TRIM_SUPPORTED, MULTIPLE_STATEMENTS_PER_CONNECTION_SUPPORTED, CAST_SUPPORTED, DERIVED_TABLES_SUPPORTED, CORRELATION_NAME_SUPPORTED, PREDICATE_SCALAR_SUBQUERY_SUPPORTED, VALUE_SUBQUERY_SUPPORTED, CORRELATED_SUBQUERY_SUPPORTED, COMPARISON_SUBQUERY_SUPPORTED, EXISTS_SUBQUERY_SUPPORTED, IN_SUBQUERY_SUPPORTED, QUANTIFIED_SUBQUERY_SUPPORTED, PERF_PREFER_IN_LISTS, STDDEV_SAMP_SUPPORTED, STDDEV_POP_SUPPORTED, EXCEPT_SUPPORTED, INTERSECT_SUPPORTED, RUNNING_COUNT_SUPPORTED, NULLIF_SUPPORTED, CREATE_AS_SELECT_SUPPORTED, INSERT_SELECT_SUPPORTED, SUBTOTALLING_SUPPORTED, ROWNUM_SUPPORTED, CREATE_VIEW_SUPPORTED, WITH_CLAUSE_SUPPORTED, PERF_PREFER_INTERNAL_STITCH_JOIN, DUAL_TABLE_SUPPORTED, SUBQUERY_IN_CASE_SUPPORTED, DENSE_RANK_SUPPORTED, MULTI_COLUMN_IN_SUPPORTED, ROWNUM_LIMIT_SUPPORTED, ROWNUM_OFFSET_SUPPORTED.





Unchecked:

NULL_VALUES_SORT_FIRST, TIMESTAMP_ADD_SUPPORTED, TIMESTAMP_DIFF_SUPPORTED, LENGTH_SUPPORTED, PI_SUPPORTED, DEGREES_SUPPORTED, RADIANS_SUPPORTED, COT_SUPPORTED, LTRIM_SUPPORTED, RTRIM_SUPPORTED, INSERT_SUPPORTED, REPEAT_SUPPORTED, CONVERT_SUPPORTED





This is the result: