Line iChart x Axis Values

[Former Member]

Hi,

This maybe a most basic and easy to answer problem - We have just started to implement xMII on-site and our problem is:

We are trying to create a line grid which will compare two sets of data against each other, basically a Value versus a batch number, we have managed to this on one set of data coming form Oracle without any problems but on the second set coming from PI via a ODBC call this doesn't seem possible. At this stage we have tried every possible data column, label attribute etc. combination based on the supplied documentation. XY graph it is possible but the line between data points is required. I am wondering is the Oracle timestamp ignored as it isn't imported with the data - based on an export of the data from the Graph - where as the PI timestamp is, could this have anything to do with ? The timestamp is not required on the x Axis just the batch Number, timestamps are not important on this data as it is a batch on batch comparison and timestamps are irrelevant.

Thanks in advance,

Emmett

[Former Member]

Sam,

Hope this is what you mean - extracted via the the web page right click - data.

What i am trying to do is Plot the Duration vs the BatchNo from the the two PI Data sets against each other.

Seperately With the Oracle data we plot Duration vs BatchNo also which works without problem.

PI Data Returned To Agg Query:

UNIT TIME DURATION

064_Drying Time Phase 10_Duration_DCrit 07/16/2007 17:25:18 794.00

064_Filtration Time Phase 12_Duration_DCrit 07/13/2007 11:01:44 833.00

064_Filtration Time Phase 12_Duration_DCrit 07/18/2007 03:29:17 837.00

066_Drying Time Phase 10_Duration_DCrit 07/11/2007 18:37:57 807.00

066_Drying Time Phase 10_Duration_DCrit 07/13/2007 12:26:38 861.00

Second set of PI Data Returned To Agg Query:

Parameter TIME BATCHNO

064_Drying Time Phase 10_BatchNo_DCrit 07/16/2007 17:25:18 203.00

064_Filtration Time Phase 12_BatchNo_DCrit 07/13/2007 11:01:44 196.00

064_Filtration Time Phase 12_BatchNo_DCrit 07/18/2007 03:29:17 206.00

066_Drying Time Phase 10_BatchNo_DCrit 07/11/2007 18:37:57 197.00

066_Drying Time Phase 10_BatchNo_DCrit 07/13/2007 12:26:38 199.00

Oracle Data Returned To Query:

UNIT TIME BATCHNO DURATION

absorbance in methanol UV 20070713173127 181 0.01 AU

absorbance in methanol UV 20070713171841 180 0.01 AU

absorbance in methanol UV 20070711172602 163 0.01 AU

absorbance in methanol UV 20070713174612 159 0.01 AU

absorbance in methanol UV 20070711173312 155 0.01 AU

absorbance in methanol UV 20070711173016 154 0.01 AU

Thanks,

Emmett

[salvatore_castro]

Emmett,

This is close, when you Right Click - Data Detail hold down the Ctrl key and this will show you the XML representation of the data. Then on this page Right Click and select View Source, this will give you the Raw XML dataset. Then copy and paste this into your next post. Thanks. Sorry for all the leg work.

-Sam

[Former Member]

Sam,

Interesting, unaware this was possible from here, xml output below

<?xml version="1.0" encoding="UTF-8" ?>

- <Rowsets DateCreated="2007-07-18T16:00:19" EndDate="2007-07-18T16:00:09" StartDate="2007-07-18T15:00:09" Version="11.5.2">

- <Rowset>

- <Columns>

<Column Description="UNIT" MaxRange="1" MinRange="0" Name="UNIT" SQLDataType="12" SourceColumn="UNIT" />

<Column Description="TIME" MaxRange="1" MinRange="0" Name="TIME" SQLDataType="93" SourceColumn="TIME" />

<Column Description="DURATION" MaxRange="1" MinRange="0" Name="DURATION" SQLDataType="7" SourceColumn="DURATION" />

</Columns>

- <Row>

<UNIT>064_Filtration Time Phase 12_Duration_DCrit</UNIT>

<TIME>2007-07-18T03:29:17</TIME>

<DURATION>837</DURATION>

</Row>

- <Row>

<UNIT>067_Filtration Time Phase 10_Duration_DCrit</UNIT>

<TIME>2007-07-18T12:29:18</TIME>

<DURATION>677</DURATION>

</Row>

- <Row>

<UNIT>079_Drying Phase 3_Duration_DCrit</UNIT>

<TIME>2007-07-18T02:29:38</TIME>

<DURATION>581</DURATION>

</Row>

- <Row>

<UNIT>083_Drying Phase 3_Duration_DCrit</UNIT>

<TIME>2007-07-17T18:00:11</TIME>

<DURATION>79</DURATION>

</Row>

- <Row>

<UNIT>083_Drying Phase 3_Duration_DCrit</UNIT>

<TIME>2007-07-17T18:39:13</TIME>

<DURATION>119</DURATION>

</Row>

</Rowset>

- <Rowset>

- <Columns>

<Column Description="Parameter" MaxRange="1" MinRange="0" Name="Parameter" SQLDataType="12" SourceColumn="Parameter" />

<Column Description="TIME" MaxRange="1" MinRange="0" Name="TIME" SQLDataType="93" SourceColumn="TIME" />

<Column Description="BATCHNO" MaxRange="1" MinRange="0" Name="BATCHNO" SQLDataType="7" SourceColumn="BATCHNO" />

</Columns>

- <Row>

<Parameter>064_Filtration Time Phase 12_BatchNo_DCrit</Parameter>

<TIME>2007-07-18T03:29:17</TIME>

<BATCHNO>206</BATCHNO>

</Row>

- <Row>

<Parameter>067_Filtration Time Phase 10_BatchNo_DCrit</Parameter>

<TIME>2007-07-18T12:29:18</TIME>

<BATCHNO>207</BATCHNO>

</Row>

- <Row>

<Parameter>079_Drying Phase 3_BatchNo_DCrit</Parameter>

<TIME>2007-07-18T02:29:38</TIME>

<BATCHNO>199</BATCHNO>

</Row>

- <Row>

<Parameter>083_Drying Phase 3_BatchNo_DCrit</Parameter>

<TIME>2007-07-17T18:00:11</TIME>

<BATCHNO>198</BATCHNO>

</Row>

- <Row>

<Parameter>083_Drying Phase 3_BatchNo_DCrit</Parameter>

<TIME>2007-07-17T18:39:13</TIME>

<BATCHNO>198</BATCHNO>

</Row>

</Rowset>

- <Rowset>

- <Columns>

<Column Description="UNIT" MaxRange="1" MinRange="0" Name="UNIT" SQLDataType="12" SourceColumn="UNIT" />

<Column Description="TIME" MaxRange="1" MinRange="0" Name="TIME" SQLDataType="12" SourceColumn="TIME" />

<Column Description="BATCHNO" MaxRange="1" MinRange="0" Name="BATCHNO" SQLDataType="12" SourceColumn="BATCHNO" />

<Column Description="DURATION" MaxRange="1" MinRange="0" Name="DURATION" SQLDataType="12" SourceColumn="DURATION" />

</Columns>

- <Row>

<UNIT>enantiomer - warning CGP49309 HPLC</UNIT>

<TIME>20070717094846</TIME>

<BATCHNO>801071R0197</BATCHNO>

<DURATION>0.11 %w/w</DURATION>

</Row>

- <Row>

<UNIT>enantiomer CGP49309 HPLC</UNIT>

<TIME>20070717094846</TIME>

<BATCHNO>801071R0197</BATCHNO>

<DURATION>0.1 %w/w</DURATION>

</Row>

- <Row>

<UNIT>identity Nujol IR</UNIT>

<TIME>20070717094846</TIME>

<BATCHNO>801071R0197</BATCHNO>

<DURATION>corresponds to the reference</DURATION>

</Row>

- <Row>

<UNIT>process train</UNIT>

<TIME>20070717094846</TIME>

<BATCHNO>801071R0197</BATCHNO>

<DURATION>083</DURATION>

</Row>

- <Row>

<UNIT>retention sample</UNIT>

<TIME>20070717094846</TIME>

<BATCHNO>801071R0197</BATCHNO>

<DURATION>Yes</DURATION>

</Row>

</Rowset>

</Rowsets>

Thanks,

Emmett

[Former Member]

Sam/Emmett:

I'd get away from using the AggregateQuery and use the BLS capabilities to do this. You might want to use the Normalize action block on the PI data so that you get one rowset inset of two rowsets.

[salvatore_castro]

Emmett,

I agree with Rick, it will benefit you greatly to use BLS, it is much more capable and maintainable going forward. Basically in the editor you can join your various disjoint XML datasets together into something that the iChart can relate. As of now the problem you are having is because of the various datasources involved, two are Y value Rowsets and the Third is for X value plotting. In BLS you can create one Rowset of all values and the chart will easily plot this. Also in the future if the data changes slightly the BLS will only have to change slightly if at all as opposed to a hacked solution without where a lot will have to change.

If you are unsure about it play around with the actions, you will be surprised at how easy it is to pick up, especially since each action has an associated help document :)...Hope this helps.

-Sam

[Former Member]

Gents,

Thanks very much for the input, from my limited exposure to BLS it seems practice is the best route. I am presuming the way to go would be to import all three queries into a BLS transaction and map there XML output to the normalize functions output, is it relatively easy to combine the dataset into one row ?

Apologies for the apparent lack of impetuous on my part - but I won’t actually have a chance to try this until early next week.

Thanks again.

Emmett

[sufw]

Emmett,

In BLS, you can execute each query separately. Take a look at the documentation of the Union, Normalize and Join action blocks - they should help you in doing this.

Sascha

[Former Member]

Hi,

Have finally got around to using the BLS functions and have merged the data successfully into the format i require and am successfully writing the results to a file. How do i then have my iGrid on the web page look at this file for results etc ?

Emmett

Just figured this out xAcute etc.

Message was edited by:

Emmett O'Connor

[jcgood25]

Emmett,

In your BLS transaction you'll need to create a Transaction Output property (type XML and don't forget to check the output box). From a convention standpoint I typically call my property 'OutputXML' just to keep it very simple to distinguish from any inputs, etc.

Then use an assignment block to map the transformed xml document (in place of the xml saver is probably the appropriate location) to the newly created Transaction property.

Finally, feed your iGrid with an XacuteQuery template that points to the transaction you've created and requests the 'OutputXML' parameter. If your transaction has configurable inputs then you can associate them to the XacuteQuery template's Param.x on the parameters tab of the template editor. Look at the XacuteQuery template references in the help docs for more info on specifics.

Regards,

Jeremy Good

[Former Member]

Sascha,

I have had limited exposure to BLS, put don't think i need to use in this project, we are trying to map PI Data versus more PI Data based on the query's returned from PI and Oracle Data versus Oracle data based on it returned data. We are basically using the aggregate query to populate all data returned to an iGrid and from here based on an users row selection and using some javascript pass values to the queries to populate iCharts on screen. Below is some of the XML - hope this is what you and sam where looking for

aggregate query xml (populates iGrid)

<?xml version="1.0" encoding="UTF-8" ?>

<AggregateQuery Duration="2" DurationUnits="D" MappedParamSource.1="Param.2" MappedParamSource.2="Param.3" MappedParamSource.3="Param.1" MappedParamSource.4="Param.3" MappedParamTarget.1="QDIS_Query]2.Param.2" MappedParamTarget.2="QDIS_Query_CritParam_PI_ODBC_Duration.Param.1" MappedParamTarget.3="QDIS_Query]2.Param.1" MappedParamTarget.4="QDIS_Query_CritParam_PI_ODBC.Param.1" Param.1="'20070704'" Param.2="'20070711'" Param.3="'*-7d'" SaveDate="07/13/2007 09:34:57" SourceName.1="QDIS_Query_CritParam_PI_ODBC_Duration" SourceName.2="QDIS_Query_CritParam_PI_ODBC" SourceName.3="QDIS_Query]2" SourceTemplate.1="IMIS_NRL/EOC_Test/QDIS_Query_CritParam_PI_ODBC_Duration" SourceTemplate.2="IMIS_NRL/EOC_Test/QDIS_Query_CritParam_PI_ODBC" SourceTemplate.3="IMIS_NRL/EOC_Test/QDIS_Query]2" Version="11.5.2" />

XML Display template used to display PI Data

<?xml version="1.0" encoding="UTF-8" ?>

<iChart AutoRefresh="true" AutoScale.1="true" AutoScale.2="true" AutoScale.3="true" AxisLabelColumns="064_Drying Time Phase 10_BatchNo_DCrit" ChartBackgroundColor="#FFFFFF" CustomLabelColor="#000000" DatalinkColumns="064_Drying Time Phase 10_BatchNo_DCrit" DescriptionColumns="064_Drying Time Phase 10_Duration_DCrit,064_Drying Time Phase 10_BatchNo_DCrit" GlobalServerScaling="false" IncludeValueInLabel="true" LabelColumns="064_Drying Time Phase 10_Duration_DCrit,064_Drying Time Phase 10_BatchNo_DCrit" LegendBackgroundColor="#FFFFFF" LegendDescriptionLabel="" LegendLabelColor="#404040" LegendShowValues="true" LegendUsePenColor="false" LegendWidth="25" MainBackgroundColor="#FFFFFF" MarkerSize="3" MarkerStyle="1" MaxRange.3="0.0" PenColor.1="#000000" PenMarkerStyle.1="3" PenMarkerStyle.2="1" RefreshRate="180" SaveDate="07/17/2007 17:47:09" ShowMouseTracking="true" ShowMouseTrackingDatalinkValue="false" ShowScooter="true" ShowTimeControl="false" Title="QDIS Test Results" TitleColor="#000000" ValueColumns="064_Drying Time Phase 10_Duration_DCrit" Version="11.5.2" WriterRoles="Developers" XAxisDateFormat="" XAxisLabelColor="#000000" XAxisTimeFormat="" />

Thanks,

Emmett

[salvatore_castro]

Emmit,

The XML that I was referring to is the actual data, actual values can be modified for security, that is returned from the Aggregate Query and not the XML definition of the query. This way I can see what you're doing without having your data sources handy. Hope this helps.

-Sam

[Former Member]

Udayan,

On your reply - yes time data is returned to the trend for some reason via the aggregate query - we are not requesting this via the aggregate query, though it is part of the two underlying PI SQL Queries for sorting purposes. This is why i thought maybe the chart was defaulting and using the time stamps because it could see them. The oracle query doesn't return time to the chart, even though it is part of the underlying query - i was thinking possibly it could not interogate the time format in which Oracle was returning the time ?

[Former Member]

Sam,

Thanks for the input but this is not the problem, the PI UDS etc. has been setup successfully and tag data is easily retrievable from the PI Server. We have moved to the next level wereby we are also connecting to the pi server via a OSi PI ODBC Driver - which allows us to run limited SQL syntax against the PI Server and retrieve data this way, this is more suitable for the data extraction we are currently performing as it allows easy manipulation and passing of parameters to the SQL Query Syntax.

Our problem is that for some reason the returned columns from this aggregate query (two SQL calls to the PI Server) don't seem to be able to be able to be trend X versus Y on a Line Graph. Y values aren't a problem, but trying to plot these against a seperate column on the X axis doesn't produce any values on the x Axis. XY Plot works but want to have interconnecting lines between the Data Points - stumped having tryed multiple value column, datalink column, Axis label column etc etc combinations ??

[salvatore_castro]

Emmett,

Ok, that's fine I still recommend against using ODBC and using the OLEDB connection, but since you can't download the OLEDB UDS as of now, this will have to do :)......

Anyway, what does the returned XML look like? Can you copy a <b>sample</b> of the structure into a post so that I can see what's going on? Thanks.

-Sam

[Former Member]

What do you see currently on the X Axis?

Does the SQL query into PI returns with a date time column as well?

As far as I know I think the Axis Value Column in the Data Mapping tab should solve the problem incase of queries other than TAG type.

[sufw]

Emmett,

you mentioned the term "aggregate query" in your post - I would recommend you move away from aggregate queries and start using Business Logic Services as this gives you a lot more flexibility and power.

At this level of detail, I can image you would set up a BLS transaction to query both your Oracle and PI data sources in separate queries and then use the Normalise or TimeInterpolator action blocks to merge the separate datasets obtained by those queries into one for charting.

I'm not sure how familiar you are with BLS, so this may be preaching to the converted; in my opinion BLS is what adds most value to xMII as it enables you to write programmatic logic to merge, transform and otherwise process data coming from different systems. BLS can be a little daunting to get one's head around at first, but it's really worth it and should allow you to solve your problem.

As suggested by Sam, please post some XML fragments and we might be able to provide more detailed answers.

HTH,

Sascha

[salvatore_castro]

Emmett,

First off I highly recommend using the SAP xMII PI UDS for your connection to PI instead of ODBC, it will be faster and more reliable. Just make sure that the machine where the UDS is installed also has the PI SDK installed, you can read the documentation for more details. Once you have this connection setup you can perform Tag Queries against the PI system and pull back tag values for whatever time range that you need. Hope this helps.

-Sam