9.3.2 Import Data from a Database for Analysis

Summary

The database used in this tutorial has been set up on Microsoft Azure.

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., to get the desired results for the graphing.


The procedure is based on Origin 2023b.

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

Import Data from Database

  1. Start a new project. Choose Data: Connect to Database: New... menu or click clicking the Open SQL Editor button on the Database Access toolbar.
    ImportDataDatabase 1.png
  2. Choose Connection string radio button and click OK. Paste the Connection String below.
    Driver={SQL Server}; Server=olab.DATABASE.windows.net; Port=1433; 
    DATABASE=sample1; Uid=Olabts; Pwd=Origin2023;

    If you are using ODBC Drive 18 for SQL Server, use

    Driver={ODBC Driver 18 FOR SQL Server}; Server=olab.DATABASE.windows.net; Port=1433; 
    DATABASE=sample1; Uid=Olabts; Pwd=Origin2023;
  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 left panel, double click on the SalesLT.Product node. select * from SalesLT.Product will show on right
  5. ImportDB for Analysis SQL.png
  6. Click the Preview the result data button Button db Preview Import.png to view the data in the bottom panel.
  7. Click the OK button to import the data to the active worksheet.
    ImportDB for Analysis book1.png
  8. To import data based on another SQL query from the same database to new sheet, right click the sheet tab and choose Duplicate Sheet without Data
    ImportDB for Analysis Dup no data.png
  9. On Sheet2, click DBConnector.png and choose SQL Editor...
  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.
    int cate=1;

    The dialog will look like this.

    ImportDB for Analysis LT substitution.png
  11. Click OK to go back SQL Editor. Then put the following SQL script to the top right panel. This SQL will query two columns from the database, one is the product's category name, the other is the total of LineTotalfor each category in Parent Category 1
    SELECT SalesLT.ProductCategory.Name, SUM(SALEANDPRODUCT.LineTotal) AS LineTotal
    FROM
        (SELECT SALEINFO.LineTotal, PRODUCTINFO.ProductCategoryID
        FROM
            (SELECT SalesLT.SalesOrderHeader.OrderDate, SalesLT.SalesOrderDetail.LineTotal, SalesLT.SalesOrderDetail.ProductID
            FROM SalesLT.SalesOrderHeader 
            INNER JOIN SalesLT.SalesOrderDetail 
            ON SalesLT.SalesOrderHeader.SalesOrderID=SalesLT.SalesOrderDetail.SalesOrderID) AS SALEINFO
        INNER JOIN 
            (SELECT SalesLT.Product.ProductID, SalesLT.Product.ProductCategoryID
            FROM SalesLT.Product) AS PRODUCTINFO
            ON SALEINFO.ProductID=PRODUCTINFO.ProductID) AS SALEANDPRODUCT
    INNER JOIN SalesLT.ProductCategory
    ON SALEANDPRODUCT.ProductCategoryID=SalesLT.ProductCategory.ProductCategoryID
    WHERE SalesLT.ProductCategory.ParentProductCategoryID = $(cate)
    GROUP BY SalesLT.ProductCategory.Name
    ORDER BY LineTotal
  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.
    ImportDB for Analysis LT preview.png
  13. Before closing the dialog, choose File: Save Connection and Query as .... Save it as LineTotal_by_parentCategory.ODQ.
  14. Click the OK button. Query result shows on Sheet2
    ImportDB for Analysis total by cate.png

Data Filter and Statistics

  1. Instead of using SUM(), INNER JION, GROUP BY to get Statistics. We can first do simple query and import the columns we are interested. Then using Origin's filter and statistics, etc. to get desired result and graphing.
  2. Let's start a new book this time. E.g. Book2.
  3. Choose Data: Connect to Database: menu and pick LineTotal_by_parentCategory.ODQ.
  4. Replace the right with the following Query, which just inner join multiple tables and query 3 columns: Category Name, ParentCategoryID and LineTotal.
    SELECT SalesLT.ProductCategory.Name ,SalesLT.ProductCategory.ParentProductCategoryID, SalesLT.SalesOrderDetail.LineTotal
    FROM SalesLT.SalesOrderDetail
    INNER JOIN  SalesLT.Product
    ON SalesLT.SalesOrderDetail.productID = SalesLT.Product.ProductID
    INNER JOIN SalesLT.ProductCategory 
    ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID 
    ORDER BY SalesLT.ProductCategory.ProductCategoryID
  5. Select column B (long name is ParentProductCategoryID), 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
  6. Then there will be a filter icon on the top left corner of the column header. Click on it, and then select Equals... from the context menu.
    ImportDB for Analysis filterContext.png
  7. There will be a dialog open, just keep the default Value to be 0 and click OK button.
    ImportDB for Analysis equals1.png
  8. Select column C (LineTotal), then select menu Statistics: Descriptive Statistics: Statistics on Columns to open the Statistics on Columns dialog.
  9. In the open dialog, set Grouping Range to be column B. You can just click the triangle button to select column P from list on the right.
    ImportDB for Analysis stats db 1.png
  10. Go to the Plots tab, check Box Charts checkbox.
    ImportDB for Analysis stats db 2.png
  11. Click OK button to get the results.
    ImportDB for Analysis stats box.png
  12. It's very easy to change filter condition and get updated statistics. E.g. go back to Sheet1. Double click on =1 and change to =2
  13. Go to DescStatsOnCol1 sheet. The Recalculation lock turned yellow, meaning input changed. Click on it and choose Recalculate. Now the LineTotal of all products in parentCategoryID=2 shows.
    ImportDB for Analysis stats box cate2.png

    You can click the Recalculation lock and set Recalculation Mode to Auto. When source data changes or filter changes, the statistics result will be updated automatically.

Radar Plot

  1. Statistics on Columns tool also generates a flat result sheet which we can use to do further analysis or graphing.
  2. Go to DescStatsQuantities1 sheet.
  3. Highlight column F (Sum) and select Plot: Specialized: Radar menu to make a radar plot.
    ImportDB for Analysis radar.png
  4. Go back to DescStatsQuantities1 sheet and add filter on column B (Name) and exclude Mountain Frames, Road Frames and Touring Frames. Rescale the Axis to see more details of products with smaller sum of LineTotal values.
    ImportDB for Analysis radar2.png