Creating time dependent hierarchy from non time dependent source

[Former Member]

Hi,

I need to create a time dependent hierarchy, source is Non SAP system and time independent. It does not have any date from or dateto. I only need to track changes at end of every month. We need capability to run reports based on historical hierarchy structure based on data from inforprovider.

I tried below options:

1. Created "Entire Hierarchy is Time-Dependent". In BW hierarchy's were loaded for each period. But, in Bex I only see three options, default date, choose specific Key date or reference a Date Variable. I do not see option for "From Data, Derivation type". I guess I need fourth option to link hierarchies based on data in cube.

2. Created "Time-Dependent Hierarchy Structures", with temporal hierarchy joins. But, in this case when structure changes, hierarchy strcutre is overwriting instead of phasing out and phasing in a new record.

Can you please advice, how we can accomplish this.

Any help highly appreciated.

Regards,

Chandra.

[MGrob · Accepted solution]

Hi

You're right if you plan on looking at your hierarchy at various points in time you need to create a temporal hierarchy join. As your source is nonsap you have to code or derive some date information at one point. If you work with the key date then your hierarchy will show the valid state it has between your from and to date slice.

hope that helps

Martin

[Former Member · Accepted solution]

Thank you for your response. To your point I can safely rule out option 1, ie entire hierarchy time dependent. Since we cannot look back on structure based on a date in data.

For Option 2: Are you trying to say, I have to some how identify a structure change (may be complex ABAP), and code to phase out old and phase in a new node?

I am looking forward to a more detailed and specific solution direction.

Appreciate any help

Regards

Chandra.

[Former Member · Accepted solution]

Hi Chandra,

You can do one thing here. You can add multiple layer in first layer add DSO which will take care about changes and when you are loading data in further layer (Again in one more DSO) right a routine and update delta records and insert valid from (today's date) and valid to date for changed records.

Then do full load to Hierarchy from this DSO so that time dependent master data gets updated.

We have used similar method for a Master Data, you can try to do it for Hierarchy.

Regards,

Ganesh

[Former Member · Accepted solution]

Hello Chandra,

Since you want to track hierachy changes only at the end of every month, can we store the hierarchy data from non SAP source in a DSO at the end of the month? Now your existing hierarchy should be something like this, Node21, costcenter (52B), Dateto (31.12.9999) and Datefrom (01.01.1000). Write a ABAP program which identifies the changes in the new hierarchy data from existing hierarchy and prepare a file with adjusted Dateto and Datefrom. For e.g. if you load at the end of November and your costcenter 52B has changed, then prepare something like this, Node21, costcenter (52B), Dateto (30.11.2013) and datefrom (01.01.1000) and second record Node151, costcenter (52B), Dateto (31.12.9999) and Datefrom (01.12.2013).

After the file is created, delete your existing hierachy using SAP provded APIs and load your prepared file. Maybe someone can give you even better idea to adjust datefrom and dateto ( !)

[Former Member · Accepted solution]

Thank you all for your valuable inputs.

I was looking for more simpler solution, and avoid heavy routines, especially since we have complex hierarchy with nodes with more than one parent. I thought BW should provide an easier option to run  hierarchies with temporal join with data, even with 'Entire hierarchy is Time-dependent'.

I am stuck with similar constraint for time dependent master data attributes. Bex lets you lookback at historical attributes based on fixed (single value ) Key date, but I find no option to display attribute based on a keydate derived dynamically from historical data in cube. Do you guys find this as a limitation? I wish I am misisng something...

Appreciate your quick response.

Regards,

Chandra.

[Former Member · Accepted solution]

in case of attributes, it is easier to model it as transaction data itself. While loading transaction data, lookup master data and fill the attributes. The history will be maintained in the cube. (As on that day status of attributes - historical reporting). Add time dependent attribtes in master data and you don't have problems in data reload scenarios as well (you can still build historical status).

[Former Member · Accepted solution]

Arunan,

Thank you.

Looks like storing all required attributes as characteristics in transaction data, is the only option. I am also weighing on storing flat hierarchy nodes in transaction data, as a mechanisam to get historical hierarchy structure.

Thank you all.

Regards,

Chandra.

[Former Member · Accepted solution]

yes you can store the hierarchy as well in transaction data in a flat format with multiple characters. I guess you will loose the tree structure display in report but anyways, you can achieve the reporting needs.

[Former Member · Accepted solution]

Arunan,

Thank you for support. We plan to use Analysis for historical  power users. So navigation should not be an issue.

Thank you.

Chandra.