Asynchronous Refresh with the REST API for Azure Analysis Services

We are pleased to introduce the REST API for Azure Analysis Services. Using any programming language that supports REST calls, you can now perform asynchronous data-refresh operations. This includes synchronization of read-only replicas for query scale out.

Data-refresh operations can take some time depending on various factors including data volume and level of optimization using partitions, etc. These operations have traditionally been invoked with existing methods such as using TOM (Tabular Object Model), PowerShell cmdlets for Analysis Services, or TMSL (Tabular Model Scripting Language). The traditional methods may require long-running HTTP connections. A lot of work has been done to ensure the stability of these methods, but given the nature of HTTP, it may be more reliable to avoid long-running HTTP connections from client applications.

The REST API for Azure Analysis Services enables data-refresh operations to be carried out asynchronously. It therefore does not require long-running HTTP connections from client applications. Additionally, there are other built-in features for reliability such as auto retries and batched commits.

Base URL

The base URL follows this format:

https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/

For example, consider a model named AdventureWorks, on a server named myserver, located in the West US Azure region. The server name is:

asazure://westus.asazure.windows.net/myserver

The base URL is:

https://westus.asazure.windows.net/servers/myserver/models/AdventureWorks/

Using the base URL, resources and operations can be appended based on the following diagram:

• Anything that ends in "s" is a collection.
• Anything that ends with "()" is a function.
• Anything else is a resource/object.

For example, you can use the POST verb on the Refreshes collection to perform a refresh operation:

https://westus.asazure.windows.net/servers/myserver/models/AdventureWorks/refreshes

Authentication

All calls must be authenticated with a valid Azure Active Directory (OAuth 2) token in the Authorization header and must meet the following requirements:

• The token must be either a user token or an application service principal.
• The user or application must have sufficient permissions on the server or model to make the requested call. The permission level is determined by roles within the model or the admin group on the server.
• The token must have the correct audience set to: "https://*.asazure.windows.net".

POST /refreshes

To perform a refresh operation, use the POST verb on the /refreshes collection to add a new refresh item to the collection. The Location header in the response includes the refresh ID. The client application can disconnect and check the status later if required because it is asynchronous.

Only one refresh operation is accepted at a time for a model. If there is a current running refresh operation and another is submitted, the 409 Conflict HTTP status code will be returned.

The body may, for example, resemble the following:

 {
    "Type": "Full",
    "CommitMode": "transactional",
    "MaxParallelism": 2,
    "RetryCount": 2,
    "Objects": [
        {
            "table": "DimCustomer",
            "partition": "DimCustomer"
        },
        {
            "table": "DimDate"
        }
    ]
}

Here's a list of parameters:

Name	Type	Description	Required?	Default
Type	Enum	The type of processing to perform. The types are aligned with the TMSL refresh command types: full, clearValues, calculate, dataOnly, automatic, add and defragment.	False	automatic
CommitMode	Enum	Determines if objects will be committed in batches or only when complete.Modes include: default, transactional, partialBatch.	False	transactional
MaxParallelism	int	This value determines the maximum number of threads on which to run processing commands in parallel. This aligned with the MaxParallelism property that can be set in the TMSL Sequence command or using other methods.	False	10
RetryCount	int	Indicates the number of times the operation will retry before failing.	False	0
Objects	Array[]	An array of objects to be processed. Each object includes:"table" when processing the entire table or "table" and "partition" when processing a partition.If no objects are specified, the whole model is refreshed.	False	Process the entire model

CommitMode equal to partialBatch can be used when doing an initial load of a large dataset that may take hours. At time of writing, the batch size is the MaxParallelism value, but this may change. If the refresh operation fails after successfully committing one or more batches, the successfully committed batches will remain committed (it will not roll back successfully committed batches).

GET /refreshes/<refreshId>

To check the status of a refresh operation, use the GET verb on the refresh ID. Here's an example of the response body. The status field returns “inProgress” if the operation is in progress.

 {
    "startTime": "2017-12-07T02:06:57.1838734Z",
    "endTime": "2017-12-07T02:07:00.4929675Z",
    "type": "full",
    "status": "succeeded",
    "currentRefreshType": "full",
    "objects": [
        {
            "table": "DimCustomer",
            "partition": "DimCustomer",
            "status": "succeeded"
        },
        {
            "table": "DimDate",
            "partition": "DimDate",
            "status": "succeeded"
        }
    ]
}

GET /refreshes

To retrieve a list of historical refresh operations for a model, use the GET verb on the /refreshes collection. Here is an example of the response body. At time of writing, the last 30 days of refresh operations are stored and returned, but this is subject to change.

 [
    {
        "refreshId": "1344a272-7893-4afa-a4b3-3fb87222fdac",
        "startTime": "2017-12-09T01:58:04.76",
        "endTime": "2017-12-09T01:58:12.607",
        "status": "succeeded"
    },
    {
        "refreshId": "474fc5a0-3d69-4c5d-adb4-8a846fa5580b",
        "startTime": "2017-12-07T02:05:48.32",
        "endTime": "2017-12-07T02:05:54.913",
        "status": "succeeded"
    }
]

DELETE /refreshes/<refreshId>

To cancel an in-progress refresh operation, use the DELETE verb on the refresh ID.

POST /sync

Having performed refresh operations, it may be necessary to synchronize the new data with replicas for query scale out. To perform a synchronize operation for a model, use the POST verb on the /sync function. The Location header in the response includes the sync operation ID.

GET /sync?operationId=<operationId>

To check the status of a sync operation, use the GET verb passing the operation ID as a parameter. Here's an example of the response body:

 {
    "operationId": "cd5e16c6-6d4e-4347-86a0-762bdf5b4875",
    "database": "AdventureWorks2",
    "UpdatedAt": "2017-12-09T02:44:26.18",
    "StartedAt": "2017-12-09T02:44:20.743",
    "syncstate": 2,
    "details": null
}

Possible values for syncstate include the following:

• 0: Replicating. Database files are being replicated to a target folder.
• 1: Rehydrating. The database is being rehydrated on read-only server instance(s).
• 2: Completed. The sync operation completed successfully.
• 3: Failed. The sync operation failed.
• 4: Finalizing. The sync operation has completed but is performing clean up steps.

Code sample

Here's a C# code sample to get you started:

https://github.com/Microsoft/Analysis-Services/tree/master/RestApiSample

To use the code sample, first do the following:

1. Clone or download the repo. Open the RestApiSample solution.
2. Find the line “client.BaseAddress = …” and provide your base URL (see above).

The code sample can use the following forms of authentication:

• Interactive login or username/password
• Service principal

Interactive login or username/password

This form of authentication requires an Azure application be set up with the necessary API permissions assigned. This section describes how to set up the application using the Azure portal.

1. Select the Azure Active Directory section, click App registrations, and then New application registration.

2. In the Create blade, enter a meaningful name, select Native application type, and then enter “urn:ietf:wg:oauth:2.0:oob” for the Redirect URI. Then click the Create button.

3. Select your app from the list and take note of the Application ID.

4. In the Settings section for your app, click Required permissions, and then click Add.

5. In Select an API, type “SQL Server Analysis Services” into the search box. Then select “Azure Analysis Services (SQL Server Analysis Services Azure)”.

6. Select Read and Write all Models and then click the Select button. Then click Done to add the permissions. It may take a few minutes to propagate.

7. In the code sample, find the method UpdateToken(). Observe the contents of this method.
8. Find the line “string clientID = …” and enter the application ID you previously recorded.
9. Run the sample.

Service principal

Please see the Automation of Azure Analysis Services with Service Principals and PowerShell blog post for how to set up a service principal and assign the necessary permissions in Azure Analysis Services. Having done this, the following additional steps are required:

1. Find the line “string authority = …” and enter your organization’s tenant ID in place of “common”. See code comments for further info.
2. Comment/uncomment so the ClientCredential class is used to instantiate the cred object. Ensure the <App ID> and <App Key> values are accessed in a secure way or use certificate-based authentication for service principals.
3. Run the sample.