Friday Dec 14, 2012

The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values

Do you know what these values are telling you?

19854 99 1 1 1 1 3
1077 99 0 1 1 1 0
262691 99 1 1 -1
56 99 0 1 1 1 2
1 98 1 1 1 1
1 99 0 1 1 1
748796 1 1 1 4
351633 1 1 1 1 1 2
1877829 97 1 1 4
840 99 1 1 1 1
27 99 0 1 1 1 3
1 97 1 1 -1
66712 99 1 1 1 1 2
53213 1 1 1 1 1 3
2560 98 1 1 4


Check out The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values (Doc ID 1509754.1)

This customer is adding an additional processing burden, adding no value.  The incoming data should be scrubbed to eliminate the overhead. 

Wednesday Dec 05, 2012

Demantra Engine on Linux or Unix Failure Hanging Error Not Starting Debugging Install and Configuration Checklist

Did you know that there is a 21 point checklist to ensure that your engine will run correctly?   Check out note 1086704.1 in My Oracle Support.



Friday Oct 26, 2012

WARNING Retrying Bulk Insert for file:sqlldr due to Communication Error:256

WARNING Retrying Bulk Insert for file:sqlldr due to Communication Error:256

I am running my engine on Linux and am receiving an intermittent message

"WARNING Retrying bulk insert for file: sqlldr due to communication Error: 256"

The engine seems to have completed successfully, but it is not clear if this error caused some of the forecast to not complete. It is also not clear what caused the error.

Generally if you see only the WARNING of it, it means that next retries of the same load request have eventually succeeded and so the run a a whole is not affected.

In order to know more about what happens, look for .log/.bad files left in the engines bin directory or possibly a quote of them within the specific engine log that had the issue.  The sqlnet.log file may also have some information about it and perhaps at the database server side there may be some log/alert regarding what happened.  Look at the alert.log.

In general it could be that the database server/network was over loaded at the time and somehow the connection was rejected/failed/aborted either due to specific setting on concurrent connections/sessions or inadvertently due to glitch in network/os/hardware.

If this repeats and becomes more frequent during the run you should look further into it as mentioned above.

You can also track this using either SQL*Trace or java.util.logging. 

- Globally enable logging by setting the oracle.jdbc.Trace system property java -Doracle.jdbc.Trace=true

- Client Side Tracing:

Your SQLNET.ORA file should contain the following lines to produce a client side trace file:

trace_level_client = 10
trace_unique_client = on
trace_file_client = sqlnet.trc
trace_directory_client = <path_to_trace_dir>

Server Side Tracing:

To enable server side tracing, use the following parameters:

trace_level_server = 10
trace_file_server = server.trc
trace_directory_server = <path_to_trace_dir>

Tracing Levels:

The following values can be used for TRACE_LEVEL* parameters:

    16 or SUPPORT — WorldWide Customer Support trace information
    10 or ADMIN — Administration trace information
    4 or USER — User trace information
    0 or OFF — no tracing, the default

Additional information is readily available via the web.

Wednesday Sep 19, 2012

Demantra Engine Failure Hanging Error Not Starting Debugging INstall and Configuration Checklist

There is a new Demantra engine configuration checklist to ensure that your enginge setup and configuration is correct.  These are the questions that

development will ask should your issue turn into a bug.   See the following MyOracleSupport note:

Demantra Engine on Linux or Unix Failure Hanging Error Not Starting Debugging Install and Configuration Checklist Note ID 1086704.1

Friday Sep 14, 2012

Introducing the new Demantra Guided Resolutions!

There is a new method to find your solution called guided resolution or search helper.

Currently, we cover 5 specific topical areas:

Oracle Demantra 7.x and Above Data Loading Guided Resolution Note 1461899.1

Oracle Demantra Load Data Issues Not Necessarily Error Code Related Guided Resolution Note 1468884.1

Oracle Demantra 7.x and Above Workflow Issues Guided Resolution Note 1353217.1

Oracle Demantra 7.x and Above Worksheet Related Guided Resolution Note 1486639.1

Oracle Demantra 7.x and Above Engine Related Guided Resolution Note 1486634.1

The guides will be updated with the latest proven solutions on a regular basis, keeping the contect relevant.

What is a guided resolution?   What is the best practice using a guided resolution?

How to Use the Demantra Guided Resolution, a Proactive Services Tutorial.  Note 1473992.1

Friday Jul 06, 2012

Demantra Controlling MDP_MATRIX Combinations Assigned to Forecasting Tasks Using TargetTaskSize

New parameter: TargetTaskSize

Old parameter: BranchID  Multiple, deprecated onwards

Parameter Location: Parameters > System Parameters > Engine > Proport
Default: 0
Engine Mode: Both
Details: Specifies how many MDP_MATRIX combinations the analytical engine attempts to assign to each forecasting task.  Allocation will be affected by forecsat tree branch size.  TaskTargetSize is automcatically calculated.  It holds the perferred branch size, in number of combinations in the lowest level.
This parameter is adjusted to a lower value for smaller schemas, depending on the number of available engines.

  - As the forecast is generated the engine goes up the tree using max_fore_level and not top_level -1.  Max_fore_level has
    to be less than or equal to top_level -1.  Due to this requirement, combinations falling under the same top level -1
    member must be in the same task.  A member of the top level -1 of the forecast tree is known as a branch.  An engine
    task is therefore comprised of one or more branches.

    - Reveal current task size
      go to Engine Administrator --> View --> Branch Information and run the application on your Demantra schema.  This will be deprecated in since there is no longer a means of adjusting the brach size directly.  The focus is now on proper hierarchy / forecast design.

    - Control of tasks
      The number of tasks created is the lowest of number of branches, as defined by top level -1 members in forecast
      tree, and engine sessions and the value of TargetTaskSize.  You are used to using the branch multiplier in this
      calculation.  As of, the branch ID multiple is deprecated.

    - Discovery of current branch size
      To resolve this you must review the 2nd highest level in the forecast tree (below highest/highest) as this is the
      level which determines the size of the branches.  If a few resulting tasks are too large it is recommended that
      the forecast tree level driving branches be revised or at times completely removed from the forecast tree.

    - Control of foreacast tree branch size
        - Run the following sql to determine how even the branches are being split by the engine:
          select count(*),branch_id from mdp_matrix where prediction_status = 1 and do_fore = 1 group by branch_id;
          This will give you an understanding if some of the individual branches have an unusually large number of
          rows and thus might indicate that the engine is not efficiently dividing up the parallel tasks.

        - Based on the results of this sql, we may want to adjust the branch id multiplier and/or the number of engines
          (both of these settings are found in the Engine Administrator)

          select count(*), level_id from mdp_matrix where prediction_status = 1 and do_fore = 1 group by level_id;

          This will give us an understanding at which level of the Forecast tree where the forecast is being generated. 
          Having a majority of combinations higher on the forecast tree might indicate either a poorly designed forecast
          tree and/or engine parameters that are too strict

          Based on the results of this we would adjust the Forecast Tree to see if choosing a different hierarchy might
          produce a forecast, with more combinations, at a lower level.

          For example:

            - Review the 2nd highest level in the forecast tree, below highest/highest, as this is the level which
              determines the size of the branches.

            - If a few resulting tasks are too large it is recommended that the forecast tree level driving branches
              be revised or at times completely removed from the forecast tree.
            - For example, if the highest level of the forecast tree is set to Brand/All Locations.

            - You have 10 brands but 2 of the brands account for 67% and 29% of all combinations.

            - There is a distinct possibility that the tasks resulting from these 2 branches will be too large for
              a single engine to process.  Some possible solutions could be to remove the Brand level and instead
              use a different product grouping which has a more even distribution, possibly Product Group.
            - It is also possible to add a location dimension to this forecast tree level, for example Customer. 
              This will also reduce forecast tree branch size and will deliver a balanced task allocation.

            - A correctly configured Forecast Tree is something that is done by the Implementation team and is
              not the responsibility of Oracle Support. 

Allocation will be affected by forecast tree branch size.  When TargetTaskSize is set to 0, the default value, the system
automatically calculates a value for 'TargetTaskSize' depending on the number of engines.

  - QUESTION:  Does this mean that if TargetTaskSize is 1, we use tree branch size to allocate branches to tasks instead
               of automatically calculating the size?
    ANSWER: DEV Strongly recommends that the setting of TargetTaskSize remain at the DEFAULT of ZERO (0).

  - How to control the number of engines?
    Determine how many CPUs are on the machine(s) that is (are) running the engine.  As mentioned earlier, the general
    rule is that you should designate 2 engines per each CPU that is available.  So for example, if you are running the
    engine on a machine that has 4 CPU then you can have up to 8 engines designated in the Engine Administrator.  In this
    type of architecture then instead of having one 'localhost' in your Engine Settings Screen, you would have 'localhost'
    repeated eight times in this field.

    Where do I set the number of engines?
        To add multiples computers where engine will run, please do a back-up of Settings.xml file under
        Analytical Engines\bin\ folder, then edit it and add there the selected machines.
        Example, this will allow 3 engines to start:

        - <Entry>
          <Key argument="ComputerNames" />
          <Value type="string" argument="localhost,localhost,localhost" />

Otherwise, if there are no additional engines defined, the calculated value of 'TargetTaskSize' is used.
(Oracle does not recommend changing the default value.)

The TargetTaskSize holds the engines prefered branch size, in number of level 1 combinations.

  - Level 1 combinations, known as group size

The engine manager will use this parameter to attempt creating branches with similar size.
  * The engine manager will not create engines that do not have a branch.

The engine divider algorithm uses the value of 'TargetTaskSize' as a system-preferred branch size to create branches that
are more equal in size which improves engine performance.  The engine divider will try to add as many tasks as possible to
an existing branch, up to the limit of 'TargetTaskSize' level 1 combinations, before adding new branches.

Coming up next:
- The engine divider
- Group size
- Level 1 combinations
- Engine Parameters


Tuesday Jun 26, 2012

For Proactive Services: Demantra Engine2k Log File Analysis Engine Performance Optimization

Hello!  This is the latest from the Oracle Proactive Services Team.  This presentation will guide you to interpreting the enginewk log file.  Specifically, when you have a performance issue with the engine, is it the database or the engine itself.  

Look for more diagnostics soon!

Proactive Services Community Doc, Avoiding the Unexpected with Proactive Services

Wednesday May 02, 2012

Demantra Engine Common Run Time Errors

Hello!  These errors are common.  Please take a minute to review.  Regards!

* DP shapes are defined but seem to have no effect

1. Verify that the DP shape causals were defined on the relevant BATCH (or parent BATCH profile in case of simulation) and not only on profile 0. Profile 0 is only used as template when creating new profiles.
This is common for customers that upgraded from versions that did not support profile IDs.

2. Check if the "CONSTANT" causal is missing in the CAUSAL_FACTORS table. Run the next query (modify the profile id value if needed):
select * from causal_factors where engine_profiles_id=1
If no row has 'CONSTANT' value in the factor_name column, it should be added.

* Engine run crash after first branch have finished when using many engines in parralel

When not all engines were started yet, due to the 10 seconds delay between each and first branch already finished.  Engine Manager will crash and stop the run.  Workaround: decrease number of branch multiplier so branches will be larger or decrease number of engines in parralel if multiplier is already 1.

* ERROR alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id) ERROR - Error Description?: ORA-02437: cannot validate (SARALEE_APR11.PDE_6_PK) - primary key violated

1. During the work on the ptp tuning project, I've come up against following error:

creating Promotions Data Engine? table

ERROR DB_Connection::Execute threw an exception:
ERROR alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id)
ERROR - Error Description?: ORA-02437: cannot validate (YOURCOMPANY_APR11.PDE_6_PK) - primary key violated

ERROR - Error Number?: 0x80004005 - Unspecified error

ERROR - Native Error Number?: 2437

ERROR Execute SQL? failed: alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id)
WARNING Failed to create indices on PDE table, possibly due to duplicate/NULL values, please verify related expressions in M3_CAUSAL_FACTORS and 'Init Params.Shift Dyn Promo Date?'.

The source of this issue was found in group by expression of create PDE Sql. That expression included promotion_type_id. Customer schema had same promotion on same combination at same date with different promotion types. So SQL inserted two rows to PDE_X table with same combination/promotion_id/date. After that it tried to add constraint of primary key item/location/promotion_id/sales_date and failed because of duplicate rows with same primary key.

In general it is a configuration error of customer that inserted different promotion types with same promotion_id. But engine should know to handle it or to produce some meaningful error.

As a workaround in one of the bugs DEV proposed the following : Configure Analytical Level Type? parameter in Init_Params_0 table, set value_string to be IS_SELF instead of PROMOTION_TYPE_ID.

2. During the work on patch. Price causal treatment was modified. Currently engine should use its default expression in case this causal is active.
Some customers schemas have a unique expression for price causal and engine has a problem to parse it in a right way that cause to engine errors from sql.


ERROR DB_Command::Execute threw an exception: 
ERROR SELECT sales_date datett, round(NVL 
(sum(greatest(nvl(nvl(pseudo_sale,actual_quantity),0)*(1 + 
nvl(demand_fact,0)),0)),sum(FORE_3)),6) sales, ...................... 
ERROR - Error Description?: ORA-00909: invalid number of arguments

Bug 12604957 was opened and resolved in  In case you cannot apply the fix, use workaround and set price expression to a default one.

Wednesday Apr 04, 2012

Need to Determine the Engine Status?

If you need to establish the status of the engine, begin with this SQL:

select status, engine, engine_version,fore_column_name
from dm.forecast_history

The status of an engine run is stored in the FORECAST_HISTORY table, in the “status” field.  We can also find in that table the FORE_COLUMN_NAME field. This field includes the name of the column in SALES_DATA in which the relevant forecast is stored.

Here are the possible statuses:
-1, -2 : The engine failed in the initialization phase.  Which means, before the engine manager created the engines.
 0 : The engine stopped in the optimization phase.  Which means, after the engines were created.
 1: The engine finished the run successfully.
 2: Forecast was never calculated for the relevant column that is mentioned in FORE_COLUMN_NAME.



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


« April 2014