Focal Point Banner

As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at for further assistance. Reference the community FAQ to learn more about the community.

Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    Performance tuning PMF - best practices?

Read-Only Read-Only Topic
Performance tuning PMF - best practices?
Hi Bob,

I have three large dimensions (14k records @ level 4), and I have user access rules setup based on level 2 or level 3 values for each dim. Right now PMF is taking about ten seconds to render the rolling five period report, with eight measures. I am looking for some ways to speed this up, and experience tells me to index the DIMENSIONS table for level 2 and level 3.

I had a look on our SQL Server, and only the key is indexed on the DIMENSIONS table. I want your opinion on if you think I should go down this route, or if this is one of those, "don't modify the backend," sort of things.



-WebFOCUS 8.2.01 on Windows
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Platinum Member
posted Hide Post
Hey Joey,

Is your report taking 10 seconds when you select a different value from the tree on the left (ignoring for the moment the amount of time it takes to open the tree up)? Or is this only happening on the initial run?

Posts: 164 | Registered: March 26, 2003Report This Post
posted Hide Post
Hi EricH,

It takes the same amount of time to run as the initial load when I select a dimension value to filter upon.

What does that tell you?

Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Gold member
posted Hide Post
On the same topic, - if additional Indexing is not suggested as an option, how about partitioning the PMF SQL database data. Is there any suggestions for that?

WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
Posts: 83 | Registered: March 28, 2006Report This Post
Platinum Member
posted Hide Post
Joey -

What this tells me is that the problem is not related to the trees, but rather the report itself. With very large dimensions, you may have performance issues related to setting up that tree on the left hand side, but this does not appear to be the case.

To answer your original question and to address Endre's follow up, the answer is "Yes", you can add indexes to the DB. To expand on this, you can use any number of DB techniques, as long as you do not alter the basic structures of the database itself - the table & column names, the primary indexes, the identity fields, etc.

So the next question is what techniques to use. If you're working in SQL Server, the first thing I'd recommed is to shrink and rebuild your database. I have seen significant improvements by doing this one simple step. Next you can try adding additional indexes. Since the problem is on the report side, you can first run the report once. Then turn SQL tracing on in your Server console, then run the report again from the dimension tree, then turn SQL tracing off. This way, your SQL trace will not be cluttered up by the SQL used to populate the tree.

The most likely bottleneck is the select against the core MEASURES table, since this contains all your Measure actual and target values. You will probably see joins to the TIME, PM_MEAS_SRC, and PM_MEAS_SRC_OBJ_CR tables as well. I have found that adding a unique index to the MEASURES table using the MEASURE_SERIES column along with the DIMENSIONnn_ID columns (but only for the dimensions that you actually use!) along with an additional index on PM_MEAS_SRC that puts MEASURE_SERIES first gave some good improvements. But your mileage may vary!

As always, make sure to back up your DB before doing this - but you knew that already Smiler

One possible down side to adding indexes is that your Measure loads may run a little longer if you add indexes to the core MEASURES table, since the indexes have to be built up.

Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post

Not sure if you have already addressed this issue. My recommendation is to add the indexes - you can afford the performance hit at load time, but end users will complain about slow screen loading times.

Second, in older versions of PMF there were issues with database artifacts if you dropped and recreated and dimensions a few times. You may have a couple of extra thousand rows in your dimension table if you dropped and recreated this large L4 dimension as described above. To see if you have ghost records, run these two SQL's and compare the results:

1) Query to show current dimensions and number of rows.

select count(d.dimension_source_id), s.dimension_source_id, s.dimension_level_name, s.dimension_type
from dimensions_source s, dimensions d
where d.dimension_source_id = s.dimension_source_id
group by s.Dimension_source_ID, s.dimension_level_name, s.dimension_type
order by s.dimension_level_name

2) Query that shows all dimensions (including ghost dimensions) and number of rows:

select count(d.dimension_id), d.dimension_source_id from dimensions d
group by d.dimension_source_id

If there are a lot of ghost records, take a Database backup, and clear out the unassociated rows.

I'm rolling off my project this week, so let me know if you (or anyone else) needs help.


WF 7.65. Solaris. PMF 5.11 on Oracle 10g
Posts: 48 | Location: New York | Registered: March 25, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    Performance tuning PMF - best practices?

Copyright © 1996-2020 Information Builders