SQLエディタでのデータベース分析

サマリー

Originは、ADO または ODBCを使って、沢山のポピュラーなデータベースからデータをインポートすることが出来ます。データが入力されたら、Originのデータフィルタと分析機能により分析テンプレートを用意します。このチュートリアルでは、このような分析テンプレートの作成方法と、保存した分析テンプレートをデータベースからインポートしたデータに適用して、分析結果を更新する方法を説明します。

ここで使用するSQLデータベースは AdventureWorks データベースです。AdventureWorks データベースの取り付け方法についての詳細情報は、CodePlex のWebサイトを参照してください。

必要なOriginのバージョン:Origin 9.0 SR0

学習する項目

このチュートリアルでは、以下の項目について解説します。

  • SQLエディタを使ってデータベースから特定年のデータをインポートする
  • 表示したい製品をしぼるために列フィルタを追加する
  • 異なる国の異なる製品の合計コストのピボットテーブルを作成する
  • 結果を確認するために棒グラフを作成する
  • ワークシートにフローティンググラフを挿入して分析テンプレートとして保存する
  • 保存した分析テンプレートをロードし、SQLクエリを変更してデータベースを再インポートし、他の年の分析を実行する

ステップ

サーバマシンnoho上にSQLサーバAdventureWorks2008を予めセットアップ済であることを想定しています。

データベースからデータをインポートする

  1. 新しいプロジェクトを開始します。データベースアクセスツールバーのSQLエディタを開くボタンをクリックしてSQLエディタを起動します。
    ImportDataDatabase 1.png
  2. メニューからファイル:接続文字列の編集を選択してテキストボックスに以下の接続文字列を入力します。
    Provider=SQLOLEDB.1;
    Password=labtalk2015;
    Persist Security Info=TRUE;
    USER ID=CONNECT;
    Initial Catalog=AdventureWorks2008;
    DATA SOURCE=noho
            
  3. テストボタンをクリックして接続を確認します。大丈夫ならOKをクリックしてデータベースにアクセスします。
  4. 次に次のクエリをSQLエディタのテキストボックスにコピーして貼り付けます。次のクエリで2003年のデータを表示します。
    SELECT CR.Name AS CustomerCountry,
            Pr.Name AS ProductName,
            Pr.Color AS ProductColor,
            PC.Name AS ProductCategory,
            PS.Name AS ProductSubcategory,
            SOH.OrderDate AS OrderDate,
            SOD.OrderQty AS OrderAmount,
            SOD.LineTotal AS TotalCost
    FROM Person.CountryRegion AS CR
    INNER JOIN Person.StateProvince AS SP
    ON SP.CountryRegionCode = CR.CountryRegionCode
    INNER JOIN Person.Address AS A
    ON A.StateProvinceID = SP.StateProvinceID
    INNER JOIN Person.BusinessEntityAddress AS BEA
    ON BEA.AddressID = A.AddressID
    INNER JOIN Person.Person AS P
    ON P.BusinessEntityID = BEA.BusinessEntityID
    INNER JOIN Sales.PersonCreditCard AS PCC
    ON PCC.BusinessEntityID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderheader AS SOH
    ON SOH.CreditCardID = PCC.CreditCardID
    INNER JOIN Sales.SalesOrderDetail AS SOD
    ON SOD.SalesOrderID = SOH.SalesOrderID
    INNER JOIN Production.Product AS Pr
    ON Pr.ProductID = SOD.ProductID
    INNER JOIN Production.ProductSubcategory AS PS
    ON PS.ProductSubcategoryID = Pr.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS PC
    ON PC.ProductCategoryID = PS.ProductCategoryID
    WHERE SOH.OrderDate BETWEEN '1/1/2003' AND '12/31/2003'
            
  5. プレビューボタンをクリックして、下側のパネルでクエリが表示するデータを確認します。
    Query I(c).png
  6. SQLエディタダイアログで、メニューからファイル:アクティブワークシートに保存を選択して、データベース接続とクエリーをアクティブワークシートに保存します。SQLエディタを閉じます。
  7. データインポートボタンをクリックし、データをワークシートにインポートします。ワークブックの左上のアイコンは、そのシートにSQLクエリーが含まれることを示しています。
    Import into sheet(b).png

データフィルタ

  1. OriginにはExcelと同じようなデータフィルタ機能があります。この機能により、必要としないデータを削除することなく、データを抽出して、グラフ作成や分析を行うことが出来ます。
  2. E列を選択します。(ロングネーム:Product Subcategory)bikeデータのみを分析のために選択するには、ワークシートデータ操作ツールバーでデータフィルタの追加/削除ボタンをクリックして、この列にデータフィルタを追加します。
    ImportDataDatabase 9.png
  3. 列ヘッダの左上にフィルタアイコンが表示されます。この上でクリックすると表示されるリストで、全て選択のチェックを外し、Mountain BikesRoad BikesTouring Bikesを選択します。
    Add Filter(c).png
  4. 隠れたデータに関するメッセージが現れた場合には、はいを選択して、OKをクリックします。
  5. 3種類の自転車データだけワークシートに表示されます。

ピボットテーブルと棒グラフを作成

  1. ピボットテーブルを作成して、異なる国の自転車の種類についてそれぞれの合計コストを確認できます。
  2. ワークシートで何も選択せずに、メニューからワークシート:ピボットテーブル:ダイアログを開くと選択します。
  3. 開いたダイアログで、行ソースとして、CustomerCountryを選択します。
  4. 列ソースとして、ProductSubcategory(フィルタ付きの列)を設定します。
  5. 毎年の合計コストを確認するために、サマリー出力タイプ合計にして、ピボットテーブルのデータソースTotal Cost に設定します。
    Pivot Table b vNext.png
  6. OKをクリックします。Pivot1という新しいワークシートが作成されます。
  7. 最初の列の右上にある緑色のカギマークをクリックし、コンテキストメニューの再計算モード:自動を設定します。これにより、もしデータが再度SQLクエリからインポートされた場合でも、ピボットテーブルの操作が更新されます。
    Recaluculate pivot auto(b).png
  8. 処理されたデータを選択して、縦棒ボタンをクリックして縦棒グラフを作成します。
    Column Plot(b)t.png

グラフを編集して分析テンプレートを作成

  1. Y軸のタイトルをダブルクリックし、総売り上げ($)と入力します。
  2. Y軸をダブルクリックして、ダイアログボックスを開きます。
  3. Y軸の設定ページで、再計算のドロップダウンリストから自動を選択します。OKをクリックします。
    Set Axis Recalc(b).png
  4. グラフウィンドウで右クリックし、コンテキストメニューからレイヤタイトルを追加/変更を選択します。タイトルを Bike Sales by Year に設定します。必要あれば、年のタイトルもこのように追加します。

ワークシートにフローティンググラフを追加して分析テンプレートとして保存

  1. ワークシートの灰色の部分を右クリックして、グラフを追加を選択し、元データのワークシートにグラフを追加します。
  2. グラフブラウザのダイアログでグラフを選択してOKをクリックします。必要に応じて大きさや位置を変更してください。グラフに編集を加えたい場合は、ダブルクリックしてグラフウィンドウで開き、編集します。編集後は、グラフウィンドウのタイトルバーにある、戻るボタンをクリックしてワークシートに戻ります。
  3. 1つのワークブックに、データベース接続、データフィルタ、分析(ピボットテーブル)、グラフのすべての要素を含めました。
  4. メニューから、ワークシート:ワークシートをクリアを選択し、ワークブックからデータをクリアするか尋ねられたら、はいを選択します。Note: 通常、分析テンプレートを保存するとデータが自動でクリアされますが、データベース接続の場合自動では行われません。そのため、ここではこの操作が必要です。
  5. メニューからファイル:ワークブックを分析テンプレートとして保存を選択するか、ワークブックタイトルバーを右クリックして分析テンプレートとして保存を選択し、テンプレートを保存します。
    Save as Analysistemp(d).png

クエリを変更してデータを再インポートし、分析を自動的に更新

  1. メニューからファイル:最近使ったブックを選択して、先ほど保存した分析テンプレートをロードします。数値の入っていない、空のテンプレートワークブックが開きます。
    Blank temp(c).png
  2. SQLエディタを開くをクリックして、SQLクエリーがロードされたSQLエディタを開きます。2004年のデータを表示するように変更して、エディタを終了します。はいを選択して、ワークシートに変更を保存します。
    Change query(c).png
  3. データベースアクセスツールバーのデータのインポートボタンButton db Import Data.pngをクリックしてデータを再インポートします。
  4. フィルタの再適用ボタンReapply filter.pngでフィルタを再適用します。
  5. 全てのデータが更新されます。グラフは 2004年のBike Sales を表示し、年のタイトルもグラフに反映されます。
  6. 作成したプロジェクトには、次の2つのワークブックがあります。2003年のデータがあるBook1 と、2004年のデータがあるBook2
    End result(b).png
  7. このテンプレートとSQL再インポート機能により、SQLエディタで範囲を調整して再インポートするだけで、必要な年のBikeの売り上げを確認できます。