Articles in this section

See more

Writing SQL Queries Against Strategy Dashboards (REST endpoint) (Versions: 10.3+)

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

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

1. Locate the Dashboard:

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

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

Determining a Dashboard Id (Web)

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

1. Locate the Dashboard:

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

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

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

strategyDashboard('<DashboardId>',’<VizualizationId>’,’<UseValueFormat>’,'<PromptJSON>')

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

  • DashboardId(string): Unique Identifier for Dashboard.
  • 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 Strategy. Defaults ON, to disable Pass 0. 
  • PromptJSON(string): Optional field. Answers to any prompts utilized in the Dashboard. 

Sample query:

SELECT * FROM strategyDashboard('5CF7727D4F9FCB8D93DA9086F16042F9’,’Visualization 1’)

Note: The VisualizationId parameter can be either the visualizations key which is a unique GUID generated internally by Strategy or its name a value controlled and inputted by the Dashboards developer. Since visualization names are controlled by end users, it is possible for duplicate visualization names to exist in a Dashboard. If duplicate visualization names are utilized in a Dashboard, 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 strategyDashboardMetadata function; described later in this article.

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

mceclip6.png

As compared to the data you see in Strategy: 

mceclip7.png

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

mceclip6.png

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 strategyDashboard('5CF7727D4F9FCB8D93DA9086F16042F9',’Visualization 1’) AS dos

Result Alterations

In order to facilitate easier testing, data extracted from some Strategy 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 Dashboard. The below shows one such example of this alteration:

mceclip0.png

Grid based Visualization with two headers

mceclip1.png

Data setup in Strategy Editor

mceclip2.png

Result data extracted by QuerySurge

As you can see, the results in QuerySurge are slightly different. In Strategy 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 Dashboard Metadata

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


Syntax:

strategyDashboardMetadata('<DashboardId>',’<MetadataFormat>’,'<PromptJSON>')
  • DashboardId(string): Unique Identifier for Dashboard.
  • 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 Dashboard. 

Example:

SELECT * FROM strategyDashboardMetadata('5CF7727D4F9FCB8D93DA9086F16042F9')

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

mceclip8.png

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:
mceclip3.png

Querying Dashboard Prompt Metadata

When developing data tests for Strategy Dashboards, prompt metadata may be needed in order to properly answer metadata prompts that need to be passed to a Dashboard function call (which includes strategyDashboard and strategyDashboardMetadata). To obtain prompt metadata for a Dashboard, use the strategyDashboardPromptMetadata function as follows:

Syntax:

strategyDashboardPromptMetadata('<DashboardId>')

Example:

SELECT *
FROM strategyDashboardPromptMetadata('5CF7727D4F9FCB8D93DA9086F16042F9')

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

mceclip4.png

Note: the values within the last column of a “strategyDashboardPromptMetadata” 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 Dashboard; 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:

mceclip5.png

Warning: Passing a Dashboard 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 Dashboard.

Passing Prompts to a Dashboard

In many cases Strategy Dashboards require one or more prompts to be answered prior to execution. All prompt data can be passed in either the strategyDashboard or strategyDashboardPromptMetadata 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 strategyDashboardMetadata('5CF7727D4F9FCB8D93DA9086F16042F9', 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 Dashboard, 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 Strategy  Documentation.

Related articles