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
- Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.

- 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
- Click the Test button to test if the connection is fine. If fine, click the OK button to connection to the database.
- From the top left panel, double click on Production.Product table to add the table name on right panel.
- Click the Preview the result data button
to view the data in the bottom panel.

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

- 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.
- Right click on Sheet1 tab and choose Add to add a new sheet.

- 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.
- 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.

- 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
- 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.

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

- 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
- Go to the first worksheet with the imported data from Production.Product in the above section.
- 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.

- 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.

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

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

- Select column I and J (StandardCost and ListPrice), then select menu Statistics: Descriptive Statistics: Statistics on Columns to open the Statistics on Columns dialog.
- 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.

- Click OK button to get the results.

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

- Double click on the Clothing axis to open the Axis Dialog. Please note that the below procedure is based on Origin 2017.
- 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.

- 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.
- Set Display to Decimal: 1000, and Divide by Factor to 1000000 (one million). Click Apply button.

- 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.
- 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.
|