Stop Rollup for Fact Items

[cognostechie]

I am creating a Framework Model for a healthcare company and the data is the Lab Results. So, when a guy goes to hospital, the doctor asks him to go for a Blood Test, Glucose Test, Cholesterol test etc. The Test Result is a number so let's say the guy's test result says his Cholesterol is  170. Now the same guy goes to the hospital again after 6 months and his Cholesterol is 190.

Issue - When I drag and drop the Name and the Test Result, it shows one line for that guy and the number shown is 360 which is wrong. It is adding up the number from both the lines !

This is a very common situation. It used to apply to Inventory of Products which is not additive so in the earlier versions of FM, we always used to mark that Fact column as 'Unsupported' (The property Regular Aggregate was marked as Unsupported) which used to prevent automatic summing up but it is not doing so in 10.1.   Did something change or are we supposed to do it another way?

[cognostechie]

Ok, I marked the usage property as 'Unknown' and that worked but was it always like this? I remember setting only the Aggregate property to 'Unsupported' and that used to work without having to mark the usage property to 'Unknown'.

[tjohnson3050]

By default any numeric data item gets a usage property set to Fact.  If a number should never be aggregated, the usage property should be set to attribute, then it will never aggregate.

[cognostechie]

I know that but my question was different.

[CognosPaul]

It sounds like the Test Result is a numeric value, that needs to be aggregated as "Last".

Getting the last value is something more commonly found in dimensional models. While you should never mix a DMR and relational model, sometimes you can get away with it. Create a DMR time dimension, and a measures dimension. Drag in Lab Result into the measures, and set the Aggregation Rules to Last against your time dimension. Use the DMR Time Dimension and the Lab Result from the Measure Dimension.

[blom0344]

numerical values are only true facts if they are additive. You cannot perform an additive calculation on blood sugar levels, hence not a true fact. If you have a date(time) available you can use a special filter like:

[checkdate] = maximum(checkdate for [patientno])

[cognostechie]

blom - I do have a column called 'Last Test Result Date' and a standalone filter associated with that in case they want to see only the most recent Test result for that individual but they also want to be able to see previous results for analytical purposes to determine how the patient is doing compared to the past.

Paul - There is a max function in relational too which will do exactly the same as 'Last' in DMR (if that's what you meant).

Well, I figured what tjohnson said is how I had done in the past when I saw my earlier models. It's just that it was so many years ago and I have been doing mostly cubes and ETL recently that it just slipped my mind ! Actually when you mark it as Attribute/Unknown, it marks the Regular Aggregate property to 'Count' which will also cause problems in this case. So apart from setting the usage to Attribute/Unknown, also mark the Regular Aggregate as 'Unsupported' and it will treat it as a Non-additive measure. We can still perform calculations on the data because it still treats the data as numeric but it does not use the sum function in the SQL. I prefer to mark the usage as 'Unknown' because it is really not an attribute.     

[CognosPaul]

It's important to remember that Max is not the same as Last. It's also important to remember that a person may not be tested for each metric at the same time.

Consider the following:

2012-01-01 | A |  95
2012-01-01 | B |  65
2012-01-01 | C |  73
2012-02-11 | A | 105
2012-04-16 | C |  25


The last values are:

A: 105
C:  25
B:  65

Max values:

A: 105
C:  73
B:  65

Values on the last day of data:

C |  25

Ultimately, the best way to deal with this scenario is to treat it as an SCD, and rebuild the ETL accordingly.

Using a DMR model will also allow you to compare against the previous values.

[cognostechie]

You are correct Paul. DMR would do it but I am also creating Cubes. Not sure if I described myself clearly so I will share another way to achieve the same result with a relational package without changing the ETL . Create a query subject as

Select. CustId , max(date) as 'Last Date' from .....
Join this to the customer query subject on 1:1 .  In the business layer, add. this Last Date as a column to the customer query subject.  Create a standalone filter with the expression .. Transaction Date (of Fact table) equals Last Date.
In the report, just drag this filter and you get the last transaction of each individual (same as using the Last function).  Without using this filter, they can also see all tests for each individual.