Connecting Microsoft Power BI to QPR Web Service

The following is an example Power BI query that allows you to connect to the QPR MEA Web Service and import data. It first authenticates with the service to obtain a session ID, and then uses that session to query for specific data. The example query retrieves a list of all QPR EnterpriseArchitect/ProcessDesigner models, including their names and full IDs. This example requires that QPR Authentication is enabled on the server and that the user account used for authenticating is allowed to access the Web Service and data.

let
    authUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate",
    authBody = "{""logOnName"":""qpr"",""password"":""demo""}",
    
    authResponse = Web.Contents(
        authUrl,
        [
            Headers = [#"Content-Type"="application/json"],
            Content = Text.ToBinary(authBody)
        ]
    ),
    
    sessionIdJson = Json.Document(authResponse),
    sessionId = sessionIdJson[d],

    dataUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects",
    
    dataBody = "{""sessionId"":""" & sessionId & """,""query"":""[PG].Models"",""attributes"":""name,fullid""}",

    dataResponse = Web.Contents(
        dataUrl,
        [
            Headers = [#"Content-Type"="application/json"],
            Content = Text.ToBinary(dataBody)
        ]
    ),
    
    JsonData = Json.Document(dataResponse),

    // Step 1: Get the list of ResultObjects
    ResultObjects = JsonData[d][ChildElements],

    // Step 2: Define a helper function to extract a named attribute
    // from a ResultObject's ChildElements list
    GetAttribute = (obj as record, attrName as text) =>
        let
            props = obj[ChildElements],
            match = List.Select(props, each _[Name] = attrName),
            result = if List.Count(match) > 0 then match{0}[Value] else null
        in
            result,

    // Step 3: Build a table by extracting each attribute directly
    FinalTable = Table.FromList(
        ResultObjects,
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    ),

    // Step 4: Add a column for each attribute using the helper function
    WithName = Table.AddColumn(FinalTable, "name", each GetAttribute([Column1], "name")),
    WithFullId = Table.AddColumn(WithName, "fullid", each GetAttribute([Column1], "fullid")),

    // Step 5: Remove the original Column1
    FinalResult = Table.RemoveColumns(WithFullId, {"Column1"})

in
    FinalResult

How the Query Works

The query performs the following steps:

• Authentication: The query sends a request with a username and password to the QPR Web Service authentication endpoint.
• Session ID: Upon successful authentication, a temporary session ID is received.
• Data Request: The session ID is used with a second request to the data query endpoint. This request specifies what data to retrieve.
• Data Processing: The query receives the data in JSON format, parses it, and transforms it into a structured table with columns for each requested attribute.

How to Use and Customize the Query

To use this query in Power BI, you will need to copy the M code into the Power BI Advanced Editor. You must customize the following parameters to match your specific QPR environment and data requirements:

• authUrl: The URL for the QPR Web Service authentication endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate" with the URL of your QPR server's authentication service.
• authBody: The login credentials (username and password) required to access the web service. Modify the "logOnName" and "password" values. For example, to use the username "myuser" and password "mypassword", change the line to: authBody = "{""logOnName"":""myuser"",""password"":""mypassword""}",
• dataUrl: The URL for the QPR Web Service data query endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects" with the URL of your QPR server's query service.
• dataBody: Defines the specific data query to be executed. It contains the session ID, the query itself, and the attributes to be returned.
  • query: Modify the value to target different objects. The example "[PG].Models" retrieves all QPR EnterpriseArchitect/ProcessDesigner models. You can change this to query other objects as needed.
  • attributes: Modify the comma-separated list of attributes to retrieve. The example "name,fullid" returns the name and full ID of each queried object (which are models in the example). You can add or remove attributes based on what is available for the queried object type. See the Supported Attributes page in QPR Knowledge Base.

After customizing these variables, the query will connect to your QPR instance, retrieve the specified data, and load it into Power BI as a table for reporting and analysis.