I have a finance cube where I use the Lastnonempty function to get the last balance of an account. This works fine in the time dimension, but when I look at the account hierarchy, e.g. a parent account it does not display the sum of the children but the lastnonempty value. Even when I specify an unary operator, the result is the same.
PS. This runs currently on a Developer Edition.
|SoerenK Wednesday, December 05, 2007 10:35 AM|
Are you explicitly using LastNonEmpty as your aggregation function or have you enabled Account Intelligence and have assigned LastNonEmpty to this specific account type? If you have not gone through the process of setting up Account Intelligence, unary operators will not work.
|Bryan C. Smith Wednesday, December 05, 2007 1:13 PM|
I have tried both solutions, but with the same result.
As far as I can interpreted the books a semiadditive aggregation like the lastnonempty is semi in the sense that it gives the last nonempty value along the time dimension, but acts a an ordinary sum in all other dimensions?
|SoerenK Wednesday, December 05, 2007 4:18 PM|
".. a semiadditive aggregation like the lastnonempty is semi in the sense that it gives the last nonempty value along the time dimension, but acts a an ordinary sum in all other dimensions .." - but maybe in your scenario, the child values, which you're expecting to be sum, occur at different points on the time dimension? In that case, only the values at the last point in the time dimension with data willget summed.
|Deepak Puri Wednesday, December 05, 2007 5:55 PM|
I don't think that is the case here as Ihave the correct values at all the leaves. I have included an example here fromExcel where the problem is visible. The Amount is the movement amount in the finance cube and the balance is calculated in the ETL process on all balance accounts. AsI only have 9 month of financial data within the first fical year including opening balances, I can use the movement Amount as a reference for the balance calculations.
As you can see in the spreadsheet summing up to a parent for the balance value looks as it finds a lastnonempty value and inserts that at the parent.
Sorry that part of the example is in Danish, But you only need to know that AKTIVER = ASSETS
| I. Immaterielle anlægsaktiver
| II. Materielle anlægsaktiver
| III. Finansielle anlægsaktiver
As you can see in the example the total of Goodwill should have been 9.813.283,72, but it is 2.706.433,17. Exactly the same as the account INDRLEJTG. You can also see that it gets the correct values at the leaves, but the sum is incorrect.
PS. one thought I have had is that my time dimensionhave a date field as a primary key and not an integer TimeKey as I have seen in SSAS Step By Step bookfrom Microsoft. Could that have an impact?
|SoerenK Wednesday, December 05, 2007 8:25 PM|
"As you can see in the spreadsheet summing up to a parent for the balance value looks as it finds a lastnonempty value and inserts that at the parent. .." - just to clarify, take the example of Goodwill (Balance = 2.706.433,17). The balance is tha same as for the child: INDRLEJTG alone. But if you show the Balance ofall children across your time dimension, on what date does this last value (Balance = 2.706.433,17) occur; and are all the other child Balances (GOODWA, .. etc) empty on that date?
|Deepak Puri Wednesday, December 05, 2007 8:50 PM|
The last value (balance = 2.706.433,17) occurs in the transaction below:
AcctName BookipingDate Amount Balance
|Indr lej lokaler tilgang
None of the orther transaction/balances in that part of hierarchy is dated in September, The closest i get is 2007-08-31.
Does that influence on how the lastnonempty funtion sums across non time dimensions?
|SoerenK Wednesday, December 05, 2007 10:00 PM|
I was struggling with this concept too. When working with individual cell, the lastnonempty() is very straightforward. When working with multiple cells and aggregation, this is where the confusion arises. What lastnonempty() does on the aggregation of multiple members on non time dimension is: find the lastest time member where one of the non time members has value on, then apply sum across the members on this latest time member. This is exactly what happened in your case, only one line has value on sept, which is the lastest time being applied to the rest of lines resulting no values except this line.
This is really a calculating order issue. Ideally, you wish it would calculate the individual member using lastnonempty() function (each member get values on different last time member possibly), then sum the results, which seems more useful to me.
|Yongli Yang Wednesday, December 05, 2007 10:31 PM|
So the simple LastNonEmpty aggregation will not return the desired result in this case - but I would have expected that a '+' custom rollup unary operator for the child accounts would work, summing up each leaf LastNonEmpty value.
|Deepak Puri Wednesday, December 05, 2007 11:34 PM|
Tkanks for the help it has been very useful.
Just to summarise:
The Lastnonempty function only add values, in non time dimensions,that are aligned in the time dimension. Meaning all balance transactions must have the same date as onlybalances of the same newest date will be added.
I tried to alignall the latesttransactions to the same date - and it now works!
This leaves my with a newproblem:
The financecubewill contain around 12dimensions (site,department, group , enterprise, .....). Lets say on an average there are 10 entries in each dimension and about 1500 account number. Then we need to generate 1500 * (12)^10 rows to cover all dimensionsat each time grain. There must be a better way?
|SoerenK Thursday, December 06, 2007 7:17 PM|
Have you considerred Deepak's suggestion to use rollup operator? I am also thinking that you might be able to define calculation to achieve what you want. If your data member is in sort of hieararchy structure, you could calculate the goodwill item by summing its children member against lastnonempty measure.
I have not tried this myself,but it is something worth a try. I don't know how the current time member play a role in this situation.
|Yongli Yang Thursday, December 06, 2007 11:12 PM|
Since there are many dimensions other than Account and Time, is it true that all Balance entries for a given Account are aligned on Time? In that case, dynamically rolling Balance up from the leaf Accounts (using rollup operators or calculations) may not be too slow for 1500 accounts.
|Deepak Puri Friday, December 07, 2007 1:22 AM|