9.3.2 Import Data from a Database for 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.

This tutorial shows how to import data from database into Origin worksheet by using SQL Editor. And then perform some operations on the data, such as filter, statistics, etc., so to get the desired results for the graphing.

Minimum Origin Version Required: Origin 9.0 SR0

What you will learn

This tutorial will show you how to:

  • Import two SQL queries of same database into two sheets of a workbook data using SQL Editor.
  • Apply data filter to the worksheet data.
  • Perform descriptive statistics on columns.
  • Make graphs such as column plot, etc.

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.
    ImportDataDatabase 1.png
  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. From the top left panel, double click on Production.Product table to add the table name on right panel.
  5. Click the Preview the result data button Button db Preview Import.png to view the data in the bottom panel.
    ImportDataDatabase 3.png
  6. Click the Import data to worksheet button Button db Import Data.png to import the data in the table to the active worksheet.
    ImportDataDatabase 4.png
  7. To import data based on another SQL query from the same database to new sheet, click the minimum button on the title bar of SQL Editor dialog to make it shrink into a bar.
  8. Right click on Sheet1 tab and choose Add to add a new sheet.
    ImportDataDatabase 5.png
  9. Click the minimum button of SQL Editor dialog again to expand the dialog. You can see Production: Product still shows in right panel. You can ignore it for now.
  10. Select Query: LabTalk... menu to open the LabTalk Support Settings dialog. Check the Enable LabTalk (%, $) Substitution checkbox, and put the following script to the text box.
    string strDateBegin1$ = "1/1/2003";
    string strDateEnd1$ = "12/31/2003";
    string strDateBegin2$ = "1/1/2004";
    string strDateEnd2$ = "12/31/2004";

    The dialog will look like this.

    ImportDataDatabase 6.png
  11. Click OK to go back SQL Editor. Then put the following SQL script to the top right panel to replace existing content Production.Product. This script is going to query three columns from the database, one is the product's category name, other twos are line total for each category on year 2003 and 2004 respectively.
    SELECT TABLE2003.Name, TABLE2003.LineTotalFor2003, TABLE2004.LineTotalFor2004
    FROM
    	(SELECT Production.ProductCategory.Name, LINETOALANDNAME2003.LineTotalFor2003
    	FROM 
    		(SELECT SUM(SALEANDPRODUCT2003.LineTotal) AS LineTotalFor2003, Production.ProductSubcategory.ProductCategoryID
    		FROM
    			(SELECT SALEINFO2003.LineTotal, PRODUCTINFO2003.ProductSubcategoryID
    			FROM
    				(SELECT Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderDetail.LineTotal, Sales.SalesOrderDetail.ProductID
    				FROM Sales.SalesOrderHeader 
    				INNER JOIN Sales.SalesOrderDetail 
    				ON Sales.SalesOrderHeader.SalesOrderID=Sales.SalesOrderDetail.SalesOrderID
    				WHERE Sales.SalesOrderHeader.OrderDate BETWEEN '%(strDateBegin1$)' AND '%(strDateEnd1$)') AS SALEINFO2003
    			INNER JOIN 
    				(SELECT Production.Product.ProductID, Production.Product.ProductSubcategoryID
    				FROM Production.Product) AS PRODUCTINFO2003
    			ON SALEINFO2003.ProductID=PRODUCTINFO2003.ProductID) AS SALEANDPRODUCT2003
    		INNER JOIN Production.ProductSubcategory 
    		ON SALEANDPRODUCT2003.ProductSubcategoryID=Production.ProductSubcategory.ProductSubcategoryID
    		GROUP BY Production.ProductSubcategory.ProductCategoryID) AS LINETOALANDNAME2003
    	INNER JOIN Production.ProductCategory
    	ON LINETOALANDNAME2003.ProductCategoryID=Production.ProductCategory.ProductCategoryID) AS TABLE2003
    INNER JOIN
    	(SELECT Production.ProductCategory.Name, LINETOALANDNAME2004.LineTotalFor2004
    	FROM 
    		(SELECT SUM(SALEANDPRODUCT2004.LineTotal) AS LineTotalFor2004, Production.ProductSubcategory.ProductCategoryID
    		FROM
    			(SELECT SALEINFO2004.LineTotal, PRODUCTINFO2004.ProductSubcategoryID
    			FROM
    				(SELECT Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderDetail.LineTotal, Sales.SalesOrderDetail.ProductID
    				FROM Sales.SalesOrderHeader 
    				INNER JOIN Sales.SalesOrderDetail 
    				ON Sales.SalesOrderHeader.SalesOrderID=Sales.SalesOrderDetail.SalesOrderID
    				WHERE Sales.SalesOrderHeader.OrderDate BETWEEN '%(strDateBegin2$)' AND '%(strDateEnd2$)') AS SALEINFO2004
    			INNER JOIN 
    				(SELECT Production.Product.ProductID, Production.Product.ProductSubcategoryID
    				FROM Production.Product) AS PRODUCTINFO2004
    			ON SALEINFO2004.ProductID=PRODUCTINFO2004.ProductID) AS SALEANDPRODUCT2004
    		INNER JOIN Production.ProductSubcategory 
    		ON SALEANDPRODUCT2004.ProductSubcategoryID=Production.ProductSubcategory.ProductSubcategoryID
    		GROUP BY Production.ProductSubcategory.ProductCategoryID) AS LINETOALANDNAME2004
    	INNER JOIN Production.ProductCategory
    	ON LINETOALANDNAME2004.ProductCategoryID=Production.ProductCategory.ProductCategoryID) AS TABLE2004
    ON TABLE2003.Name=TABLE2004.Name
  12. Click the last button Button db Preview LabTalk Substitute.png to view the SQL query script with subsitudted variable. Click Preview the result data button Button db Preview Import.png to view the data in the bottom panel.
    ImportDataDatabase 7.png
  13. Click the Import data to worksheet button Button db Import Data.png to import the query into the the active Sheet2.
    ImportDataDatabase 8.png
  14. Close SQL Editor. You will be prompted to save the change. You can choose No since the query settings are saved in corresponding sheet automatically when clicking Import button. You can also choose Yes to save the query of active sheet as an ODQ file for future use.

Data Filter and Statistics

  1. Go to the first worksheet with the imported data from Production.Product in the above section.
  2. Select column I (long name is StandardCost), and then add a data filter to this column by clicking the Add/Remove data filter button on the Worksheet Data toolbar.
    ImportDataDatabase 9.png
  3. Then there will be a filter icon on the top left corner of the column header. Click on it, and then select Greater Than... from the context menu.
    ImportDataDatabase 10.png
  4. There will be a dialog open, just keep the default Value to be 0 and click OK button.
    ImportDataDatabase 11.png
  5. Add a filter in column P (long name is ProductLine) as well with condition as follows.
    ImportDataDatabase 12.png
  6. Select column I and J (StandardCost and ListPrice), then select menu Statistics: Descriptive Statistics: Statistics on Columns to open the Statistics on Columns dialog.
  7. In the open dialog, set Grouping Range to be column P. You can just click the triangle button to select column P from list on the right. And in the Plots tab, check Box Charts checkbox.
    ImportDataDatabase 13.png
  8. Click OK button to get the results.
    ImportDataDatabase 14.png

Radar Plot

  1. Go to the Sheet2.
  2. Highlight all three columns, and select Plot: Specialized: Radar menu to make a radar plot.
    ImportDataDatabase 15.png
  3. Double click on the Clothing axis to open the Axis Dialog. Please note that the below procedure is based on Origin 2017.
  4. With Scale selected on the left panel. Make sure Use Same Options for All Axes checkbox is unchecked. Select Axis1 to Axis4 on the left panel. Then set From to 0, and choose By # of Majors for Major Ticks with value of 6. Click Apply button.
    ImportDataDatabase 18.png
  5. Still with Axis1 to Axis4 selected on the left, go to Tick Labels tab. Before Axis is chosen in Show Tick Labels. Please expand the dropdown list and choose Before Axis again since we want to apply it to all Axes.
  6. Set Display to Decimal: 1000, and Divide by Factor to 1000000 (one million). Click Apply button.
    ImportDataDatabase 19.png
  7. Now go back to Scale tab. Select Axis1 - Clothing only on the left and change To value to 1.5. Repeat this operation for Axis3 - Accessories and Axis4 - Components, with values of 0.7 and 6 respectively. Note: because Divide by Factor was set to 1000000 in the previous step, it's much easier to type the To value now.
  8. Click OK button.
    ImportDataDatabase 20.png
    We can see that, for all four categories, line totals for 2004 is bigger than that for 2003. And Bikes is the biggest.