Writing SQL Queries Against MicroStrategy Reports

With QuerySurge BI Tester for MicroStrategy, data testers can use familiar SQL syntax to retrieve query data from MicroStrategy Reports and validate it against any QuerySurge-supported Source or Target. MicroStrategy 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 MicroStrategy, see the article Connecting QuerySurge to MicroStrategy. 

MicroStrategy Asset Types

In order to test MicroStrategy it is important to know the some of the core asset types:

Project: Are the highest level asset in MicroStrategy. 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 MicroStrategy report, the Report ID must be identified. The following describes the process of extracting a report ID using Microstrategy 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 MicroStrategy report, the Report ID must be identified. The following describes the process of extracting a report ID using Microstrategy 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 MicroStrategy 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 MicroStrategy 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 MicroStrategy 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 MicroStrategy 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>