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.

   

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 to view the data in the bottom panel.

   

6. Click the Import data to worksheet button to import the data in the table to the active worksheet.

   

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.

   

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.

    

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 to view the SQL query script with subsitudted variable. Click Preview the result data button to view the data in the bottom panel.

    

13. Click the Import data to worksheet button to import the query into the the active Sheet2.

    

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.

   

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.

   

4. There will be a dialog open, just keep the default Value to be 0 and click OK button.

   

5. Add a filter in column P (long name is ProductLine) as well with condition as follows.

   

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.

   

8. Click OK button to get the results.

   

Radar Plot

1. Go to the Sheet2.
2. Highlight all three columns, and select Plot: Specialized: Radar menu to make a radar plot.

   

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.

   

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.

   

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.

   

   We can see that, for all four categories, line totals for 2004 is bigger than that for 2003. And Bikes is the biggest.