New post
Question
· Oct 22, 2017

Retrieving the absolute last entry from set of members in a level

#Analytics #Analyzer #InterSystems IRIS BI (DeepSee)

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:

%LAST([SensorDate].Members,Measures.SensorReading)

 

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 - 

Discussion (12)3
Log in or sign up to continue
Alessandro Marin · Oct 24, 2017

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

0 0
Alessandro Marin · Oct 25, 2017

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
}

}
 

0 0
Evgeny Shvarov · Oct 23, 2017

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.

0 0
Alessandro Marin · Oct 24, 2017

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

0 0
Steve Pisani · Oct 25, 2017

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..

0 0