UI5 Viz Charts and MII Data - Multiple Rowsets

[Former Member]

I'm having trouble with charting in MII 14. The iChart applets work fine, but we would like to switch to HTML 5.

A typical scenario here is a "trend" line type chart that shows the results of 2 or more tags for comparision purposes. Tag data is fetched from a PCo query template.

The i5Chart will take upwards of a minute to render a chart where there are maybe 1000 results between all the tags. The time is being spent inside something called "CombinedRowsetsQuery". I bacame frustrated with the i5Chart and tried out the Viz line chart which the i5Chart appears to be based on. I learned there that you need a "Flattened Dataset" in order to be able to chart the data and a Flattened Dataset can only have one data binding path expression. (I assume that CombinedRowsetsQuery is trying to flatten my data for me). I de-selected "return results in multiple rowsets" in the query template editor and set up a simple line chart with the following code:

            var queryTemplate = "/QTPath//GenericPcoSingleRowsetQuery"; // returns all tags in a single rowset
            var startDate = new Date();
            var endDate = new Date();
            startDate.setHours(startDate.getHours() - 4);

            var tags = [{
                tag: "TAGTHEFIRST"
                }, {
                tag: "TAGTHESECOND"
                }, {
                tag: "SINUSOID"
                }];

            var params = miiUtils.createPcoAggregateQueryParams(tags, startDate, endDate); // formats dates and formats "SelectedTags" string
            var url = miiUtils.createRequestURL(queryTemplate, params); // serializes the template path and params into a nice url.

            var model = new sap.ui.model.json.JSONModel();
            model.loadData(url);

            var dataset = new sap.viz.ui5.data.FlattenedDataset({

                dimensions: [{
                    axis: 1,
                    name: 'Time',
                    value: "{DateTime}"
                }],

                measures: [{
                    name: "First Tag",
                    value: "{TAGTHEFIRST}"
                }, {
                    name: "Second Tag",
                    value: "{TAGTHESECOND}"
                }, {
                    name: "Sine Wave",
                    value: "{SINUSOID}"
                }],

                data: {
                    path: "/Rowsets/Rowset/0/Row"
                }

            });

            var chart = new sap.viz.ui5.Line({
                width: "100%",
                height: "100%",
                title: {
                    visible: true,
                    text: "UI5 Viz Chart Test"
                },
                dataset: dataset
            });
            chart.setModel(model);
            chart.placeAt("chart");

The chart produced is attached in an image. As you can see, even though the 3 series all span the same time range, the chart draws each measure as if it exists in isolation, repeating the time range along the x-axis. I assume that this is because the timestamps for the tags do not exactly match. This seems like it should be a simple and common use-case for Viz charts, so I must be missing something important. Is it possible to bind multiple rowsets objects as series to be drawn on a Viz chart? How would I go about doing that? Thanks, Allan Irvine

[swaroop_anasane · Accepted solution]

Hi Allan,

Tag query gives you separate rowset(s) only for each tag. To get a single rowset, i would suggest you use three tag queries and bind it to an aggregate query. This would give you the desired result.

You can also use OLEDB command query.

Thanks,

Swaroop

[Former Member · Accepted solution]

Thanks Swaroop,

So there is no way to just get the chart to display each rowset object as a discrete series on a Viz chart? It seems like pretty basic functionality that I've used in other js charting packages,

Unfortunately I can't use a solution that requires that I drastically change the query behavior. We're porting older MII 11.5 stuff to MII 14 and trying to get away from the applets,

Hopefully this will be a feature in MII 15 or a newer version of UI5,

Allan

[swaroop_anasane · Accepted solution]

Hi Allan,

How was it working on 11.5. In 11.5 as well it gives different rowsets for each tag in the tag query.

On js however there is a way to modify json coming from query, iterate through the records and insert property value. In this way you can restrict the dates in json by filtering the records based on timestamp range.

data = [];

$.each(data, function (index) {

check date and set property for data[index]

});

Hope it helps.

Thanks,

Swaroop

[Former Member · Accepted solution]

Hi Swaroop,

Using the iChart java applets in MII 11.5 and 14 "just works" out of the box with a PCo or Tag query that returns multiple Rowsets. We have hundreds of these charts in production today.

Moving to MII 14 we had hoped to use the HTML 5 charts and grids but find they don't work for lots of reasons.

The only success I've had in even getting 2 tags to display on an i5Chart is by hard linking a Display Template to a Query Template in the workbench. This performs terribly (over a minute to render easy cases) and I've profiled the page to discover that the time is being spent inside something called "CombinedRowsetsQuery". Obviously that is not the name of my query template so I have to make the following assumptions:

1) Multiple rowsets are known by SAP to not be usable with the UI5 controls.

2) SAP provides this shim method "CombinedRowsetsQuery" to combine the rowsets data when it detects multiple rowsets are returned by a QT,

3) That method is performing horribly because the data is production floor data and the timestamps go down to the second. It is a rare case that the DateTimes exactly match.

So as for parsing and flattening the data myself that is possible but the performance will still be terrible as I will have to do on the client what MII is doing on the server. Also I will be consuming way more memory than necessary as there will be lots of nulls and redundancies in a data structure that holds values for all 3 tags for every possible timestamp.

That also introduces new code and possible failure points that I don't think should be necessary.

The example I provided uses the "Get Response in Multiple Rowsets" setting in the PCo Workbench and it's turned off. That way the data is already in a single rowset when loaded into the model. As you can see, the Viz chart draws each tag in its own section of the x-axis, which is not what we're looking for,

I've worked with other JS charting packages that allow you to define each series to be drawn independently. I was hoping there was such a way to interact with VIZ charts,

[salvatore_castro · Accepted solution]

I have seen performance of this library vary greatly depending on the browser version (specifically with IE9 and earlier) and also with older versions of the SAP UI5 library that are deployed.  Please share your SAP UI5 and browser version so that we can look a bit deeper.

Sam

[Former Member · Accepted solution]

Hi Salvatore,

The specific performance problem I'm seeing is on the server side. The time is spent waiting for an XHR response. The server method being called is "CombineQueryRowsets" or something close to that.

Client side the performance has been great. We are delivering to IE11 and I usually use Chrome (latest) during development.

MII Version is 14.0 SP5 Patch 16.

UI5 Controls are 1.20.4

All I want to do is display 2 or more tags on a line chart without using Java Applets and in a timely fashion. How do I do it?

Thanks,

Allan

[salvatore_castro · Accepted solution]

In MII 14.0 SP04 we introduced a new i5Chart display template and JS library to accommodate this so you don't have to instantiate the Viz library directly; I highlight this here:

http://scn.sap.com/community/manufacturing/mii/blog/2013/10/31/sap-mii-and-html-5-demystified

The help documentation also covers this along with the available JS functions for the new i5Chart and i5Grid display objects:

http://help.sap.com/saphelp_mii140sp04/helpdata/en/4f/718613eaa144b2a51c8493f9bbd6b3/content.htm?fra...

I recommend using these rather than manipulating the Viz library directly.

Hope this helps,

Sam

[Former Member · Accepted solution]

Hi again Sam,

What you suggest is the first thing I tried, and is enumerated in my first post in this thread. The issue I'm having is illustrated in the following image:

That is 8.5 seconds waiting for "CombineQueryRowsets" to return for a query template using 2 tags for 24 hours of data. There are maybe 1000 rows in the total set. I did not call CombineQueryRowsets, I do not want the rowsets combined.

I can execute the same template via ajax in under 1 second. This issue also seems to scale rather poorlyand by the time I have a query users actually want, the wait can be upwards of 1 minute,

The performance of the iChart Java Applet is quick as expected.

Now, I'll paste in some assumptions I made earlier in this thread:

0) i5Chart is a specialization of or wrapper around a VIZ UI5 chat.

1) Multiple rowsets are known by SAP to not be usable with the UI5 controls.

2) SAP provides this shim method "CombinedQueryRowsets" to combine the rowsets data when it detects multiple rowsets are returned by a QT,

3) That method is performing horribly because the data is production floor data and the timestamps go down to the second. It is a rare case that the DateTimes exactly match.

For my next attempt, I un-select "Return results in multiple rowsets" in workbench

This is the result I get (each tag gets its own piece of the x-axis) very strange...

This does drastically reduce the time spent in combineQueryRowsets.

I would much prefer to skip the whole "combining of the rowsets" step as it seems like unnecessary processing. iChart applets do not force rowsets to be combined, why do the i5Charts? This is why I tried my luck at VIZ charts, I thought maybe they could be configured to do what I need.

So, for now our enterprise is forced to keep JRE 1.6.0_43 (really old and probably a security risk) on our users machines because we need to deliver charts via Java Applets because the SAP Html 5 solution simply does not provide acceptable performance.

Hopefully I am wrong about that, and I am just doing something wrong.

Thanks,

Allan

[salvatore_castro · Accepted solution]

Allen,

I see your issue and please raise a ticket for this so that we can get you a patch.  I think you're also using an older library of SAP UI5 as the chart should have the lines overlapping so be sure to include your Components.xls from here in your ticket in order to speed up processing:

http://<server:port>/nwa/sysinfo

Select the Components Info tab and the press the Export to Spreadsheet button.  Also, be sure to reference this SCN post.

Sam

[Former Member · Accepted solution]

Ok, thanks.

We do have access to newer versions. If that's all we need then that's perfect,

I'll see if I can get a dev server upgraded and re-test.

Allan

[Former Member · Accepted solution]

Hi Sam, I'm posting this components.xls file for your reference.

Thanks,

Allan Irvine