Writing SQL Queries Against MicroStrategy Dossiers (Versions: 10.3+)

Versions: 8.0+

With QuerySurge BI Tester for MicroStrategy, data testers can use familiar SQL syntax to retrieve query data from MicroStrategy Dossiers 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 a MicroStrategy REST API, see the article Connecting QuerySurge to MicroStrategy REST API.

 Determining a Report Id (Developer)
To extract a MicroStrategy Dossier, the Dossier ID must be identified. The following describes the process of extracting a Dossier ID using MicroStrategy Developer.

1. Locate the dossier:

2. Right click the dossier and select "Properties":

3. Locate the "ID" field and copy the unique identifier for the report:

Determining a Dossier Id (Web)

To extract a MicroStrategy Dossier, the Dossier ID must be identified. The following describes the process of extracting a Dossier ID using MicroStrategy Web.

1. Locate the Dossier:

2. Right click the Dossier and select "Properties":

3. Locate the "ID" field and copy the unique identifier for the Dossier:

Querying Dossier 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 microstrategyDossier table function, which instructs the BI Tester driver to execute the Dossier and pull back the query data as a table for you to query. The function's syntax is as follows:

microstrategyDossier('<DossierId>',’<VizualizationId>’,’<UseValueFormat>’,'<PromptJSON>')

The function's arguments must be surrounded by single quotes. Following is a sample SQL statement against the Dossier "New Dossier" which has no prompts (the FormattedData & Prompt parameters are optional).

• DossierId(string): Unique Identifier for Dossier.
• VizualizationId(string): Key or Name of the visualization to extract
• UseValueFormat(integer): Optional field. Flag indicates if numeric data extracted should be included in the format displayed in MicroStrategy. Defaults ON, to disable Pass 0. 
• PromptJSON(string): Optional field. Answers to any prompts utilized in the Dossier. 

Sample query:

SELECT * FROM microstrategyDossier('C9FDA9F643F19FF41A755CA956C38F11’,’Visualization 1’)

Note: The VisualizationId parameter can be either the visualizations key which is a unique GUID generated internally by MicroStrategy or its name a value controlled and inputted by the Dossiers developer. Since visualization names are controlled by end users, it is possible for duplicate visualization names to exist in a Dossier. If duplicate visualization names are utilized in a Dossier, QuerySurge will extract data only from the first matching entity. In these cases, we recommend utilizing the visualizations key which are unique. Visualization keys can be determined through using the microstrategyDossierMetadata function; described later in this article.

Below is an example output in QuerySurge when executing the microstrategyDossier command:

As compared to the data you see in MicroStrategy: 

The following output is the same results utilizing UseValueFormat value of 0:

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 dos."Qualified Employees", 
       dos."Company Cost" 
FROM microstrategyDossier('C9FDA9F643F19FF41A755CA956C38F11',’Visualization 1’) AS dos

Result Alterations

In order to facilitate easier testing, data extracted from some MicroStrategy Visualizations may be re-ordered. This is done as some Visualizations contain multiple headers which would make it difficult to validate specific date points. To avoid this QuerySurge will create separate columns for all Attributes and Metrics contained within the Dossier. The below shows one such example of this alteration:

Grid based Visualization with two headers

Data setup in MicroStrategy Editor

Result data extracted by QuerySurge

As you can see, the results in QuerySurge are slightly different. In MicroStrategy the number of columns generated are dynamic as it needs to create a cartesian product with benefit type and the two displayed metrics (Qualified Employees and Company Cost), while in QuerySurge all attributes and metrics have their own columns. This alteration allows for simple querying of datasets without the need for complex SQL. 

Querying Dossier Metadata

When developing data tests for MicroStrategy Dossiers, it can be useful to see information about the datasets returned by the Dossier such as what columns are available and to extract visualization keys. Metadata can also be used to compare a report between two MicroStrategy Dossier or servers. . To obtain metadata for a Dossier, use the microstrategyDossierMetadata function as follows:

Syntax:

microstrategyDossierMetadata('<DossierId>',’<MetadataFormat>’,'<PromptJSON>')

• DossierId(string): Unique Identifier for Dossier.
• MetadataFormat(integer): Optional field. Flag indicating what format to return metadata in. Default value is 0, Pass 1 for alterative format. 
• PromptJSON(string): Optional field. Answers to any prompts utilized in the Dossier. 

Example:

SELECT * FROM microstrategyDossierMetadata('C9FDA9F643F19FF41A755CA956C38F11')

Note: microstrategyDossierMetadata can only retrieve one report per metadata query, and it cannot be combined with a Dossier data query (i.e. joins are not supported). This query returns the following columns:

• Chapter Name
• Chapter Key
• Page Name
• Page Key
• Visualization Name
• Visualization Key
• Columns
• Visualization Type

The example below is truncated for brevity:

The second parameter for this function is optional, and by default is set to ‘0’. If a ‘1’ is provided to this parameter, the metadata is displayed in a table that breaks down the Attributes, Metrics, and possible Column Sets/Spark Graphs (will be empty if not available) associated with each Visualization. The query having the ‘MetadataFormat’ parameter set to 1 returns the following columns:

• Chapter Name
• Chapter Key
• Page Name
• Page Key
• Visualization Name
• Visualization Key
• Attributes
• Metrics
• Column Set/Spark Graph Names
• Visualization Type

The example below is truncated for brevity:

Querying Dossier Prompt Metadata

When developing data tests for MicroStrategy Dossiers, prompt metadata may be needed in order to properly answer metadata prompts that need to be passed to a Dossier function call (which includes microstrategyDossier and microstrategyDossierMetadata). To obtain prompt metadata for a Dossier, use the microstrategyDossierPromptMetadata function as follows:

Syntax:

microstrategyDossierPromptMetadata('<DossierId>')

Example:

SELECT *
FROM microstrategyDossierPromptMetadata('C9FDA9F643F19FF41A755CA956C38F11')

Note: microstrategyDossierPromptMetadatacan only retrieve one report per metadata query, and it cannot be combined with a Dossier 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

The example below is truncated for brevity:

Note: the values within the last column of a “microstrategyDossierPromptMetadata” 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 Dossier; 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 Dossier ID that does not have prompt 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 Dossier.

Passing Prompts to a Dossier

In many cases MicroStrategy Dossiers require one or more prompts to be answered prior to execution. All prompt data can be passed in either the microstrategyDossier or microstrategyDossierPromptMetadata 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 microstrategyDossierMetadata('C9FDA9F643F19FF41A755CA956C38F11', 0,
'{
  "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 Dossier, if possible.

'{ "prompts": [] }'

If there are prompts that do not have a default answer and are required to be answered, 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 different categories and can be answered with an object that can represent either of these categories listed below:

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 MicroStrategy Documentation.