Queries are the backbone of SAP xApp Manufacturing Integration and Intelligence (SAP xMII). They allow access to a heterogeneous blend of data sources through a simple point and click interface. Once a query is configured, it may be used over and over again on different Web pages and in different visual objects without repeating any definition. In addition, all queries can be modified at runtime to produce different results, or different subsets of the original dataset through the use of query parameters. For a description of the SAP xMII queries, see the Query Construction Guide.
Query templates are configured through the Template Editor. There are a number of common elements between all SAP xMII queries (Tag queries, SQL queries, XML Queries, and Alarm queries). These elements and their configuration are described below. Specific query configuration parameters can be found in the guides for the specific query types.
This section is used to choose the source of the data for the query. Only the appropriate connector types are displayed, depending on the type of query to be configured. The server, mode, and method can be selected from this page. Selecting a Server from the list updates the Modes list with individual server specific modes. All enabled SAP xMII remote servers are also available in this view. Modes are described in the Query Construction Guide.
This section is used to assign some common parameters for the query such as the maximum number of rows to be returned from the query as well as the numeric format to be utilized when outputting numeric data values.
The Inline Transform gives the user the ability to assign a stylesheet to manipulate the data further after it has been retrieved through a system connector. An example would be the need to perform calculations to provide summary data. This stylesheet is intended for pre-processing of raw data. Another stylesheet can always be used to format the output of the data through the use of the Stylesheet parameter.
The query caching parameters define whether a query should be cached, and if so, how long the cached data should be considered "fresh." Once a cached data set has been in cache for the entered duration, the next time the same query is called, the data will be retrieved again from the data source. In order for a query request to be cached, it must have the same parameter values, including the same start time and end time. If a query is a parameterized query, once all the parameters are parsed into name-value pairs, the name-value pairs must match exactly for the query to be retrieved from cache. If the query is set for caching, but the parameters are set at runtime and change, a new cache will be created for each unique set of name-value pairs. Only the most recent instance of a query template will be cached. that means that if at runtime you change the parameter values, the previous cache instance will be overwritten.
For example, if you have a single query defined that brings back all the material usage for a batch from a database, the query may look like the following:
Select
b.BatchID,
b.BatchName,
m.MaterialID,
m.MaterialName,
m.Quantity
from
Batch b,
BatchMaterialHistory m
where
b.BatchID = M.BatchID and
b.BatchID = [Param.1]
In this example, Param.1 may will be passed in at run time from an HTML page, Agent, or other user interaction. But the query and the result set will be different for each BatchID requested. Only the last instance of the query to be run will be saved into cache.
To clear the cache, use the following URL: http://<servername>/Lighthammer/Illuminator?Service=QueryCaching&Mode=ClearCache
Click here for more information on Number Formatting.
This section is used to assign parameters used in a selection of a date range for time-sensitive queries. In a tag or alarm query, the date filter is applied to the timestamp column. In a SQL query, the date filter is applied to the Date Column that is set on the SQL Query Details tab. In terms of priority, the Schedule is the highest, followed by TimePeriod, then Time, and finally by the StartDate/EndDate parameters. The Duration and DurationUnits parameters are utilized whenever the Time parameter is used -or- whenever one or both of the StartDate/EndDate parameters is not provided. The button with the ellipsis (...) next to each date can be used to pop-up a calendar from which dates/times may be selected. The Clear buttons to the right of the Start and End Dates set their respective parameters to blank, and the Clear buttons next to the Time Period and Schedule lists deselect any selected Time Period or Schedule. The Interval Count is an integer parameter that can be used to divide the overall query time into evenly spaced segments.
When you wish to use a schedule, you should select either the CurrentShift or PreviousShift time period. The default is CurrentShift. Based on the configuration of the Schedule, the current shift or previous shift is dynamically determined by the system and the appropriate time period parameters are used in the query. See Schedules for further explanation of the use of the Schedule Parameter.
The Allow Future Dates check box sets/resets the AllowFuture query parameter. If you have an end date that resolves into the future, you can choose to "truncate" the end date to the current date-time of the server, or use the full time scale. By truncating to now, you can look at a query such as Shift to Date, or Week to Date, etc., but have the end date not go to the full duration. That makes things like charts use the full allocated space for data. If you don't have data that runs into the future, but don't truncate the end date, the chart object may only be half-filled - because the chart X-Axis will be scaled for the full time duration, even if there is no data.
Click here for more information on Date Formats.
This page is used to preload [Param.1] through [Param.32] with a desired value for the query being constructed, if that query uses the token placeholders. For details on the query parameters configured by this page, refer to the Query Parameter Reference Guide and the Query Construction Guide.
When parameters are entered here and the query is saved, they also act as default values, and if no values are provided at runtime, the values entered here will be used.
This page is used to define an inline transform and to define the parameters to be used by the transform. For more information on inline transforms, see the In-Line Transforms guide and the Pre-Defined InLine Transform Reference guide. When parameters are entered here and the query is saved, they also act as default values, and if no values are provided at runtime, the values entered here will be used.
Individual templates can be secured for read (use) and write (modify) permissions based on roles. Each function can be assigned to one or more roles. The default reader role is "Everyone" and the default writer roles are "Administrators" and "Developers." Only those roles with write permissions can edit the template. The read permission gives the selected roles the ability to use the template on a Web page. If the user does not have read permission to the template, an applet that uses the template on a Web page does not render data for that user. This permission capability allows groups to protect their templates from modification by users in other project development groups.