The customer has a Product dimension with 3 levels, Division/Department/Class. We'd need to do the following on a regular basis:

1. Add new values to the dimension.
2. Change Class level values.

I believe we can do #1 by simply reloading the dimension whenever needed with 'Delete if not in new' unchecked? This would add the new values, but leave the existing values as they were and we wouldn't have to reload any measures etc?

What about #2? Say we have a dimensional value 'Home and Garden/Pillows/Color Pillows' and it would need to be changed to 'Home and Garden/Pillows/Pattern Pillows'? We're not adding a new value, but would need to change an existing one. The value of Class in the source table is changed, but all the other values stay as they were, including the key field. If we did this the same way as #1, what would happen? Would PMF update/change the current values, but leave the internal key as is, or would it create a new internal key and we'd need to reload measures using this dimension?

Many thanks for your help.

Cheers,
Mika

Sorry nobody got back to you.

For #1 you're right on the money.

For #2, in all extant PMF including the immediate next release, there's no way to change an existing Dimension value without having to reload and relink all measures. That's because PMF uses the *path* to determine dimensional linkages.

There is a way to work around this limitation. One caveat is, it's pretty work intensive. You'd have to consolidate all your source data into a separate DB table, in that file you'd dump and dereference all the measure data along with the keys and the paths, and then go through the consolidated table to find the new pathways.

We recognized a while ago that this could be changed to better support slow-changing of existing dimension values; as a result we plan a major rearchitecture for PMF 5.3 that allows you to use key-based matching for dimensions (along with a host of other features that will make maintenance of PMF much easier).

Sorry this one isn't a quick and easy answer.

thanks

Getting back to this question again...

Bob, for item #2, how would the technique you described work, what would we need to do to make it happen (in more detail)? It does sound, as you said, pretty work intensive.

As we're only changing a description of an existing class, which is already loaded into PMF, would we be able to modify the value from 'aaa' to 'abc' in the PMF table itself? What is the table name?

Thanks again.
Mika

Mika -

It turns out that you can update the internal PMF database in place without doing a dump and load. The internal table that needs to be updated is the DIMENSIONS table. DIMENSIONS is the driver table for the remaining reporting tables and it contains information for all dimensions (including TIME).

I'll give an example using the standard pmf_manufacturing demo database. Let's suppose the data in your Products Dimension changes. You can simulate this by adding a DEFINE at the bottom of centinv.mas:

 DEFINE PRODTYPE/A19 = IF (PRODTYPE EQ 'Analog') THEN 'AnalogXXX' ELSE PRODTYPE;, $  

If you run this through the Dimension Load Preview you will see the mismatches. Next, you update the DIMENSIONS table. There are various ways to do this, perhaps the simplest is to directly update via SQL:

update DIMENSIONS 
set LEVEL_01_VALUE = 'AnalogXXX'
where LEVEL_01_VALUE = 'Analog'
  and DIMENSION_SOURCE_ID in (select DIMENSION_SOURCE_ID from DIMENSIONS_SOURCE where DIMENSION_TYPE = 'P')  

Now if you run the Preview you will see that everything matches up. Note that if you try to run a report at this point, it will still show as 'Analog'. However, once you do a Save & Load Dimension, the reporting tables will be refreshed and you will see 'AnalogXXX'.

The key to this process is that the source data matches up with the DIMENSIONS table. Then doing a Dimension Load refreshes the various reporting tables.

EricH

Hi Eric,

Thanks, we'll try and give this a go in the near future. Using this method will make updating the PMF database much easier and I know the customer will also appreciate this, as there will be description changes on a regular basis.

Many thanks again for your help!

Cheers,
Mika

Hi Team,

I'm dealing with the same issue as Mika (Hello Mika! Hope you are doing well in Aussieland).

I've tried the SQL update technique, and didn't meet with total success.

I ran the update, and that part worked. I can look in my DIMENSIONS table and I see the dimension value has been modified as I wanted. Additionally, if I try to do a Dimension load, I can see the modified values on the preview screen.

The part that didn't work is that all my reports still show the old dimension value. Additionally, the dimension tree (Rolling 5 Period report) shows the old value.

Is there a second table involved besides DIMENSIONS that I need to update with the new value?

Cheers,

Joey

Hi Joey,

Yes, the XXX_TREE table also should be updated. The XXX represents the dimension name of the table. So in Eric's example of the PRODUCT table this would be PRODUCT_TREE.

The next dimension load would properly update this XXX_TREE table, but you can alternately update it directly (this is used for most reports).

update PRODUCT_TREE set PRODUCT_LEVEL01_VALUE = 'AnalogXXX' where PRODUCT_LEVEL01_VALUE = 'Analog'

Hope this helps,
Mike

Hi Joey,

I missed a few other tables that also need to be updated. These tables are used for optimization on certain tree reports.

PM_DIMS_W_SRC01
PM_DIMS_W_SRC02
PM_DIMS_W_SRC03
PM_DIMS_W_SRC04
PM_DIMS_W_SRC05
PM_DIMS_W_SRC06

where:
update PM_DIMS_W_SRC01 set LEVEL_01_VALUE = 'AnalogXXX' where LEVEL_01_VALUE = 'Analog' and DIMENSION_TYPE = 'P'
update PM_DIMS_W_SRC02 set LEVEL_01_VALUE = 'AnalogXXX' where LEVEL_01_VALUE = 'Analog' and DIMENSION_TYPE = 'P'
...

NOTE:
We have developed a new technique that eliminates the need to go through and update all of these tables separately.

A tech memo will be released on this process shortly.

Best regards,
Mike

Hi Mike,

I see that the PM_DIMS_W_SRC## tables correspond to dimensions based on the number of dimension levels. So, my Location dimension has three levels, and if I wanted to change a Location value, I'd only need to update PM_DIMS_W_SRC03, and not any of the other PM_DIMS_W_SRC## tables.

I've got this to work now and I'm satisfied with the results.

Thank you for your help.

Cheers,

Joey