With QuerySurge BI Tester for Strategy (formerly MicroStrategy), data testers can use familiar SQL syntax to retrieve query data from Strategy Reports and validate it against any QuerySurge-supported Source or Target. Strategy connections can be set up in the QuerySurge Admin view, and utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and Test Suite Connection overrides).
This article details information about the REST Connection version for Strategy within the BI Tester. For details on setting up a connection to a Strategy REST API, see the article Connecting QuerySurge to Strategy REST API.
For details about the SOAP based version of Strategy information and working with Reports within the SOAP connection, see the article Writing SQL Queries Against Strategy Reports
Strategy Asset Types
In order to test Strategy it is important to know some of the core asset types:
Project: the highest-level asset in Strategy. Projects allow for separation of assets between groups.
Grid/Graph Reports: Reports allow users to view data. Reports can be displayed in a grid format in which all values are displayed in column/row format or as a graph. All report data can be extracted and queried by QuerySurge’s BI Tester.
Document: Are containers which display information from one or more reports, as well as formatting and custom text.
Dashboard: An interactive document.
Prompt: A question posed to the user during report execution which impact report processing.
Filter: A condition which must be met by data to be displayed.
Metric: A measure, usually a calculation of underlying data.
Attribute: Is a business concept which provides context for a metric.
Determining a Report ID (Developer)
To extract a Strategy Report, the Report ID must be identified. The following describes the process of extracting a Report ID using Strategy Developer.
1. Locate the Report:
2. Right click the Report and select "Properties":
3. Locate the "ID" field and copy the unique identifier for the Report:
Determining a Report ID (Web)
To extract a Strategy Report, the Report ID must be identified. The following describes the process of extracting a Report ID using Strategy Web.
1. Locate the Report:
2. Right click the Report and select "Properties":
3. Locate the "ID" field and copy the unique identifier for the Report:
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 Strategy follow a familiar form. The main element is the microstrategyReport 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:
microstrategyReport('<ReportId>',’<NumberValueFormat>’,'<PromptJSON>')
The function's arguments must be surrounded by single quotes. Following is a sample SQL statement against the Report "New Report" which has no prompts (the NumberValueFormat & Prompt parameters are optional).
SELECT * FROM microstrategyReport('AEEC6977488672CC78F66C9542FCA9F1')
When executed in QuerySurge, the following results are returned:
As compared to the data you see in Strategy:
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 nr."Qualified Employees", nr."Company Cost" FROM
microstrategyReport('AEEC6977488672CC78F66C9542FCA9F1')
AS nr
Querying Report Metadata
When developing data tests for Strategy 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 Strategy servers. To obtain metadata for a Report, use the microstrategyMetadata function as follows:
Syntax:
microstrategyMetadata('<ReportId>','<PromptJSON>')
Example:
SELECT * FROM microstrategyMetadata('AEEC6977488672CC78F66C9542FCA9F1')
Note that microstrategyMetadata 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 following columns:
- Table Name
- Column Name
Example:
Querying Report Prompt Metadata
When developing data tests for Strategy Reports, prompt metadata may be needed in order to properly answer metadata prompts that need to be passed to a Report function call (which includes microstrategyReport and microstrategyMetadata). To obtain prompt metadata for a Report, use the microstrategyPromptMetadata function as follows:
Syntax:
microstrategyPromptMetadata('<ReportId>')
Example:
SELECT *
FROM microstrategyPromptMetadata('AEEC6977488672CC78F66C9542FCA9F1')
Note that microstrategyPromptMetadata 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 following columns:
- Prompt Name
- Prompt Title
- Prompt ID
- Prompt Key
- Prompt Type
- Minimum Answers
- Maximum Answers
- Answer Name
- Answer ID
- Answer Type
- JSON Formatted Answer
Example:
Note: the values within the last column of a “microstrategyPromptMetadata” function call have a JSON representation of a Prompt Answer, but is structured for only one answer. It’s a good starting point to use this to write the JSON answer, but will not give the full answers for the entire Report; you can copy each JSON answer and format the answers for a fully qualified prompt answer JSON (the Prompt JSON format is described later on in this article).
The full JSON answer can be viewed by copying the cell to a text editor, or by right-clicking on the cell and selecting “View Field in Popup”, such as the following:
Warning: passing a Report ID that does not have prompts associated with it will result in an error response from QuerySurge; this method is only intended to get information about the kinds of prompts and possible answers you can give to a Report.
Passing Prompts to a Report
In many cases Strategy Reports require one or more prompts to be answered prior to execution. All prompt data can be passed in either the microstrategyReport or microstrategyMetadata request as a JSON request with the general structure of it being as follows:
'{ "prompts": [ <answerObjectOne>, <answerObjectTwo>, ... ] }'
And here is an example of it in use:
SELECT *
FROM microstrategyMetadata('AEEC6977488672CC78F66C9542FCA9F1',
'{
"prompts":[
{
"key": "3EBDD91E11D73EDBB0007781A96E4BD0@0@10",
"type": "OBJECTS",
"answers":[
{
"id": "1DD7987511D70C8AB000E1BED06B4BD0",
"type": "attribute"
}
]
},
{
"key": "54BFEB1C11D745AFB0008081A96E4BD0@0@10",
"type": "ELEMENTS",
"answers":[
{
"id": "h20144;1DD7988E11D70C8AB000E1BED06B4BD0"
}
]
}
]
}')
Note, passing in an empty prompt for an answer (like the following) will result in using the default values that are part of the Report, if possible.
'{ "prompts": [] }'
If there are prompts that do not have a default answer and are required to be answered, or if there are remaining open prompts when passing in this “default” empty prompt, an error will be thrown stating that prompts weren’t fully answered in the request.
To derive the JSON to create these JSON prompt answers, prompts fall under 3 simpler categories and/or 4 more complicated categories, and can be answered with a JSON object that can represent either of these categories listed below.
OBJECT, ELEMENT, and VALUE Prompts
For OBJECT type prompts, answer(s) can be constructed like so:
{
"key": "<Object Prompt Key>",
"type": "OBJECTS",
"answers": [
{
"id": "<Object 1 Answer ID>",
"type": <Object 1 Answer Type>
},
{
"id": "<Object 2 Answer ID>",
"type": <Object 2 Answer Type>
},
...
]
}
For ELEMENT type prompts, answer(s) can be constructed like so:
{
"key": "<Element Prompt Key>",
"type": "ELEMENTS",
"answers":[
{
"id": "<Element 1 Answer ID>"
},
{
"id": "<Element 2 Answer ID>"
},
...
]
}
And lastly, for VALUE type prompts, answer(s) can be constructed like so:
{
"key": "<Value Prompt Key>",
"type": "VALUE",
"answers":[
"<Value 1 Answer String>", "<Value 2 Answer String>", ...
]
}
More information about answer prompts through the REST API can be found here from the official Strategy Documentation.
EXPRESSION and LEVEL Prompts
There are 3 subcategories for EXPRESSION type prompts: Attribute Qualification, Hierarchy Qualification, and Metric Expression. For Attribute Qualification EXPRESSION type prompts, answer(s) can be constructed like so:
{
"key": "<Attribute Qualification Prompt Key>",
type": "EXPRESSION",
"answers": {
"content": "<Attribute Qualification Prompt Title>",
"expression": {
"operator": "Equals" <or other operators (Equals, Between, etc.)>,
"operands": [
{
"type": "form",
"attribute": {
"id": "<Form Associated Attribute ID>",
"name": "<Form Associated Attribute Name>"
},
"form": {
"id": "<Form ID>",
"name": "<Form Name>"
}
},
{
"type": "constants",
"dataType": "Real" <or other valid DataType>,
"values": [""] <set to empty>
},
...
]
}
}
}
As seen with the Attribute Qualification Prompt, there is a more complicated structure involved with building out a response as compared to OBJECT/ELEMENT/VALUE type prompts. More information on how to build out the expression object can be found here.
For Hierarchy Qualification EXPRESSION type prompts, they are mostly the same as Attribute Qualification Prompt but the ‘constants’ are dependent on the datatype associated with the Hierarchy Qualification, which is automatically derived from the data retrieved on the associated attributes. Here’s an example of an answer(s) that can be constructed:
{
"key": "<Hierarchy Qualification Prompt Key>",
type": "EXPRESSION",
"answers": {
"content": "<Hierarchy Qualification Prompt Title>",
"expression": {
"operator": "Equals" <or other operators (Equals, Between, etc.)>,
"operands": [
{
"type": "form",
"attribute": {
"id": "<Form Associated Attribute ID>",
"name": "<Form Associated Attribute Name>"
},
"form": {
"id": "<Form ID>",
"name": "<Form Name>"
}
},
{
"type": "constants",
"dataType": <Hierarchy Form Associated Attribute Datatype>,
"values": [""] <set to empty>
},
...
]
}
}
}
And the last for the EXPRESSION type prompts is the Metric Expression, which again falls into a similar structure as the previous two subcategories for EXPRESSION types but does not have a ‘form’ field associated with it; answer(s) can be constructed like so:
{
"key": "<Metric Expression Prompt Key>",
"type": "EXPRESSION",
"answers": {
"content": "<Metric Expression Prompt Name>",
"expression": {
"operator": "Equals" <or other operators (Equals, Between, etc.)>,
"operands": [
{
"type": "<Metric Expression Prompt Answer Type>",
"id": "<Metric Expression Prompt Answer ID>",
"name": "<Metric Expression Prompt Answer Name>"
},
{
"type": "constant",
"dataType": "Real" <or other valid DataType>,
"value": "" <set to empty>
}
]
}
}
}
Note, as seen in the above examples, the ‘constant(s)’ type object within the operands can either take in a singular value or an array of values. This depends on if the type being passed is ‘constant’ or ‘constants’:
...
{
"type": "constant",
"dataType": "Real"
"value": ""
}
Or...
{
"type": "constants",
"dataType": "Real",
"value": [""]
}
...
The ‘value’ associated with a ‘dataType’ may or may not need double quotes surrounding the value to pass, depending on the datatype provided (such as ‘Integer’ datatype values should not be passed with double quotes).
Additionally, the ‘dataType’ and ‘operator’ values within the expression object may also need to be modified depending on what’s expected for the expression. The best way to figure this out would be to look within the GUI of answering the prompt and seeing what’s given back to determine how to construct/modify the expression object, like in the example below.
For here, the expression associated would be something like so:
{
...
"expression": {
"operator": "Between"
"operands": [
...
{
"type": "constant",
"dataType": "Integer",
"value": 1
},
{
"type": "constant",
"dataType": "Integer",
"value": 100
}
Or...,
{
"type": "constants",
"dataType": "Integer",
"value": [1, 100]
}
]
}
}
Lastly, prompts with multiple nested pages to be answered are currently not supported within QuerySurge SQL function calls.
More general information about answer prompts through the REST API can be found here from the official Strategy Documentation.