Hello, I think that my question was not so easy to understand that's why anybody commented on it, ok, here is the solution I found, and works nicely!
it was taken from the paterns published by Marco Russo
this would be the enhanced db diagram
the parent-child hierarchy relies on the pvi_code (autonumeric) and pvi_parent fields of the pnl_view (table/dimension), this asures that every element in this dimension is unique.
Since the "Unary Operator" property of the parent-child hierarchy is not working, the pvi_sign comes to save the day, the sign is applied to the fact table by joining the pnl_view and pre_fact_table, this produces the pnl_fact table you see in the diagram.
now the udm model for my requirements is complete, I have the Schema
dimension to change the structure of the Balance
report, and depending on the parameters set in the pnl_view table (position and sign) I can "operate" in different manners with the balance elements to show several indexes on-the-fly.
Ok, there is an additional requirement that put me to the test also, and was an area
view, the balance sheet is an aggregation from several departments, some figures come directly from transactional systems but there are others that are calculations based on lower levels of the hierarchy and are independent from the department.
The challenge here was to turn a single balance sheet into a departmental balance sheet, and of course the sum of all departments should be the company balance sheet.
To solve this I used CTE to "discover" the logical order of all the operations involved in the balance sheet and find the "leafs" of the tree, this combined with another signs set to be applied to the leafs and upper levels in order to get the full tree per department.
After doing so all the balance sheets are pre-calculated (including exchange rates) ready to be processed by SSAS.
I hope this thread could help somebody, to solve his/her recurssion troubles with hierarchical dimensions