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 community@tibco.com 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)    Dimensions load - Delete if not in new

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Dimensions load - Delete if not in new
 Login/Join
 
Member
posted
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,


WebFOCUS 769
Windows 2003 SP2
Tomcat app server
Excel, HTML, PDF
 
Posts: 17 | Location: Montreal | Registered: March 05, 2009Report This Post
Master
posted Hide Post
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


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Member
posted Hide Post
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?


WebFOCUS 769
Windows 2003 SP2
Tomcat app server
Excel, HTML, PDF
 
Posts: 17 | Location: Montreal | Registered: March 05, 2009Report This Post
Master
posted Hide Post
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.


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Member
posted Hide Post
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?


WebFOCUS 769
Windows 2003 SP2
Tomcat app server
Excel, HTML, PDF
 
Posts: 17 | Location: Montreal | Registered: March 05, 2009Report This Post
Master
posted Hide Post
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


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    Dimensions load - Delete if not in new

Copyright © 1996-2020 Information Builders