Yes, that would work. You can test that in RelatedCubes/Patients and RelatedCubes/Patients. The downside of this approach is having to use cube relationships, which are more complex to maintain and in general have slower queries.

Hi Evgeny,

I think I'd better leave the answer to somebody with more experience in this field.

I did not plan to introduce such a script though I actually do have scripts doing operations on databases, mappings, security, etc. It would not be too difficult to implement such a script. There is already something similar for Ensemble namespaces.

One problem however is that, as I hope it will be clear from my series, there is no universal solution for all user cases. In spite of this my opinion is that the solution of intermediate complexity outlined here would be the most likely to be adopted by most users.

What is exactly that custom listing? Is it defined in Listing Group (those ones should work using %LISTING)?

I am glad point 1 helped, though I see that you flagged as accepted answer another one which is about the problem you have not yet solved.

> when I choose the dimension appears only the name of the relation and in the field dimension level didn´t appear anything. 

Please clarify what the issue is, preferably in a new post as Evgeny suggested

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
}

}
 

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

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