Load dimension and fact from file with different hierarchy structures

[retko1985]

Hello,

I have a complex task, where I have quite uncommon file format, and I am not able to Load it correctly. I hope someone will be able to help me or point to the right direction.

Here is an example very similar to the file that I have:

     Also in attachment as excel.

Description.

- There are some hierarchy levels (you can imagine like Companies which are owned by other Companies)

     - B is a country, which has different structure from country A.

     - A is quite clear, A33333 is Company on level 3; but B33333 is NOT level 5, but its level is determined by column Level_4, by the suffix of its value - so B33333 is level 3 because on the same row we have Level 4 = A0001L3 --- L3 is saying that it is Level_3.

     - Level_3 for B is skipped.

Other important thing is, that this file is already aggregated, and I only want to show numbers (using only, not sum) based on the selections made.  (I wont be able to select more than 1 company!, so only should return always 1 value - or 1 row)

- So for example, if I select A, but nothing else - my text box for example will show 90.

- If I select A33333, it will show 100.

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

So I was thinking how to work with this, and the thought is to create a hierarchy table having 3 main columns, like superior_key; subordinate_key; name. This way, if I get again different structure for other country, I will be able to make it same.

Can someone help me with this, how should I approach this.

Thank you.

[retko1985]

Does anyone have any idea how can I process file like this??

Thanks