# 18. Columnar DB & BI
________________________________________
列指向ストレージ  
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_columnar_storage_disk_mem_mgmnt.html

________________________________________
## 1. 用語
________________________________________

用語        |意味
------------|-------------------
No SQL      |Oracle, mysql, postgres, sqlite, accessなどではないDBの総称
KVS         |Key Value Store。辞書のこと。文脈によってはNo SQLのジャンルの1つ
列指向DB    |列指向なDB
DWH         |DataWare House。情報系DBのためのDBMSやサービスの総称。概ね列指向DB
業務系DB    |基幹システムや会計システムのDBのこと
情報系DB    |BI用のDBのこと。概ね業務系DBのレプリケーションスレーブ、DWHのどちらか
BI          |DWH, csvなどからクロス表、棒、折れ線、帯、パイ、ヒストグラムなどを出す仕組み
データマート|BIツールで直接利用することを想定して整形した（クロス表の元データにできる）テーブル群
OLAP        |きれいなエクセル&UIのこと。勘違いしやすいが必ずしもリアルタイムデータを見ることではない
R-OLAP      |同期スレーブRDBに直接リクエストしてリアルタイムデータを見る方式。表示が遅い
M-OLAP      |非同期集計済みDBにリクエストして前日以前のデータを見る方式。表示が速い

-
________________________________________
## 2. Columnar DB
________________________________________
共通の特徴

```text
構造
1. ディスク上の保持の仕方が、列で連続である
2. SELECTする際、使用された列の値だけ読み取られる(通常のrdbは1行全体を必ず読み取る)

特性
1. SELECT時に使用する列数に応じて、ディスクIO・消費メモリが少なくなり、速度が変わる
2. 1行だけ取得する場合は、使用する列数だけJOINするような挙動のため、通常のRDBより少し遅い
3. 1000万行取得する場合は、列指向DBでも遅い
4. 本質的には集計のアルゴリズム自体がRDBより速いわけではない
5. 集計が速い×
6. 使用する列が少なければ速い○
7. INSERT・UPDATEがテーブルの列数の数だけ遅くなる。ただしせいぜい列数*2倍かかる程度である
    - ただし、非同期DBならそもそもダイレクトローディングする前提になるため重要ではない
```

例：DrSumの場合

```text
要約
Amazon redshift的に言えば、全て4ByteなByteDict形式（redshiftのByteDictは1byte）

詳細
1. テーブルごとに本体（DAT）と列の数だけ列テーブル（DRX）を持つ
    DRX：各カラムの値を列挙型とみなし値を持つテーブル。NULLはNID=1。DRXの行数＝カーディナリティ数
    DAT：行データ。各属性値はDRXのNID値
2. SELECT
    ①必要な列だけメモリに展開される
    ②DAT同士で普通に結合される。列値は全てint（DRXもDATも20億（21億なため）を超えられない）
      ※ インメモリDBの場合はこの限りではない
    ③実際の値であるDRXが、SELECT対象の列だけ結合される
        →列が少ないほどディスクIOやJOIN数が減り、高速になる
3. INSERT・UPDATE
    ①DRXごとにINSERT・UPDATE対象の値が存在するかチェック
    ②存在しない値はDRXへINSERT
    ③DATへINSERTやUPDATEの適用
4. DELETE
    - DATだけDELETE
    - デフラグライクな操作をすることでRDXの値が消去される

Dr.Sum固有の制限
1. no unique key joinが極端に遅い
2. Betweenなどを使った結合は不可能。つまりID&期間でマスタを結合する方式は不可能
```

________________________________________
## 3. 業務系DB、情報系DB、BI
________________________________________
考え方

```text
1. 業務用DBとBI用DBは分ける
    - BIでの利用が業務DBへの影響が出ないようにするため
2. 集計は列指向DBでも速いわけではない。そのため集計済みデータ（キューブ）や集計用列の追加をする
    - 集計済みデータをいつ作るか、それをいつDWH内に保存するか
3. リアルタイム問題の本質は以下の通り
    - 業務系DBと情報系DBのレプリケーションまたは転送などをどうするか
        - 同期レプリケーション
        - 同期（別製品）。この場合は業務系DBからDWHへのコピーは双方に負荷がかかる
        - 非同期ダイレクトローディング（Oracle SQL Loaderなど）
    - 業務系DB外のデータは誰がどうやっていつBIで見える状態にするのか（予算などExcel管理されている情報）
    - 集計や集計列の付与はいつするか、どのDBでやるのか
        - R-OLAP（同期レプリケーションスレーブへの集計SQLを毎回BI閲覧時に発行）
        - M-OLAP（非同期DB形式。集計済みデータ等も作成。日次など）
            - 集計に時間がかかるなら、定期バッチの間隔が必要になる
    - Materialized Viewは便利
```

情報系DBのテーブル設計（雪結晶スキーマとその他）

```text
本質
1. 適切に設計されたERでからナチュラルPKを排除して、一部を結合済みにするイメージ
    - 業務系DBの段階でナチュラルPKが排除できているならそれらはそのままでOK
2. JOIN数を減らす
3. 集計用キーを付与する
4. DWHはオプティマイザがないことが多く、基準表（駆動表）の決定が重要
5. 外部表側の結合列はunique keyが好ましい

例
1. 注文テーブルと注文明細テーブルはJOIN済みの1つのテーブルにする
2. 商品マスタと商品カテゴリマスタはJOIN済みの1つのテーブルにする
3. 逆に、日付情報はカレンダーマスタに切り出す（分析軸になると予想されるため）
4. カレンダーマスタは集計用列（その年の何日目、何週目、四半期はどれかといった情報）を持たせる

Navigation Bridge Table
- あるキーが時系列で分割・統合された際の集計を補佐するためのリンクテーブルの事
- 例、2020年の4月に部署が分割されたが、その年の分割前の1/2をそれぞれの実績にカウントしたい
```

BIツールのOLAPなUI設計

```text
ドリルダウン : クロス表の集計単位を大まかなものから詳細なものまで切り替えられるようにすること
ドリルスルー : クロス表から元の明細表へのリンクがあり、行き来できること
スライシング : Where条件に商品Type=XXなどして、特定の条件に対する表示もできるようにすること
ダイシング   : 集計軸の異なる集計や、同じ集計でもグラフ自体を切り切り替えられるようにすること
```

________________________________________
## 3. BIシステム構築の進め方について
________________________________________
基本的な流れ

- いわゆるアジャイル形式の方がよい

BI 要件要素

- 分析対象時点（発生時点・引き継がない制（Transaction）or 最新・引継ぎ制（Current））
- 管理ポイント（集計軸。年、月、日、曜日、商品分類、営業所、地域、顧客名、性別、年代など）
- 管理項目（評価対象値。売上、予算、利益、利益率など）
- 補足鮮度（リアルタイム性。前日末、月末など）
- 保存鮮度（何年保持する必要がある：現在はレンジパーティション出来れば永久でも良い）

BI 設計要素

- 業務系DB
- 情報系DB
- 業務系DB外のデータソース一覧
- R-OLAP vs M-OLAP
- BIのUI