9.3.3 SQL Editor for Database Analysis

Summary

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

This tutorial shows how to import data from database using SQL Editor, perform common operations such as column filtering and pivot table, and then use pivot table result to plot graph. Finally put graph in workbook so everything is inclusive that the workbook can be saved as an analysis template for future use.


The procedure is based on Origin 2023b.

What you will learn

This tutorial will show you how to:

  • Use SQL Editor to import data from database
  • Add column filter to only show products of interest.
  • Create a pivot table for sum of LineTotal of different products in different States.
  • Plot column graph to visualize the result.
  • Insert the graph into the worksheet and save such self-contained workbook as analysis template.
  • Load the analysis template and reimport data.

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 ctring 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 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 SalesLT.SalesOrderDetail.LineTotal,
SalesLT.ProductCategory.Name,
SalesLT.ProductCategory.ParentProductCategoryID, 
SalesLT.Address.StateProvince,SalesLT.Address.CountryRegion  
FROM SalesLT.SalesOrderDetail
INNER JOIN SalesLT.Product
ON SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID
INNER JOIN SalesLT.ProductCategory 
ON SalesLT.ProductCategory.ProductCategoryID =SalesLT.Product.ProductCategoryID 
INNER JOIN SalesLT.SalesOrderHeader 
ON SalesLT.SalesOrderHeader.SalesOrderID =SalesLT.SalesOrderDetail.SalesOrderID 
INNER JOIN SalesLT.CustomerAddress 
ON SalesLT.CustomerAddress.CustomerID = SalesLT.SalesOrderHeader.CustomerID
INNER JOIN SalesLT.Address 
ON  SalesLT.Address.AddressID =SalesLT.CustomerAddress.AddressID 
ORDER BY SalesLT.ProductCategory.ParentProductCategoryID
  5. Click the OK button to import data to workbook. By default the SQL is saved together with worksheet.
  6. The icon on the top left of the workbook indicates the sheet contains an SQL query.
    ImportDB for Analysis template afterimport.png

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 C (ParentProductCategoryID) and column E (CountryRegion) and then click the Add/Remove data filter button on the Worksheet Data toolbar to add a filter on them.
    ImportDataDatabase 9.png
  3. Click the funnel icon on column E. From the list that appears, uncheck United Kingdom and click OK.
    ImportDB for Analysis template filter.png
  4. If a reminder message about hidden data appears, select the Yes radio button and click OK.
  5. Click the funnel icon on column C, it shows Equals, Less than, .... numeric filter menus since the ParentProductCategoryID are treated as numbers.
    ImportDB for Analysis template filterC.png
  6. Choose Equals... and enter 1 as Value. Click OK.
  7. Now the worksheet only shows the sales of various Bikes in USA.
    ImportDB for Analysis template filter done.png

Create Pivot Table and Plot Column Graph

  1. We can create a pivot table to see total sales of each bike type in different states.
  2. With nothing selected in worksheet, select Restructure > Pivot Table
  3. In the dialog that opens set Recalculation Mode to Auto. This will make the pivot table manipulation update if the data is re-imported from the SQL Editor.
  4. Set the Row Source as Name.
  5. Set the Column Source as StateProvince (the column with the filter on it).
  6. In order to see the Total Sales, set the Summarize by option as Sum , and set the Pivot Table Data Source option that appears as LineTotal.
    ImportDB for Analysis template pivotDB.png
  7. Click OK. A new worksheet is created named Pivot1. Rename it as Summary
  8. Now highlight the pivoted data and click on the Column Plot button to create a column plot.
    ImportDB for Analysis template column.png

Customize Graph and Create Analysis Template

The Colifornia Sales are much higher than other states, making it hard to see Utah sales column. Also we can customize the color and pattern of column/bars to make it more appealing.

  1. Single click on any California bar. On the mini toolbar that shows, go to Single tab and click Plot on Right Y so California bars will be plotted against a right Y axis.
    ImportDB for Analysis template ca right graph.png
  2. Double click the right Y axis title and set it as %(1Y, @LC), which means use 1st Y plot's Comments info. as Axis title. The right Y axis title will become California.
  3. Double click the left Y axis title and set it as Others.
  4. Double click on any Y Axis to open the Axis dialog.
  5. Select both Left and Right on the left panel. On Scale tab, set the Rescale dropdown to Fxied From - Auto To.
    ImportDB for Analysis template axis fixfrom autoto.png
  6. Select Horizontal on the left panel. On Scale tab, set the Rescale dropdown to Auto.
  7. Go to Tick Labels tab, Select both Left and Right on the left panel and set Display as Engineering:1k. Click OK.
    Set Axis Recalc(b).png
  8. Customize fill colors and patterns of columns, legend, horizontal tick labels, etc. e.g.
    ImportDB for Analysis template axis final graph.png

Add Graph in Worksheet and Save Analysis Template

  1. Right click on Summary sheet tab and choose Add Graph as Sheet:[Graph1] to add the graph as a separate sheet in workbook.
    ImportDB for Analysis template graph sheet.png
  2. 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. Now workbook is self-contained with everything: database connection, data filtering, analysis (pivot table) and graph.
  4. Choose File: Save Workbook as Analysis Template... and name it LineTotal_By_State

Change Query and Re-import Data to Automatically Update Analysis

  1. Go to File: Recent Books menu to load the LineTotal_by_State you just saved. This will open a blank analysis template workbook.
    ImportDB for Analysis template load.png
  2. Go to Sheet1. Click on the DBConnector.png and click Import or click the Import Data button Button db Import Data.png to reimport from database.
  3. Summary sheet and Graph1 sheet are both updated as well.
    ImportDB for Analysis template load imp.png
  4. In the new workbook, click the funnel on column C and change check 2 only.
    ImportDB for Analysis template mod filter.png
  5. You can also click on the DBConnector.png and click SQL Editor to modify the Query as well.
  • You may need to manually reapply filter Reapply filter.png after reimport new data from Database, or choose Worksheet: Worksheet Script... menu. Check After Import checkbox and enter script wks.runfilter() so that everytime after import new data, all filters will auto apply.
  • By default when you save project file (OPJU), the imported database data is excluded so that the OPJU file size will not be huge. But you can click on the DBConnector.png and uncheck Exclude imported when saving so that data is kept with OPJU file.
  • Or keep Exclude imported when saving checked but instead, click on DBConnector.png and check Auto Import so that everytime the database data will auto import when reopen the OPJU file.
English | Deutsch | 日本語