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.
New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.
What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.
During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.
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, 2005
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?
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
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.
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, 2009