UDS HDA Problem

[Former Member]

Hi there,

we have a problem that a UDS HDA access does not deliver all data. For querying mass data we would like to reduce the amount of data via dividing the query time range into several intervals. Example configuration for the query:

Mode: Statistics

Method: AVG

Duration: 60

Duration Unit: M

Number Intervalls: 60

With this settings we would like to have back 60 values which are the average over one minute each. The number of rows given back is correct (60) but the last few values are missing! In the example for 60 Minutes these are the last 3 values having a value of "NA". The more data requested the bigger the effect! When requesting 8h (480 Minutes and 480 Intervals) the last 30 values are missing. The UDS logs show no errors. We also double checked with a query from Excel and get back all values.

Any clues?

[jcgood25]

Aside from Duration and DurationUnits, what are the specific Tag Query parameters you are modifying?

[Former Member]

I forgot to mention that the row count was set high enough to 50000.

Here are the settings of the template:

<TagQuery AllowBuffering="false" AllowFuture="true" CacheDuration="0"
CacheDurationUnits="M" Comment="" Connector="" DateFormat="MM/dd/yyyy
HH:mm:ss" Debug="false" Description="" DocType="TagQuery"
Duration="480" DurationUnits="M" EndDate="" ExcludeRemotes="true"
Group="" ID="-1" InlineTransform="" IntervalCount="480"
IsCachable="false" Mask="" Method="AVG" Mode="Statistics"
NumberFormat="0.00"
...
Password="" PathID="277" ReaderRoles="XMII Users" Resolution="0"
RowCount="50000" SaveDate="02/15/2010 20:25:38" Schedule=""
Server="UDS_OPCHDA" Service="" StartDate="" TagName.1="Temperature"
...
Time="" TimePeriod="" TotalizerFactor="1.0" Trace="false"
UseGroupMask="false" UserName="" Version="12.0.9 Build(13)"
WriterRoles="XMII Developers,XMII Administrators"/>

All other values, params and names are empty!

Edited by: Matthias Wald on Feb 15, 2010 10:05 PM

Edited by: Matthias Wald on Feb 15, 2010 10:07 PM

[jcgood25]

If the time on your MII server is ahead of the time on your underly HDA datasource (what is it by the way?) then that would explain the last few NA records, but then I would expect the 8 hour test to have the same few NA records at the end for the potential future times.

What version of the OPC-HDA UDS are you using, and are you seeing any related errors in the logs?

Based upon your query template everything seems legit, you are only varying the Duration and IntervalCount to match for 'minutely averages'. The looping should all be done at the UDS level - what do you get if you request a block of time using specific dates and not the last N minutes?

[agentry_src]

I am curious. Why you are using a statistics mode instead of a history query? Not that there is anything wrong with it, I just would not have thought of doing it that way.

Regards,

Mike

Edited by: Michael Appleby on Feb 16, 2010 12:50 PM

[Former Member]

Hello Michael,

yeah, you are right. My excuse for using statistics mode. Did not know that it is for demonstration only.

By the way I tested History mode with a duration of 60 minutes and 60 intervals. This setting brings UDS into 100% CPU and a

query timeout occurrs. So, mode Statistics seems to be the only way to have "partly aggregation" to reduce mass data.

@Jeremy

There are no errors in UDS debug logs. Historian used is CanaryLabs TrendHistorian. Time is the same on all machines.

I have done some further analysis and comparison with the query results from Excel and MII. The result for a fixed time

range of 1 hour and 60 intervals (every minute)

From: 15.02.2010 17:00

To: 15.02.2010 18:00

Intevals: 60

From the result one can see that one entry from MII is "NA", which is the last one. The more data is requested, the more

NA values are returned. The pattern is like this that the data is shifted by one entry. So the last entry in the Excel result

is the entry 17:56:00 in MII.

I have also checked this pattern with more entries (480 minutes, 480 intervals). Then the shift is much bigger but can be

verified. This results in som 30 values as NA.

So, to conclude, this seems to be a bug.

The other question is: Why are all the values in integers? Even if the format is set to "0.00"? In the Excel result can

be seen that this values are reals. Please, do not recommend to use aliasing as this simply is not a convenient solution for

hundreds of tags and brings another layer to maintenance errors into the system.

Thansk a lot!

[Former Member]

Here's the data.

Sorry, the number of intervalls is 30. This means averages for every 2 Minutes.

MII			Excel
DateTime	Temperature		Timestamp	Temperature
02/15/2010 17:00:00	331		2.15.2010 5:00:00 PM	331
02/15/2010 17:02:00	330		2.15.2010 5:02:00 PM	330,96
02/15/2010 17:04:00	331		2.15.2010 5:04:00 PM	330,41
02/15/2010 17:06:00	331		2.15.2010 5:06:00 PM	330,58
02/15/2010 17:08:00	330		2.15.2010 5:08:00 PM	330,58
02/15/2010 17:10:00	330		2.15.2010 5:10:00 PM	330,21
02/15/2010 17:12:00	329		2.15.2010 5:12:00 PM	329,65
02/15/2010 17:14:00	328		2.15.2010 5:14:00 PM	328,75
02/15/2010 17:16:00	326		2.15.2010 5:16:00 PM	327,53
02/15/2010 17:18:00	325		2.15.2010 5:18:00 PM	326,52
02/15/2010 17:20:00	325		2.15.2010 5:20:00 PM	325,57
02/15/2010 17:22:00	323		2.15.2010 5:22:00 PM	324,63
02/15/2010 17:24:00	322		2.15.2010 5:24:00 PM	323,4
02/15/2010 17:26:00	321		2.15.2010 5:26:00 PM	322,52
02/15/2010 17:28:00	321		2.15.2010 5:28:00 PM	321,25
02/15/2010 17:30:00	321		2.15.2010 5:30:00 PM	320,72
02/15/2010 17:32:00	321		2.15.2010 5:32:00 PM	320,88
02/15/2010 17:34:00	323		2.15.2010 5:34:00 PM	321,32
02/15/2010 17:36:00	324		2.15.2010 5:36:00 PM	322,56
02/15/2010 17:38:00	325		2.15.2010 5:38:00 PM	323,59
02/15/2010 17:40:00	326		2.15.2010 5:40:00 PM	324,74
02/15/2010 17:42:00	327		2.15.2010 5:42:00 PM	325,99
02/15/2010 17:44:00	328		2.15.2010 5:44:00 PM	327,07
02/15/2010 17:46:00	329		2.15.2010 5:46:00 PM	328,06
02/15/2010 17:48:00	330		2.15.2010 5:48:00 PM	329,27
02/15/2010 17:50:00	331		2.15.2010 5:50:00 PM	330,26
02/15/2010 17:52:00	332		2.15.2010 5:52:00 PM	330,79
02/15/2010 17:54:00	333		2.15.2010 5:54:00 PM	332,2
02/15/2010 17:56:00	334		2.15.2010 5:56:00 PM	333,44
02/15/2010 17:58:00	-2147483648		2.15.2010 5:58:00 PM	334,37

[agentry_src · Accepted solution]

Hi Matthias,

Can you put the following into a tracer message property:

number(330,96)

and see what it returns when evaluated or run? You maybe running into a number format issue (the commas).

Also, for how many tags are you running each query? And how big is the xml being returned?

Regards,

Mike

Edited by: Michael Appleby on Feb 17, 2010 12:15 PM

[agentry_src]

Matthias,

I also noticed that the numbers in the right hand column are neither consistently rounded or truncated to return the numbers on the left. That confuses me quite a bit.

Is it possible that the two datasets are not the results from the same query? (I hope so)

Thanks,

Mike

[Former Member]

Hello Michael,

the left side of the posted data is the result from MII (see the headings) exported as csv directly from Workbench.

The right side has nothing to do with MII. Otherwise it would not be usefull for prooving MII results. So, the right side shows

the result from an Excel Sheet where a query with the same mode and parameters have been executed within Excel from VB.

The library used is the library form the historian vendor. This is why I use it as an independent validation source.

The number of tags is 1. And the amount of data returned is as you can see. There are only 30 values as requested by the query parameters.

Again, the Excel result matches the MII result but with a shift of one entry and keeping in mind that the MII results are rounded to

integers.

To shorten the whole procedure I could easily do a websession for you and show you the stuff.

thanks,

Matthias

[Former Member · Accepted solution]

Hi Michael,

you are right. the number(330,96) throws an exception whereas number(330.96) is ok.

[agentry_src]

Matthias,

Except for the variance of the numbers, I would say that the numbers are being evaluated until they hit a non-numeric character (comma) and the rest of the value is truncated. But the result sets are different even allowing for that happening. That is what I find confusing. (the third and fourth values in the left column are both 331, but if the value represents truncation due to the commas, the values should both be 330) There are worse deviations further down the two lists.

And just for clarification, you max out the cpu where the UDS resides when running a single tag History query of some nominal row count under 500? If so, that does not sound good. Please confirm that this is the behavior you are seeing? Can you run a History query for the same parameters and post the results for comparison?

For right now, I would prefer to keep our conversation here for all to see. If it turns out to be a bug, then a ticket should be written and then we can have a webex or some such.

I am not sure what to do about the mismatch of number formats. You may be able to inherently change it within NW by changing languages, but I do not remember if that will ripple through to MII (I don't think it will).

Thanks,

Mike

[Former Member]

Hi Michael,

if you shift the rows of the MII result 1 row down then most values do match keeping rounding in mind.

Thats why I think there is probably some interval boundary problem there.

But this issue is different from the other one with 100% CPU. Lets focus first on this one.

If you want I can also post the debug log for the query stated above.

Best Regards

[agentry_src]

Matthias,

I tried the shifting and it still was inconsistent. One reason I wanted to see the History query run is to see if the behavior is similar. I am rather surprised that the CPU would go to 100% for a simple history tag query. Do you happen to know what the sampling interval and compression values are for this specific or group of tags?

Also could you post a 60 rowcount History Event query so we can see "real" recorded data with the same parameters as the other queries? This will flesh out the picture of the OPC data environment.

And yes, I would like to see the debug logs, both UDS and NW.

Thanks,

Mike

[agentry_src]

Matthias,

I just thought of something else. Try doing your query using TWA (Time Weighted Average) instead of AVG (Arithmetic Average). See if the data is a better match for that you see in the Spreadsheet.

Thanks,

Mike

[Former Member]

Hi Michael,

ok, that tipp helped a lot in the correct direction! With TWA,SDV it does not bring any NA values for the time ranges tested above.

But it brought again NA values when extending the duration / time range. With this, I think I found the root cause of the problems

(with the NA values). The NA values came in due to the fact that there are time intervalls where no values are availeable in the

historian.

Result is shown in next post.

There a comparison between HistorEvent and TWA queries is done.

The gap where values are missing is between 11.02.2010 18:12 and 12.02.2010 09:25. What the error is that MII sorts the missing values to the end! This breaks the correlation of timestamp to value as can be seen with the excel result. MII concatenates the next values after the gap directly to the last value.

The first value after the gap 12.02.2010 09:25:00 322 (Excel) is place at 02.11.2010 18:13:00 322.

I think the root cause of the problems is when aggregation intervals have no data. In this case MII sorts them to the end of

the resultset.

[Former Member]

MII HistoryEvent

02.11.2010 18:11:34 310

02.11.2010 18:11:44 311

02.11.2010 18:11:48 310

02.11.2010 18:12:01 311

02.11.2010 18:12:03 311 02.12.2010 09:25:04 323

02.12.2010 09:25:09 321

02.12.2010 09:25:20 323

02.12.2010 09:25:21 322

02.12.2010 09:25:22 321

[Former Member]

MII TWA,SDV

02.11.2010 18:08:00 309

02.11.2010 18:09:00 309

02.11.2010 18:10:00 309

02.11.2010 18:11:00 310

02.11.2010 18:12:00 311 02.11.2010 18:13:00 322

02.11.2010 18:14:00 320

02.11.2010 18:15:00 320

02.11.2010 18:16:00 319

02.11.2010 18:17:00 318

02.11.2010 18:18:00 318

02.11.2010 18:19:00 316

02.11.2010 18:20:00 316

02.11.2010 18:21:00 315

02.11.2010 18:22:00 315

02.11.2010 18:23:00 314

02.11.2010 18:24:00 313

02.11.2010 18:25:00 313

02.11.2010 18:26:00 312

02.11.2010 18:27:00 311

02.11.2010 18:28:00 311

u2026

02.11.2010 18:46:00 302

02.11.2010 18:47:00 301

02.11.2010 18:48:00 -2147483648

02.11.2010 18:49:00 -2147483648

Excel sheet proove (TWA)

11.02.2010 18:08:00 309

11.02.2010 18:09:00 309

11.02.2010 18:10:00 309

11.02.2010 18:11:00 310

11.02.2010 18:12:00 311 11.02.2010 18:13:00 11.02.2010 18:14:00 u2026 12.02.2010 09:22:00 12.02.2010 09:23:00 12.02.2010 09:24:00 12.02.2010 09:25:00 322

12.02.2010 09:26:00 320

12.02.2010 09:27:00 320

12.02.2010 09:28:00 319

12.02.2010 09:29:00 318

12.02.2010 09:30:00 318

12.02.2010 09:31:00 316

12.02.2010 09:32:00 316

12.02.2010 09:33:00 315

12.02.2010 09:34:00 315

12.02.2010 09:35:00 314

12.02.2010 09:36:00 313

[Former Member]

The problems in Average mode shows more often as there are more conditions that lead OPCHDA_NODATA values.

Excerpt from spec:

2.9.2.7. AVERAGE

The average aggregate adds up the values of all good raw data in a given interval, and divides the sum by the number of good values. If any non-good values are ignored in the computation, the aggregate quality will be uncertain/subnormal.

If no good data exists for an interval, the quality of the aggregate for that interval will be bad, OPCHDA_NODATA.

All interval aggregates return timestamp of the start of the interval. Unless otherwise indicated, qualities are good, calculated.

TWA helps in that it interpolates values at interval boundaries. But suffers when there are real gaps (machine down, weekend,

whatever).

The problem with the integers still remains

Any ideas/solutions?

[agentry_src]

Hi Matthias,

Data inconsistencies drive me nuts until I know the cause.

If MII is indeed sorting (and I think that your hypotheses is probably correct), then enter a ticket. That is a bug and it should not work that way.

A brute force method of dealing with the commas would be to do a stringreplace or xslt to perform the same function and replace with decimal points (periods). I will think further on whether there is a more elegant mechanism. I don't think there is anything in the UDS to fix it. There might be something on the historian associated with the user name. It would certainly be worth looking into.

Done for the day, so good luck. I will check back tomorrow.

Thanks for the interesting puzzle,

Mike

[jcgood25]

When you test your query in the browser with xml results what SQLDataType do you see for the tag? Do you see multiple decimals in the xml data?

Is the NumberFormat setting for your query template set to 0? This would explain the 'integer' effect in csv output.

We recently saw this csv behavior with 12.2 testing, but also observed that 12.0 and 12.1 do the same thing. Prior to 11.5 csv output was always the raw format, so whatever number of decimals you saw in the xml would show up in the csv so you could format it however you like, so I believe they will be addressing this, but in the short term you should be able to control the format from the query template (simiilar to the html output will follow the query NumberFormat).

[Former Member]

Hi Jeremy,

as stated in one of the previous posts changeing any of the settings "0 / 0.00 / 0.000" does not do anything to the values in

XML. But the stuff with the integers was my fault. This temperature was indeed an integer. Other R4 values are given as

reals. Hmm, going a bit more deeper into it I think the problem is that the original tag is of type I4 and the average result

is then expected to also be of type I4. Averages are in general of type real, are they? I think we have to look in the spec

for this...

Display setting "number of decimal places" does not have any effect on the xml values. There the full

data is given.

<Rowsets DateCreated="2010-02-18T12:07:07" EndDate="2010-02-15T18:00:00"
StartDate="2010-02-15T17:00:00" Version="12.0.9 Build(13)">
	<Rowset>
		<Columns>
			<Column Description="DateTime" MaxRange="0" MinRange="0"
					Name="DateTime" SQLDataType="93" 
					SourceColumn="DateTime"/>
			<Column Description="" MaxRange="100" MinRange="0"
					Name="TWA_Temperature" SQLDataType="4" 
					SourceColumn="Temperature"/>
			<Column Description="" MaxRange="100" MinRange="0"
					Name="SDV_Temperature" SQLDataType="4" 
					SourceColumn="Temperature"/>
		</Columns>
		<Row>
			<DateTime>2010-02-15T17:00:00</DateTime>
			<TWA_Temperature>331</TWA_Temperature>
			<SDV_Temperature>0</SDV_Temperature>
		</Row>

-

---

By the way, can someone file the bug (with the NA, see other posts) for me? I have spent enough time. Nobody pays me

for that. Its a customer issue, so it would be nice to see a fix soon.

Thanks a lot and regards..

[agentry_src]

Hi Mathias,

It will gather more attention if you can get the customer to log the ticket. We are already doing some investigation into the issues that Jeremy mentioned.

Regards,

Mike

[Former Member]

Hi Mike,

thanks a lot for your effort. We will file a ticket via the customer. By the way, how are your investigations proceeding?

Regards,

Matthias

[agentry_src]

Hi Matthias,

The historians that I usually have access to are in the process of moving. Not sure when I will be able to tinker with the data to recreate your situation. So I have not been able to duplicate your results.

Can you send the ticket number to me so I can follow the progress?

Regards,

Mike

Edited by: Michael Appleby on Mar 3, 2010 2:52 PM

[Former Member]

Hi Michael,

the ticket number is 187760.

Looking forward to hearing any news about this issue...

Best regards,

Matthias

[agentry_src]

Hi Matthias,

I added some thoughts/comments to the ticket and told them to contact me if needed.

Regards,

Mike

[agentry_src]

Hi Matthias,

Your ticket has landed back in my queue. The developers have asked me to follow up with you. Please contact me with the following data/information:

"Can you get us the UDS debug from uds start, 1 query that is failing,

and stop and attach it?

What version on Canary Labs TH are they using?

What is the "actual" data supposed to be - attach it as well please so

that we have something to compare to what we see from the UDS (this

would be the data as retrieved to the excel spreadsheet mentioned in the

issue)?"

I am pretty sure you already have all this information.

Thanks,

Mike

[Former Member]

Hi Mike,

thanks a lot again for the spending the time for the web session.

How is your progress in reproducing the problem at your site?

Do you need any further support?

Please notify me if you have a fix available.

Best regards,

Matthias

[Former Member]

Hello guys.

I have been search for the UDS 100% CPU solution. In this post Matthias Wald mentioned the this problem but I didn't found the solution.

Did you guys searched more about it?

We are having this problem when the UDS is installed in the server where the PIMS is. When we move the UDS to another place, it stop to work after a while, but there is not the 100% CPU problem. Any idea?

Thanks

Italo Stefani

Vetta Technologies

[Former Member]

Hi Mike,

again, any progress?

Regards,

Matt

[agentry_src]

Hi Matthias,

I have sent you a message.

Regards,

Mike

[Former Member]

Hi Mike,

I have seen in the release information that the patch is delivered with UDS SP02_5.

Unfortunately we have problems with the new release. Please see .

After successfull re-testing I will close this thread as answered.

Thanks a lot and best regards,

Matt

[agentry_src]

Hi Matthias,

I will followup on this with the developers.

Regards,

Mike

[Former Member]

Hi Michael,

I have verified the fix of this problem in UDS version SP2_5.

Thanks a lot for the support.

Best regards,

Matt