Monday, 25 March 2013

Oracle BI Application 7.9.6.3 - Intra Day ETL with Micro Execution Plan


Abstract
The regular execution plan for BI Apps includes one or more subject area (facts, dimension and other tables), e.g.  Finance, HR and Spend and Procurement etc. More the subject area is added to an execution plan, the longer the ETL process runs.
The regular execution plan is set to run overnight to avoid its impact on source system during working day, which makes perfect sense.  Some specific users may find reports are stale if data is only refreshed once nightly, for example financial reports, invoices on hold as of now etc.
I have seen many customer would like to refresh data as and when require in some areas e.g. finance over the month end to make sure that the current status is being reflected in month end board reporting etc.
Micro ETL execution plan can refresh BI Apps results of small subject area or subset for hourly or half hourly basis or on-demand to cope up with one off requirement such as month end etc.
This blog aim is to discuss the problem and solution for intraday data updates with Micro Execution Plan, the features and limitations of the proposed solution is also discussed in subsequent sections.
Problem Description
The problem description is explained below in terms of what are the challenges and business requirement.
 Challenges
  • Users can not have access to reports with latest changed data sets
  • Users are forced to wait for a day to finish over night incremental data load
  • Users are forced to source data directly from E-business Suit or any source system for critical reports which often end in performance issues on application
Requirement
  • User wanted a  data load system which should facilitate on demand incremental data load for chosen subject area
  • User want to avoid to report directly against source data to avoid performance issues on application
  •  The system needs to report the data in near real time for business critical reports
  • The user also want to have a system which performs faster by using same BI APPS reporting metadata and also should have no impact on source system
  • The system needs to update BI Apps on demand for selected subject are particularly over a month end /quarter or year end processes.
Solution
Micro ETL execution plan is created to accommodate a business need for specific users to access updated subsets of data at frequent intervals. A micro ETL execution plan can refresh the results of a small subject area or a subset of star schema hourly or half hourly.
Idea is to create a micro version of the ETL with fact and dimension required for a set of critical reports/dashboard.  The micro ETL would extract the changed data over a specified period. The subset of data extracted for a micro ETL execution plan will be updated and patched during a regularly scheduled nightly ETL process.


As BI Apps tasks have update strategies that can update the data even though it has already been loaded into the data warehouse. It is very essential to make sure that any custom mappings participating in the Micro ETL execution should have update strategies in place.
Detail of Solution
Procedure to design a Micro ETL Execution Plan is follows
Step 1: Choose source system container from drop down list in the Design View
Step 2: Subject Are Tab > Click New > Enter Name e.g. Finance Micro ETL & click Save.
Step 3: In the Tables sub-tab, click Add/Remove > Choose Tables dialog is displayed > Query for one or more tables > Select the fact table one or more > click Add > Click OK to close the Choose Table dialog
Step 4: Click Assemble in the Subject Areas tab toolbar > In the Assembling dialog, select Selected record only > The tree view on the left side of the Subject Area Assembly dialog displays the fact tables that belong to the subject area. You can expand the fact table node to view its related tables > Deselect unnecessary fact and dimension tables
Step 5: Click Calculate Task List to assemble the tasks needed to load the tables displayed in the tree view > Accept to complete the subject area assembly process.
Step 6: Inactive any unnecessary tasks > Reassemble the subject are by clicking on Assemble.
Solution Considerations
  • The Micro ETL can cause issues with data inconsistencies, data availability and additional load on the transactional database
  • Reports spans the cross star may be inaccurate, when one of the star (fact) is refreshed more often than other.
  • Dimension table omission from micro ETL may result into reporting as unspecified record
  • Omission of aggregate table in micro ETL execution plans may result in inconsistent with the reports that use data from the detail fact  tables  while adding aggregate tables in micro ETL execution plan may be inefficient to perform that quite often
  •  Soft deletes and all downstream post load processes such as GL Reconciliation are must to include into micro ETL execution plan to avoid mismatches between transactions and balances e.g. Financial BI Apps
  •  Inclusion of Hierarchy rebuilt in Micro ETL execution plan may result in inefficient data load , but exclusion would result into data inconsistencies
Products Involved
DAC 10/11g
Business Benefits
Business Performance
  • Business users are better equipped with standard or on demand refresh mechanism for critical reporting phase
  • The users are able to avoid E-Business data extraction and processed via home grown excel factory to generate reporting packages for board reports
Cost
  • Single system for reporting avoids the multiple silos of systems and their maintenance
  • Near to real-time reporting will enable faster decision-making/ reduce losses
Operation
  • Near real-time reporting helps them to respond and manage crisis situations more efficiently
My Inference
The well informed business about the frequency of micro ETL execution plans, its impact on the dashboard results and critical reports throughout the day is the key of success. I would suggest weighing benefits against the limitations within which solution works and deciding.


Saturday, 16 March 2013

OBIEE 11g 11.1.1.6.9 is Available For BI Enterprise and Exalytics [ID 1536004.1]

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.6.0 to 11.1.1.6.8 [Release 11g]
Oracle Exalytics Software - Version 1.0.0.1.0 to 1.0.0.1.0 [Release 1.0]
Business Intelligence Server Enterprise Edition - Version 11.1.1.6.0 to 11.1.1.6.8 [Release 11g]
Information in this document applies to any platform.

Details

The Business Intelligence Enterprise Edition 11.1.1.6.9 patch set has been released and is available to download from My Oracle Support.

Per the patch readme: 
  • This patch set is available for all customers who are using Oracle Business Intelligence Enterprise Edition 11.1.1.6.0, 11.1.1.6.1, 11.1.1.6.2, 11.1.1.6.2 BP1, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7 and 11.1.1.6.8. 
  • Oracle Exalytics customers must only install this Oracle Business Intelligence patch set if it is certified for the specific Oracle Exalytics patch or patch set update that they are applying. For more information see Oracle Fusion Middleware Installation and Administration Guide for Oracle Exalytics In-Memory Machine and the Oracle Exalytics certification information.

The Oracle Business Intelligence 11.1.1.6.9 patch set is comprised of the following patches:

    Patch 16287811 (1 of 7) Oracle Business Intelligence Installer. (generic)
    Patch 16287778 (2 of 7) Oracle Real Time Decisions. (generic)
    Patch 16237960 (3 of 7) Oracle Business Intelligence Publisher. (generic)
    Patch 16287840 (4 of 7) Oracle Business Intelligence ADF Components. (generic)
    Patch 16287854 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x. (port-specific)
    Patch 16227549 (6 of 7) Oracle Business Intelligence. (port-specific)
    Patch 16287884 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer  (generic)


Note:
  • The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch.
  • This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7 and 11.1.1.6.8 patch sets.
  • However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files.
  • The instructions to apply the above patches are identical, and are contained in the readme file for patch 16287811.
  • Please bear in mind, that the readme states to apply patch 13952743 for JDeveloper, too.
List of bugs are as follows ....

13404296 BI PROVISIONING FACADE MUST TRIM RPD IN INSTANCE FOR SELECTED FA OFFERINGS
13485655 T2P: ERROR MSG EXECUTING WLST SCRIPT APPEARS DURING BIINSTANCE PASTECONFIG
13504454 BLK: BIINSTANCE COPYCONFIG FAILURE: ESSBASE MAXL LOGIN IS FAILING 
13510938 NEED OFFICIAL WAY TO INVOKE _CONFIGURERPD
13560245 BIDOMAIN/CONFIG/JDBC NOT FOUND
13565722 BLK: BI T2P PASTECONFIG ERRORED OUT WITH UNABLE TO PARSE JMS_WEBLOGIC_JNDI_URL
13589944 BI T2P: PASTECONFIG INSTANCE IS FAILING WITH JAVA.LANG.UNSATISFIEDLINKERROR
13619973 INCLUDE ESSSERVER HOST NAME IN THE MOVEPLAN
13628293 EMPTY PROCUREMENT DASHBOARD IN RC4
13683204 STRESS:FA:CRM ERROR RESPONSE[NQSERROR: 43082] ILLEGAL OPERATION ATTEMPTED CLOSED

13240778 QA: ACCESS: ERE: RGRN: ERROR ACCESSING LOV VIA KEY; CAN'T SELECT VIA KEY
13347251 QA: UNABLE TO SEE ERE IN "DYNAMIC" PAGE IN SAFARI, CHROME
13360752 UNABLE TO LOAD ALL EXTENSION COLUMNS FOR CUSTOMER IN RTD STUDIO
13408870 QA: SDCONFIG 3X VALUES COPIED TO 11G, BUT NOT USED THERE
13409526 QA: UPDATE DOC, COPYRTDDATA_README.TXT FOR STOP/START 11G
13472560 QA:ACCESS:HIGH CONTRAST SETTING APPEARS CLUTTERED, SOME ITEMS NOT VISIBLE
13506987 COPYRIGHT INFORMATION NEEDS TO BE EXTENDED TO 2012
13537093 BACKPORT BUG 13537093 TO 11.1.1.6.2
13539353 BACKPORT BUG 13539353 TO 11.1.1.6.2
13567294 REMOVED 1ST VALUE IN RULE BECOMES NAN

10022187 ACC: SELECTED ITEM IN FOLDERS PANE GETS LOST AFTER USING UP/DOWN ARROW TOO MUCH
10046249 ACC: SCHEDULE PAGES HAVE "GRIDTABLE" TABLES THAT CAN'T BE NAVIGATED
11678983 QA:LDAP CONFIG ASSUME ADMIN IS DEFINED UNDER DISTINGUISHED NAME FOR USERS
11906714 QA: TIMEZONE NOT IN SYNC IN JOB MANAGER - JOB HIISTORY
12312461 NLS:MB DATA GARBLED IN JOB HISTORY USING SQL SERVER
12542914 ACC: REPORT VIEWER STRUCTURE HAS ERRORS - NO IFRAME AND NO LANG ATTRIBUTE
12645567 NLS:WRONG WEEK OF YEAR FOR ORACLE DATE FORMAT CONTAINING WW
12825409 INTERACTIVE VIEWER CONDITIONAL FORMATTING FAILS FOR NON DISPLAY COLUMN
12877824 WEBSERVICE API - GETSCHEDULEDREPORTSTATUS ALWAYS RETURNS JOB STATUS AS SCHEDULED
12912473 BI PUBLISHER 11G SHARE REPORT XMODE=3 AND XMODE=4 NOT WORKING FOR PDF OUTPUT

13365954 ERROR WHILE OVERWRITING AN ANALYSIS VIA BI COMPOSER
13520338 STRESS:FA:CRM:SPD HEAVY CONTENTION ON FACTORYMANAGER.CREATEMODELOBJECT
13597290 11.1.1.7.0 ESSBASE DATA CONTROL CODE IS PRINTING UNWANTED DEBUG MESSAGES
13733098 REPORTS AND ANALYTICS, CANNOT SAVE NEWLY CREATED REPORT
13789351 RUP2 ST3 (PS5 RC4) - NPE WHILE TESTING OL FLOW IN KOREAN LANGUAGE
13858848 BIADFCOMPS - ANALYSIS CREATED USING COMBINE UNION IS NOT EDITABLE IN BI COMPOSER
13958089 BI COMPOSER: CAN NOT SAVE ANALYSIS AFTER FAILOVER WLS MANAGE SERVER
13973674 MAIN: NAMECLASHEXCEPTION WHEN MULTIPLE INSTANCES OF ESSBASEDC ARE LOADED
14081217 STRESS:FA:CRM:SPD BIADAPTERDCSERVICE HOLDING LARGE IN HEAP CAUSING OOM
14183506 ALWAYS SAME NPE AND STACKTRACE NO MATTER THE PROBLEM IS

10623654 FA: NLS:PS1:DATE AND TIME NOT LOCALIZED IN FR SCHEDULED BATCHES DETAIL INFO.
11652873 FA: FR ANNOTATION VIEW PANE HAS PERMISSION ISSUES FOR ANNOTATION ON TEXT OB
11735177 REHOSTING TOOL IS NOT TRANSFERRING PROPER USERNAME IN PARTITION DEFINITION
11924085 UNABLE TO RUN AN ESSBASE CALC SCRIPT IN WEB ANALYSIS
12404364 ESSBASE READ ONLY ACCESS INSUFFICIENT PRIVILEGE WITH PROMPT
12530876 PERFORMANCE OF FR REPORT QUERYING ESSBASE W/LARGE NUMBER OF DYNAMIC CALCULATIONS
12601370 FUSION B17: SAVED QUERY DOES NOT REFRESH, BUT NEW QUERY WORKS ON THOSE MEMBERS
12722750 FA: FF 5.0 GIVES BLANK SCREEN FOR HTML BOOK PREVIEW
12723793 NEED DIFFERENT URL FOR SMARTVIEW INSTALLER IN FUSION MODE
12765822 DMS LOGS LOTS OF ERRORS WHEN ESSBASESERVER1 IS DOWN

10137467 ENABLE "ZOOM TO DATA RANGE " IN OBIEE 11G
10165057 QA:THE EDGES OF MAP VIEW GOT TRUNCATED AFTER EXPORTED/PRINTED TO PDF/HTML
10167311 ADD TEST CASES FOR MDSXUDML GENERATOR AND PARSER
10199188 CHARTS X AXIS LABEL IS BLURRED AT 45/60 ANGLE
10391144 WEIRD BEHAVIOUR OF FILE OPEN DIALOG ON WINDOWS 7 COMPUTER
10409969 BH9:QA: TIMESTEN ODBC ERR INCOMPATIBLE FACT IF CONDITION STEP FEDERATED MEASUR
10419667 UT:USAGE TRACKING ERROR TEXT OF FAILED QUERY IS NOT LOGGED
10647655 COLUMN DATA FORMAT IS IGNORED ON WRITEBACK
11657725 QA:REG:MALFORMED URL ERROR WHEN PRINT KPI WATCHLIST TO PDF ON DASHBOARD PAGE
11810127 ADMINTOOL TESTS FAIL: EXTERNALIZATION


13794002 BISHIPHOME 11.1.1.6.2 CUMULATIVE PATCH 1
14054848 RUP4: BISHIPHOME CLIENT INSTALL 11.1.1.6.3 PATCH 1
14533251 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 2
14646289 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 3
14672002 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 4
14711062 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 5
14712346 11.1.1.6.5 CLIENT INSTALLER AND MAPVIEWER PATCH
14740701 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 6
15839347 11.1.1.6.6 CLIENT INSTALLER AND MAPVIEWER PATCH
15860153 REL6: BISHIPHOME CLIENT INSTALL 11.1.1.6.31 PATCH 7

I hope you find this copy and pasted information useful.