シナリオ

ローンデータをモデル化し、貸付金利を上昇させた場合の貸倒償却度合の変化についてWhat-If分析を行います。

システムアーキテクチャ

ローンデータをDBにインポート
DBに取り込んだローンデータをメモリ最適化列ストア構造に変換する（機械学習による前処理の効率化）
Rにデータをロードしディシジョンフォレストによるモデルトレーニングを実行する
トレーニング済みモデルを利用しWhat-If分析を行い、結果をメモリ最適化行ストア構造に格納する（機械学習による後処理の効率化）
分析結果をPower BIで可視化する

はじめに

ソフトウェア要件

(*) Power BI Desktop 英語版を用意してください（サンプルデータ内の位置データ（アメリカの州の略称データ）が日本語版のBingMap地図にマッピングができないため）。

SQL Server の事前設定

SQL Server 2017のDatabase Engine ServicesおよびMachine Learning Services（In-Database）のRをインストールしてください。
SQL Server 2017 内でR実行するにはsp_configureでexternal scripts enabledの設定変更が必要です。またexternal scripts enabledパラメータは設定変更の反映にSQL Server 2017の再起動が必要です。
- 1.外部スクリプト実行機能の有効化
  T-SQL
```
EXEC sp_configure 'external scripts enabled', 1;
```
- 2.SQL Server 2017の再起動
  cmd
```
net stop "SQL Server Launchpad (MSSQLSERVER)"
net stop "SQL Server (MSSQLSERVER)"
net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Launchpad (MSSQLSERVER)"
```
  netコマンドに渡すインスタンス名は環境に応じて変更してください。またSQL Server AgentサービスなどSQL Serverサービスに依存するサービスがある場合には明示的に再開してください。

サンプルデータ

LendingClub 社(貸付型クラウドファンディング事業者)が公開しているローンデータを利用します。

データのダウンロードサイトへ行き、「DOWNLOAD LOAN DATA」から期間を選択してCSVファイルとしてダウンロードします。

より多くの期間を利用するのが望ましいです。この記事で紹介するデモは「2007-2011」～「2017 Q2」までのデータをダウンロードしています。

このCSVファイルには、現在のローンステータス（借入中、遅れ、完済など）や最新の支払い情報を含む、発行されたすべてのローンの完全なデータが含まれています。

サンプルコード

CreateDatabase.sql
このチュートリアルに必要な各種データベースオブジェクトを作成します。
ImportCSVData.ps1
ダウンロードしたサンプルデータをDBにインポートします。
CreateColumnstoreIndex.sql
DBにインポートしたサンプルデータをメモリ最適化列ストア構造に変換します。
CreateModel.sql
Rにデータをロードしディシジョンフォレストによるモデルトレーニングを実行します。
ScoreLoans.ps1
貸付金利を現状維持した場合の貸付評価のスコアリングを行います。
WhatIf.ps1
貸付金利を変動した場合の貸付評価のスコアリングを行います。
LoanStatus.pbix
貸付金利を現状維持した場合と変動した場合それぞれの貸付評価をレポートし金利変動の影響を可視化します。
StateCodes.xlsx
州名と略称のマッピングデータです。Power BIレポートの中で利用します。

チュートリアル

STEP 1. データベースオブジェクトの作成

SSMSからCreateDatabase.sqlを実行し、データベースオブジェクトを作成します。

(*)データベースのデータファイルおよびトランザクションログはC:\Tiger\DATAに作成するよう記述されています。環境に応じて適宜変更してください。

CreateDatabase.sql（抜粋）

CREATE DATABASE [LendingClub]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LendingClubData', FILENAME = N'C:\Tiger\DATA\LendingClub.mdf' , SIZE = 19210240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536MB ), 
 FILEGROUP [InMemOLTP] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'InMem', FILENAME = N'C:\Tiger\DATA\InMem' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'LendingClubLog', FILENAME = N'C:\Tiger\DATA\LendingClub_log.ldf' , SIZE = 512MB , MAXSIZE = 2048GB , FILEGROWTH = 64MB )
GO

このSQL文が正常終了すると以下のオブジェクトが作成されます。

オブジェクト種別	オブジェクト名	説明
DATABASE	LendingClub	チュートリアルに使用するデータベース
TABLE	LoanStatsStaging	サンプルデータインポート用のステージングテーブル
TABLE	LoanStats	機械学習の対象となるローンデータ
TABLE	models	トレーニング済みモデルを格納するテーブル
TABLE	LoanStatsPredictions	貸付金利を現状維持した場合の貸付評価スコアを格納するテーブル
TABLE	LoanPredictionsWhatIf	貸付金利を変動した場合の貸付評価スコアを格納するテーブル
TABLE	WhatIf	What-If分析のために指定した貸付金利の引き上げ率を格納するテーブル
TABLE	RunTimeStats	実行時間を記録するためのテーブル
PROCEDURE	PerformETL	サンプルデータのETLを実行するプロシージャ
PROCEDURE	TrainLoansModel	ディシジョンフォレストによるモデルトレーニングを実行するプロシージャ
PROCEDURE	ScoreLoans	貸付金利を現状維持した場合の貸付評価のスコアリングを行うプロシージャ
PROCEDURE	ScoreLoansWhatIf	貸付金利を変動した場合の貸付評価のスコアリングを行うプロシージャ

STEP 2. サンプルデータのインポートとETL処理

PowerShellからImportCSVData.ps1を実行し、サンプルデータをインポートします。ImportCSVData.ps1の処理の流れは以下の通りです。

ImportCSVData.ps1の処理の流れ

CSVファイルを格納したフォルダ配下のCSVファイルをForeachで取り出し {
    取り出したCSVファイル内の１レコードをForeachで取り出し {
        取り出した１レコードをLoanStatsStagingテーブルへINSERT
    }
    PerformETLプロシージャを呼び出し {
        LoanStatsStagingテーブルのデータを加工してLoanStatsテーブルに転送
        LoanStatsテーブルのデータに対して特徴抽出処理 {
            IF (loan_status列値が「Late (16-30 days)」「Late (31-120 days)」「Default」「Charged Off」)
                is_bad(貸付評価) = 1
            ELSE
                is_bad(貸付評価) = 0
        }
        LoanStatsStagingテーブルをDELETE
    }
}

(*)DBへの接続情報、CSVファイルを格納したフォルダC:\Tiger\Extract\、ログを出力するフォルダ'C:\Tiger\Logs\'は環境に応じて適宜変更してください。

ImportCSVData.ps1（抜粋）

# Connection Info
$SqlServer = "." # TODO: Change the name of SQL Server instance name
$dbName = "LendingClub" # TODO: Change the name of the database

# Get a list of all the CSV files in the folder
$files = ls C:\Tiger\Extract\*.csv # TODO: Change the path to the appropriate location of the CSV files

# Log folder path
$LogPath = "C:\Tiger\Logs\" # TODO: Change the path of the log folder

(*)PowerShell内で実行しているInvoke-Sqlcmdの既定のタイムアウト値（30秒）が問題になる場合は適宜変更してください。

STEP 3. インポートデータをメモリ最適化列ストア構造に変換

CreateColumnstoreIndex.sqlを実行し、機械学習による前処理の効率化のためにインポートデータをメモリ最適化列ストア構造に変換します。

CreateColumnstoreIndex.sql

CREATE NONCLUSTERED COLUMNSTORE INDEX [ncci_LoanStats] ON [dbo].[LoanStats]
(
    [revol_util],
    [int_rate],
    [mths_since_last_record],
    [annual_inc_joint],
    [dti_joint],
    [total_rec_prncp],
    [all_util],
    [is_bad]
)WITH (COMPRESSION_DELAY = 0, MAXDOP = 1) ON [PRIMARY];

STEP 4. ディシジョンフォレストによるモデルトレーニング

CreateModel.sqlを実行し、Rにデータをロードしディシジョンフォレストによるモデルトレーニングを実行し、トレーニング済みモデルをmodelテーブルに格納します。

CreateModel.sql内で呼び出されているTrainLoansModelプロシージャが処理の実体です。トレーニングのためのデータセットはLoanStatsテーブルの75％サンプリングです。

STEP 5. 貸付金利を現状維持した場合の貸付評価のスコアリング

ScoreLoans.ps1を実行し、貸付金利を現状維持した場合の貸付評価のスコアリングを行います。

ScoreLoans.ps1内で呼び出されているScoreLoansプロシージャが処理の実体です。STEP 4で作成したモデルを利用し、LoanStatsテーブルのデータに対する貸付評価のスコアリングを行い、結果をLoanStatsPredictionsインメモリテーブルに格納します。

(*)DBへの接続情報は環境に応じて適宜変更してください。

ScoreLoans.ps1（抜粋）

$SqlServer = "."  # TODO: Change the name of SQL Server instance name
$dbName = "LendingClub" # TODO: Change the name of the database

(*)スコアリングは並列実行（対象とするレコードの範囲ごとに分担処理）しています。高い多重度でWRITEを行うようなワークロードに対してインメモリテーブルの利用は適しています。さらにSETP 7.の可視化に伴うREADとの並列実行においても同様です。

STEP 6. 貸付金利を変動した場合の貸付評価のスコアリング

WhatIf.ps1を実行し、貸付金利を現状維持した場合の貸付評価のスコアリングを行います。

WhatIf.ps1は変動させる貸付金利を対話型で受け取り（引き上げ率を％単位で指定してください）、処理の実体となるScoreLoansWhatIfプロシージャに渡します。
ScoreLoansWhatIfはSTEP 4で作成したモデルを利用し、貸付金利を変動させたLoanStatsテーブルのデータに対して貸付評価のスコアリングを行い、結果をLoanPredictionsWhatIfインメモリテーブルに格納します。