9.3.3 SQL Editor for Database Analysis

SQL-Editor-for-Database-Analysis

Summary

This tutorial is for illustrative purposes only. The connection string shown in the tutorial does not connect to an installed database. If you wish to set up an AdventureWorks database on your own server so that you can work through the process discussed in the tutorial, see this GitHub page.

Origin supports importing data from many popular databases using ADO or ODBC. Once the data is in, Origin's data filtering and analysis features allow you to set up an Analysis template. This tutorial illustrate how to setup such an analysis template and use it to import data from database with updated analysis result.

Minimum Origin Version Required: Origin 9.0 SR0

What you will learn

This tutorial will show you how to:

• Use SQL Editor to import data from database for a specific year.
• Add column filter to only show products of interest.
• Create a pivot table for total cost of different products in different countries.
• Plot column graph to visualize the result.
• Insert the graph into the worksheet as a floating graph and save such self-contained workbook as analysis template.
• Load the analysis template and change the SQL query, reimport database to do analysis for another year.

Steps

Suppose we have already set up a SQL server named AdventureWorks2008 on a server machine noho.

Import Data from Database

1. Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.

   

2. Select File: Edit Connection String... menu and put the connection string below to the text box.

   Provider=SQLOLEDB.1;
   Password=labtalk2015;
   Persist Security Info=TRUE;
   USER ID=CONNECT;
   Initial Catalog=AdventureWorks2008;
   DATA SOURCE=noho

3. Click the Test button to test if the connection is fine. If fine, click the OK button to connection to the database.
4. Copy and paste the following query into the SQL Editor text box. The query shows data for the year 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. Click the preview button to view the data the query produced in the bottom panel.

   

6. In the SQL Editor dialog, click File: Save to Active Worksheet menu to save database connection and query to the active worksheet. Close the SQL Editor.
7. Click Import data button to import data into worksheet. The icon on the top left of the workbook indicates the sheet contains an SQL query.

   

Filter Data

1. Origin has a data filter feature similar to Excel. We can use this feature to choose specific data for graphing and analysis without removing the rest of the data.
2. Select column E (Long Name: Product Subcategory). In order to choose just the bike data for analysis, add a data filter to this column by clicking the Add/Remove data filter button on the Worksheet Data toolbar.

   

3. A filter icon will appear on the top left corner of the column header. Click on it, and from the list that appears, uncheck Select All and then select Mountain Bikes, Road Bikes and Touring Bikes.

   

4. If a reminder message about hidden data appears, select the Yes radio button and click OK.
5. The sheet will now show data for just those 3 bikes types.

Create Pivot Table and Plot Column Graph

1. We can create a pivot table to see total costs of each bike type in different countries.
2. With nothing selected in worksheet, select Worksheet: Pivot Table: Open Dialog...
3. In the dialog that opens set the Row Source as CustomerCountry.
4. Set the Column Source as ProductSubcategory (the column with the filter on it).
5. In order to see the Total Cost, set the Summarize by option as Sum , and set the Pivot Table Data Source option that appears as TotalCost.

   

6. Click OK. A new worksheet is created named Pivot1.
7. Click on the green lock located on the top right of the first column and set the Recalculate Mode: Auto in the context menu. This will make the pivot table manipulation update if the data is re-imported from the SQL Editor.

   

8. Now highlight the pivoted data and click on the Column Plot button to create a column plot.

   

Customize Graph and Create Analysis Template

1. Double click the Y axis title and set it as Total Sales ($)
2. Double click on the Y Axis to open the Axis dialog.
3. On the Y axis Scale page, set the Rescale dropdown to Auto. Click OK.

   

4. Right click on the graph and select Add/Modify Layer Title from the context menu. Set the Title as Bike Sales by Year. If needed, a year title can be added this way as well.

Add Floating Graph in Worksheet and Save Analysis Template

1. Add the graph to the worksheet with the original data by right clicking gray area in worksheet and from the context menu selecting Add Graph....
2. In the Graph Browser dialog, select the graph and click OK. Resize and move as needed. If you need to customize the graph further, double click it to bring up the independent graph window again to customize. And then click the return button on graph window title to bring it back to worksheet.
3. workbook is self-contained with everything: database connection, data filtering, analysis (pivot table) and graph.
4. Choose Worksheet: Clear Worksheet... menu and choose Yes when prompt shows to clear data from workbook. Note: Usually saving analysis template will clear data automatically but for database connection, it doesn't happen automatically. So we need to do this step
5. Choose File: Save Workbook as Analysis Template... or right click on the .worksheet title bar and select Save as Analysis Template... as shown below to save a template.

Change Query and Re-import Data to Automatically Update Analysis

1. Go to File: Recent Books menu to load the analysis template you just saved. This will open a blank analysis template workbook.

   

2. Click on the SQL Editor button to open the SQL Editor with SQL query loaded. Change it to show data for the year 2004 and then exit out of the Editor.Choose Yes to save the change SQL change in worksheet.

   

3. Re-import the data by clicking the Import Data button on Database Access toolbar.
4. and reapply the filter with the Reapply data filter : button.
5. All the data will update. The graph now shows the Bike Sales for the year 2004, and the year title has changed to reflect this.
6. You project now has two books; Book1 has the data for the year 2003, Book2 has the data for the year 2004.

   

7. You can use this template and the SQL re-import feature to find the Bike sales for as many years as needed just by adjusting the year range in SQL Editor and reimport.