Parent-child dimension alternative
-
Hello
I am a newbie on Analysis Services so be patient.
I manage to build an accountable cube which has a parent-child dimension to show the PnL balance (profit and loss), but I was wondering if is possible to have multiple PnL Balance views using a separate table to hold the structure of the alternative balance views, I built a small diagram to show how this could work, I hope it gets attached
By now the cube works with the PnL_Position table to hold the hierachy of the balance view, in this diagram I did not added the pnl_parent column because I wanted to focus on the PnL_View table to manage the hierarchy.
It is worth to mention that in the records in the PnL_View are not encouraged to be unique, the "report-designer" could place an account-position several times inside the view, this could be the main reason that this approach is not possible. But what would happen if the records in the PnLView were unique?
If anyone could help on solving or giving some light over this issue would be great
Best regards
--
Douglas
-
Hello everybody, guess what! I got good and bad news, the good news first, I found what I was looking for, in this link (great!)
Many-to-many dimensions (by marcorusso)
Now the bad news, apparently there is a bug with this exciting feature of SSAS 2005, I don't have SSAS 2008 so I kindly ask any of you to see if this approach works on the new version of SSAS, anyway, the bug is described (by the same author) in the following link.
Wrong calculation for unary operator with many-to-many relationships by marcorusso
I think a workaround to this bug could be playing with the "signs law" and do a balance sheet using only the "-" sign (as far as the author says, it work suitable using only the minus sign), challenging isn't it? So the issue is not solved yet...
Best regards
--
Douglas
-
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 or department 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
Best regards
--
Douglas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply