概要
下記参考リンクを元にAzure Analysis Services(以下、AAS)をAzure Data Factory(以下、ADF) で更新するパイプラインを構成します。
参考リンク
手順
- AAS,ADFのリソースをデプロイする
- サンプルモデル作成
- 権限設定
- Pipeline作成
- 動作確認
AAS,ADFのリソースをデプロイする
AAS
クイックスタート参照
https://docs.microsoft.com/ja-jp/azure/analysis-services/analysis-services-create-server
ADF
サンプルモデル作成
下記コードをSSMSから実行してください
**クリックで展開します**
{
"create": {
"database": {
"name": "SAMPLE_AS",
"compatibilityLevel": 1500,
"model": {
"name": "モデル",
"culture": "ja-JP",
"tables": [
{
"name": "T_SAMPLE_1",
"columns": [
{
"name": "AMT_1",
"dataType": "int64",
"sourceColumn": "AMT_1"
},
{
"name": "AMT_2",
"dataType": "int64",
"sourceColumn": "AMT_2"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45WMjQwUNIBkQZKsTrRSkZwLoRvjOBDBEyQBCAipsgiQKFYAA==\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AMT_1 = _t, AMT_2 = _t]),",
" 変更された型 = Table.TransformColumnTypes(ソース,{{\"AMT_1\", Int64.Type}, {\"AMT_2\", Int64.Type}})",
"in",
" 変更された型"
]
}
}
],
"measures": [
{
"name": "MEASURE_1",
"expression": [
"",
"SUM(T_SAMPLE_1[AMT_1])"
]
},
{
"name": "MEASURE_2",
"expression": [
"",
"SUM(T_SAMPLE_1[AMT_2])"
]
}
]
},
{
"name": "T_SAMPLE_2",
"columns": [
{
"name": "AMT_3",
"dataType": "int64",
"sourceColumn": "AMT_3"
},
{
"name": "AMT_4",
"dataType": "int64",
"sourceColumn": "AMT_4"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45WMjIwMFDSUTJUitWJVjKGcIzAHBMIxxjMMYVwTMAcMwjHVCk2FgA=\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AMT_3 = _t, AMT_4 = _t]),",
" 変更された型 = Table.TransformColumnTypes(ソース,{{\"AMT_3\", Int64.Type}, {\"AMT_4\", Int64.Type}})",
"in",
" 変更された型"
]
}
}
],
"measures": [
{
"name": "MEASURE_3",
"expression": [
"",
"SUM(T_SAMPLE_2[AMT_3])"
]
},
{
"name": "MEASURE_4",
"expression": [
"",
"SUM(T_SAMPLE_2[AMT_4])"
]
}
]
}
],
"annotations": [
{
"name": "ClientCompatibilityLevel",
"value": "600"
}
]
}
}
}
}
権限設定
SSMSでAASに接続します
サーバ名を右クリックでプロパティ→セキュリティ→追加の順にクリック
ADFのプロパティ上にある、マネージドIDテナント、マネージドIDアプリケーションIDを利用して
「app:<マネージドIDアプリケーションID>@<マネージドIDテナント>」のような文字列を用意します。
SSMSに戻り、手動エントリに入力後、追加→OKの順にクリック
Pipeline作成
ADF上で、「ProcessAzureAS MSI」という名称のパイプラインを作成し、下記コードをcode欄から貼り付けます。
**クリックで展開します**
{
"name": "ProcessAzureAS MSI",
"properties": {
"activities": [
{
"name": "UntilRefreshComplete",
"type": "Until",
"dependsOn": [
{
"activity": "FilterToCurrentRefresh",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@not(equals(activity('GetAzureASRefreshStatus').output.status,'inProgress'))",
"type": "Expression"
},
"activities": [
{
"name": "GetAzureASRefreshStatus",
"type": "WebActivity",
"dependsOn": [
{
"activity": "Wait30Seconds",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://',pipeline().parameters.Region,'.asazure.windows.net/servers/',pipeline().parameters.Server,'/models/',pipeline().parameters.DatabaseName,'/refreshes/',activity('FilterToCurrentRefresh').output.Value[0].refreshId)",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://*.asazure.windows.net"
}
}
},
{
"name": "Wait30Seconds",
"type": "Wait",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"waitTimeInSeconds": 30
}
}
],
"timeout": "7.00:00:00"
}
},
{
"name": "StartProcessingAzureAS",
"type": "WebActivity",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://',pipeline().parameters.Region,'.asazure.windows.net/servers/',pipeline().parameters.Server,'/models/',pipeline().parameters.DatabaseName,'/refreshes')",
"type": "Expression"
},
"method": "POST",
"body": {
"Type": "Full",
"CommitMode": "transactional",
"MaxParallelism": 10,
"RetryCount": 2
},
"authentication": {
"type": "MSI",
"resource": "https://*.asazure.windows.net"
}
}
},
{
"name": "IfFailed",
"type": "IfCondition",
"dependsOn": [
{
"activity": "UntilRefreshComplete",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@equals(activity('GetAzureASRefreshStatus').output.status,'failed')",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "ThrowErrorOnFailure",
"type": "WebActivity",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@string(activity('GetAzureASRefreshStatus').output)",
"type": "Expression"
},
"method": "GET"
}
}
]
}
},
{
"name": "GetAzureASRefreshes",
"type": "WebActivity",
"dependsOn": [
{
"activity": "StartProcessingAzureAS",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://',pipeline().parameters.Region,'.asazure.windows.net/servers/',pipeline().parameters.Server,'/models/',pipeline().parameters.DatabaseName,'/refreshes')",
"type": "Expression"
},
"method": "GET",
"body": {
"Type": "Full",
"CommitMode": "transactional",
"MaxParallelism": 10,
"RetryCount": 2
},
"authentication": {
"type": "MSI",
"resource": "https://*.asazure.windows.net"
}
}
},
{
"name": "FilterToCurrentRefresh",
"type": "Filter",
"dependsOn": [
{
"activity": "GetAzureASRefreshes",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"items": {
"value": "@json(activity('GetAzureASRefreshes').output.Response)",
"type": "Expression"
},
"condition": {
"value": "@greaterOrEquals(item().startTime,addseconds(activity('StartProcessingAzureAS').output.startTime,-30))",
"type": "Expression"
}
}
}
],
"parameters": {
"TenantID": {
"type": "String"
},
"SubscriptionID": {
"type": "String"
},
"Region": {
"type": "String"
},
"Server": {
"type": "String"
},
"DatabaseName": {
"type": "String"
}
},
"folder": {
"name": "Azure AS"
},
"annotations": []
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
動作確認
debugをクリックすると処理パラメータの入力ができるので入力して実行（OK）します。
パラメータの確認方法
- Tenant ID:上記ADFのテナントと同様です。
- Subscription:下記画像参照
- Region:東日本ならjapaneast
- Server:下記画像参照
- DatabaseName:サンプルならadventureworks
実行完了
補足
AASのファイアウォールを設定する場合、ADFのAzure IRのIP範囲下記で確認してホワイトリストに登録しましょう。
https://docs.microsoft.com/ja-jp/azure/data-factory/azure-integration-runtime-ip-addresses
追記
Firewallの範囲を絞りたいときは、Self-Hosted IRを利用して、IRが稼働するサーバのPublic IPをFire Wallに登録しましょう（2020/7/9 動作確認済み）