Summarizing LastNonEmpty values
-
Monday, January 21, 2013 7:28 PM
Hi,
I’m working on a cube for real estate appraisal. These values are semi-additives so I use the last non-empty value (I don’t use the LastNonEmpty function because I’m in Standard Edition but the behavior is the same).
Each three years, an absolute value is given to each property. For that reason, in the Calendar hierarchy, we have a level over the Year level, so it goes something like this:
Roll year (2008-2010, 2011-2013, 2014-2016, etc.)
Year
Trimester
Month
Date
In-between assessment rolls, certificates are emitted, mainly after refurbishment, which changes the value of the property.
My problem is when I check the values over time. For a given neighborhood, in 2011, only one certificate was emitted in September for one property, which increase its value from 200,000 to 250,000. Here’s an example of what I get when I query the cube:
January 10,000,000
February
March
April
May
June
July
August
September 250,000
October
November
December
I want a value to be displayed for every month:
January 10,000,000
February 10,000,000
March 10,000,000
April 10,000,000
May 10,000,000
June 10,000,000
July 10,000,000
August 10,000,000
September 10,050,000
October 10,050,000
November 10,050,000
December 10,050,000
Is there a way to do that?
I hope this makes sense to you.
All Replies
-
Tuesday, January 22, 2013 9:10 AM
you could consider having a fact record, using a view, for each property for each month. that certainly boosts the cube size, but that is usually not a problem. as an alternative, you could go with a rather unorthodox approach. store only the differences between the last and the new appraisal. name the measures appropriately like "value diff". of course you need to set the opening values as diffs on the first month. this should aggregate normally. add a time calculation that cumulates the data from the beginning to the actual period, and that would be the actual value. such a calculation would be as easy as
Aggregate(null:[Date].[Calendar].CurrentMember, [Measures].[Value])
i have never actually tried this approach, so i don't know what traps you might run into. but it should fly in theory.
-
Tuesday, January 22, 2013 9:54 PM Thanks Krisztián,
We want to avoid the first solution. We already tried that and since we want to drill to each individual date, it means a very bloated cube with very poor performance.
At first look, your second solution seems to work. But so far, I only have one roll to test against so I can't test it against multiple rolls. The way I understand your proposition, it would aggregate against all my roles, not just the current one.
FYI, I have found this blog article and so far, it's the most promising solution I got: http://richardlees.blogspot.ca/2010/07/getting-last-non-empty-value.html
Thanks again for taking the time to answer!- Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Wednesday, January 30, 2013 1:47 AM
-
Wednesday, January 23, 2013 8:58 AMas i described, it would start with the first time period, and cumulate all the way. but it can be easily tweaked to cumulate from the beginning of each roll. the only problem is that than you can not have reports covering more than one roll periods. or only with special care.
.png)
