[新機能]AIファーストな新しいSnowflakeのダッシュボード「Dashboards 2.0」を試してみた

USE ROLE DASH_AUTHOR;
USE WAREHOUSE DASH_TEST_WH;
USE SCHEMA DASH_TEST_DB.SV;

CREATE OR REPLACE SEMANTIC VIEW TPCH_SALES_SV
  TABLES (
    customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
      PRIMARY KEY (c_custkey)
      COMMENT = '顧客',
    orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
      PRIMARY KEY (o_orderkey)
      COMMENT = '注文',
    line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
      PRIMARY KEY (l_orderkey, l_linenumber)
      COMMENT = '注文明細'
  )
  RELATIONSHIPS (
    orders_to_customers AS orders (o_custkey) REFERENCES customers,
    line_items_to_orders AS line_items (l_orderkey) REFERENCES orders
  )
  FACTS (
    line_items.net_price AS l_extendedprice * (1 - l_discount)
  )
  DIMENSIONS (
    customers.customer_name AS c_name,
    customers.market_segment AS c_mktsegment,
    orders.order_date AS o_orderdate,
    orders.order_year AS YEAR(o_orderdate),
    orders.order_priority AS o_orderpriority
  )
  METRICS (
    orders.order_count AS COUNT(o_orderkey),
    line_items.total_net_sales AS SUM(line_items.net_price)
  )
  COMMENT = 'TPC-Hの売上分析用Semantic View';

{
  "version": 2,
  "title": "TPC-H 売上ダッシュボード",
  "filters": {
    "order_year": {
      "label": "注文年",
      "mode": "single_select",
      "valueType": "STRING",
      "options": {
        "source": "query",
        "datasources": [
          "ds-order_year-options"
        ]
      },
      "default_values": []
    },
    "market_segment": {
      "label": "市場セグメント",
      "mode": "multi_select",
      "valueType": "STRING",
      "options": {
        "source": "query",
        "datasources": [
          "ds-market_segment-options"
        ]
      },
      "default_values": []
    }
  },
  "datasources": {
    "ds-order_year-options": {
      "type": "sql",
      "sql": "SELECT\n    order_year\nFROM DASH_TEST_DB.SV.TPCH_SALES_SV\nGROUP BY\n    order_year\nORDER BY\n    order_year"
    },
    "ds-sales-by-year": {
      "type": "sql",
      "columns": [
        {
          "name": "ORDER_YEAR",
          "type": "NUMBER"
        },
        {
          "name": "TOTAL_NET_SALES",
          "type": "NUMBER"
        }
      ],
      "sql": "SELECT\n    order_year,\n    AGG(total_net_sales) AS TOTAL_NET_SALES\nFROM DASH_TEST_DB.SV.TPCH_SALES_SV\nWHERE\n    {{ filter('order_year') }}\n    AND {{ filter('market_segment') }}\nGROUP BY\n    order_year\nORDER BY\n    order_year",
      "filter_bindings": [
        {
          "filter_id": "order_year"
        },
        {
          "filter_id": "market_segment"
        }
      ]
    },
    "ds-sales-by-segment": {
      "type": "sql",
      "columns": [
        {
          "name": "MARKET_SEGMENT",
          "type": "VARCHAR"
        },
        {
          "name": "TOTAL_NET_SALES",
          "type": "NUMBER"
        }
      ],
      "sql": "SELECT\n    market_segment,\n    AGG(total_net_sales) AS TOTAL_NET_SALES\nFROM DASH_TEST_DB.SV.TPCH_SALES_SV\nWHERE\n    {{ filter('order_year') }}\nGROUP BY\n    market_segment\nORDER BY\n    TOTAL_NET_SALES DESC",
      "filter_bindings": [
        {
          "filter_id": "order_year"
        }
      ]
    },
    "ds-orders-by-priority": {
      "type": "sql",
      "columns": [
        {
          "name": "ORDER_PRIORITY",
          "type": "VARCHAR"
        },
        {
          "name": "ORDER_COUNT",
          "type": "NUMBER"
        }
      ],
      "sql": "SELECT\n    order_priority,\n    AGG(order_count) AS ORDER_COUNT\nFROM DASH_TEST_DB.SV.TPCH_SALES_SV\nWHERE\n    {{ filter('order_year') }}\n    AND {{ filter('market_segment') }}\nGROUP BY\n    order_priority\nORDER BY\n    order_priority",
      "filter_bindings": [
        {
          "filter_id": "order_year"
        },
        {
          "filter_id": "market_segment"
        }
      ]
    },
    "ds-market_segment-options": {
      "type": "sql",
      "sql": "SELECT\n    market_segment\nFROM DASH_TEST_DB.SV.TPCH_SALES_SV\nGROUP BY\n    market_segment\nORDER BY\n    market_segment"
    }
  },
  "tabs": [
    {
      "tab_id": "tab-0",
      "title": "概要",
      "tiles": [
        {
          "tile_id": "sales-by-year",
          "type": "chart",
          "title": "年別 売上合計の推移",
          "datasources": [
            "ds-sales-by-year"
          ],
          "spec": {
            "mark": "line",
            "encoding": {
              "x": {
                "field": "ORDER_YEAR",
                "type": "ordinal",
                "title": "注文年"
              },
              "y": {
                "field": "TOTAL_NET_SALES",
                "type": "quantitative",
                "title": "売上合計"
              }
            }
          },
          "scope": "segment_action"
        },
        {
          "tile_id": "sales-by-segment",
          "type": "chart",
          "title": "市場セグメント別 売上合計",
          "datasources": [
            "ds-sales-by-segment"
          ],
          "spec": {
            "mark": "bar",
            "encoding": {
              "x": {
                "field": "MARKET_SEGMENT",
                "type": "nominal",
                "title": "市場セグメント",
                "sort": "-y"
              },
              "y": {
                "field": "TOTAL_NET_SALES",
                "type": "quantitative",
                "title": "売上合計"
              }
            }
          },
          "interactions": [
            {
              "trigger": "select",
              "operator": "set_filter",
              "fields": [
                "MARKET_SEGMENT"
              ],
              "targets": [
                {
                  "scope": "segment_action",
                  "fields": [
                    "MARKET_SEGMENT"
                  ],
                  "filter": "market_segment"
                }
              ]
            }
          ]
        },
        {
          "tile_id": "orders-by-priority",
          "type": "table",
          "title": "注文優先度別 注文件数",
          "datasources": [
            "ds-orders-by-priority"
          ],
          "spec": {
            "columns": [
              {
                "field": "ORDER_PRIORITY"
              },
              {
                "field": "ORDER_COUNT"
              }
            ]
          },
          "scope": "segment_action"
        }
      ]
    }
  ],
  "layout": {
    "tab-0": {
      "meta": {
        "default": true
      },
      "positions": {
        "sales-by-year": {
          "x": 0,
          "y": 0,
          "w": 12,
          "h": 4
        },
        "sales-by-segment": {
          "x": 0,
          "y": 4,
          "w": 6,
          "h": 4
        },
        "orders-by-priority": {
          "x": 6,
          "y": 4,
          "w": 6,
          "h": 4
        }
      }
    }
  },
  "spec_version": 2,
  "spec_revision": 17,
  "scopes": {
    "segment_action": {}
  }
}