What is the best way to "move" a value from an entity member to another member ?

Etienne CAUSSE

Hi everyone !
I hope everybody is doing well.

I'm currently facing a nice challenge in my new board application, regarding a quite classic "Phase in / Phase out" process.

Context:
It is a budget planning application, nothing very original about that

In this application, we initialize the process by copying the latest sales forecast data to the new year, in order to let users extrapolate easily, using split and splat to ajust to their point of view.

So far, so good.
However we have a quite complex product portfolio (around 250 000 members in the entity) and the lifecycle of our products is around 2/3 years, which means next year I need to replace around a third of my portfolio by new references.
In a lot of cases, this is manual and I've built procedures to manage that manually.

In this new challenge, I've been requested to manage automatically the replacement of old references by new ones, across all geographies and customers.

Where I stand:

Currently my first idea was:
- add a "replacement product" entity as a parent of my product;
- load the old / new mapping in the relationship product / replacement product;
- filter all quantities / sales values that have a replacement product, fill a cube structured by "replacement product" / customer / geography with these values, then export it as CSV ;
- clear these quantities and sales values from the initial cube;
- read from the export file to load them again in the initial quantity / sales value.

I think that could work, but by principle I don't like to export/import data through csv when I can avoid it

Can someone think of a way to do that by dataflow or any other internal approach ? I'm sure this is something somebody already did around here

Thanks for your time and your ideas !

Etienne

------------------------------
Etienne CAUSSE
Finance Project Manager
France
------------------------------

Stefan Böhm

Hi Jon,

without understanding the process 100%, here's a quick thought about how to avoid export/imports.

If you have a second independant Product entity (could be Replicated, could be a manual creation) then you can map the product:replacement relationship in a cube like this.

If you join (using the Join operation) the original cube witht this mapping, you will recieve a target cube with both the original and the replacement product.

You could then via dataflow lose the real Product entity, and again using a join and a mapping cube with a 1:1 mapping of products (real and replicated entity), recieve a cube again with the real and the replicated Product. Use a DF again to lose the replicated entity and now you've shifted values from one product to its replacement.

Of course you'd still have to think about a smart way to maybe clear the old product, but this is a viable approach without using an export/import.

Kind regards,
Stefan

------------------------------
Stefan Böhm
Consultant
Board Community
Germany
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Stefan Böhm,
Thanks for your input.
I have thought about this kind of design, but due to the size of the product entity (>250K members), a cube with product/replicated product/geography/customer dimensions would have a huge sparse and therefore very poor performances on the join.

These days I'm thinking maybe an export/import operation through the API instead of export/import by CSV could also be nice - I'll come back to that when the problem comes in again (it has been deprioritized in between...).

Etienne

-------------------------------------------