自社でデータを保有する最大のメリットとは？

弊社のAzure 開発のメリットとして一番に挙げたいのは、 自社でデータベースを保有することでExcel で簡単にデータを加工して取得できることです。

一例として、Excel から Azure SQL Database の情報を取得する方法をご紹介します。今回の例では指定期間の売上高を取得してみます。

目次

Azure SQL Database

データベースの作成

Azure SQL Database にデプロイするの手順に従ってデータベースを作成しサンプル(AdventureWorks)を復元します。

データベース名はAdventureWorksとします。サイズはStandard S0くらいで OK です。

追加設定より「既存のデータを使用します」で「サンプル」を選ぶと自動的に AdventureWorksLT を復元してくれます。

IP アドレスを登録する

データを取得できる場所を限定しておくと安全です。自分の IP アドレスはこちらのサイトにアクセスして調べます。

「クライアント IPv4 アドレスの追加」を選ぶと自動的に今の環境の IP アドレスを追加してくれます。

readonly ユーザーを作成する

こちらの手順に従ってデータ取得専用のユーザーを作成します。所有者権限のユーザーを使うとデータを変更・削除できてしまいますので、安全のため読取専用ユーザーでデータを取得するようにします。

Azure Data Studioをインストールして使います。

データベース作成時に設定した管理者権限で接続します。

masterに対してログインを作成する SQL を実行します。

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

AdventureWorksに対してユーザーを作成し権限を付与する SQL を実行します。

CREATE USER readonlyuser FROM LOGIN readonlylogin;
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

Excel Power Query

M 言語について

Power Query は「M 言語」で記述します。

M 言語は、inで取得するデータの中身をletに書いていきます。 Excel の数式を連続して書いていくようなイメージで取得データを簡単に定義できます。

• Power Query M 言語

引数(パラメーター)を設定する

ここでは簡単に開始日と終了日を引数として指定します。指定期間の売上データを取得するためです。

Excel のセルに引数を入力してParamsテーブルとして設定します。

テーブル名をParamsに変更しておきます。

ここから Power Query でクエリを作成していきます。引数を Power Query で扱えるデータにするためです。

データ → テーブルまたは範囲からを選びます。

Paramsテーブルからクエリが作成されました。開始日と終了日の型を修正します。

詳細エディターを開くとコードが出てきますのでdatetimeをdateに修正します。SQL にするときに時間部分が不要なためです。

let
    ソース = Excel.CurrentWorkbook(){[Name="Params"]}[Content],
    変更された型 = Table.TransformColumnTypes(Source,{{"開始日", type date}, {"終了日", type date}})
in
    変更された型

閉じて読み込みますが、引数データはすでにテーブルとして用意してあるのでデータを表示する必要はありません。「接続専用」に設定しておきます。

引数が接続専用として読み込まれました。

プライバシーの設定

先に Power Query のプライバシーの設定をしておきます。

これは引数に設定したデータを Azure SQL Database に送るときに、顧客名などプライバシーに関わるデータを誤って送らないようにするための設定です。今回はどちらも自社管理のデータなのでプライバシーを無視する設定にします。

SQL 文を書いてデータを取得する

いよいよ Azure SQL Database からデータを読み込みます。

空のクエリを追加していきます。

詳細エディターにコードを書きます。

コードについて

• 引数を指定してデータを取得するにはValue.NaviteQuery関数を利用します。
• SQL はSELECT クエリさえ押さえておけばデータ取得には問題ありません。
  • SELECT * FROM SalesLT.SalesOrderHeader WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate;の部分が SQL になります。
• WHEREに指定する引数はParamsクエリから読み込みます。
  • Params{0}は Params テーブルの 1 行目をレコードとして返し(参考)、
  • Params{0}[開始日]はそのレコードのうち「開始日」項目の値を返す(参考)、という意味になります。

let
    Target = Sql.Database(
        "excel-powerquery.database.windows.net", "AdventureWorks"
    ),
    Query = Value.NativeQuery(
        Target,
        "SELECT * FROM SalesLT.SalesOrderHeader WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate;",
        [
            StartDate = Params{0}[開始日], EndDate = Params{0}[終了日]
        ]
    )
in
    Query

実行を許可します。

上で設定した読取専用ユーザーでログインします。

クエリ名をDataに変更しておきます。

閉じて読み込むと、Azure SQL Database からデータが読み込まれます。

開始日を変更してデータを更新すると該当データがないことが分かります。

まとめ

一度設定をしてしまえば、Excel のデータ更新で常に最新のデータを取得でき、取得したデータは自由に加工できます。

今後、経営におけるデータの重要性はますます高まってくると考えられます。データの自社所有のメリットを感じていただけますと幸いです。