Querying Standard Profitability Model Statistics

After a model is built, it may be useful to determine the number and usage of certain components, such as stages or POVs and their dimensions, assignments, and drivers.

The SQL-based query, modelstats.sql, enables users to generate specific statistics for their models. These read-only queries can be used to view model characteristics and performance statistics, or to evaluate the impact of changes. The statistics can also be used to diagnose problems with models and performance.

In order to use the new query, you must have existing database views:

• Stages (HPM_EXP_STAGE)

• POVs (HPM_EXP_POV)

• Driver Selections (HPM_EXP_DRIVER_SEL)

• Assignments (HPM_EXP_ASSIGNMENT)

• Assignment Rule Selections (HPM_EXP_ASGN_RULE_SELECTION)

Note:

The Drivers view (HPM_EXP_DRIVER) is not used with model statistics.

You must have proper access to the selected database and database views. For information on creating database views, see Appendix B, in the Oracle Hyperion Profitability and Cost Management Administrator's Guide.

After installation, the SQL script, modelstats.sql, is included in the application folder. The query is database neutral and can be run against MS SQL or Oracle databases. If the model contains errors, they are not reported in the query results, and the existing statistics are still generated.

Oracle recommends that you run the query for any application before you make significant changes and then save the queries and capture the output for comparison with later results. This snapshot provides you with baseline statistics for the application, against which you can compare subsequent changes or view information to evaluate the potential impact of proposed changes. For example, if an assignment rule is used thousands of times, any modification to that rule may have a much larger effect than originally expected.

To run the modelstats.sql query:

1. Locate the database and ensure you have proper access to the following items:

   • Database Views

   • Profitability and Cost Management RDB Schema

2. Navigate to the modelstats.sql query:

   • For Windows, %hyperion_home%\products\Profitability\database\Common\MSSQLServer\view

   • For UNIX, $hyperion_home$\products\Profitability\database\Common\MSSQLServer\view

   Note:

   Because database views and queries are updated frequently, check to ensure that you have the latest versions.

3. Open the modelstats.sql query, and modify the "%" variable for each query to specify the model components against which the query is to be run, such as "application_name like ‘%’", or "layer_name like ‘%’".

   For example, in the code application_name like '%', replace the percentage sign with the application name:

   application_name like ‘bikes2’

   Note:

   You can use the percentage sign as a trailing wildcard to broaden the query range. For example, enter ‘b%’ to apply the query to all applications that begin with "b". To query all applications, enter ‘%’.

4. Review the query results.
   
   
   

   The results depend on the query selections. Some example queries are displayed below:

   • List All Stages and the Dimensions in the stage for matching Applications displays all stages and the associated dimensions for the selected application, ordered by the application name and stage order.

   • List explicit Assignment counts by source Stage for both Cost & Revenue Layers for the selected application, displaying the application name, layer, and source and destination stage names.

   • List Rule Names and how frequently they are used in an Application displays a breakdown of individual rules and the number of times each one is used.

   • List the Driver usage count by stage in Driver Association displays the usage count of each driver for the selected application, stage, and layer. As an example, you can use this information to determine whether some drivers are not really used, and whether you can safely delete them.

   To view all queries, review the most current version of the modelstats.sql file.