Office の検出/リスク評価ツールであらゆるデータ接続を把握


(この記事は 2/11 に投稿された記事、Find all your data connections with Office Discovery and Risk Assessment の翻訳です。)

今回は、Excel スプレッドシート管理機能担当プログラム マネージャーを務める Steve Kraynak の記事をご紹介します。また、本記事の執筆に協力してくれた SQL MVP の Scott Stauffer に感謝の意を表します。

データを使用しているユーザーを把握するには

皆様は自分のデータがどこで使用されているかを知る必要があると感じたことはありませんか? また、データベースへのデータ接続を行う Excel ワークブックを、どのように検出すればよいかご存知でしょうか?

この答えを求めて私たちは、SQL MVP である Scott Stauffer (英語) を訪ねました。Scott は現在、お客様のデータ ウェアハウス開設のサポートを担当しています。データ ウェアハウスはさまざまな分野の業務のデータ ソースとして適しているものです。このお客様はこの他にも、新しいエンタープライズ リソース プランニング (ERP) システムを導入し、最終的に古いシステムと置き換えることも同時に計画しています。新しいデータ ウェアハウスの良いところは、従来のシステムから新しい ERP システムへの移行をスムーズに実施できる点ですが、大量の Excel ファイルが個々に古いシステムと接続してしまっています。このため Scott とチーム メンバーは、該当するスプレッドシートとその所有者を探し出し、新しいデータ ウェアハウスと情報をやり取りするように変更を加える必要がありました。

検出/リスク評価ツールとは

 Office 2013 の検出/リスク評価 (DRA) ツールを使用すると、この問題を解決することができます。DRA では Excel ファイルおよび Access ファイルのインベントリを作成して解析を実行し、各ファイルの複雑さ、影響の大きさ、リスクの大きさを判断できるようになります。DRA は指定されたネットワーク パスおよび SharePoint サイトをクロールして Excel ファイルと Access ファイルを検出し、それぞれを解析して、各ファイルの複雑さ、重要度 (組織への影響の大きさ)、リスクを、ユーザーが構成可能な抽出条件に基づいて評価します。

データベースやデータ ウェアハウスの所有者にとって魅力的なのは、このツールを使用すればすべての Excel ファイルのデータ接続が検出可能であるということです。この記事では、DRA を使用して、データ接続を行う全ファイルのリストを取得し、各ファイルのプロパティから最終保存日時を確認する方法を説明します。このリストを取得すると、データ接続を検出して、どのワークブックが重要なデータ ソースに接続しているのを容易に把握することができます。

検出/リスク評価ツールで企業が得られるメリットとは

検出/リスク評価ツールを使用すると、古いデータ ソースに接続しているスプレッドシートの所有者を明らかにすることができます。Scott はお客様に対して、このツールを使用すると以下のような直接的なメリットがあるということを説明しました。

  1. 重要なスプレッドシートを新しいデータ ウェアハウスに更新する際に必要な作業量を判断できる
  2. スプレッドシートの所有者に対し、データ接続を古いシステムから新しいデータ ウェアハウスに切り替えるよう事前に働きかけることができる
  3. 新しい ERP システムに切り替える際のサービス中断を回避できる
  4. 新しいデータ ウェアハウスでの投資回収率を最大化できる
  5. お客様の主要目標の 1 つである、移行実施前の旧システムへの ODBC 接続の排除を達成できる

これらのメリットに加え、Scott のチームは企業全体におけるスプレッドシート利用の質を向上させることができました。これが、DRA やその他いくつかのツールを Office でリリースした主な理由です。他にも次のような効果が得られます。

  1. 各スプレッドシート間のリンクを把握できる
  2. リンクされているスプレッドシートの間でデータが最新であるかどうかを判断できる
  3. Excel に関するトレーニングの追加の実施が必要なユーザーを把握できる
  4. 企業内の全ワークブックのインベントリを取得して、作業の重複を最小化できる
  5. ポリシーやベスト プラクティスを新規に導入して Excel 利用の全体的な質を向上できる

検出/リスク評価 (DRA) ツール、および Office と SharePoint で使用可能なその他のスプレッドシート管理機能の詳細については、マイクロソフトの発表 (英語)、および TechNet に掲載されているこれらのツールの概要とスプレッドシートの管理に関するホワイト ペーパー (英語) をご覧ください。

DRA を企業で活用するには

検出/リスク管理ツールを企業で活用できるようにするには、まずボリューム ライセンス カスタマー向けのボリューム ライセンス サービス センターで DRA をセットアップし、次に TechNet に記載されている説明 (英語) に従ってインストールと構成を行います。DRA の構成時には検出を実行します。これにより、DRA が検出したすべての Excel ファイルのリストと共に、各スプレッドシートの情報量と各ファイルのデータ接続のリストを含むデータベースが作成されます。

次に、データベース スキーマを理解する手間を省くために、下記の手順に従ってワークブックのリストと Excel へのデータ接続を取得します。この例では Microsoft Power Query for Excel (英語) を使用していますが、従来の方法で外部データを Excel に取得することもできます (注: パフォーマンスに影響を与える可能性があるため、稼働中の本番環境のデータベースで下記の手順を実施することは推奨しません。オフライン環境にデータベースをコピーしてから実施してください)。

  1.  [Power Query] タブに移動し、次の手順を実行します。
    1. [From Database]、[From SQL Server Database] の順に選択します。
    2. 使用中の SQL Server の名前を入力します。このとき、必要に応じてインスタンス名も入力します。
    3. DRA の接続先のデータベース名を入力します。データベース名は DRA の構成設定で確認できます。
    4. [SQL Statement] ボックスを展開し、以下に示すクエリをコピーして、その中に貼り付けます。

    スクリーンショット内のクエリは以下のものです。

    SELECT
    DA.AccountName,
    FP.Value AS FilePath,
    FN.Value AS FileName,
    MD.Value AS Modified,
    S4.Value AS LastModifiedBy,
    N1.Value AS ConnectionCount,
    T1.Value AS DataConnections
    FROM
    DiscoveryAccounts AS DA
    INNER JOIN UDKDefinition AS UDK1 ON DA.AccountID = UDK1.AccountID
    INNER JOIN UDKDefinition AS UDK2 ON DA.AccountID = UDK2.AccountID
    INNER JOIN UDKDefinition AS UDK3 ON DA.AccountID = UDK3.AccountID
    INNER JOIN UDKDefinition AS UDK4 ON DA.AccountID = UDK4.AccountID
    INNER JOIN UDKDefinition AS UDK5 ON DA.AccountID = UDK5.AccountID
    INNER JOIN UDKDefinition AS UDK6 ON DA.AccountID = UDK6.AccountID
    INNER JOIN UDKDefinition AS UDK7 ON DA.AccountID = UDK7.AccountID
    INNER JOIN Strings AS FP ON UDK1.UDKID = FP.UDKID
    INNER JOIN Strings AS FN ON UDK2.UDKID = FN.UDKID AND FN.InventoryVersionID = FP.InventoryVersionID
    INNER JOIN Dates AS MD ON UDK3.UDKID = MD.UDKID AND FN.InventoryVersionID = MD.InventoryVersionID
    INNER JOIN Strings AS S4 ON UDK4.UDKID = S4.UDKID AND FN.InventoryVersionID = S4.InventoryVersionID
    INNER JOIN Numbers AS N1 ON UDK6.UDKID = N1.UDKID AND FN.InventoryVersionID = N1.InventoryVersionID
    INNER JOIN Text AS T1 ON UDK7.UDKID = T1.UDKID AND FN.InventoryVersionID = T1.InventoryVersionID
    WHERE
    UDK1.NAME LIKE ‘FilePath’
    AND UDK2.NAME LIKE ‘FileName’
    AND UDK3.NAME LIKE ‘Modified’
    AND UDK4.NAME LIKE ‘LastModifiedBy’
    AND UDK6.NAME LIKE ‘DataConnectionsCount’
    AND UDK7.NAME LIKE ‘DataConnections’
    AND N1.VALUE > 0
    ORDER BY
    FP.Value, FN.Value

  2. データベースのサイズによっては、クエリ エディターが表示されるまでに数分間かかります。表示後、[Apply and Close] をクリックすればワークシートにデータが送信されます。
  3. これで必要なデータを Excel 内に取得できました。このファイルでは、各ファイルのパスと名前、更新日、最終更新日時をプロパティから確認できます。また、各ファイルに対するすべてのデータ接続を含むテキスト文字列も確認できます。
  4. 必要に応じて、フィルタリングを行うこともできます。たとえば、古い ERP システムに接続しているワークブックをすべて検出する場合には、[Data Connections] 列で "MyERPSystem" (この部分はお客様が実際に使用している ERP システムの名前に置き換えてください) を含む行のみをフィルタリングします。

  5. これで、お客様の ERP システムにデータ接続する全ファイルのリストを取得できました。リストを見ると、最後に更新したユーザーを確認できるため、新しいデータ ウェアハウスに切り替える際にだれに連絡を取ればよいかがわかります。

フィードバックについて

マイクロソフトでは、お客様が企業で DRA をどのように活用されているかについて、コメントをお待ちしております。どうぞお気軽にお寄せください。

–Steve Kraynak (Excel スプレッドシート管理機能担当プログラム マネージャー)

Comments (0)

Skip to main content