Retrieving the absolute last entry from set of members in a level
Hi,
I have a persistent class “sp.SensorReading” which has a number of Properties: Date, SensorName, SensorReading. (sometimes multiple readings from the same sensor, on a given day)
...
Temp 28 Jan 33.5
Temp 29 Jan 31.2
Temp 30 Jan 33.1
Temp 30 Jan 34.1
Temp 31 Jan 32.1
Temp 31 Jan 33.1
Say I built a cube built to analyse data from this persistent class. With a simple date dimension, and SensoRName filter, I can show the default COUNT of readings for a given month/year:
Jan-2015 94
Feb-2015 54
Mar-2015 78
…
I have a date dimension with levels for Year, Quarter, MonthYear, and Date. Instead of the default COUNT measure , I want to get the last value pushed out by a sensor for a given selected period. So let’s say, the last value for temperature, for each date dimension member: This is what I would want to see if selecting on MonthYear:
Jan-2015 33.1
Feb-2015 32.2
Mar-2015 31.8
…
My approach is be to define a calculated measure which, returns the reading from the last member of the selected group of readings, using the MDX %LAST function.
Something like the following defined in the cube - Calculated Measure: LastReading:
However – As in the example for January above, sometimes, I get multiple readings on the same day for the same sensor, and if that happens to be the last day of the month, the reading are added.
Jan-2015 65.2
Feb-2015 32.2
Mar-2015 31.8
Is it possible to have a pure MDX - based expression to fix this, ie, when %LAST(...Members) actually returns multiple entries ?
(ie, Can I achieve what I want without introducing a cube dimension that is resolved based on whether a particular reading is the last reading of the month, at data upload or build – because – the period is not always defined as ‘month’, but could be Year, YearQuarter, or even arbitrary dates.
thanks -
Hi, Steve!
Hope this thread might be helpful.
I do not always have sensor reading on the last day of a month (Jan 31st, Feb 28, etc).
So similar to the issue you ran into, I get blank results when there is no reading on the last day. Of course, I want to get what essentially would be the last reading, by looking for the latest reading in that month.
Close, but still not solution... :(
err... no, I don't want to use averages. they multiple readings on the same last day can be different ,and I want the last number.
Hi, Alessandro!
But why it is not the case of what we already have solved?
In Steve's example there are multiple (two) facts in one day, and he need to get the latest. IMO this means that Steve need to get the time of the reading (or maybe get the reading in a certain day that was added to the source table last). When using time dimensions we do not have a level more granular than DayMonthYear that can compare the time part of a reading.
I tried working around this problem but so far without success. With a plugin we have access to the lowest-level data, in particular the whole time stamp containing the time of the reading (I am assuming Steve used in his source class a regular time stamp). In my test I was able to use that piece of information to get all readings in one single period (day, month,..), compare the time stamps, and return only the most recent.
Unless Steve is ok with using Average as the aggregation function for his SensorReading measure as suggested by Asaf
Yes - that is the problem. and 'no' to using average.
Got it now.
Steve, you can introduce your own data hierarchy build with expressions on this property which will have levels:
Year, Month, Day, hour (or minute).
And thus with LASTCHILD on Day level you can get the last member of the day.
No problem, your case (no time part in the timestamp, just get the fact that comes last in your source table) is even easier. Once you make the fact order in your source table work this plugin should work.
This plugin uses SensorReading, but it is possible to make it more dynamic (hint: implement a filter definition and %OnGetListingFields).
/// Use this plugin in a calculated measure as %KPI("User.LastFact","LASTFACT",1,"%CONTEXT")
Class User.PluginLast Extends %DeepSee.KPIPlugIn
{
/// Cube(s) that this plug-in is based on.
Parameter BASECUBE = "*";
/// SourceTable
Parameter LISTINGSOURCE = "SourceTable";
Parameter PLUGINTYPE = "Pivot";
// The ID is probably not even needed:
Parameter LISTINGFIELDS = "SensorReading, %ID as ID";
XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
{
<kpi name="User.LastFact" displayName="LastFact" caption="LastFact" >
<filter name="%cube" displayName="Subject Area"/>
<property name="LASTFACT" displayName="LastFact" description="" />
</kpi>
}
/// Get the base query for this plug-in.
Method %OnGetMDX(ByRef pMDX As %String) As %Status
{
Set tBaseCube = ""
// Use %cube filter to find the base cube
If $IsObject(..%filterValues) {
If (..%filterValues.%cube'="") {
Set tBaseCube = ..%filterValues.%cube
}
}
If (tBaseCube'="") {
Set pMDX = "SELECT FROM "_tBaseCube
}
Quit $$$OK
}
/// Compute the LastFact of values within the result set.
Method %OnCompute(pSQLRS As %SQL.StatementResult, pFactCount As %Integer) As %Status
{
Set tSC = $$$OK
Try {
Set tErrMsg = ""
While (pSQLRS.%Next(.tSC)) {
If $$$ISERR(tSC) {
Set tErrMsg = $System.Status.GetErrorText(tSC)
Quit
}
// Get the last fact added to the table. If unsure use pSQLRS.ID
Set tValue = pSQLRS.SensorReading
If ($ZSTRIP($P(tValue,":"),"<>W")="ERROR") {
Set tErrMsg = $P(tValue,":",2)
Quit
}
}
// place answer in KPI output
Set ..%seriesCount = 1
Set ..%seriesNames(1) = "LastFact"
If (tErrMsg'="") {
Set ..%data(1,"LASTFACT") = tErrMsg
} Else {
Set ..%data(1,"LASTFACT") = tValue
}
}
Catch(ex) {
Set tSC = ex.AsStatus()
}
Quit tSC
}
}
Steve, but it does for me exactly what you are describing.
WITH MEMBER [DateOfSale].[LastDayOfMonthRevenue] AS 'AGGREGATE([DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD,MEASURES.[Amount Sold ])'
This will show last child member which has value in the next level of hierarchy. And with AGGREGATE you can alter the Measure you want to show up.
Hope [@Alessandro Marin] can provide more info.
Hi Steve,
The %LAST MDX function returns a member and not a set.
What is the significance of getting the last reading (as oppose to getting the AVG)? Are you doing it to avoid the duplication?
Thanks,
Asaf
I would opt for a PlugIn. I need some more time to double check my implementation but I think it is working. I tried to make this work with MDX functions but we would need a time function more granular than DayMonthYear which you are using to get the dates.
I call %KPI with my plugin passing in a measure (your "SensorReading") and %CONTEXT to get the context of the row member. The listing fields (or the %OnGetListingFields method) can be used to make the measure and the Date (I am assuming you have the full date with time in your source class) available to the %SQL.StatementResult object used in the %OnCompute.
In my test I am using these listing fields from the source table, and I do not need to implement %OnGetListingFields:
Parameter LISTINGSOURCE = "SourceTable";
/// This allows you to do pSQLRS.SensorReading and pSQLRS.MyDate in %OnCompute:
Parameter LISTINGFIELDS = "SensorReading, MyDate";
In %OnCompute (which I modified from %DeepSee.PlugIn.Median) you might have to make sure the temperature of the most recent fact is returned.
I have limited time this week but it is an interesting problem to work on. I will probably send you more details about my KPI asap. Thank you for your patience
So it would seem, that I need to differentiate, in the DeepSee index tables, which of the two readings on the last day of the month, is actually the last one.
I do not have time. :)
The data is being loaded from a flat file into the persistent cache table, which has just Sensor/date/value.. (prior to the DeepSee indices being built). So...., the sequence is important, and the row ID in the persistent cache class becomes the differentiation - and potentially - something I can use.
In the absence of time, I thought I should create a dimension that is calculated at index build time based on the source.%Id(), or a combination of Date_source.%Id(). The ID is an integer that is incremented with every new row in the source table.
I feel certain I can use the source ID to differentiate what is the last reading for the day. I'll be trying an approach along the lines of using the raw ID for help. Any other ideas welcome..
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue