Writing SQL Queries Against SAP Business Objects WebIntelligence Data Providers

Versions: 8.0+

Versions:6.0+

With QuerySurge BI Tester for SAP Business Objects WebIntelligence, data testers can use familiar SQL syntax to retrieve query data from WebIntelligence reports and validate it against any QuerySurge-supported Source or Target.

WebIntelligence reports have multiple processing levels, and data can be retrieved independently from these different levels. Two key levels for report data verification are the report data providers and the report visualization elements. In this article we discuss how to extract data from report data providers. A WebIntelligence report can have one or multiple data providers, which supply source data for the report visualizations.

Note: To extract data from WebIntelligence visualization elements, see Writing SQL Queries Against SAP Business Objects WebIntelligence Elements

Connection Setup

WebIntelligence connections can be set up in the QuerySurge Admin view, and be utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and Test Suite Connection overrides).

For details on setting up a Connection to WebIntelligence, see the article Connecting QuerySurge to SAP Business Objects WebIntelligence.

Determining a report's Document ID and Query Names

To retrieve WebIntelligence query data, a document must be identified using its Document ID number. To find the Document ID for a WebIntelligence document:

1. Log into the SAP Business Objects BI Launch Pad, and click the Documents tab
2. Open the folder that contains the desired WebIntelligence document
3. Right click the document and click Properties
4. Note the numeric ID number (first number in the "ID, CUID" field)

Note: BI Tester for WebIntelligence can only retrieve WebIntelligence documents; other objects (such as Crystal Reports) are not supported.

Next, we need to find the names of queries (or "data providers") contained within this document:

1. Open the WebIntelligence document and enter Design mode
2. Click Edit Data Provider on the toolbar
3. Note the name of the desired query

To preview the base data returned by a query, click the Refresh button:

The combination of a document ID and query name can be used to query the report with a QuerySurge BI Tester connection. Each document ID/query name combination will be treated as a separate table in QueryPairs and Staging Queries, as explained below.

Querying Report Data

The query syntax for BI Tester queries is based on H2 SQL and supports most of the H2 SQL grammar and its function calls, so SELECT-type queries against WebIntelligence follow a familiar form. The main element is the webiReport table function, which instructs the BI Tester driver to execute the report and pull back the query data as a table for you to query. The function's syntax is as follows:

webiReport(documentId, 'queryName'[, flowId])

• documentId (integer): ID found in document's properties
• queryName (string): Name of query found in query editor
• flowId (integer; optional; defaults to 0): If a query generates multiple flows, the ID of the flow to retrieve

The queryName string argument must be surrounded by single quotes, and single quotes within the argument value (i.e. apostrophes within a query name) must be escaped with an additional single quote. Following is a sample SQL statement against the our Sales Analysis Dashboard report:

SELECT * FROM webiReport(5608, 'Query 1', 0);

When executed in QuerySurge, the following results are returned (truncated for brevity):

Queries can include standard syntax like joins, group by, single-valued functions, aggregate functions, aliases, and more. Note that references to column names with spaces or special characters must be surrounded by double quotes:

SELECT
  rpt."Year",
  rpt."Quarter",
  rpt."State",
  rpt."City"
  rpt."Lines"
  rpt."Category"
  rpt."SKU number"
  rpt."Quantity sold"
  rpt."Margin"
  rpt."Sales revenue"
FROM webiReport(5608, 'Query 1', 0) AS rpt;

Passing Query Filter Parameters

Many WebIntelligence reports take advantage of parameter functionality, which allows filtering of report data for exploration and analysis, building one report for many users, and other use cases. To automate testing of these reports, the webiReport function supports passing in a JSON-formatted parameter string:

webiReport(documentId, 'queryName', flowId, paramsJson)

Where paramsJson is a JSON array of parameter objects:

[
  {
    "queryName": "Order List",
    "paramName": "Start Date",
    "values": [ { "value": "2005-01-01T00:00:00.000Z" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "End Date",
    "values": [ { "value": "2007-09-28T00:00:00.000Z" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "Country",
    "values": [ { "value": "US" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "Region Name",
    "values": [ { "value": "Idaho", "path": "[[0,\ US]]" } ]
  },
  {
    "queryName": "Members By Join Year",
    "paramName": "Join Year",
    "values": [
      { "value": "2005" },
      { "value": "2006" },
    ]
  }
]

The outer array can contain any number of parameter items, and each individual parameter item's values array may further contain any number of value items. Fields are defined as follows:

• queryName: Name of the query the specifies the parameter prompt
• paramName: Parameter name, as visible in Query Filters pane
• value: Textual, numeric, or date/time (see formatting convention below) value to be answered for the parameter prompt
• path (Optional): Used for some hierarchical parameters; see explanation below

Note: When passing parameters to webiReport function, the flowId argument is also required even if the query has only one flow.

The parameter string must be minified (that is, formatted without line breaks, for which there are "JSON minifier" tools available on the internet), and then passed in as a fourth argument to the webiReport function:

SELECT *
FROM webiReport(6629, 'Order List', 0,
'[{"queryName":"Order List","paramName":"Start Date","values":[{"value":"2005-01-01T00:00:00.000Z"}]},{"queryName":"Order List","paramName":"End Date","values":[{"value":"2007-09-28T00:00:00.000Z"}]},{"queryName":"Order List","paramName":"Country","values":[{"value":"US"}]},{"queryName":"Order List","paramName":"Region Name","values":[{"value":"Idaho","path":"[[0,\ US]]"}]},{"queryName":"Members By Join Year","paramName":"Join Year","values":[{"value":"2005"},{"value":"2006"}]}]');

Date/Time parameter formatting

Date/time values used in parameters must be formatted using the following convention:

yyyy-MM-ddThh:mm:ss(.s+)(zzzzzz|Z)

The following table describes each format code:

Character	Description	Optional
yyyy	A four digit that represents the year
-	Separators between parts of the date portion
MM	A two-digit numeral that represents the month
dd	A two-digit numeral that represents the day
T	A separator indicating that time-of-day follows
hh	A two-digit numeral that represents the hour
:	A separator between parts of the time-of-day portion
mm	A two-digit numeral that represents the minute
ss	A two-integer-digit numeral that represents the whole seconds
.s+	The fractional seconds preceded by a dot separator	Optional
zzzzzz	zzzzzz represents the timezone according to the W3C recommendation. zzzzzz is of the form +|-	Optional
Z	The zero-length duration timezone, which is the UTC canonical representation	Optional

Source: SAP BusinessObjects RESTful Web Service SDK User Guide, section 6.3

Examples:

• No offset (timezone of server utilized): 2017-12-31T22:59:59.999
• Time with explicit UTC timezone offset: 2017-12-31T22:59:59.999+01:00
• Zulu time at UTC: 2017-12-31T23:59:59.999Z

Hierarchical Parameter Values

Hierarchical parameters allows "drilling down" into logical hierarchies of the business data and offer more flexibility in filtering reports. For some hierarchical parameters, WebIntelligence requires the path of hierarchy leading to the final parameter value. This can be accomplished by adding a path attribute to the value item:

{ "value": "value text", "path": "[[type,\ level,\ index],...]" }

With the following path components:

• type: Data type, represented by either a number or name:
  • 0 (or String)
  • 1 (or Date)
  • 2 (or Numeric)
• level: Value of the parameter at the particular level in the hierarchy
• index: Optional; included if the hierarchy has indexed levels

In our example above, the path to the Region Name "Idaho" is through the Country "US":

{ "value": "Idaho", "path": "[[0,\ US]]" }

Querying Report Metadata

When developing data tests for WebIntelligence reports, it can be useful to see information about the datasets returned by the report for learning about the columns available. Metadata can also be used to compare a report between two WebIntelligence servers. To obtain metadata for a query, use the webiMetadata function as follows:

SELECT * FROM webiMetadata(5608, 'Query 1');

A flow can optionally be specified:

SELECT * FROM webiMetadata(5608, 'Query 1', 0);

Note that webiMetadata can only retrieve one report per metadata query, and it cannot be combined with a report data query (i.e. joins are not supported). This query returns the query names (as table names) and column names for all queries in the report: