NOTE: This Article describes the process of querying Strategy Reports using the SOAP endpoint. This endpoint has been removed in later versions of Strategy. Please use our REST based connections/syntax for newer versions of Strategy.
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).
For details on setting up a connection to Strategy, see the article Connecting QuerySurge to Strategy.
Strategy Asset Types
In order to test Strategy it is important to know the some of the core asset types:
Project: Are the highest level asset in Strategy. Project's allow for separation of assets between groups.
Grid/Graph Reports: Reports allow users to view data. Reports can be displayed in 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 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>','<PromptXML>')
The function's arguments must be surrounded by single quotes. Following is a sample SQL statement against the report "Individual Sales Analysis" which has no prompts.
SELECT *FROM microstrategyReport('2627169745E4D76AC2A16980DCF35B9A','')
When executed in QuerySurge, the following results are returned:
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 isa."Region", isa."Sales"FROM microstrategyReport('2627169745E4D76AC2A16980DCF35B9A','') AS isa
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>','<PromptXML>')
Example:
SELECT *FROM microstrategyMetadata('2627169745E4D76AC2A16980DCF35B9A','')
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 query names (as table names) and column names for all queries in the report (the example here is truncated for brevity):
Passing Prompts to a report
In many cases Strategy reports require one more prompts to be answered prior to execution. All prompt data can be passed in either the microstrategyReport or microstrategyMetadata request. The XML structure for MicroStrategy prompts can be complex, however it can be quickly generated using a document. Following is a sample SQL statement against the report "Supplier Profit Margin, Percent Growth" which has a single element prompt to select a quarter. In this example 2016 Q3 was selected.
SELECT *FROM microstrategyMetadata('D1AE5B4E11D5C4D04C200E8820504F4F','<rsl><pa pt="7" pin="0" did="E638358A11D5C49EC0000C881FDA1A4F" tp="10"><mi><es><at did="8D679D4A11D3E4981000E787EC6DE8A4" tp="12"/><e emt="1" ei="8D679D4A11D3E4981000E787EC6DE8A4:20163" art="1" disp_n="2016 Q3"/></es></mi></pa></rsl>')
The prompt data displayed above, was generated with the following steps.
1. Right click on an empty section of the screen and select "New"
2. Click "Document"
3. Select "Blank Document"
4. Click "OK"
5. Locate the report
6. Select Report
7. Click "Open"
8. Click "Text" button to insert a text box.
9. Draw a text box in the document. Add the text "{&PROMPTXML}"
10. Click "View"
11. Click "HTML"
12. Select 2016 Q3 and Click "Finish"
13. Copy XML Prompt data from added text field
Customization of prompt inputs can be done by modifying the XML extracted. Below are some example prompt XML's for different prompt types.
Multiple Element Prompts:
<?xml version="1.0" encoding="UTF-8"?>
<rsl>
<pa pt="7" pin="0" did="C61D52BC4B222E14F1AA2580A0D2600B" tp="10">
<mi>
<es>
<at did="8D679D5011D3E4981000E787EC6DE8A4" tp="12" />
<e emt="1" ei="8D679D5011D3E4981000E787EC6DE8A4:101" art="1" disp_n="Bantam Books" />
</es>
</mi>
</pa>
<pa pt="7" pin="0" did="E638358A11D5C49EC0000C881FDA1A4F" tp="10">
<mi>
<es>
<at did="8D679D4A11D3E4981000E787EC6DE8A4" tp="12" />
<e emt="1" ei="8D679D4A11D3E4981000E787EC6DE8A4:20163" art="1" disp_n="2016 Q3" />
</es>
</mi>
</pa>
</rsl>
Multiple Element Prompts + Value Prompt:
<?xml version="1.0" encoding="UTF-8"?>
<rsl>
<pa pt="7" pin="0" did="56B3B4F2468AA31CDC5B059237F0E4B2" tp="10">
<mi>
<es>
<at did="8D679D5011D3E4981000E787EC6DE8A4" tp="12" />
<e emt="1" ei="8D679D5011D3E4981000E787EC6DE8A4:101" art="1" disp_n="Bantam Books" />
</es>
</mi>
</pa>
<pa pt="4" pin="0" did="899FF924425152E19D764FA95A54083B" tp="10">201609</pa>
<pa pt="7" pin="0" did="E638358A11D5C49EC0000C881FDA1A4F" tp="10">
<mi>
<es>
<at did="8D679D4A11D3E4981000E787EC6DE8A4" tp="12" />
<e emt="1" ei="8D679D4A11D3E4981000E787EC6DE8A4:20163" art="1" disp_n="2016 Q3" />
</es>
</mi>
</pa>
</rsl>
Object Prompt:
<?xml version="1.0" encoding="UTF-8"?>
<rsl>
<pa pt="6" pin="0" did="24B52FC5453E645252C5468AE448E652" tp="10">
<mi>
<fct qsr="0" fcn="0" cc="1" sto="1" pfc="0" pcc="1">
<at did="96ED3EC811D5B117C000E78A4CC5F24F" tp="12" />
</fct>
</mi>
</pa>
</rsl>