Delete List Item no longer found in source

TimG

I am working to remove Gross shipment data that is no longer found in the source DWH feed.

 

Shipment actuals can be removed from the source data and I need to remove those not found in the source from the Data Hub and from the spoke planning model. When the shipment actuals are removed from the source them remain in Anaplan. I have found an option for this but I'm looking to see if there is a better practice for removing the now "bad" list items.

 

Option: After loading the transaction ID to the list I am loading the data to the transaction detail module. In the second import that loads the module I am using the "Which target items to clear prior to import" setting set to "All Items". This clears the data from the transaction detail module. Once the data is cleared I run a delete action on the list members. The delete criteria is set to check if a cell in the transaction detail module is blank. The also requires the transactions for all regions to be loaded into the Data Hub all at the same time and not at different times for different regions. It also prevents the Data Hub from being able to only send new or modified transactions daily.

 

Is there a better way?

rob_marshall

@TimG ,

 

I think you are close, not sure you need today(), but that can definitely help.  Also, I want to revise what I stated above which should help.

• When you are loading data to the Data Hub from the source, create a boolean in the source file which is always true.
• Either create a property on the transaction list (boolean) or in the transactional module (boolean) where you are importing the data.  If you want to bring in all months for that transaction, then put in on the list.  If you want it at the transactional level, then put it in the transactional module.  Or, it can be both.  Map the boolean in the source to this line item.
• Create an action to clear this boolean (use the boolean as the filter)
• The process to load data is now:
  • Clear the boolean
  • Load the data

Now, we know what has been loaded and what needs to be delete (anything with a false is old).

Loading to your spoke model:

• Assuming you have a view to load the data from the hub to the spoke, update it to use the filter where the boolean is now true.  This gives you the data that needs to be updated.
• In the data hub, you know which data has been updated from the source as well as the data that has not been updated (those with a false).
• You can create a line item, again in the transaction module with the formula Not New (again a boolean).
• Create a view based on this line item.
• In the spoke model, create a line item or property (again it depends on your setup, but best practice is to place this in a module) that is a boolean (Needs to be Deleted?)
• Use the view in the data hub that you just created to populate this boolean.
• Run an action in the spoke model to delete where these members are true.

 

This way, you will not have to clear all data and then load all of data in the spoke model.

 

Let me know if this helps and if not, we can hash it out.

 

Rob

rob_marshall

@TimG ,

 

Yes, I believe there is a better way in that you don't have to clear all of the data for every load as well as reloading it.  Can you do some validation within the data hub to see what has been removed?  Usually, you can add a column to the source feed (New?) with a value of True.  Make sure every records has this.  Before you import the data, in either your list or in a SYS Properties module, ensure this is false (by running an action).  When you load the data, this boolean will now be true so anything that is false, should be able to be removed.  Do the same process in your spoke model from the Data Hub.

 

Hope this helps,

 

Rob

TimG

Hi Rob, Thank you for the help on this.

Am I thinking of this correctly? During the Data Hub import, add an import date and if the date does not match "today()" then it gets a false (we are importing today's date separately). Then, delete the false items and repeat in planning model? That makes sense. In both scenarios I need to load all transaction data everyday in order to confirm the data is still accurate. I'm not sure there is a way around this.

 

 

 

 

TimG

Thank you for the details here. This will solve for not clearing all of the data first.

 

I really appreciate your time and help on this process. 

rob_marshall

@TimG ,

 

Great question and glad I could help.

 

Rob