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

2022-10-20
技術

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

形態 Excel からデータを加工して取得する
SaaS x
自社開発 o

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

目次

Azure SQL Database

データベースの作成

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

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

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

IP アドレスを登録する

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

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

readonly ユーザーを作成する

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

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

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

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

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

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

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

Excel Power Query

M 言語について

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

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

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

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

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

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

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

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

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

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

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

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

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

プライバシーの設定

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

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

powerquery 22 powerquery 23

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

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

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

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

コードについて

  • 引数を指定してデータを取得するには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

実行を許可します。 powerquery 13 powerquery 14

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

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

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

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

まとめ

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

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

ご相談は無料にて承っております。お気軽にお問い合わせください!