Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parent-child dimension alternative


Parent-child dimension alternative

Author
Message
Douglas Acosta-479754
Douglas Acosta-479754
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 104
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
Douglas Acosta-479754
Douglas Acosta-479754
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 104
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
Douglas Acosta-479754
Douglas Acosta-479754
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 104
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search