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.

Cheers,

Joey

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?

EricH

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?

Joey

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?
thnx
Endre

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


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.

EricH

Joey,

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.


-Seth