Reporting on Measures - Data seems to hold multiple duplicate rows

[Martyn Houghton]

Whilst trying to diagnose some discrepancies between the number of rows returned via a measure and a report, I though I would try running a report on the saved columns held with the measure by using the 'Measure' source option in the report designer. However the output is not quite what I was expecting. The measure in question captures the data ranging column (example below is date_logged - h_datelogged) as the second column value, so entering the same  period as the measure  sample into the report, I get between 3 to 4 times as many rows returned with duplicate rows, but not necessarily identical.

It appears the measure data structure is not quite I was expecting, which was one row for each request that was counted in the measure that meet the criteria. I have been trying to work out what logic or structure it is using but I am stumped. 

Anyone else tried reporting on measure entities and worked out what it is doing?

Cheers

Martyn
 

[NeilWJ]

Hi Martyn,

It records rows per measure sample instance. Each time measure runs it record additional data per row used to calc that sample. If your measure runs once a day you will have 7 sets of additional row data per sampled record.

It none of the additional column values you are recording have changed over that 7 days then they will look like duplicates records but they are not. They are just a snapshot of that data at the time the sample was taken. 

So lets say call F000001 is active on monday.... measure runs and records a sample row. On Tuesday the measure runs again and F000001 is still active and so is included in sample and another sample row is created. Over the period of 7 days F000001 is always active and so is included in each sample execution. This means you can expect to see 7 rows in the h_data_sampledata table for  F000001 for that 7 day period....the additional data fields capture may or may not be the same depending on if that actual data changed in the record during the sample periods.


Cheers

[Martyn Houghton]

@NeilWJ

Thanks for the clarification, however how do we retrieve the specific records which are relating to each sample, in your example how do I report on rows in Mondays sample only?

Also given my measures are measuring request logged or requests closed in the sample period, in our case monthly, they should only appear in one sample?

Cheers

Martyn

[NeilWJ]

Hi Martyn,

So it should be a join between "h_data_samples" and "h_data_sampledata" ... in your db query tool try query like below (obv change out values for your ones) and see if it gives you duplicates...if it does then the measures service is doing something wrong when storing the data..

select h_data_sampledata.* FROM h_data_measures INNER JOIN h_data_samples ON h_data_measures.h_id = h_data_samples.h_fk_measure_id
INNER JOIN h_data_sampledata ON h_data_samples.h_id = h_data_sampledata.h_fk_sample_id
WHERE h_data_measures.h_title = 'Total Incidents Logged by Source - Monthly'
AND h_data_samples.h_sample_date between '2010-01-01 00:00:00' and '2019-12-31 23:59:59'

Cheers

[Martyn Houghton]

@NeilWJ

I will give that a go and let you know.

In terms of using a report on a measure, I presume by selecting the measure from the drop down would select the correct measure, so the report would only need to select on the h_sample_date field to equate to your SQL above?

Cheers

Martyn

[Martyn Houghton]

@NeilWJ

I have used your query to get the count of records in the sample source to compare it to the measure count, however though I no longer have duplicates it highlights the discrepancies between the number of rows returned by the query and the number displayed on the measure. 

 

Query returns 461 rows, which is what I believe is correct and the measure shows 268, which is wrong and too low.

The discrepancies are logged with Hornbill Support under IN00156006.

Cheers

Martyn

 

[Martyn Houghton]

@NeilWJ

Now taking you SQL and attempting to map this to a Report on a Measure just using the same criteria as the SQL

Selecting all columns and then the same sample period.

The results is very large csv with 10,135 rows, with blank rows and results from different measures.

It appears that even though you select a measure on the General tab it is not applied.

Attempting to put this in manual is not possible as the Filter option for Measure Sample does not list any columns.

 

There appear to be a couple of Bugs in the reporting on measures component?

Cheers

Martyn

 

[NeilWJ]

Hi Martyn,

I will take a look at the reporting and will get support to replicate your measure setup so we can see what its doing when collecting data.

Cheers

[Martyn Houghton]

@NeilWJ

Thanks. Let me know if you need any further information.

Cheers

Martyn

[NeilWJ]

Have allocated time tomorrow to look at the reporting. So will let you know.

Cheers

[NeilWJ]

@Martyn Houghton,

So took a look at the reporting and believe i have addressed reporting side. Going to have a run through it with dean on monday...i will send you the sql it generates so you can try it in db tool just to see difference. With insights next week i wont be able to push this to live until after that and that said hope to see you there on wednesday.

Cheers

[Martyn Houghton]

@NeilWJ

Thanks for the update and see you on Wednesday.

Cheers

Martyn

[NeilWJ]

@Martyn Houghton not sure if it was communicated or if you are aware but the the sql query for getting measure sample counts is now stored in h_sql_used column in the h_data_samples table. This is currently live.

[Martyn Houghton]

@NeilWJ

Thanks for the confirmation. That has helped is get to the bottom of our anomalies.

Cheers

Martyn