Wednesday Mar 12, 2014

Troubleshooting Strategy and Processes that Execute Enormous data sets in Demantra

Hello Demantra Customers!   There is a new white paper that discusses large data set loading.

See MOS Note 1634925.1

Recently we have encountered a few scenarios where customers tried to process huge amounts of data within a Demantra process.
Examples:

  • Customer wanted to load over 50M records via EP_LOAD process on weekly bases.
  • Customer wanted to process millions of rows via BLE process on daily bases.
  • Customer wanted to load via integration interface 40M rows on weekly bases.


In all of the above the customer complained about the system inability to process the amount of data, either being too slow or it simply could not be completed at all meaning the process erred out.
In all of the above examples an escalated defect was logged, and development worked troubleshooting the problem.

An additional common theme between all the above examples and many more is that there was no real need to have a product fix although performance improvement opportunities may have been identified, they were not the final solution that addressed the problem.
What helped was understanding that there is no real need to process all the data and recognizing the data that really needed to be loaded.  The data could have been loaded in a more efficient way using some best practices and creative thinking.

The scope of this document is to provide some guidance how to troubleshoot such customer situations.
The document will focus on three main areas:

  • Learning and understanding the business needs
  • Understanding the actual data that needs to be loaded, understand the gap between this number and the number of rows that the customer actually tries to load.
  • Provide some best practices that can help the customer work with the data efficiently.


This document will not deal with the initial Data load processes although some of the best practices can be adopted for such situations as well.
We will illustrate the concepts in this document using three real life examples of service requests and/or defects that were logged on behalf of the customer.

 

Wednesday Oct 23, 2013

Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.

Hello!  Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.
This is an updated re-release of the generic Partial Schema Exporter Tool.  The generic patch is for 7.3.1.x and 12.2.x.
TABLE_REORG was introduced in 7.3.1.3 12.2.0.  Therefore for 7.3.1.x the schema must be at 7.3.1.3 or above.

This is build 3 of the patch.

It contains fixes for the following bugs
- BUG 17495971 - DEMANTRA 12.2 - CUMULATIVE HISTORY NOT CORRECT
  It now only uses DATA_PUMP COMPRESSION only on Enterprise Edition for 11g and and up.

- Bug 17452153 - 1OFF:16086475:TRYING TO FILTER DROP DOWN IN A METHOD CALL USING MORE THAN 1 ATTR
  It now builds GL level filters with and without the GL id column where applicable.

These bugs are also fixed in 7.3.1.6 and 12.2.3.

Friday Jun 21, 2013

Announcing Oracle-Demantra 12.2.1 Release

We are excited to announce Oracle Demantra 12.2.1 is now available for new and existing customers. All customers who are not incorporating Demantra with other VCP products are welcome to upgrade without any restrictions. Customers who are using Demantra in conjunction with VCP products will need to upgrade VCP to 12.2.1 which requires application and participation in Oracle E-Business Suite early adopter program.

Demantra 12.2.1 includes a wide array of new features driven by customer requirements and needs. Key features include:

·       Streamlined import and export from Microsoft Excel

·       Support Gregorian Month data aggregation in weekly system

·       Multilanguage support for eleven languages

·       Promotion Calendar Optimization

·       Enhanced integration with Advanced Planning Command Center (VCP 12.2.1 Required)

Demantra 12.2.1 will work with JD Edwards EnterpriseOne 9.1 using the AIA 11.4 for the Value Chain Planning Base Integration Pack.

Demantra 12.2.1 will only work with VCP 12.2.1.

Demantra 12.2.1 and VCP 12.2.1 will work with EBS 12.1.3 or EBS 12.2.1.

Wednesday Feb 13, 2013

Demantra Adding Partitions to SALES_DATA? Check out this 'sample' plan

Sample Action Plan to create partitions on Demantra Sales_data table.  This has been used recently and was successful.  USE at your own RISK after careful study and adjustment to meet your needs.  This was produced to use in an 11gr2 RDBMS environment.   

I will be providing a table_reorg action plan, available 7.3.1.3 and later soon for partitioned and non-partitioned tables.  These can be used to replace MyOracleSupport (MOS) note 1085012.1.

Ping me with any questions/comments.   Regards!   Jeff

conn / as sysdba
Run utlrp.sql / dbms_recomp  Recompile all the objects.
set pages 1000
set lines 300
spool demantra_object_status.lst
spool object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by 2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
spool off

1) EXP SALES_DATA TABLE

expdp Demantra/<PASSWD> table_name=SALES_DATA DIRECTORY=exportdir DUMPFILE=exptrans%U.dmp PARALLEL=3

2)Adding size to existing Tablespace to create the Partitions for NEW SALES_DATA
==================================================================================
Add 50GB of datafile space to  TABLESPACE "TS_SALES_DATA"

-Eg: Alter tablespace TS_SALES_DATA add datafile '<Location>/file_name' size n;

3) Creating  partition table for SALES_DATA and loading data into new table
===========================================================================
CREATE TABLE DEMANTRA.SALES_DATA_PART
  ((    "ITEM_ID" NUMBER(10,0) NOT NULL ENABLE,
    "LOCATION_ID" NUMBER(10,0) NOT NULL ENABLE,
    "SALES_DATE" DATE NOT NULL ENABLE,
    "ITEM_PRICE" NUMBER(20,10),
    "SYNCRO_SIG" NUMBER(1,0) DEFAULT -1,
    "LAST_UPDATE_DATE" DATE DEFAULT SYSTIMESTAMP,
.
.
.
    "TOTALDISTRIBUCION" NUMBER(20,10),
    "BUDGET_VALOR" NUMBER(20,10),
    "PRON_PLDEM" NUMBER(20,10),
    "PRECIO_FUTURO" NUMBER(20,10),
    CONSTRAINT "SALES_DATA_PK" PRIMARY KEY ("ITEM_ID", "LOCATION_ID","SALES_DATE")
              USING INDEX PCTFREE 30 INITRANS 10 MAXTRANS 255
              TABLESPACE "TS_SALES_DATA_X" ENABLE)
         SEGMENT CREATION IMMEDIATE
   PARTITION BY RANGE (SALES_DATE)
PARTITION SALES_DATA_042010 VALUES LESS THAN (TO_DATE('01-May-2010','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA1
PARTITION SALES_DATA_052010 VALUES LESS THAN (TO_DATE('01-JUN-2010','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA2
.
.
.
PARTITION SALES_DATA_112014 VALUES LESS THAN (TO_DATE('01-DEC-2014','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA15
PARTITION SALES_DATA_122014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA16
as select * from demantra.sales_data
   PCTFREE 30 PCTUSED 60 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING PARALLEL 8
   STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
   FREELISTS 5 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
   );
 
 
3) Drop  SALES_DATA cascade / Please check the Export first step is completed and moved the dump to safe place.
=============================================================================================================================
Drop table DEMANTRA.SALES_DATA cascade;

5) rename new  Table name to SALES_DATA
===================================
      alter table SALES_DATA_PART
       rename to
       SALES_DATA;

5.1) GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON  SALES_DATA TO DEMANTRA;

Run utlrp.sql / dbms_recomp
spool Invalid_objects_after_Partition.lst
select object_name,object_type,owner,status from dba_objects where status='INVALID'
spool off
   
       
6) create dependencies for SALES_DATA After Renaming table back to Sales_DATA
==============================================================================
IMP: Try to Recompile all the objects so see all the dependent objects are valid (If not use the below scripts to create or replace the objects)

- Recreate the indexes on Sales_Data Table (Local)
i) Script to create the indexes on Sales_data table is sales_data_index_ddl.txt

spool index_sales_data.lst
select index_name,table_name,status from dba_indexes where table_name='SALES_DATA';
Spool off

- Create the Synonym
i) Script to create the synonym sales_synonym_ddl.txt

- Create Triggers on Sales_Data
i) Script to create triggers is sales_trigger_ddl.txt

IMP: This step have to do only if Step 5:- Recompile output shows Any demantra packages are invalid
- Try to recompile the Procedures/Packages of SALES_DATA If this does not work then recreate the procedures and packages
i) Script to use to recreate package is Package_body_Sales_Data.zip
ii) Scripts to use to recreate the Package body is Package_body_Sales_Data.zip

- Create Materialized View on Sales_data
i) BIEO_EXPORT_1_JUNTA_DEMAND.MV
ii) BIEO_EXPORT_2_JUNTA_DEMAND.MV

7)execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname => 'SALES_DATA');

7.1)  collect stats on SALES_DATA

use this:

BEGIN    DBMS_STATS.GATHER_TABLE_STATS (      ownname          =>  '"DEMANTRA"',  ESTIMATE=>30 ,    tabname          =>  '"SALES_DATA"',      granularity      =>  'GLOBAL AND PARTITION',      cascade          =>  TRUE,      no_invalidate    =>  FALSE    ); END; /

Proof: 30% gathered.
Select table_name, last_analyzed,sample_size from user_tables;


spool index_sales_data.lst
select index_name,table_name,status from dba_indexes where table_name='SALES_DATA';
Spool off

i) Run utlrp.sql / dbms_recomp

ii) Please collect this data information:-
==============================================================
spool Demn_obj_status_after.lst
spool object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by 2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
spool off

8) Test eveything from application side

Wednesday Dec 19, 2012

Un-used Indexes on MDP_MATRIX Consuming Resources

Disable un-used Indexes:

As much as it is recommended to create relevant indexes, it is advised not to have too many indexes on the mdp_matrix table.  Too many indexes will cause long waits on the table as indexes needs to get updated every time the table is updated.  There are many seeded indexes on mdp_matrix, every out of the box data model level has an index on the matrix table.  If a level is unused in the specific data model of the implementation, it is advisable to disable that index.  If the customer is not sure if and how indexes are utilized, the DBA can monitor all indexes.  After a few cycles of operation, the DBA should go over that list and see which indexes have not been used.  Consider disabling them.

There are scripts on the net to monitor indexes or use the monitoring usage clause in the alter index statement.

Thursday Sep 06, 2012

Using DB_PARAMS to Tune the EP_LOAD_SALES Performance

The DB_PARAMS table can be used to tune the EP_LOAD_SALES performance.  The
AWR report supplied shows 16 CPUs so I imaging that you can run with 8 or more
parallel threads.  This can be done by setting the following DB_PARAMS
parameters.  Note that most of parameter changes are just changing a 2 or 4 into an 8:

DBHintEp_Load_SalesUseParallel = TRUE
DBHintEp_Load_SalesUseParallelDML = TRUE
DBHintEp_Load_SalesInsertErr = + parallel(@T_SRC_SALES@ 8) full(@T_SRC_SALES@)
DBHintEp_Load_SalesInsertLd  = + parallel(@T_SRC_SALES@ 8)
DBHintEp_Load_SalesMergeSALES_DATA = + parallel(@T_SRC_SALES_LD@ 8) full(@T_SRC_SALES_LD@)
DBHintMdp_AddUpdateIs_Fictive0SD = + parallel(s 8 )
DBHintMdp_AddUpdateIs_Fictive2SD = + parallel(s 8 )

Tuesday Jul 10, 2012

Data Loading Issues? Try the new Demantra Data Load Guided Resolution

Hello!   Do you have data loading issues?  Perhaps you are trying the new partial schema export tool.  

New to Demantra, the Data Load Guided Resolution, document 1461899.1.  This interactive guide will help you locate known solutions to previously discovered issues quickly.  From performance, ORA and ODPM errors to collections related issues that have no known hard number error.   This guide includes the diagnosis of data being imported into Demantra and data being exported from Demantra. 

Contact me with any questions or suggestions.   Thank You!

Tuesday Jun 26, 2012

Latest EP_LOAD_SALES Performance Improvement for 7.3.1.4 and 12.2

Hello!   Take a look at the latest EP_LOAD_SALES solution.  New for 7.3.1.4 and 12.2, there are 3 additional parameters to control EP_LOAD_SALES.    Click Here

Thursday Jun 14, 2012

Java JRE 7 Automatic Upgrade and Demantra Requirements - Action Required

The following applies to ALL Demantra, EBS and Demantra Oracle Integrations:

All EBS desktop administrators must disable JRE Auto-Update for their end-users immediately.
See this externally-published article:

    URGENT BULLETIN: Disable JRE Auto-Update for All E-Business Suite End-Users
    https://blogs.oracle.com/stevenChan/entry/bulletin_disable_jre_auto_update

Why is this required?

If you have Auto-Update enabled, your JRE 1.6 version will be updated to JRE 7.

    This may happen as early as July 3, 2012.
    This will definitely happen after Sept. 7, 2012, after the release of 1.6.0_35 (6u35). 

Oracle Forms is not compatible with JRE 7 yet.  JRE 7 has not been certified with Oracle E-Business Suite yet.
Oracle E-Business Suite functionality based on Forms -- e.g. Financials -- will stop working if you upgrade to JRE 7.

Related News

Java 1.6.0_33 is certified with Oracle E-Business Suite.  See this externally-published article:

    Java JRE 1.6.0_33 Certified with Oracle E-Business Suite
    https://blogs.oracle.com/stevenChan/entry/jre_1_6_0_33

Tuesday Jun 12, 2012

Demantra 7.3.1.3 Released and Certified!

Hello1

Latest Demantra and VCP certification is released.     Demantra 7.3.1.3, patch 14076370.  VCP 12.1.3.7, Use note 746824.1.

All available at My Oracle Support.

Wednesday May 02, 2012

Latest / General patch for 7.3.0 And 7.3.1 ARU #14324102Patch 13426248

This patch resolves:


1. Bulk loader causing crash on Linux
2. Various PE related issues
3. Simulation with DP shapes. Consecutive simulation run, when DP shapes were introduced, was failed because vector of DP shapes used by engine were not reset between engine runs in simulation mode.

Available at https://support.oracle.com

Do You Want to Improve Your Data Load Performance in 7.3.1.x ?

Oracle has released 14810986 available ay My Oracle Support, https://support.oracle.com

1. This a re-release. The original patch has been replaced.
2. This is not customer-specific. It is a generic EP_LOAD patch that improves performance for 7.3.1.x installations.

Regards!

Wednesday Apr 25, 2012

Partial Database Schema Update

The Non Invasive Partial Schema Export Utility Extracting Partial Schema Dumps for Analysis (Doc ID 1448266.1) is now available in a patch.

Using MOS, download checkin  13930627.

Regards!

Sunday Apr 15, 2012

Tired of Downloading Your Entire Schema? How About a Partial Download?

Proceed to document 1448266.1, Demantra Non Invasive Partial Schema Export Utility Extracting Partial Schema Dumps for Analysis.  In this document you will find instructions and 2 SQL scripts.  Give it a try.  If you have a problem, feel free to post to this BLOG or the community located at:

https://communities.oracle.com/portal/server.pt/community/demantra_solutions/231

Regards!   Jeff

Wednesday Mar 28, 2012

Demantra USA Based Companies and SOX Compliance

A USA based company is assessing Demantra Trade Promotion Management (TPM) capability.  It appears that SOX
is necessary in their case due to the nature of what TPM does and the necessity for auditability.  Do we
have any detail on SOX compliance for Demantra?


Answser
-------

SOX compliance with regards to IT:

1.  Requires auditing of data changes done by who, what, when

    a. Audit trail profiles can be set up for key financial series and view them in audit trail reports

    b. One functionality we do not have which typically is asked for is user login history. We have only
       active sessions, history is not available.

2.  Segregation of duties

    a. With respect to TPM, you could have deduction and financial analyst for settlement be different
       from promotion creator, promotion approver or sales team.

    b. Budget Approver for funds can be different from funds consumer.

    c. Promotion creator can be different than promotion approver

    d. For a US customer you may have to write some custom scripts to capture promotion status change
       and produce an external report as part of compliance.

One additional requirement is transparency of forward commitments entered into with retailers / distributors
for trade spending, promotions.  Outside of Demantra - Consumer Goods Trade Funds Analytics.

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today