Hi, we are loading dimensions from a staging table that we truncate everytime. That staging table is created by scanning a product table from the datawarehouse. We have changed the product table data and knew that it would create new records in the dimension. The problem is that after loading, the dimensions that don't exist in the staging were deleted in the PMF dimensions. That shouldn't be the case if we don't check the "Delete if not in new". In the preview, we see those records that don't exist in the staging and are highlighted in yellow with a minus sign meaning "Records not in new". And yet it gets deleted. If we check the "Delete if not in new", then the preview shows again those records but in red this time with the minus meaning that they will be deleted. So how is it supposed to work? Are we not having a proper understanding of dimension load?

After the load, the previously loaded measures disappear from PMF but querying in the PMF measures table, we can find the records and see that the dimension id is still there but doesn't exist in the dimensions table. We had an understanding of PMF that previously loaded dimensions linked to measures would stay unchanged and new loaded dimensions would be linked to measures loaded at that time, even if the datawarehouse changes. It think it's what they call a slowly changing dimension.

Thank you,

If you're trying to do an incremental load then you're right to leave "delete if not in new" unchecked. Please check the format of your source data to make sure the "old" Dimensions values *match* on keys and values. Also make sure the pathways to the Dimensions aren't changing.

With "delete if not in new" checked, if *keys or values or pathway* for already-existing records are changing between each load, then the loader will essentially obsolete the old records on load. If they are identical, then they will be *unotouched* and should still be present and usable.

If the pathways are changing to the Dimensions, then you need to do "delete if not in new" (because the new pathways are not in the old and you need them deleted).

Remember also to re-load your linked Measures after the load.

Hope this makes sense. If you're still having trouble please open a case with Customer Support Services.

thanks

Thanks for the information Bob, but like stated previously, we load a staging table that we truncate on every load. When we load the staging, of course, only the new values are in that staging table. Then my understanding would be that the staging is compared to PMF dimension table. PMF seing that the staging has only new records, and the delete if not in new is unchecked should have an end result of only adding those records and leaving the old dimensions in PMF untouched. Which is not the case.

Just to let you know also that we have found a solution to this. It was to not truncate that staging table and basically do ourselves the work to insert new records when dimensions are changed and keep the old records as well. When the PMF load, it basically just replicates the staging table.

I find that weird and that is why I am posting this. Shouldn't we be able to only populate the records that we want add/changed into the staging table and PMF load should take care of the work (that we are now doing in the staging)? As long as we match the proper key/values/pathway, it should be fine and I understand that.

To make it simple, here's an example.

In the staging, there is a record:
Key: HouseRedGreen
Value1: House
Value2: Red
Value3: Green

In PMF, there is a record:
Key: HouseRedBlue
Value1: House
Value2: Red
Value3: Blue

If "Delete if not in new" is unchecked:
PMF should have 2 records

If "Delete if not in new" is checked:
PMF should have only 1 record, HouseRedGreen.

Am I wrong?

OK... now I get it and understand why there's been confusion.

There's no idea of "incremental source" dimension loads in PMF. Any table used to load a Dimension, including your staging table, would have to always have all the values you want to be in the product dimension. That's why they values were not being confirmed when you re-loaded and are showing up in red if you check the box to "delete if not in new."

The reason becomes clearer when you get to the Measure loads. Every Measure load goes back to the staging table to look for linking keys. Linking keys aren't stored in the PMF dimension table; they're looked up during measure load in the source Dimension table. The only link from measure to dimension internally in PMF is an internally generated key, not based on your original linking key. This nets a little speed when reported, at the expense of a little more complexity on loading.

While the Measure loader is there looking at the dimension source data, it also looks for other things like possible measure data that is linked to a dimension level value you haven't gotten around to loading yet (so it's searching for completeness for you). So there are additional benefits to this approach.

Hope this helps.

Thanks for the explanation. I kinda have guessed that it might be it, but of course, a confirmation from the "Master" is priceless!

We can live with that.

Is this documented somewhere?

Sure. There's info about the way keying works in the PMF Administrator's Guide, section 4 of the PDF and online help based documentation. It's not as thorough as we get here. Think of FocalPoint as a realtime extension to doc - there's a trove of info here that moves faster and better than the doc ever can.

thanks