Help us understand the problem. What is going on with this article?

Azure Analysis ServicesをAzure Data Factory で更新する

概要

下記参考リンクを元にAzure Analysis Services(以下、AAS)をAzure Data Factory(以下、ADF) で更新するパイプラインを構成します。

参考リンク

https://github.com/furmangg/automating-azure-analysis-services

手順

  1. AAS,ADFのリソースをデプロイする
  2. サンプルモデル作成
  3. 権限設定
  4. Pipeline作成
  5. 動作確認

AAS,ADFのリソースをデプロイする

AAS

クイックスタート参照
https://docs.microsoft.com/ja-jp/azure/analysis-services/analysis-services-create-server

ADF

クイックスタート参照
https://docs.microsoft.com/ja-jp/azure/data-factory/quickstart-create-data-factory-portal#create-a-data-factory

サンプルモデル作成

下記コードをSSMSから実行してください

image.png


クリックで展開します
{
  "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に接続します
image.png

サーバ名を右クリックでプロパティ→セキュリティ→追加の順にクリック
image.png

image.png

ADFのプロパティ上にある、マネージドIDテナント、マネージドIDアプリケーションIDを利用して
「app:<マネージドIDアプリケーションID>@<マネージドIDテナント>」のような文字列を用意します。
image.png

SSMSに戻り、手動エントリに入力後、追加→OKの順にクリック

image.png

Pipeline作成

ADF上で、「ProcessAzureAS MSI」という名称のパイプラインを作成し、下記コードをcode欄から貼り付けます。
image.png


クリックで展開します

{
    "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)します。
image.png

パラメータの確認方法

  • Tenant ID:上記ADFのテナントと同様です。
  • Subscription:下記画像参照
  • Region:東日本ならjapaneast
  • Server:下記画像参照
  • DatabaseName:サンプルならadventureworks

image.png

実行完了
image.png

補足

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 動作確認済み)

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away