Home

Data Mining Configurator

1. fa PID_3 True PID 5 PID_2 True PIC a PID_1 True PID True Pare Es Mame_5 Es E Name Pee RA M e MM 0 Pi Unified Data Browser Database Access Tab Data Mining Configurator NOTE Both data mining configuration tools are capable of configuring Data Mining OPC Servers on the local machine only When configuring data mining first you should decide what type of action you want to perform with databases there are two basic options e Read the data periodically and display them in an OPC client e g in GraphWorX e Manipulate database data upon user request The first option is represented by Data Sources the second option by Data Manipulators Both of these objects need to specify a Database Connection For instruction on how to create each of these objects in the configuration database please see the sections below Data Mining Configuration Quick Start Data mining configuration involves the following basic steps 1 Start the Data Mining Configurator 2 Connect to a database i e where to get the data 3 Select a data set or data manipulator to mine within the connected database i e which group of data within the connected database 4 Specify which data items to monitor within the chosen data set i e which values to read from the selected group of data 5 Create a GraphWorX display that connects to data items in your data mining configuration database 6 Visuali
2. ComPlus Applications Dell directx Avery LabelPro File name Files of type KML Files rl Cancel W Create new items MW Update existing items Importing Configuration Data From an XML File Activating the Database Once your configuration is complete you need to make sure that it is the active database The database that is currently active is the one that the server uses To make the current database active select Make Active from the File menu If the Make Active selection is grayed out then the current database is already the active database A dialog box appears showing both the current active database and database that is currently being edited as shown in the figure below To set the edited database as the active database click the Yes button DBOPCServerConfigurator x Current active database is 2 C Program FilesiSmariProcessviemtExamples DEMO DatabaseMining DBOPESserverConfiqurator mdb Active database is the database that the server will load when started Currently edited database is Documents and Settings 4drministratoriMy Documents DBOPRCServerConfigurator test mdb Do you want to set edited database as active database Activating the Database 55 Data Mining Configurator User s Manual 56 eo oS Edit Menu The Edit menu commands are listed in the table below Edit Menu Commands FUNCTION SHORTCUT COMMAND KEYS New Create
3. 2 Select Open from the File menu as shown in the figure below File Edit View Go Tools Help Mew Ctri h Save As N Connection Properties Configuration Wizard Ctrl Cow Export CS Import Opening the Example Configuration 3 Browse to the Program Files SMAR PROCESSVIEW Examples Database Mining Examples directory and select the DBOPCServerExample mdb file as shown in the figure below Look in gt Database Mining Examples e er Ed F administration mdb 4 0BOPC_GlobalAliascfg mdb Fay DBOPCserverConfigurator mdb Fa DBOPCServerExample mdb F Northwind mdb File name DBOPCS erverE sample mdb Files of type Microsoft Access Databases mdb Cancel Data Mining Configurator Selecting the Data Mining Example Configuration Database 4 The example data mining configuration opens in the Configurator as shown in the figure below The example configuration contains two different database connections These connections contain several data sources and data items tags Fy Databases EE DataSourcedt 2 12 AccessCo 1 Connection Mame AccessConnection 2 Description I E DataSourceD2 BE Tag2 3 Connection Type ms Access MDB database ace Taga 4 Select or Enter MS Access Database E E Program Files 5mar Process iew Examples Database Mining Browse Rie Tag4 Datasource05 a a ly cu D Ln ha E E mr iD D D o a a a ae c
4. SQL queries in a data manipulator object should be used only for databases that do not support stored procedures e g Excel MS Jet In all other cases stored procedures functions should be used to manipulate database data The reason for this is that stored procedures offer wider functionality and because they are precompiled on the server execution is much faster 72 Data Mining Configurator 5 When you have finished configuring the data manipulator properties click the Apply button The new data manipulator appears under the database connection in the tree control Data Manipulator Properties A data manipulator which is intended for processing data stored in a connected database manipulates data using a SQL write command or by executing a stored procedure Configuring a data manipulator requires the following basic steps as shown in the figures below 1 Inthe Name field tyoe a name for the new data manipulator 2 Specify the type of data manipulator to use SQL Command or Stored Procedure If you use a SQL command click the Edit buttons to create edit your SQL queries If you choose stored procedure click the button to select a stored procedure SQL queries in a data manipulator object should be used only for databases that do not support stored procedures e g Excel MS Jet In all other cases stored procedures functions should be used to manipulate database data The reason for this is that stored proced
5. gt 1 E 12 Gre gt Firsthame Rowset Columns 2 Employees Country EmployeelD HireDate HomePhone Lasthame Selecting Column From Database Table 6 The Wizard displays the data source information that you specified as shown in the figure below Click the Next button to complete the data source connection The next step is to define data items values to read from the data source You have the option of creating a static data item or a dynamic data item option depends on the type of database connection A static data item simply displays data values from the connected database A dynamic data item allows you to manipulate data values from the connected database To configure a static data item check the Create Static Tag Under This Data Source check box NOTE You cannot go back after this step Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for you Object type Data Source Name Employees Description Update rate 10 Data source type Database table Table name Employees Enable writes FALSE Database Mining Wizard cannot go back after this step The data object part of the wizard will be finished lt Back Finish Cancel Help Data Mining Wizard Confirming Data Source Connection Data Mining Configurator Data Source Wizard SQL Command To configure the data source properties
6. 2 Set Database Polling 4 Enable Update Update Rate sec 1 Apply Reset Add New E D a adus h 3 hiarkie In IPA Data Source Demo As you can see in the Employees _ By_PID data source configuration above the data set specifies a table in the administration mdb Access database called Employees To view the tables in the database Click on the button next to the Table Name field as shown in the figure above 2 This opens the Unified Data Browser which displays a list of tables in the database as shown in the figure below As you can see the database contains two tables Employees and ProjectDetails In this demo data source the Employees table already has been selected MM Unified Data Browser E E x a 1 m2 rer Employees Tables O ACCESS TABLE 1 System Tables Employees E ProjectDetails w Employees Table in Unified Data Browser To view the same the database tables in Microsoft Access start Microsoft Access must be installed through Microsoft Office and open the administration mdb file in the C Program Files SMAR PROCESSVIEW Examples Database Mining Examples directory in the Data Mining Configurator User s Manual PROCESSVIEW installation The Employees database table in the database contains several columns and rows of data about four different employees as shown in the figure below e Bob e Russel e Dave e Arthor The columns list the personal
7. When you complete a data source configuration using the Data Mining Wizard the Wizard displays the data source information that you specified as shown in the figure below Click the Next button to complete the data source connection The next step is to define data items values to read from the 45 Data Mining Configurator User s Manual 46 data source You have the option of creating a static data item or a dynamic data item option depends on the type of database connection A static data item simply displays data values from the connected database A dynamic data item allows you to manipulate data values from the connected database To configure a static data item check the Create Static Tag Under This Data Source check box NOTE You cannot go back after this step Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for you Attribute Object type Data Source UNSUNG D A E MONEE eE Pee eion E Aa A O ete Update rate 10 Data source type SOL command SOL read command Ernployeel OM Create Static Tag under this Data Source object Database Mining Wizard cannot go back after this step The data object part of the wizard wall be finished Back Pest gt Finish Cancel Help Data Mining Wizard Confirming Data Source Connection For additional information about configuring data items please see the
8. ining Configurator Data M US Q SIMUL Sill al PS MAY 06 DATA MINING CONFIGURATOR VERSION 8 TM S FOUNDATION USER S MANUAL Data Mining Configurator E DBOPCServerConfigurator Data Mining Configurator by Smar 3 iS iol x File Edit View Go Tools Help Eak N es fs e le Databases e EA AccessConnectio E accessConnection H E AccessExample 2 4ccessExample Es 12 AdministrationDB Ra dministrationDB E F Datalogger El Datalogger mas ExcelFil E ee 28 ExcelFile EL SOLExample El SOLExample 6 Objects NUM 2 PVI EWDMCME Specifications and information are subject to change without notice Up to date address information is available on our website web www smar com contactus asp Table of Contents TABLE OF CONTENTS DATA MINING CONFIGURA OR it A A A AAA 5 INTRODUCTION e a el at 0 5 GONFIGURING THE DATA MINING ORG SERVER ci a 6 DATA MINING CONFIGURATION QUICK STAR Escalada 7 DATA MINING ONEFIGURATION DEMO ee ee ee a ee 7 STARTING THE DATA MINING CONFIGURATOR ccccceccceeceeeeeceeeesaeeeeeeeeeeseeeeeseeaseeeeesseaseeeeeseaaeeeessaaseeseeessaaees 16 CREATING A NEW CONFIGURATION DATABASE uti ds 17 DATA MINING CONRIGURATION WIZARD cuasi diran aaia 18 EXAMPLE CONFIGURATION USING DATA MINING CONFIGU
9. o mo MM m e E S e HEBE RADA 2 2 44 ja Oe add m Object Dimensions L 21 T 117 W 156 H 24 100 Primary Layer a Data Mining Demo in GraphWorx Configuration Mode 4 This opens the Property Inspector for the data item Click on the PPT DE tab as shown in the 14 figure below As you can see the AdministrationDB Employees_By_PID Name_2 item ID has already been specified in the Data Source field Property Inspector E x Text PPT DE Data Source D Drag Drop Data Source udiministrationD B E mployees_By_PIO Name_2 Data Tags Expression Point Type I Update OPC Request Type STRING Data Enty Client Display Type STRING E Contin Format fio P Imitialalue o M Keppad TF Show Unit Thousands Separators Unit Position Right Leading Zeros Unit Text Property Inspector for Name_2 Data Item Data Mining Configurator 5 Click on the Data Tags button to open the Unified Data Browser The Unified Data Browser opens to the Database Access tab as shown in the figure below You can see that the browser tree control is similar to the tree control in the Data Mining Configurator you can choose from configured data items from the Employees_By_PID data source connection The Name_2 item ID has already been selected Notice that each ItemID e g SMAR DatabaseOPCServer 3 AdministrationDB Employees_By_PID Name_2 contains the following information Serve
10. Click on the PPT DE tab as shown in the figure below As you can see the AdministrationDB Employees_By_PID Name_2 item ID has already been specified in the Data Source field Property Inspector No x Text PPT DE Data Source Drag Drop Data Source sdminigtrationO 6 Employees By PID Name Data Tags Expressior OPC Request Type STRING gt Client Display Type STRING Format ho y FS Initial alue Show Unit Thousands Separators Unit Positions Right a Leading Zeros Point Type IY Update Data Entry Confirm zi Keypad Unit Text e Property Inspector for Name_2 Data ltem Click on the Data Tags button to open the Unified Data Browser The Unified Data Browser opens to the Database Access tab as shown in the figure below You can see that the browser tree control is similar to the tree control in the Data Mining Configurator you can choose from configured data items from the Employees By PID data source connection The Name_2 item ID has already been selected Notice that each ltemiD e g SMAR DatabaseOPCServer 3 AdministrationDB Employees By_PID Name_2 contains the following information Server name e g SMAR DatabaseOPCServer 3 Database connection name e g AdministrationDB Data source name e g Employees By _PID Data item name e g Name_2 MA Unified Data Browser lt gt ala tm Ge go x Bre Smar DatabaseOPcserver 31 A4dministrationDB
11. Data Item Properties section later in this document 1 Now you will define a data item value to read from the data source e g the FirstName column Give the data item a name e g FirstName_1 as shown in the figure below Data Mining Configurator Static Tag wizard Please specify the following static tag object properties Specify Static Tag properties 1 Static Tag Mame First ame 1 2 Data Source parameters to bind Add Remove Static Tag returns complete table roweet as an array Back Next gt Eimer Cancel Help Data Mining Wizard Defining a Value to Read 2 Now you need to specify a value to read from the selected column in the database table The value corresponds to the record number in the database For example in the First Name column in the database record number 1 is the value Nancy E A Table 1 Davolio Yancy sales Representative 2 Fuller Andrey Vice President sales 3 Leverling Janet vales Representative 4 Peacock Margaret sales Representative 5 Buchanan steven sales Manager b Suyama Michael sales Representative F King Robert sales Representative o Callahan Laura Inside Sales Coordinator 4 Dodsworth Anne vales Representative Autohumber Record 14 4 lie LIL oF Y Employees Table in Northwind Database 3 Click the Add button in the Wizard To read the value Nancy enter 1 as the Parameter Value as shown in the figure below 47 Data Mining Configura
12. Database Mining Wizard Which type of data object would you like to create Wizard will create data object depending on the choice below Choose a data object type you would like to create Create new Data Source Data source object can be used for reading the data from the databases lt can access the specific database table directly or use SOL read command to get data Create new Data Manipulator Date manipulator object ts tended for processing the data stored in the databases Ib can manipulate with the data by SAL write command or by executing stored procedure There are twa working objects in database mining Data Source and Data Manipulator These objects can work with the data stored in databases via reading or writing operations Access to database data it enabled via Data Source or Data Manipulator objects only lt Back Finish Cancel Help Data Mining Wizard Data Object Options The next step in the Data Mining Wizard is to connect to a database i e where to get the data You have two options for database connection Create a New Connection or Use an Existing Connection as shown in the figure below Choose an option and then click the Next button to continue 5 6 Data Mining Configurator Connection object wizard Please select whether you want to create a new connection or use an existing connection Choose a new database connection or use an existing connection for your new Data O
13. GraphWorx by Smar File View Tools Configure Help Static definition Name Surname Bob Richards Russel smith Dave Jones Dynamic definition Name Bob Richards Surname Salary in 4500 2100 1500 Salary in 4500 loj x PID PID PID_1 PID PID_2 PID PID_32 A 85 Data Mining Configurator User s Manual Data Mining Demo in GraphWorx Runtime Mode 7 The items displayed in GraphWorX correspond to the information in the Employees database table in the Administration database For example the itemID AdministrationDB Employees By PID Name_2 has the value Russel because Russel is the second name listed in the database table as shown in the figure below odds al bf Create table in Design view Tables Bl Create table by using wizard f a ae 2 Create table by entering data Employees E E Forms a Reports ProjectDetails eS Seroups amp Employees Table pee Ce J Department Bob Richards Applications Engineer Product Development 2 Russel smith Applications Engineer Product Development 3 Dave Jones 14 Engineer Product Development 4 Arthar Anderson Sales Representative Sales and Marketing 0 Employees Table in Administration Database Database Access Tag Syntax In the Database Access tab of the Unified Data Browser shown in the figure below the tree control is similar to the tree control in the Data Mining Configurator you can choose from c
14. IF the First row of your Excel data source contains columns heading they can be used as Field names E Databases Click to Advanced button to set other connection properties Advanced Apply Reset Edd New Database Connection Properties Microsoft Excel Connection Data Mining Configurator Microsoft SQL Server Connection In the Microsoft SQL Server connection properties select a SQL Server from the drop down list The default SQL server is local Then select the database on the specified SQL Server file as shown in the figure below By default SQL Server database connections use Windows integrated security 1 Connection Mame Microsft SOL Server Connection 2 Connection Type ms SOL Server database 3 Select a SOL Server Mame local 4 Select the Database on the Server Log on to the server using Windows Integrated security Click to Advanced button to set other connection properties 4 Database Connection Properties Microsoft SQL Server Connection Oracle Connection In the Oracle connection properties specify an Oracle database name Then supply the User Name and Password required to log on to the specified Oracle database as shown in the figure below El Databases EA Er AccessConnection 1 Connection Marne Oracle Connection 2 Connection Type oracle SQL Server database 3 Enter a Oracle Server Name AAA 4 Enter Information to
15. It also contains contact information About Application Database Connections The first step in data mining configuration is to connect to a database i e where to get the data The Databases tree control is where you configure your connections to different types of databases including Microsoft Access Microsoft SQL Server Microsoft Excel Oracle and other ODBC data sources The Database Connection object is equivalent to a physical database connection To correctly configure database connection you need to specify a database type and fill in the appropriate number of connection parameters Creating a New Database Connection To create a new database connection 1 Right click on the Databases tree control of the Configurator and select New gt Database Connection from the pop up menu as shown in the figure below Database Connection Ctrl C Rename paltin Delete Creating a New Database Connection 2 The properties dialog box for the new database connection appears in the right hand pane of the Configurator as shown in the figure below E Databases El AdministrationDE 1 Connection Mame Administration CE 2 Connection Type ms Access MDB database 3 Select or Enter MS Access Database Process View Examples DEMO DatabaseMining administration mdb Browse Click to Advanced button to set other connection Advanced Add Mew a SS SSS SSS SS SS SS SS Setting the Properties
16. Access file is the default active data mining configuration database e administration mdb This Microsoft Access file is the demo database connection e DBOPC gdf This is a GraphWorX display that allows you to view the data sources and data items specified in the demo database connection To view the DBOPCServerConfigurator mdb demo configuration database e Open the Data Mining Configurator from the Windows Start menu by selecting Programs gt Smar ProcessView gt Tools gt Data Mining Configurator e This opens the DBOPCServerConfigurator mdb database in the Configurator as shown in the figure below The main tree control of the Configurator is called Databases Under this tree control you can see the following items e The demo database connection called AdministrationDB which connects to the administration mdb Microsoft Access database e A demo data source called Employees By PID e Several data items e g Name_1 Name_2 Name_3 PID_1 PID 2 etc e These demo items are described in greater detail in the Data Mining Configurator Help documentation OBOPCServerConfigurator mdb DBOPCServerConfigurator IO x File Edit View Go Tools Help C ae Ab gt e E Ma Databases j oOo non may de E to fe EE E E Seah dministrationDE 1 Connection Mame AdministrationDB G E Employees Ey PID 2 El Name_1 2 Connection Type M5 Access MDB database E a eal Namie 3 Select or Ente
17. Objects E Tables Create table by using wizard Rae Create table by entering data mi Queries Categories Ea Forms Record 14 4 i Reports a Pages Macros Employees Order Details Orders Products res Modules Shi ppers BS Groups Suppliers FA Favorites Customers Table in Northwind Database The example configuration database contains the following data sources and data tags DataSource01 Tag1 Reads a single value from the CompanyName column in the Customers table DataSource02 Tag2 Reads a complete rowset from the Customer ID column in the Customers table DataSource03 Tag3 Reads all values from the Company Name column in the Customers table DataSource04 Tag4 Reads all values from the Customers table in a multidimensional array DataSource05 Tag5 Reads a complete rowset from the Customer ID column in the Customers table DataSource06 Tag6 Reads a single value from a list list of parameters DataSource07 Tag7 Reads a single value from the CompanyName column in the Customers table DataSource07 Tag8 See single value Reads a single value from the CompanyName column in the Customers table DataSource09 Tag10 SQL command reads a complete rowset from the Customer ID column in the Customers table DataSource10 Tag11 SQL command reads a single value from the CompanyName column in the Customers table DataSource11 Tag12 SQL command reads all values from the Customers table in a multidimensional arr
18. in the Data Mining Wizard 7 Select SQL Command as shown in the figure below Click the Next button NOTE You cannot go back after this step Data Source wizard Please specify the following data source object properties 1 Enter Data Source name it will be used as a part of tag identification Name E mployecs 2 Specity Data Source Database Table uses database table to get data f SOL Command uses SELECT statement to get data from tables Enable Multi rows Dataset possible to get more than one row as a result 3 Set database W Enable Updates Update Rate sec ro Back Finish Cancel Help Data Mining Wizard Data Source Properties In the SQL Command Parameters field click the Add button Enter a Parameter Name i e column name and choose a Parameter Data Type from the drop down list To view or modify the SQL query click the Edit button This opens the SQL Command Editor which enables you to manually edit the query string Click the Next button Data Source object Please specify the following SQL command properties of Data Source object Specify SQL Command properties of your DataSource 1 SOL Command Edit 2 SOL Command Parameters Parameter Mame Add O EmplopeelD VT_BSTR Remove i lt Back Hent gt Finish Cancel Help Data Mining Wizard SQL Command Parameters 41 Data Mining Configurator User s Manual 42 1 The Wizard displa
19. in the figure Mame Mame_ 1 Bind Dataset Parameters Add j Remove Move Up Move Down il 2 Specify Dataset Columns i Specific Column Column Mame Complete Rowset returned as an array Apply Reset Add New Data Item Demo below The columns list the personal data for each employee e PID Employee s identification number e Name Employee s first name e Surname Employee s last name e Title Employee s job position e Department Company department in which the employee works e Salary Employee s salary In this example the Name column is already selected for the Name_2 data item MA Unified Data Browser a gt fe ll EAS Rowset Columns iz Employees Department Mame PID Salary Surname Title Names Column in Unified Browser Once you have selected a table you need to specify a data set parameter i e a specific value that you want to read from the database table Click the Add button to enter a Parameter Value as shown in the figure below 12 Mame Name 2 1 Bind Dataset Parameters Enter Parameter Yalue Parameter value E Value Data Type YT I4 Move Down 2 Speci fe 5 Specifying Data Set Parameters The Parameter Value corresponds to the record number in the database table For data item Name_2 the Parameter Value is 2 as shown in the figure above As you can see in the figure below in
20. is used by the Data Mining ActiveX controls and two Data Mining Manipulator ActiveX controls in the DataLoggerExample gdf display For more information about this example please see the Data Manipulator Example application note on the PROCESSVIEW product CD 91 Data Mining Configurator User s Manual 92
21. name Then supply the User Name and Password required to log on to the specified Oracle database as shown in the figure below Click the Next button to continue Oracle SOL Server Database Connection Please specity the following to connect Oracle server 1 Enter a Oracle Server Name 2 Enter Information to Log On to the Database User Mame DAA AAA Password ed lt Back Finteh Cancel Help Data Mining Wizard Oracle Connection ODBC Wizard To create an ODBC database connection in the Data Mining Wizard click the Advanced button to establish database connections through the Windows Data Link Properties Click the Next button to continue 36 Data Mining Configurator ODBC Datasource Connection Please specify connection attributes ODBC datasource attributes Attnibube Name Oracle Database Type ODBC datasource Provider MS0450L 1 Persist Securty Info False Click the Advanced button to change attributes Advanced lt Back Pinter Cancel Help Data Mining Wizard ODBC Connection Step 4 Configuring the Data Object After you connect to a database the next step in the Data Mining Wizard is to configure the data source or data manipulator Note For additional information about configuring data sources please see the Data Source Properties section later in this document For additional information about configuring data manipulators please see the Data Manipulator Properties section l
22. table for the data item You can select a Specific Column or use a Complete Rowset To choose a specific column click the button and browse for a column name e g Name from the list of columns in the database table as shown in the figure below MA Unified Data Browser gt fel 1 E 2 Hep Name Fowset Columns AE Employees Department E IO PID Salary Surname Title Specifying Column Name in Data Item 81 Data Mining Configurator User s Manual 3 The Parameter Value corresponds to the record number in the database table For data item Name_2 the Parameter Value is 2 as shown in the figure above As you can see in the figure below in the Name column record number 2 corresponds to the second value in the column Thus the value for record number 2 is Russel which is highlighted in the figure below Thus the Parameter Value for data item Name_2 is resolved as Russel E Employees Table ET TT TT 1 Bob A MIE A Arthor O Me gt ES 3 Dave ee Richards Applications Engineer Product Development smith Applications Engineer Product Development Jones LA Engineer Anderson Sales Representative Sales and Marketing Product Development Record 14 4 fal er r of Record Number in Database Table 4 When you have finished configuring the data item properties click the Apply button The new data item appears under the data source in the tr
23. the figure below You have the option of creating additional data items tags In this case we will click the Finish button to complete the configuration 27 Data Mining Configurator User s Manual 28 Static Tag object The wizard will create the new Static Tag object with the following attributes Database Mining Wizard creates the following Static Tag for your Attribute Name First ame_ Database Mining Wizard cannot go back after this step The static tag part of the wizard will be finished lt Back Hests Finish Cancel Help Data Mining Wizard Confirming Data Item Value 19 The new configuration that you created using the Wizard appears in the Data Mining Configurator as shown in the figure below Ee Databases E E Mew Database Connection 1 Connection Name Mew Database Connection E El Employees i E5 FirstName_t 2 Connection Type ms Access MDE database 3 Select or Enter MS Access Database C Program Files Common Files Smar Test Northwind mdb Browse Click to Advanced button to set other connection Advanced Add Mew New Wizard Configuration in Data Mining Configurator 20 The next step is to connect to the new data item and visualize the data in a GraphWorX display To a new GraphWorX display select Start gt Programs gt Smar ProcessView gt ProjectWorX gt GraphWorx Before you can visualize the data you must activate the data mini
24. Data Mining Configurator 1 Specify Dataset Database Table Enable Multi rows Dataset f SOL Command Stored Procedure Read SQL Command Edit Get Params SQL Command Parameters Add Remove Edit Parameter Mame Move Up CustomerID Parameter Data Type St 2 Set Dat Jw Ene Apply Reset Add Mew Specifying SQL Parameter Name 3 To view or modify the SQL query click the Edit button This opens the SQL Command Editor which enables you to manually edit the query string as shown in the figure below Click OK If you want to read more than one row from the selected database table check the Enable Multi rows Dataset check box This way you do not need to add individual rows to the Table Parameters field SOL Command Editor select From Categories where categorvID SQL Command Editor You can also change the column ordering in the data source configuration by selecting a column and clicking the Move Up or Move Down buttons Click the Remove button to delete the selected column s 71 Data Mining Configurator User s Manual Creating a New Data Manipulator Data manipulators allow you to create and edit custom SQL queries as well as use stored procedures to modify data in a connected database To create a new data manipulator 1 Right click on a database connection in the tree control of the Configurator and select New gt Data Manipulator from the pop up men
25. Dynamic data items can be indexed by parameters configured at the data source level It allows changing parameter values dynamically in runtime e g using aliases variables or scripting Note Alias names should have quotation marks around them If you are using aliases the aliases and quotes should use the following syntax lt lt lt Alias1 gt gt lt lt Alias2 gt gt lt lt Alias3 gt gt gt Examples Suppose you have a database table Sample with columns PID Name Description and data source with name Sample_by_ PID referring to this table with one parameter PID The table does not contain any rows with PID greater than 5 1 The OPC DA tag SMAR DatabaseOPCServer 3 Sample_by_ PID lt 1 gt lt Name gt accesses the Name column for row with PID 1 2 The OPC DA tag SMAR DatabaseOPCServer 3 Sample_by PID lt 1 gt Recordset accesses the whole table row with PID 1 Table row s values are returned in array of VARIANT values one or two dimensional depends on data source configuration 3 Inserts into column oriented data item configured to access table column Name with PID set 10 Writing string John into this data item will create a new table row with the following values PID 10 Name John Description database default value 4 Inserts into record set oriented OPC tag with PID set 11 Writing array of VARIANTs will create a new table row u
26. Employees By PID Mame OPCDA Database Access Global Aliases Graphwor Ely Databases 2 18 AdministrationDB ba Employees By PID E surname_5 surname_2 surname_1 Salary_3 Go Salary 2 E5 salary_1 EPDS 5 PID_2 E5 PID_1 Es Name_3 ss Mame _ 2 Mame Use Specific Column Column Mame Parameters True True True True True True True True True True Data Mining Configurator Surname 3 Surname 2 Surname 1 Salary 3 Salary z Salary 1 PID 3 FID 2 PID 1 Name a Name 2 1 Data Item Specified in Unified Browser Close out of the Unified data Browser and the Property Inspector dialog box Enter the GraphWorX display into runtime mode You can see the data coming from the Employee database table as shown in the figure below Notice that the Parameter Value for data item Name_2 is resolved as Russel in the visualization display The Static definition section simply displays the values from the Employee database table for the configured data items but the data cannot be manipulated The Dynamic definition section also displays values from the Employee database table but in this case only one row of data is displayed at a time and you change the currently viewed row by clicking the three PID buttons as shown in the figure below The dynamic configuration uses local aliases and global aliases which enable you to switch between rows during runtime mode yf DBOPC gdf
27. FTEXCEL CONNECTION o ld a a el Stee 63 MICROSOFT SOE SERVER CONNECTION sanatorio a S 64 ORAC ECE CONNECTION rato cedros dt de ed lr dt td 64 ODBC CONNEC THON netas oca 65 DATA SOURCES AND DATA MANIPULA TOR Sui a a a aa n aaia Aaa aai aaea 65 DATA SOURCES da A tico 65 DATA MANIPULA TO Scania e oe e a e o 65 DO S AND DON TS FOR DATA MINING 02d ds 65 TROUBLESHOOTING DATA SOURCES AND DATA MANIPULATOR S oocccccccccncnnnnnnononnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnss 66 Data Mining Configurator User s Manual IV CREATING Ay NEVEODA TAS OURGE a pr ia is Aeron 67 DATA SOURGE RORER ME Sorrir oo ado aaa 68 DATABASE TABLE Susi ee nC eat ge eet te Opn ene ete eR a ery eer ne eee mr erence eer et mene ee eres 69 SOECONIMAND oracion teen etn eg 71 CREATING A NEW DATA MANIPULAT OR Wisin eae a Ae aaa 73 DATA MANIPULATOR PROPER WES 40d 74 EC OMIM NID Streets Becta A II ce toeeteauten a 74 STOREDPROCEDURE mmie ec Pr et Pe ee eee eee ne ee eee eee ee ee reece 77 DATATE M weet vas tacosce a she den T dar onede net vans E A E A A A O A 78 DATAITEMS FOR DATA SOUR CES arsaremen ne aA aT 78 STATE DATAMTEMS tt added le lc 78 DYNAMIC DATA ITEMS unta ciore iba iodo arca 78 EXAMPLE e RR ARO 79 DATA ITEMS FOR DATA MANIPULATO A Sustrato oido 80 CREATING A NEW DATA ITEM oen a a aea aa e e a 80 DATAITEM PROPERTIE O urea arnet or a 81 TESTING YOUR DATA TAGO rriro arne e e lenin 83 VISUALIZING DATA IN GRAPHWORX canastas 84 DATABASE ACGESS TAG
28. Log On to the Database User Name Password Click to Advanced button to set other connection properties Advanced Apply Reset Add Mew Database Connection Properties Oracle Connection 63 Data Mining Configurator User s Manual ODBC Connection An ODBC connection shown in the figure below is a connection to a generic open data source Click the Advanced button to establish database connections through the Windows Data Link Properties dialog box Fy Databases 1 Connection Name ODBC Connection 2 Connection Type onec data source Click to Advanced button to set other connection properties dvanced Apply Reset Add New Database Connection Properties ODBC Connection Data Sources and Data Manipulators 64 Once you have established a connection to a database the next step is to select a data source or data manipulator to mine within the connected database i e which group of data within the connected database Data sources and data manipulators work with the data stored in databases via read write operations Data Sources A data source can be use to read data from a database Every data source is connected to particular database connection Data sources are designed to periodically read database tables and display them in an OPC Data Access client A data source supports two different ways to read data from a database e Direct access to database table view e SQL rea
29. Mining Configurator 2 This opens the DBOPCServerConfigurator mdb database in the Configurator as shown in the figure below The main tree control of the Configurator is called Databases Under this tree control you can see the following items e The demo database connection called AdministrationDB which connects to the administration mdb Microsoft Access database e Ademo data source called Employees By_PID e Several data items e g Name_1 Name_2 Name_3 PID_1 PID_2 etc Data Mining Configurator User s Manual These demo items are described in greater detail in the sections below 5 DBOPCServerConfigurator mdb DBOPCServerConfigurator a e ioj xj Fie Edit View Go Tools Help e CE a Bl sala 2 9 Eley Databases Su AdministrationDe 1 Connection Marne AdministrationDE 5 E p 2 Connection Type ms Access MDB database Employees By PID 3 Select or Enter MS Access Database E Mame_1 Process iew Examples DEMO DatabaseMining administration mdb Browse E Name_2 E Mame_3 5 PID_1 85 PID_2 85 PID_3 fg Salary_1 E Salary 2 EE Salary_3 E surname_1 E Surname 2 i E Surname 3 Click to Advanced button to set other connection Advanced addon 4 giem Ready 11 Object s um a Data Mining Configuration Demo Reset Demo Database Connection The Data Mining Configurator default configuration database provides a demo database
30. RATION WIZARD 19 BASIC STEPS IN THE DATA MINING CONFIGURATION WIZARD ooocccccccccccccccccnnnnnnnnnnccncnnnnncncncnnnnnnnnnnnnnnnncncnnnnnnnnnnnccnnnnns 30 MICROSOFT ACCESS WIZARD o tae eee ame tare 34 MIGROSOFLEXCEL WIZARD cee atleast iene 34 MICROSOFT SOL SERVER WIZARD sue an tl became tet ated td ll seer 35 ORACLE WIZARD pa colorado ibaole caca ciao 36 ODBGWZARD tse a ae cc o o US 36 DATASOURCE WIZARD DATABASE TABLE 0 teeta we eae este nana ete aires 37 DATA SOURGE WIZARD SOL COMMAND court It ooo 41 DATA MANIPULATOR WIZARD SQL COMMAND cccccceeeeeeeeeee eee eee e ee ee ee eae none een nn nn nn nn error aaeaaeeeeeeeeeeeeeeeeeeeeeeeeeaaaaaas 42 DATA MANIPULATOR WIZARD STORED PROCEDURE suscrita ada 44 TOOEBAR Sail lei lll e ela 49 MENU Si a it e E be 91 FIE ME NU A E AO 51 ACTINATINE TAE DATABAS E outils london it E aE 56 E DEENEN episodios 57 ss N E E a r asec seeaaaeaeeaaceateeae 58 GONENU eni a a a einicctaias 59 TIOL S ME NOU oae a xe oe cere cca ge aoc a a a a S e 59 OR TION Scsi etait cee tecasevet ceca 59 COMPACTING AND REPAIRING MICROSOFT ACCESS DATABASES coccccccccccccccnnnononnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnrrrrrnnrnnnnnn 60 A O O 61 DATABASE CONNECTIONS sonido his oleada ancodadanesdneedceasesscdraseeateecte 61 CREATING A NEW DATABASE GONNEC TION spcciratrotoistioriplci toronto 61 DATABASE GCONNECTHONPROPERTES tata disidentes as a aaa eee eee cios 62 MIGROSOFT ACGESS CONNECTION coccion lloros 63 MIS ROSO
31. SCRIPTION Lists available languages Depending on which item you have selected the view on the left will change If English is checked the languages will appear as their English name If Localized is checked the languages will appear with the native country in parentheses for languages with several dialects only When Native is checked the languages are displayed the way they would be written in that language Installed Locales Only If this is checked local languages appear in the box Available Language Checking this box allows you to choose from available language Translations Only translations only Go Menu The Go menu commands are listed in the table below Go Menu Commands COMMAND FUNCTION KEYS CTRL ALT Left Moves the cursor back to the previously selected Back l Arrow item in the tree control Poniard CTRL ALT Moves the cursor forward to the previously selected Right Arrow item in the tree control SHORTCUT Up One Level Moves the cursor up one level in the tree control ALT Down Moves the cursor to the next item down in the tree Next Item Arrow control Previous Item ALT Up Arrow Moves the cursor to the next item up in the tree view Expand Item ALT Left Arrow Expands an item that contains a submenu Collapse Item ALT Right Arrow Collapses an item that contains a submenu Page Up ALT PgUp Moves the cursor up to the first item in the tree Page Down ALT PgDown a the cursor down to the last visible i
32. SYNTAX rererere ee eee 87 DATA MINING EXAMPLES rinon ra a E EE O E E aa Ee 88 EXAMPLE 1 DATA MINING CONFIGURATION DEMO 1 00 0 i aaa e eee eee eee aaaaeeeeeee renee 88 EXAMPLE 2 DATA SOURCE CONFIGURATION EXAMPLES 00 0 EE EE rr i iii 88 EXAMPLE 3 DATA MINING LOGGING EXAMPLES cosita AA cab 91 Introduction DATA MINING CONFIGURATOR The basic purpose of PROCESSVIEW data mining is to visualize data from various data sources such as a Microsoft Access database in a customizable user friendly display format that enables users to interact with and manipulate the data The Data Mining Configurator establishes connections with data sources that can be mined from PROCESSVIEW applications such as GraphWorX where users can visualize data In general the Data Mining Configurator enables you to e Connect to databases i e where to get the data e Select data sets to mine within the connected databases i e which group of data within the connected database e Specify data items to monitor within the chosen data sets i e which values to read from the selected group of data Once you have configured your database connections using the Data Mining Configurator you can configure a GraphWorX display in order to visualize the data In GraphWorX the Unified Data Browser enables you to create process points using data items from your data mining configuration database Thus the data mining configuration database serves as a medium throug
33. The Standard toolbar shown below contains the following command buttons New Creates a new configuration database Open Opens an existing configuration database Import Imports configuration data from a text file txt or a Microsoft Excel file CSV Export Exports configuration data to a text file txt or a Microsoft Excel file csv Configuration Wizard Launches the Data Mining Wizard Back Moves the cursor back to the previously selected item in the tree control Next Moves the cursor to the next item in the tree control Up One Level Moves up one level in the tree control Cut Deletes current selection sending it to the clipboard Copy Copies the current selection to the clipboard Paste Pastes the current contents of the clipboard List Displays items as a list Details Displays items as a list with details Dialog View Displays additional configuration options Global Refresh Refreshes the data for the entire Configurator screen About Displays information about the application Help Topics Displays help for the selected items 49 Data Mining Configurator User s Manual Menus The menu bar of the Configurator contains the following menus e File e Edit e View e Go e Tools e Help Note You can also access many of the menu commands by right clicking items in the tree control of the Configurator and selecting command functions from the pop up menus File Menu The File menu command
34. a Items Dynamic data items are available under every data source object without being configured The syntax of a fully qualified OPC DA tag ID is as follows SMAR DatabaseOPCServer 3 DataSourceName lt ParameterValues gt DataltemName where e DataSourceName is the name of the data source object in the server configuration 177 Data Mining Configurator User s Manual 78 e ParameterValues are values for data source parameters delimited with semicolons see the tables below e DataltemName can be one of lt Column Name gt Recordset Refresh LastScanTime see the tables below DATA TYPE DESCRIPTION VALUE SYNTAX Sting VT_BSTR e Real VT_R8 Boolean VT_BOOL BOOL True or BOOL False VT_DATE 8 byte floating point number DATETIME date value The DATE type is implemented using double precision numbers Days are represented by whole number increments starting with 30 December 1899 midnight as time zero Hour values are expressed as the absolute value of the fractional part of the number DESCRIPTION DATA ITEM NAME ACCESS RIGHTS Can address the only one table column identified Column by its name placed into the last section of OPC DA tag name delimited by lt gt Recordset Read Write Addresses the whole table row Any write forces the data source to re read data CO RENESN from connected data source LastScanTime Returns time of the last data source update
35. a table e g Employees and then click OK 38 i Unified Data Browser gt 0D tl 5 Ore Data Mining Configurator Employees Tables Oo ACCESS TABLE ee Views Categories H L System Tables Customers y Tables ss Employees Order Details Orders Products Shippers Selecting a Database Table 4 If you view the database table in Microsoft Access you can see the column names e g Employee ID Last Name First Name etc in the Employees table as shown in the figure below The next step is to select a column name from which to read the data For example we will use the First Name column which lists several employee names e g Nancy Andrew Janet etc E A Table 1 Davolio 2 Fuller Andrew 3 Leverling Janet 4 Peacock Margaret 5 Buchanan steven b Suyama Michael F King Robert o Callahan Laura 9 Dodsworth Anne Autohumber Record 14 4 Lie LOLA oF Y sales EEE Vice President sales vales Representative sales Representative sales Manager sales Representative sales Representative Inside Sales Coordinator vales Representative Employees Table in Northwind Database 5 Click the Add button in the wizard This opens the Unified Data Browser which lists all the columns in the database table as shown in the figure below Select the column name e g FirstName from the database table 39 Data Mining Configurator User s Manual 40 MM Unified Data Browser O
36. a tags return VT_EMPTY and Bad quality then writing to this tag will create a new record in the database table Since the table may have more columns than just the one requested in the data tag it uses the following criteria to fill in other values e t uses tag parameters and the actual value to create the record the rest of the columns use database defaults e Ifthe data tag is record based then it uses all the values passed during the write Enable Deletes The Enable Deletes on VT_EMPTY value check box is available only when the Enable Writes check box is checked It works for record based data tags only If you write VI_EMPTY value then it will delete related records from the database table 69 Data Mining Configurator User s Manual 70 SQL Command The SQL Command data source is designed to access database data using SQL commands based on SELECT statements The SQL command can be parameterized by SQL command parameters Parameters in a SQL command are represented by question marks and they are filled in the same order as configured in SQL Command Parameters section of the configuration dialog Database stored procedures returning cursor variables row sets can be also accessed using a SQL command based on EXECUTE or CALL statements To enter a SQL command to read from the database 1 Select the SQL Command Stored Procedure option under the Specify Data Set section as shown in the figure below E Datab
37. abases F T AdministrationDB 1 Connection Name AdministrationDE 2 Connection Type ms Access MDE database E E Employees_By_PID 3 Select or Enter MS Access Database E Name_1 Process iew Examples DEMO DatabaseMining administration mdb Browse a a5 Mame 2 MName_3 45 PID_1 45 PID_2 45 PID_3 45 Salary_1 45 Salary_2 fh Salary_3 fi Surname_1 ns Surname _ E of Surname Click to Advanced button to set other connection advanced Reset Add Hew Ready 1 Objects NUM FA Stand Alone Data Mining Configurator The second tool is embedded in the Unified Data Browser on the Database Access tab as shown in the figure below This tab is available together with OPC Data Access browser It was designed for ease of use to allow users to make on the fly modifications in the server address space This second tool supports a subset of features available in the Data Mining Configurator application For more information please see the Unified Data Browser Help documentation MM Unified Data Browser 3 y x mpe e X ep e gt fo Smar DatabaseOPClserver 31AdministrationDB Employees _By_FPID Name_2 opc Da Database Access Global Aliases Graphw or EE surname_3 True Surname 3 Es Surname _ True Surname ea Surname_1 True Surname E Salary 3 True Salary gl Salary True Salary a Salary_1 True Salary E Databases E A AdministrationDE z Employees By PID
38. alog box shown below which lists the initialization properties for the current database connection 50 Data Mining Configurator Database connection properties y l X File name Examples DEMO D atabaseM ining DBEOPCS erverContigurator mdb These are the initialization properties for curent database connection in alphabetical order Name al Data Source C Program File Extended Properties Jet OLEDB Compact Without Replica Repair False Jet OLEDB Create System Database False Jet OLEDB Databaze Locking Mode 1 Jet OLEDE Database Password Jet OLEDE Dont Copy Locale on Compact False Jet OLEDB Encrypt Database False Jet OLEDB Engine Type A Jet OLEDE Global Bulk Transactions Jet OQLEDB Global Partial Bulk Opes 2 Jet QLEDE New Database Password Jet OLEDB Registry Path Jet OLEDE SFP False Jet OLEDB System database Locale Identifier Provider Mame Version Microsoft OLE OB Provider for Jet 04 00 8227 Close Database Connection Properties Dialog Box Exporting Configuration Data Exporting Data to a Text or CSV File The Configurator offers the flexibility of exporting data from your configuration database to a text txt file or a Microsoft Excel csv file To export data select Export from the File menu This opens the Export Configuration Data to File dialog box as shown in the figure below You can then specify the delimiters for exporting the data Unless you specify delimite
39. ancel Help Connecting to Data Source Through the Data Link Properties The database connection properties vary slightly with each connection type e Microsoft Access Connection e Microsoft Excel Connection e Microsoft SQL Server Connection e Oracle Connection e ODBC Connection 61 Data Mining Configurator User s Manual 62 Microsoft Access Connection In the Microsoft Access connection properties click the Browse button and select an Access mdb file as shown in the figure below E Databases l AccessConnection 1 Connection Mame AccessConnection 2 Connection Type ms Access MDB database 3 Select or Enter MS Access Database C Program Files Microsoft Office Office Samples Morthwind mdb Browse Click bo Qdvanced button to set other connection Add Mew Database Connection Properties Microsoft Access Connection Microsoft Excel Connection In the Microsoft Excel connection properties click the Browse button and select an Excel xls file as shown in the figure below lf the first row of your Excel data source e g spreadsheet contains column headings you can choose to use the column headings as field names by checking the Use First Row as Field Names check box as shown in the figure below 1 Connection Mame Excel Connection 2 Connection Type ms Excel XLS data source 3 Select or Enter MS Excel File Mame Browse I Use First Row as Field Names
40. as shown in the figure below A data source can be use to read data from a database lt can access a specific database table directly or use a SQL read command to get data A data manipulator which is intended for processing data stored in a connected database manipulates data using a SQL write command or by executing a stored procedure Choose an option and then click the Next button to continue An example wizard configuration is described in the section below 18 Data Mining Configurator Database Mining Wizard Which tyoe of data object would you like to create Wizard will create data object depending on the choice below Choose a data object type you would like to create Create new Data Source Data source object can be used for reading the data from the databases It can access the specific database table directly or use SOL read command to get data Create new Data Manipulator Data manipulator object ts mtended for processing the data stored in the databases Ib ican manipulate with the data by SOL write command ar by executing stored procedure There are two working objects in database mining Data Source and Data Manipulator These objects can work with the data stored in databases via reading or writing operations Access to database data is enabled via Data Source or Data Manipulator objects only Back Finish Cancel Help Data Mining Wizard Data Object Options Example Configuration Using Data Mini
41. ases a AccessConnection Mame DatasourceDa E DataSourcedl FF DataSource02 Desc SOL Command Example with complete recordset E DataSourceds E E DataSourcel4 1 Specify Dataset o Database Table Enable Multi rows Dataset Dakasourcede E DataSource07 O SOL Command Stored Procedure En Read SOL Command Edit Get Params E DakaSourceid E E Datasource11 SQL Command Parameters E DataSourcel2 Parameter Mame Add ee DataManipulator o CustomerID YT_ESTR er f 38 ExcelFile Remove Edit Move Up Move Dow Stored Procedure Browse 2 Set Database Polling e Enable Update Update Rate sec 1 Apply Reset Add New Data Source Properties SQL Command 2 In the SQL Command Parameters field click the Add button Enter a Parameter Name i e column name and choose one of the following Parameter Data Types from the drop down list as shown in the figure below Click OK NOTE You can also click the Get Params button to select a parameter from the database e VT_14 4 byte integer e VT_R8 8 byte floating point number double e VT_DATE Date in OleDateTime format the DATE type is implemented using an 8 byte floating point number Days are represented by whole number increments starting with 30 December 1899 midnight as time zero Hour values are expressed as the absolute value of the fractional part of the number e VT_BSTR String e VT _BOOL Boolean
42. ater in this document Data Source Wizard Database Table To configure the data source properties in the Data Mining Wizard 1 Select Database Table as shown in the figure below Click the Next button NOTE You cannot go back after this step 37 Data Mining Configurator User s Manual Data Source wizard Please specify the following data source object properties 1 Enter Data Source name it will be used as a part of tag identification Name Employees 2 Specify Data Source Database Table uses database table to get data C SOL Command fuses SELECT statement to get data from tables Enable Multi rows Dataset possible to get more than one row as a result 3 Set database MW Enable Updates Update Rate zec fio lt Back Finish Cancel Help Data Mining Wizard Data Source Properties 2 Inthe Table Name field click the button as shown in the figure below Data Source object Please specify the following Table properties of Data Source object Specify Database Table properties of your Data Source 1 Table Name Employees El 2 Table Parameters Index Parameter Mame O EmplopeelO Remove Enable Writes f Enable Inserte Enable Deletes lt Back Met gt Finteh Cancel Help Data Mining Wizard Selecting a Database Table 3 This opens the Unified Data Browser which lists all the tables in the database as shown in the figure below Select
43. ay DataSource12 Tag13 SQL Command Example reads multiple rows from the ContactName column in the Customers table WriteValue Write1 Writes values to these tags from GraphWorX and displays the changes online WriteValue Write2 Writes values to these tags from GraphWorX and displays the changes online It is recommended that you view these data tags in the Data Mining ActiveX in GraphWorX so you can see how the data are displayed in the Grid ActiveX when connections to data tags are established for both one dimensional and multidimensional arrays For more information please see the Data Mining ActiveX help documentation Example 3 Data Mining Logging Examples The third data mining example provides a data mining logging configuration that connects to different data sources using the Data Mining ActiveX control and Data Manipulator ActiveX control Data Mining Configurator For this example the PROCESSVIEW installation includes the following data mining example files in the Program Files SMAR PROCESSVIEW Examples Database Mining Examples directory e DataLoggerExample gdf This is a GraphWorX example display that contains Data Mining ActiveX controls and Data Manipulator ActiveX controls e DBOPCServerDBGridExample mdb This is an example data mining configuration database that contains some preconfigured example data sources and data manipulators that are used in the logging example e DataLogger mdb This is a logger database that
44. bject Create new connection This will create a new connection object in the configuration ou can select the type of the connection and set its properties Use existing connection lt Back Finish Cancel Help Data Mining Wizard Database Connection Options In the Connection Name field enter a name for the new database connection Then select the type of database e g Microsoft Access Microsoft SQL Server Microsoft Excel or Oracle you would like to use from the drop down list as shown in the figure below Click the Next button to continue Connection object This wizard will create the connection object depending on the choice below 1 Enter Connection name it will be used as a part of tag identification Connection Name New Database Connection 2 Choose the type of database you would like to use Database Type us Excel LS data source MS Access MDB database MS Excel SLS data source M5 SOL Server database Oracle SQL Server database ODBC data source Finish Cancel Help Data Mining Wizard Creating New Database Connection Specify the database as shown in the figure below which shows a Microsoft Access database connection The example below uses the Northwind mdb database which is installed with Microsoft Access Click the Next button to continue 21 Data Mining Configurator User s Manual Microsoft Access Database Connection Please select a file name for MS Access Fil
45. connection called AdministrationDB as shown in the figure below The example configuration shows a connection to a Microsoft Access database called administration mdb which is located in the C Program Files SMARIPROCESSVIEWExamples Database Mining Examples directory in the PROCESSVIEW installation By Databases ERr Administration DE H E Employees By PID 1 Connection Mame AdministrationDE i 2 Connection Type ms Access MDB database 3 Select or Enter MS Access Database Process lew Examples DEMO DatabaseMining administration mdb Browse Click to Advanced button ta set other connection Advanced Apply Reset Add Hew Database Connection Demo Demo Data Source The Data Mining Configurator demo database connection called AdministrationDB contains a data source called Employees_By_PID as shown in the figure below This example configuration shows a data source connection for the administration mdb database Data Mining Configurator Fl Databases FIG AdministrationDB EE Employees By PID ve 25 Name_1 a 2 ca He Name_3 1 Specify Dataset Database Table Enable Multi rows Dataset Y T PID 3 SQL Command a Salary_1 Table Mame Employees a Salary_2 ae Salary_3 Table Parameters Surname_1 Parameter Mame Add a Surname _ lt 0 PID eae Surname _ Remove Move Up Move boven Enable Writes Enable Inserts Enable Deletes on YT EMPTY value
46. ctDetails Selecting a Database Table Data Mining Configurator 1 In the Table Parameters field click the Add button This opens the Unified Data Browser which lists all columns in the selected table Select the desired columns from the specified table as shown in the figure below and then clickf OK NOTE If you want to read more than one row from the selected database table check the Enable Multi rows Dataset check box This way you do not need to add individual rows to the Table Parameters field NOTE You can also change the column ordering in the data source configuration by selecting a column and clicking the Move Up or Move Down buttons Click the Remove button to delete the selected column s MA Unified Data Browser gt 0 1 E Rre PID Rowset Columns 2 Employees Salary Surname Title Selecting a Database Column Enable Writes Direct database access allows users to make modifications in the specified database table may work for views with limitations given by the connected database server or engine When the Enable Writes check box is checked you can write a value to OPC tags and it will update related records in the database table This works for table based data sources only Note Writes are disabled for multi row datasets Enable Inserts The Enable Inserts check box is available only when the Enable Writes check box is checked In case the record is missing in the database table dat
47. cting to Microsoft Excel or Microsoft Jet connections Data manipulator stored procedures to manipulate data only Troubleshooting Data Sources and Data Manipulators When I select SQL Command under Data Source what should it do SQL command will behave similarly to a table based data source it will send data from a record set returned by this command You can parameterize it using data item parameters When I select enable Multi rows Dataset what should it do what should expect as the result You will get multiple rows from a SQL query or table In the case of a single column it will return a single dimensional array of values where each value represents one row When you use Recordset then you will get a two dimensional array like table content all columns and rows Note One limitation is that Multi rows Datasets is read only What are SQL Command parameters and why should I provide more than one of these SQL command parameters specify a set of parameters used by a SQL command the SQL command can use more than one parameter Why and when should I give more than one table parameter You may want to locate a table row using different criteria e g if you have an Employees table you can index it using name mid name surname or you can use personal ID When should check Enable Writes How are the writes possible for example from GraphWorX If you will use a table based data source with a single row as ou
48. d fe GenBroker Monitor a El Data Monitor ise fy Smar DatabaseOPl server E 27 Alarm Monitor GbSsmar DatabaseOPCserver ajc OPC Ery Good Nc In specific Data Tag Quality Indicated in DataSpy Data Monitor GraphWorx Once you have configured and tested your database connection data sources and data items in the Data Mining Configurator the next step is to visualize the data in GraphWorX You also can view data in the Data Mining ActiveX which displays one dimensional and multidimensional arrays of data from database connections inside a grid display which is embedded in an ActiveX container For more information please see the Data Mining ActiveX help documentation The Data Mining Demo provides an example GraphWorX display that shows how to configure your data mining connections so you can view data coming from the database 1 Go to the C Program Files Smar Processview Examples Database Mining Examples directory in the PROCESSVIEW installation and open the DBOPC gadf file 2 The display opens in GraphWorX configuration mode as shown in the figure below The display contains two different examples of data mining visualization static and dynamic A static display simply displays the data values from the connected database A dynamic display allows you to manipulate data values from the connected database The table in the static definition section contains some column names from the Employees database table as shown in the
49. d command based on SELECT statement Data Manipulators A data manipulator which is intended for processing data stored in a connected database manipulates data using a SQL write command or by executing a stored procedure Every data manipulator is connected to particular database connection Data manipulator objects are designed to manipulate database data and structure using stored procedures or simple SQL commands when the database server or engine does not support stored procedures In comparison with data sources a data manipulator should not return any cursor variables row sets Data manipulation actions are executed upon user request only there is no polling mechanism SQL queries in a data manipulator object should be used only for databases that do not support stored procedures e g Excel MS Jet In all other cases stored procedures functions should be used to manipulate database data The reason for this is that stored procedures offer wider functionality and because they are precompiled on the server execution is much faster Do s and Don ts for Data Mining Do not use e Stored procedures in data manipulators with Microsoft Access databases Data Mining Configurator Spaces in stored procedures names CREATE INSERT UPDATE statements in data source SQL command Data manipulator stored procedures to view data Do use Stored procedures in data manipulators with Microsoft SQL and Oracle SQL queries when conne
50. data for each employee e PID Employee s identification number e Name Employee s first name e Surname Employee s last name e Title Employee s job position e Department Company department in which the employee works e Salary Employee s salary Create table in Design view Create table by using wizard los Create table by entering data TEE E Department Richards Applications Engineer E conan 2 Russel amith Applications Engineer Product Development 3 Dave Jones GA Engineer Product Development 4 Arthor Anderson Sales Representative Sales and Marketing O Employees Table in Administration Database Demo Data ltems As shown in the figure below the Employees By_PID data source configuration contains several data items e Name 1 e Name 2 e Name_3 e PID 1 e PID 2 e PID 3 e Salary_1 e Salary 2 e Salary 3 e Surname_1 10 Data Mining Configurator e Surname 2 e Surname 3 Ee Databases l 2 AdministrationDE Name Employees By PID Erp By PID empires By PID Eo ones E Name_1 i 25 Name_3 1 Specify Dataset a PID_1 Database Table Enable Multi rows Dataset a S PID 3 C SOL Command aoe E Salary_1 Table Mame Employees Ed gt Es Salary_2 Es Salary_3 Table Parameters gt ices Parameter Name Add bee urname PID oe Surname 3 Remove gt Move Uo EJ i Data Items in Employees Table The data items correspond to the info
51. e Select or enter a M5 Access Database Mame Ic Program Files Common Files SmarT est Northwind mdb d Back Next gt Pret Cancel Help Data Mining Wizard Specifying Database 7 The Wizard displays the database connection information that you specified as shown in the figure below Click the Next button to complete the database connection NOTE You cannot go back after this step Connection object The wizard will create the new Connection object with the following attributes Database Mining Wizard creates the following Connection for your Attribute Value Name New Database Connection Provider Microsoft Jet OLEDB 4 0 Data Source C Program Files Common Files Smart Database Mining Wizard cannot go back after this step The connection part of the wizard will be finished Finish Cancel Help Data Mining Wizard Confirming Database Connection 8 Now you will configure the data source properties as shown in the figure below Select Database Table or SQL Command In the example below Database Table is selected Click the Next button NOTE You cannot go back after this step 22 Data Mining Configurator Data Source wizard Please epecity the following data source object properties 1 Enter Data Source name it will be uted az a part of tag identification Mame Employees 2 Specify Data Source Database Table uses database table to get data C SOL Comma
52. e Displays a list of options that you can choose to show or Columns hide in the view Opens the Select Language dialog box see below Choose the language you wish to use for your system Unicode version only and click OK For navigation purposes use the buttons and check boxes in the List section Global Refresh Refreshes the data for the entire Configurator screen Subtree CTRL E5 Refreshes only the data contained in the currently selected Refresh subtree Selecting Languages The Select Language function on the View menu allows you to choose which language to use in your display Choosing Select Language from the View menu opens the Select Language dialog box shown in the figure below Select Language NOTE A language resource dll is required for language switching Select Language E EO x List f English C Localized Australia English Belize Englith Canada English Canbbean p English Ireland English Jamaica C Native English Hew Zealand English Republic of the Philippin ish South Africa M Installed Locales Only Trinidad y Tobago United Kingdon ish United States English Zimbabwe ll Available Language Translations Orly Cancel Help Select Language Dialog Box Define the parameters listed in the table below Then click OK to return to the work area 57 Data Mining Configurator User s Manual 58 Select Language Parameters PARAMETER DE
53. e connection in the tree control of the Configurator and select New gt Data Source from the pop up menu as shown in the figure below Rename Multiply nter MS Access Database halaka amples SEM32DEMG DatabaseMir Creating a New Data Source 2 The properties dialog box for the new data source appears in the right hand pane of the Configurator as shown in the figure below Eve Databases Sra AdministrationDE Mame Employees _By_PID H E Employees By PID Desc ME 1 Specify Dataset f Database Table T Enable Multi rows Dataset SOL Command Stored Procedure Table Name Employees Table Parameters pe Parameter Mame Add E des Remove Edite Move Un Mowe Down Enable Writes Enable Inserts Enable Deletes ton YT EMPTY value 2 Set Database Polling he Enable Update Update Rate sec 1 Apply Reset Add Mew Setting the Properties for the New Data Source 3 Inthe Name field type a name for the new data source 4 Specify a Data Set Database Table or SQL Command Stored Procedure to mine within the connected database 5 Specify an Update Rate in seconds to poll for updates from the database Data Mining Configurator 6 When you have finished configuring the data source properties click the Apply button The new data source appears under the database connection in the tree control Data Source Properties A data source can be use to read data from a databa
54. e g Sales by Year and then click OK Data Items Data Mining Configurator MM Unified Data Browser ES x e gt alt EB D Qrer Sales by Year Procedures 15 Functions Procedures Employee Sales by Country Invoices Filter Selecting a Stored Procedure Once you have selected a data set or data manipulator to mine within the connected database the next step is to specify which data items to monitor within the chosen data set i e which values to read from the selected group of data A data item represents a single value in a data set There are two general types of data items e Data items for data sources e Data items for data manipulators Data ltems for Data Sources Two types of data items are used for data sources e Statically defined preconfigured e Dynamically requested parameter values are set in runtime Data items are also accessible as OPC Data Access tags i e they are visible in OPC DA browsers as ordinary OPC DA tags The ProglD of the Data Mining server is SMAR DatabaseOPCServer 3 Static Data Items Static data items must be preconfigured and cannot be changed in runtime mode Basically there are used two types of static data items e Column oriented data item Gets value s from the table column specified in the configuration e Record set oriented data item Gets the whole table row s as an array of VARIANT values single or multi dimensional array Dynamic Dat
55. e tables Stored Procedure Calls stored procedure to change the data in already existing database tables Back Fintsh Cancel Help Data Mining Wizard Data Manipulator Properties 44 Data Mining Configurator 1 In the Stored Procedure Name field click the button This opens the Unified Data Browser which lists all the stored procedures in the database Select a procedure e g Sales by Year and then click OK Data Manipulator object Please specify the following Store Procedure properties of Data Manipulator object Specify Stored Procedure of your Data Manipulator Stored Procedure Mame dbo Sales by ear lt Back Eime Cancel Help Data Mining Wizard Selecting a Stored Procedure 2 The Wizard displays the data manipulator information that you specified as shown in the figure below Click the Finish button to complete the data manipulator connection Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for you Attribute Object type Data Manipulator Mame Sales Description Data manipulator type Stored procedure Stored procedure name dbo Sales by ear Database Mining Wizard cannot go back after this step The data object part of the wizard will be finished Cancel Help Data Mining Wizard Confirming Data Manipulator Connection Step 5 Configuring Data Items
56. ection object The wizard will create the new Connection object with the following attributes Database Mining Wizard creates the following Connection for you _ Attribute Value Mame New Database Connection Provider Microsoft Jet OLEDE 40 Data Source C Program Files Common Piles S5mar gt Database Mining Wizard cannot go back after this step The connection part of the wizard will be finished Back Next gt Finish Cancel Help Data Mining Wizard Confirming Database Connection Microsoft Access Wizard To create a Microsoft Access database connection in the Data Mining Wizard click the button and select an Access mdb file as shown in the figure below Click the Next button to continue Microsoft Access Database Connection Please select a file name for M5 Access File Select or enter a MS Access Database Name CAProgram Files Common FlestSmar Test N orthwmind mdb d Back Next gt Cancel Help Data Mining Wizard Microsoft Access Connection Microsoft Excel Wizard To create a Microsoft Excel database connection in the Data Mining Wizard click the button and select an Excel xls file as shown in the figure below Click the Next button to continue If the first row of your Excel data source e g spreadsheet contains column headings you can choose to use the column headings as field names by checking the Use First Row as Field Names check box as Data Mining C
57. ee control Testing Your Data Tags Before you try to connect to data sources and view your data tags it is highly recommended that you verify if the tags present inside the display are well configured from the server side To do that you have to check the quality of your data tags using OPC DataSpy 1 Start OPC DataSpy from the Windows Start menu by selecting Programs gt Smar ProcessView gt OPC DataSpy 2 In OPC DataSpy expand the tree control and browse to your data tag Right click on the data tag s and select Monitor from the pop up menu as shown in the figure below El OPC Items E g My Computer El 3 OPC Data Access E i Eo Smar AlarnServer_ 1 fa Smar DatabaseOPCServer ver 3 a 3 AccessConnection E e DataManipulator DataSourcedl a a DataSourcedz pim a m n oluman iim lB m oalastscanTime add lB m mRecordset Himi 3 DDR etresh Browsing for Data Tags in OPC DataSpy 3 The data tag s now appear in under the Data Monitor tree control as shown in the figure below The tag quality is indicated in the right hand pane If the tag quality is good then the tag is ready for data mining If the tag quality is bad go back to the Data Mining Configurator and check your data source and data item configurations 82 Visualizing Data in Data Mining Configurator dase Source Value Gualit Et ope Items a a My Computer Er E Network Neighborhood El Internet with GenBroker ce Connecte
58. en the only way to manipulate the data is to use SQL commands One command is used to do specific action and a resulting SQL command is used to get output values in the same database transaction For example when you insert a new table row into a Microsoft Access database you can then read an auto generated value from the table using the SELECT identity command Another example is if you will insert update something then you can get back a sum of certain column s The range of its use is very wide and depends on specific implementation Where should stored procedures be written For example if connect to SQL Server should 65 Data Mining Configurator User s Manual 66 the stored procedure be written in SQL and already available for us to use it Stored procedures are stored in the database server and allow you to manipulate database content They are usually faster than using SQL commands from the client side To get more information you will need to study the documentation for the specific database server What should I expect when I check Complete Rowset When you check the Complete Rowset returned as an array check box it will return a single row of data all columns in a single dimensional array When Enable Multi rows Dataset is checked you will get a multi dimensional array as a result similar to the Recordset data item Creating a New Data Source To create a new data source 1 Right click on a databas
59. figure below e Name Employee s first name e Surname Employee s last name e Salary Employee s salary e PID Employee s identification number Each column contains process points that correspond to data items e g Name_1 Name_2 Name_3 PID _1 PID 2 etc in the Employees_By_PID data source from the demo data mining configuration A process point must be configured for each data item We will continue to look at the Name_2 data item as an example Under the Name column in the GraphWorX display double click the second process point under the Name column as shown in the figure below 83 Data Mining Configurator User s Manual 84 4 DBOPC gdf GraphWorX by Smar 5 oj xj File Edit View Format Arrange Draw Dynamics Tools Runtime Help asuan 4 B8RI gt 10 o Pe 9As Taam APP HAP gH Qt rctuncosews zio zle z u E FCOG GQGAl ete ile 1 2 R Static definition go Name Surname Salary in PID 2222979229 2292292792 229229229 222222227 i a a a 22222272299 m 222222997 PPP PP PP 229292272 E a a a A 222277227 2722222792 2222272202 2722722222 i t s Dynamic definition Name Surname Salary in PID PID_1 2292229209 22929999209 92929299909 PID P1 ee EA AE AO AAA Object Dimensions L 21 T 117 W 136 H 24 100 Primary Layer de Data Mining Demo in GraphWorx Configuration Mode 3 This opens the Property Inspector for the data item
60. following basic steps as shown in the figure below In the Name field type a name for the new data item El Databases Sera AdministrationDe Mame MA E El Employees By PID Mame_1 1 Bind Dataset Parameters 2 y es PID_1 7 Remove oe Le PID 2 a PID_3 Move Up Salary _1 A Salary _2 Move Down a Salary 3 a Surname _ 1 2 Specify Dataset Columns Surname _2 S 7 Surname _3 Specific Column Column Mame Mame i Complete Rowset returned as an array pply Reset Add Mew i Data Item Properties Data Mining Configurator 1 Inthe Bind Data Set Parameters field click the Add button to enter data set parameter values as shown in the figure below Enter a Parameter Value i e column name and choose one of the following Value Data Types from the drop down list e VT_I4 4 byte integer e VT_R8 8 byte floating point number double e VT_DATE Date in OleDateTime format the DATE type is implemented using an 8 byte floating point number Days are represented by whole number increments starting with 30 December 1899 midnight as time zero Hour values are expressed as the absolute value of the fractional part of the number e VT_BSTR String e VT_BOOL Boolean 1 Bind Dataset Parameters Enter Parameter Yalue Parameter Value E 2 Specif Yalue Data Type Sr Move Down Entering Parameter Value for Data Item 2 Select the data set columns
61. for the New Database Connection 3 Inthe Connection Name field tyoe a name for the new connection 60 Data Mining Configurator 4 Select a Connection Type from the drop down list Microsoft Access Microsoft SQL Server Microsoft Excel Oracle or another ODBC data source 5 Specify the database or data source to which you want to connect 6 When you have finished configuring the database connection properties click the Apply button The new connection appears under the Databases tree control Database Connection Properties Establishing a database connection requires the following basic steps as shown in the figures below 7 In the Connection Name field type a name for the new connection 8 Select a Connection Type from the drop down list Microsoft Access Microsoft SQL Server Microsoft Excel Oracle or another ODBC data source 9 Specify the database or data source to which you want to connect 10 Click the Apply button to save your settings NOTE You can also click the Advanced button to establish database connections through the Windows Data Link Properties dialog box as shown in the figure below BS Data Link Properties Connection Advanced Al Specify the following to connect to Access data 1 Select or enter a database name COCA OL Oe 2 Enter information to log on to the database User name Admin Password IW Blank password Allow saving password Test Connection C
62. h which data can be channeled from multiple data sources e g Microsoft Access Microsoft SQL Server Microsoft Excel Oracle and ODBC data sources to a visualization medium i e GraphWorX where you can view or manipulate the data Key features of data mining include e Brings database access to any OPC client e Supports many data sources including Microsoft Access Microsoft SQL Server Microsoft Excel MSDE and Oracle e Easy to use dataset support includes database table access single field read or write column read or write row read or write and two dimensional array read or write as well as simple SQL commands e Data manipulators enable access to database stored procedures and execution of complex SQL commands e Database Connection Wizard e Database object browser e Support for aliasing one data tag can access multiple database fields e Data Mining grid ActiveX control e Data Mining Configurator e Integration of data mining configuration into Unified Data Browser Data Mining Configurator User s Manual Configuring the Data Mining OPC Server The data mining OPC server can be configured using two configuration tools The first is the stand alone Data Mining Configurator shown in the figure below This document describes how to use the Data Mining Configurator 4 DBOPCServerConfigurator mdb DBOPCServerConfigurator E JO x File Edit View Go Tools Help Del Ale oo eR gt si 2 1p Fl Dat
63. ing Wizard Welcome Screen 4 Back Step 2 Choosing a Data Object In the Data Mining Configuration Wizard you have two options for the data object type Create a New Data Source or Create a New Data Manipulator as shown in the figure below A data source can be use to read data from a database It can access a specific database table directly or use a SQL read command to get data A data manipulator which is intended for processing data stored in a connected database manipulates data using a SQL write command or by executing a stored procedure Choose an option and then click the Next button to continue Database Mining Wizard Which type of data object would you like to create Wizard will create data object depending on the choice below Choose a data object type you would like to create Create new Data Source Data source object can be used for reading the data from the databases It can access the specific database table directly or use SOL read command to get data Create new Data Manipulator Data manipulator object ts intended for processing the data stored tn the databases Ib can manipulate with the data by SAL write command or by executing stored procedure There are two working objects in database mining Data Source and Data Manipulator These objects can work with the data stored in databases via reading or writing operations Access to database data is enabled via Data Source or Data Manipulator objects
64. ining Configurator User s Manual 68 Database Tables To configure a data source object with direct table access you must fill in the fully qualified database table name or view To locate specific table row s table parameters can be used Parameters define a subset of table columns used together as a filter for returned table row s Parameter values are specified by data items To get data from a specific table in the database 11 Select the Database Table option under the Specify Data Set section as shown in the figure below El Databases 2 18 AdministrationDB Marne Employees_By_PID H EE Employees By PID Desc El 1 Specify Dataset Database Table Enable Multi rows Dataset SOL Command Stored Procedure Table Mame Employees Table Parameters EL Parameter Mame Add A L Remove Edit Move Wp Move Down Enable Writes Enable Inserts Enable Deletes on TT EMPTY value 2 Set Database Polling I Enable Update Update Rate sec 1 Apply Reset Add Mew Data Source Properties Database Table 1 Inthe Table Name field click the button This opens the Unified Data Browser which lists all the tables in the database as shown in the figure below Select a table e g Employees and then click OK BS Unified Data Browser gt alo ESO Ela 2 Help Employees Tables ACCESS TABLE H E System Tables Rs JEmployees L Tables Proje
65. ke to use from the drop down list as shown in the figure below Click the Next button to continue Data Mining Configurator Connection object This wizard will create the connection object depending on the choice below 1 Enter Connection name it will be used as a part of tag identification Connection Name New Database Connection 2 Choose the tyoe of database you would like to ute Database Type MS Excel 2L5 data source MS Access MDB database MS Excel 4L5 data source MS SOL Server database Oracle SOL Server database ODBC data source lt Back Finteh Cancel Help Data Mining Wizard Creating New Database Connection 3 Specify the database as shown in the figure below which shows a Microsoft Access database connection The example below uses the Northwind mdb database which is installed with Microsoft Access Click the Next button to continue Microsoft Access Database Connection Please select a file name for M5 Access File Select or enter a M5 Access Database Name C Program Files Common Files 5 mar Tes Northwind mdb d Back Next gt Finish Cancel Help Data Mining Wizard Specifying Database 4 The Wizard displays the database connection information that you specified as shown in the figure below Click the Next button to complete the database connection NOTE You cannot go back after this step 33 Data Mining Configurator User s Manual 34 Conn
66. lue corresponds to the record number in the database For example in the First Name column in the database record number 1 is the value Nancy H Employees Table 1 Davolio sales Representative 2 Fuller TE Vice President Sales 3 Leverling Janet vales Representative 4 Peacock Margaret sales Representative 5 Buchanan steven sales Manager b Suyama Michael sales Representative F King Robert sales Representative o Callahan Laura Inside Sales Coordinator 4 Dodsworth Anne vales Representative Autohumber Record 14 4 11 LOL oF Y Employees Table in Northwind Database 16 Click the Add button in the Wizard To read the value Nancy enter 1 as the Parameter Value as shown in the figure below 26 Data Mining Configurator Enter Parameter Yalue E x Parameter Value Data Mining Wizard Defining a Value to Read 17 The value is added to the parameters to bind section as shown in the figure below To read the value Nancy enter 1 as the Parameter Value as shown in the figure below Static Tag wizard Please specify the following static tag object properties Specify Static Tag properties 1 Static Tag Mame FirstName_1 2 Data Source parameters to bind Remove Static Tag returns complete table roweet as an array Back Ment gt Finish Cancel Help Data Mining Wizard Defining a Value to Read 18 The Wizard displays the data item information that you specified as shown in
67. mand Edit M Bind Results 3 Read SQL Command Edit Back Finish Cancel Help Data Mining Wizard SQL Command Parameters i 43 Data Mining Configurator User s Manual 2 The Wizard displays the data manipulator information that you specified as shown in the figure below Click the Finish button to complete the data source connection Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for your Attribute Object type Data Manipulator Mame Employees Description Data manipulator type SAL command SAL execute command EmplopeelD Bind results TRUE SQL read command Employee DO Database Mining Wizard cannot go back after this step The data object part of the wizard will be finished Cancel Help Data Mining Wizard Confirming Data Manipulator Connection Data Manipulator Wizard Stored Procedure To configure the data manipulator properties in the Data Mining Wizard Select Stored Procedure as shown in the figure below Click the Next button NOTE You cannot go back after this step Data Manipulator wizard Please specify the following data manipulator object properties 1 Enter Data Manipulator name it will be used az a part of tag identification 2 Specity Data Manipulator Type SQL Command Executes SOL UPOATE of INSERT statement to change the data in already existing databas
68. me followed by a number The default base text is the name of the item selected for multiplication To modify the base text change the Base Text field appropriately In the First Number field specify the number to appear next to the first multiplied item In the Number of Items field specify how many items you wish to create In the Numeric Places field specify the minimum length of each number to append Values that take up less space than the specified amount of numeric places will have zeros before the number If you want to multiply all subfolders as well check the Including Subtree check box Click the OK button to do the multiplication The example configuration shown in the Multiply Item dialog box above creates three new items with the following names AdministrationDBOO1 AdministrationDB002 AdministrationDB003 All subfolders will also be multiplied Data Mining Configurator View Menu The View menu commands are listed in the table below View Menu Commands FUNCTION COMMAND SHORTCUT KEYS Toolbar _____ Toggles the toolbar Status Bar Toggles the status bar Displays items as a list Details F10 Displays items asa list along with detailed information about the configuration of each item Dialog View Toggles the configuration window right hand pane Displays a list of options for sorting the columns in the Sort By right hand pane of the screen The options listed depend on the level within the view Show Hid
69. mpact Microsoft Access databases which can be either configuration databases or historical databases using the Compact Repair MS Access Database dialog box shown in the figure below To open this dialog box select Compact Repair MS Access Database from the Tools menu Microsoft Access based databases are subject to database fragmentation over time and the support for the database will compact the target database reclaim unused space and drastically improve database performance It is critical that no users or client applications are connected to the database at the time of compacting and that if the Backup Original Database option is selected there is plenty of available hard disk space Compact Repair MS Access Database Compact Repair Connected Configuration Database f Other Database User name Admin Password M Use blank password W Backup original database before compacting starts recommended T3 Make sure that all users are disconnected from the database before the compactng epalring procedure starts Cancel Compacting and Repairing Microsoft Access Databases 59 Data Mining Configurator User s Manual Help Menu The Help menu commands are listed in the table below COMMAND SHORTCUTKEY FUNCTION Help Topics Launches the online Help for the Configurator Launches the About Box which contains information about the product version number copyright and available disk space
70. nd uses SELECT statement to get data from tables Enable Multirows Dataset possible to get more than one row as a result 3 Set database IY Enable Updates Update Rate sec fro FAO Cancel Help Data Mining Wizard Data Source Properties 9 Inthe Table Name field click the button as shown in the figure below Data Source object Please specity the following Table properties of Data Source object Specify Database Table properties of pour Data Source 1 Table Name Employees El 2 Table Parameters Index Parameter Mame 0 ErmployeelD Remove Enable Writes F Enable Inserts P Enable Deletes Back Next gt Finish Cancel Help Data Mining Wizard Selecting a Database Table 10 This opens the Unified Data Browser which lists all the tables in the database as shown in the figure below Select a table e g Employees and then click OK 23 Data Mining Configurator User s Manual MA Unified Data Browser gt ala 11 52 Employees Tables AES ACCESS TABLE ee Views Categories H H D System Tables Customers E Tables fe Employees Order Details Orders Products Shippers Selecting a Database Table 11 If you view the database table in Microsoft Access you can see the column names e g Employee ID Last Name First Name etc in the Employees table as shown in the figure below The next step is to select a column name from which to
71. ndows has an installed automation object that has the capability to work with XML Thus programmers can create modify their configurations outside the Configurator using Visual Basic if desired XML supports schemas A schema is a special XML file that specifies the data structure of an XML data file To import data select XML Import from the File menu This opens the Import XML File dialog box as shown in the figure below Give the file a name and then choose the directory from which you want to import the data You can then specify the delimiters and choose from the following import settings Click Open 54 Create new items When the import file contains items that are not yet in the configuration database then it creates them Otherwise it skips these items Update existing items When the import file contains items that are in the configuration database then it updates them using data from the import file Otherwise it skips these items Data Mining Configurator NOTE Either Create new items or Update existing items must be selected Otherwise there is nothing to import Selecting XML Validate from the File menu does not import an XML data file but it will try to validate its structure using stored XML schema Once it passes this validation the XML file is acceptable for import by the Configurator Import XML File Borland Chaintast Common Files ACD systems Adaptec Adobe Avantan Connect
72. ng Configuration Wizard The following example walks you through the steps in creating a simple Microsoft Access database connection using the Data Mining Configuration Wizard To run the Data Mining Wizard 1 Select Configuration Wizard from the File menu as shown in the figure below File Edit View Go Tools Help Mew Ctrl HN Open Ctro Save As Connection Properties Configuration Wizard Ctrl Cow Export LS Import Starting the Data Mining Configuration Wizard 2 The introduction screen for the Data Mining Wizard appears Click the Next button to continue 19 20 Data Mining Configurator User s Manual Welcome to the Smar Data Mining Configurator Wizard This wizard helps you to set up the configuration for database mining The wizard allows to create connections data sources data manipulators and data item objects To continue click Next Finish Cancel Help Data Mining Wizard Welcome Screen 3 You have two options Create a New Data Source or Create a New Data Manipulator as shown in the figure below A data source can be use to read data from a database It can access a specific database table directly or use a SQL read command to get data A data manipulator which is intended for processing data stored in a connected database manipulates data using a SQL write command or by executing a stored procedure Choose an option and then click the Next button to continue
73. ng configuration database by selecting Make Active from the File menu 21 You need to configure your data mining connections so you can view data coming from the database Create a process point in GraphWorxX by selecting Intrinsics gt Process Point from the Dynamics menu as shown in the figure below Data Mining Configurator Dynamics Tools Runtime Help Actions d Selectors A Intrinsics Time Date Set Pick Tab Order E Sees Pushbutton Hh el Velie Ree co Checkbox l Edit Local Aliases i Radio Button Edit Connections Display Button Wizard Creating a Process Point in GraphWorX 22 This opens the Property Inspector for the data item Click on the PPT DE tab as shown in the figure below Click the Data Tags button Property Inspector E x Test PPT DE DataSource Drag Drop Data Source Jatabase Connection Employees FirstMame 1 Data Tags Expression Point Type Y Update OPC Request Type STRING Client Display Type STRING Data Entry E Conf E Format Keypad Initial Value Show Unit Thousands Separators Unit Position E I eadinn Ferms Property Inspector for FirstName_1 Data Item 23 The Unified Data Browser opens to the Database Access tab as shown in the figure below You can see that the browser tree control is similar to the tree control in the Data Mining Configurator you can choose from configured da
74. nipulation SQL command e g you can read the ID of a newly created record Stored Procedure Stored procedures are the preferred way of using data manipulators They are much more flexible than SQL commands and usually are faster in processing Their wide range of capabilities allows for complex database data manipulation To get data from a stored procedure in the database 1 Select the Stored Procedure option under the Specify Data Manipulator section as shown in the figure below Fl Databases Sie ai AccessConnection EE Datasource01 E Datasource02 E DataSourced3 E DataSourced4 E DataSourceds E DataSource E DataSource E DataSourced9 E DataSourceio E Datasourcel 1 E DataSourcel i Oe DataManipulator ExcelFile Name DataManipulator Desci Insert a new row and view khe row Specify Data Manipulator f SOL Command Execute SOL Command Edit Z Bind Results Read SGL Command Edit SL gommand Parameters Parameter Mame Add Categoryhame T_BSTR 1 Description T_BSTR Remove Edit Move Up Move Daw EEE I Stored Procedure Procedure Mame Sales by Year Apply Reset Add New L Data Manipulator Properties Stored Procedure 2 In the Procedure Name field click the button This opens the Unified Data Browser which lists all the stored procedures in the database as shown in the figure below Select a procedure
75. nter a SQL command to read from the database 1 Select the SQL Command option under the Specify Data Manipulator section as shown in the figure below 73 Data Mining Configurator User s Manual 74 E Databases E E AccessConnection Mame DataManipulator EE DataSourcedl E Datasource02 E DataSourced3 5 DataSourcel4 Specify Data Manipulator E DataSourceds E Datasourced Desc Insert a new row and view khe row f SOL Command Me E DataSource Execute SOL Command Edit E Datasource09 4 Bind Results Ej DataSource 10 Read SQL Command Edit E Datasourcel 1 E ataSourcel 7 SQL Command Parameters P DataManipulator Parameter Name Add H B ExcelFile 0 CategoryName T_ESTR 1 Description VT_BSTR Renae Edit Move Up Move Lovin EEE Stored Procedure Procedure Wane lt Enter stored procedure name gt pply Heset Add Mew E Data Manipulator Properties SQL Command 2 In the SQL Command Parameters field click the Add button Enter a Parameter Name i e column name and choose one of the following Parameter Data Types from the drop down list as shown in the figure below Click OK NOTE If you have multiple values parameters you can bind them by checking the Bind Results check box e VT_14 4 byte integer e VT_R8 8 byte floating point number double e VT_ DATE Date in OleDateTime format the DATE type is implemented using an 8 byte floating
76. onfigurator shown in the figure below Click the Next button to continue Microsoft Excel Datasource Connection Please select a file name for MS Excel File Select or enter a M5 Excel File Mame EE I Use First Row as Field Names IF the first row of your Excel data source contains columns heading they can be used as Held names Back Einst Cancel Help Data Mining Wizard Microsoft Excel Connection Microsoft SQL Server Wizard To create a Microsoft SQL Server database connection in the Data Mining Wizard select a SQL Server from the drop down list The default SQL server is local Then select the database on the specified SQL Server file as shown in the figure below By default SQL Server database connections use Windows integrated security Click the Next button to continue Microsoft SOL Server Database Connection Please specify the following to connect SOL server 1 Select or enter a database name Simple lt lt 2 Select or enter a server name local Refresh 3 Enter information to log on to the server f Use Windows NT integrated security recommended Use a specific user name and password User name Password Blank Password Back Finish Cancel Help Data Mining Wizard Microsoft SQL Server Connection 35 Data Mining Configurator User s Manual Oracle Wizard To create an Oracle database connection in the Data Mining Wizard specify an Oracle database
77. onfigured data items from the Employees By_PID data source connection E Unified Data Browser e gt Smar DatabaseOPCserver 3 A4dministrationDB Employees By PID Mame 2 OPCDA Database Access Global aliases Graphworx Use Specific Column Fly Databases Elf AdministrationDiB El surname_3 Surname 3 kini Employees By PID EN surname Surname Salary Salary Salary PID PID PID Aa La RA Po oe PQ Pi Mame Unified Browser Browsing Databases For example in the figure above the Name_2 item ID has been selected The full ItemID is SMAR DatabaseOPCServer 3 AdministrationDB Employees_By_PID Name_2 Each item ID in the Database Access tab contains the following information 86 Data Mining Configurator Server name e g SMAR DatabaseOPCServer 3 Database connection name e g AdministrationDB Data source name e g Employees By_PID Data item name e g Name_2 Data Mining Examples The ProcessView installation includes several data mining example files in the Program Files Smar ProcessView Examples Database Mining Examples directory Example 1 Data Mining Configuration Demo The ProcessView installation provides a default Data Mining Demo configuration To view the demo files go to the C Program Files SMAR PROCESSVIEW Examples Database Mining Examples directory in the ProcessView installation This directory contains the following files e DBOPCServerConfigurator mdb This Microsoft
78. only Finish Cancel Help Data Mining Wizard Data Object Options 31 Data Mining Configurator User s Manual 32 Step 3 Connecting to a Database After you select a data object data source or data manipulator the next step in the Data Mining Wizard is to connect to a database i e where to get the data Note For additional information about configuring database connections please see the Database Connections section later in this document 1 You have two options for database connection Create a New Connection or Use an Existing Connection as shown in the figure below Choose an option and then click the Next button to continue Connection object wizard Please select whether you want to create a new connection or use an existing connection Choose a new database connection or use an exishng connection for your new Data Object f Create new connection This will create a new connection object in the configuration fou can select the type of the connection and set its properties Use existing connection Select the name of the connection that you want to use for your date object Select connection name gt lt Back Finish Cancel Help Data Mining Wizard Database Connection Options 2 Inthe Connection Name field enter a name for the new database connection Then select the type of database e g Microsoft Access Microsoft SQL Server Microsoft Excel or Oracle you would li
79. p Data Mining Configurator Data Manipulator wizard Please specify the following data manipulator object properties 1 Enter Data Manipulator name it will be uted as a part of tag identification Mame Employees 2 Specify Data Manipulator Type f SOL Command Executes SOL UPDATE or INSERT statement to change the data in alread existing database tables Stored Procedure Calls stored procedure to change the data in already existing database tables Back Finish Cancel Help Data Mining Wizard Data Manipulator Properties 1 In the SQL Command Parameters field click the Add button Enter a Parameter Name i e column name and choose a Parameter Data Type from the drop down list To view or modify the SQL query click the Edit button This opens the SQL Command Editor which enables you to manually edit the query string Click the Next button When the Bind Results check box is checked it executes binding of a SQL query in the same transaction and returns its rowset as output parameters This allows you to read some database data immediately after executing a data manipulation SQL command e g you can read the ID of a newly created record Data Manipulator object Please specify the following SQL command properties of Data Manipulator object Specify SOL Command properties of pour Data Manipulator 1 SQL Command Parameters Parameter Name Add Employeel O VT_BSTR Remove 2 Execute SQL Com
80. point number Days are represented by whole number increments starting with 30 December 1899 midnight as time zero Hour values are expressed as the absolute value of the fractional part of the number e VT_BSTR String e VT_BOOL Boolean Data Mining Configurator Mame DatalManipulator Desc Insert a new row and view che row Specify Data Manipulator f SOL Command Execute SOL Command Edit 4 Bind Results Read SOL Command Edit HE 1 SOL Command Parameters Add Remove Edit Maye Up Parameter Data Type VT_ESTR crcl Apply Reset Add Mew Move Down y MEA Specifying SQL Parameter Name 3 To view or modify the SQL query click the Edit button This opens the SQL Command Editor which enables you to manually edit the query string as shown in the figure below Click OK SOL Command Editor nsert into Categories CategoryName Description values 7 7 SQL Command Editor 75 Data Mining Configurator User s Manual 76 You can also change the column ordering in the data source configuration by selecting a column and clicking the Move Up or Move Down buttons Click the Remove button to delete the selected column s Bind Results When the Bind Results check box is checked it executes binding of a SQL query in the same transaction and returns its rowset as output parameters This allows you to read some database data immediately after executing a data ma
81. r MS Access Database 5 Name_3 i E PDI Process iew Examples DEMO DatabaseMiningladministration mdb Browse fig PID_2 E PID_3 Salary_1 E Salary E Salary_3 El Surname_i si es Surname Click to Advanced button to set other connection Advanced g Surname_3 Add Mew Ready 1 Objeck s E Data Mining Configuration Demo Example 2 Data Source Configuration Examples The second data mining example provides a data mining configuration database with several different types of preconfigured data sources For this example the PROCESSVIEW installation includes the following data mining example files in the Program Files SMARIPROCESSVIEW Examples Database Mining Examples directory 87 Data Mining Configurator User s Manual 88 DBOPCServerExample mdb This is an example data mining configuration database that contains many example data sources and data tags Northwind mdb This is an example Microsoft Access database to which the example data mining configuration database is connected DBOPC PainReliefRECIPE XLS This is an example Microsoft Excel workbook to which the example data mining configuration database is connected DBOPC_GlobalAliasCfg mdb This is a global aliasing configuration database that is used with the Excel data source example In order to use the Excel example you must open this database in the Global Aliasing Configurator and activate the database by selecting Make Active from
82. r name e g SMAR DatabaseOPCServer 3 Database connection name e g AdministrationDB Data source name e g Employees_By_PID Data item name e g Name_2 MM Unified Data Browser J x e gt a 1 E 2 6 E X Hee Smar DatabaseOPlserver 31AdministrationDB Employees _By_FPID Name_2 opc Da Database Access Global Aliases Graphw ort Name Use Specific Column Column Name Parameters E surname_3 True Surname surname_2 True Surname surname_1 True Surname E Salary_3 True Salary l Salary 2 True Salary Salary _1 True Salary pI 3 True PID GRUE True PID 5 PID_1 True PID Ga Name_3 bebe PJ ame z M ame E Databases E A AdministrationDE z Employees By PID True Name eee ee MM w oe PD FP 0 Data Item Specified in Unified Browser 6 Close out of the Unified data Browser and the Property Inspector dialog box 7 Enter the GraphWorX display into runtime mode You can see the data coming from the Employee database table as shown in the figure below Notice that the Parameter Value for data item Name_2 is resolved as Russel in the visualization display Note The Static definition section simply displays the values from the Employee database table for the configured data items but the data cannot be manipulated The Dynamic definition section also displays values from the Employee database table but in this case only one row of data is displayed at a time and
83. read the data For example we will use the First Name column which lists several employee names e g Nancy Andrew Janet etc H Employees Table 1 Davolio 2 Fuller rem 3 Leverling Janet 4 Peacock Margaret 5 Buchanan steven b Suyama Michael F King Robert o Callahan Laura 9 Dodsworth Anne Autohumber Record 14 4 1 LOIL oF Y sales Tee Vice President Sales vales Representative sales Representative sales Manager sales Representative sales Representative Inside Sales Coordinator vales Representative Employees Table in Northwind Database 12 Click the Add button in the wizard This opens the Unified Data Browser which lists all the columns in the database table as shown in the figure below Select the column name e g FirstName from the database table 24 Data Mining Configurator MA Unified Data Browser ao 1 E re Firsthame Rowset Columns aie Employees Country Employee Extension HireDate HomePhone Lasthlane Selecting Column From Database Table 13 The Wizard displays the data source information that you specified as shown in the figure below Click the Next button to complete the data source connection The next step is to define data items values to read from the data source You have the option of creating a static data item or a dynamic data item option depends on the type of database connection A static data item simply displa
84. rmation in the Employees database table in the Administration database For example the itemID AdministrationDB Employees_By_PID Name_2 has the value Russel because Russel is the second name listed in the database table as shown in the figure below pe administration Database i e D EG en n hee anew x jaa E ghee 2d Create table in Design view Tables Create table by using wizard Quel eri es Create table by entering data Employees E Forms T ER 5 ProjectDetails Reports E Employees T Table a Surname Tit do Bob Richards Applications Engineer TEE EEE 2 Russel amith Applications Engineer Product Development 3 Dave Jones GA Engineer Product Development 4 Arthor Anderson Sales Representative Sales and Marketing O Employees Table in Administration Database How do you determine which value to display for each itemID This is defined in the data item properties For example click on the Name_2 data item in the tree control under the Employees By_PID to view the data item properties as shown in the figure below 11 Data Mining Configurator User s Manual E Databases B E AdministrationDe E Employees_Ey_PID To select a column name from the database table choose Specific Column and then click the button next to the Column Name field as shown in the figure above This opens the Unified Data Browser which displays a list of columns in the Employees database table as shown
85. rs in the Export Configuration Data to File dialog box the file uses Commas as delimiters by default Each group contains headings and columns that provide information about each item such as descriptions and associated translations and expressions It also provides the tree pathway for each item Choose the directory to which you want to export the data from your database In the Save As Type field choose the file type txt or csv that you would like to save 51 Data Mining Configurator User s Manual 52 Export all the configuration data ad I ma q BS ci 2 MMtSounds VE a Pager WCRSkins JS FontInstall Script Fonts Scriptwizard T GenBroker Snapshots File name Save as Ippe Mex files cev Cancel Delimiters Tab Comma Other ES Exporting Configuration Data Exporting Data to an XML File The Configurator also allows you to export data from your configuration database to an XML file The XML export import functionality was mainly developed for Windows platforms that do not support databases e g Windows CE and Windows Embedded XML has the following advantages over the CSV import export function To export data select XML Export from the File menu This opens the Export XML File dialog box as shown in the figure below Give the file a name and then choose the directory to which you want XML has a standardized format unlike the text CSV format which uses
86. s a new item depending on what is selected in the tree control CTRL R Renames the selected item Multiply CTRL M Opens the Multiply Item dialog box which allows you to multiply an item in the tree control CTRL DEL Deletes the selected object Cut CTRL X Cuts the selected object from the view and places it on the clipboard CTRL C Copies the selected object to the clipboard CTRL V Pastes the last object placed on the clipboard Select All CTRL A Selects all objects in a list The selection is shown in the upper right hand section of the viewer Invert Unselects all selected items and selects all unselected Selection items in a list in the upper right hand section of the viewer Multiplying Items The Configurator allows you to multiply items in the tree control Multiplication provides a simple way of developing configurations where there are many similar items in a given category To multiply an item 1 Select the item in the tree control that you wish to multiply 2 Either right click the item and select Multiply from the pop up menu or select Multiply from the Edit menu This opens the Multiply Item dialog box shown below Multiply Item E x First number K fi Numeric places 3 Cancel 3 Humber of items i Including subtree Base text dministrationD E ltem name address Space dministrationDE Multiply Item Dialog Box When the items are multiplied they are all given a base na
87. s are listed in the table below COMMAND SHORTCUT KEY FUNCTION CTRL N Creates a new configuration database CTRL O Opens an existing configuration database Save As Saves the current database under a different name as a Microsoft Access mdb file Connection Displays the current database connection properties Properties Configuration Launches the Data Mining Wizard Wizard Exports configuration data from your database to a CSV Export CTRL W text file txt or a Microsoft Excel file csv You can specify the delimiters and what to export Imports data into your configuration database from a CSV Import text file txt or a Microsoft Excel file csv You can then specify the delimiters and choose from the import settings XML Export Exports configuration data to an XML file XML Export A Exports configuration data to an XML Schema file Schema XML Import Imports configuration data from an XML file This feature does not import an XML data file but it will try to validate its structure using stored XML AEN ARANG schema Once it passes this validation the XML file is acceptable for import by the Configurator Activates the current database If this command is not available then the current database is already the Make Active active database Exit Closes the application Database Connection Properties Selecting Connection Properties from the File menu opens the Database Connection Properties di
88. se It can access a specific database table directly or use a SQL read command to get data Configuring a data source requires the following basic steps as shown in the figures below 7 Inthe Name field type a name for the new data source 8 Specify a Data Set Database Table or SQL Command to mine within the connected database 9 Database polling properties should be specified for any data source object Specify an Update Rate in seconds to poll for updates from the database The Enable Update check box must also be checked in order to specify an updated rate When updates are disabled then the data mining server scans the database only once when the first data item is requested or on the user request invoked by refreshing the data item 10 When you have finished configuring the data source properties click the Apply button The new data source appears under the database connection in the tree control E Databases E E AdministrationDB Mame Employees_By_PID H E Employees By PID Desc El 1 Specify Dataset Database Table Enable Multi rows Dataset SOL Command Stored Procedure Table Mame Employees ES Table Parameters Parameter Mame Add PID Remove Edit Move Wp Pave Dai Enable writes Enable Inserts Enable Deletes on v7 EMPTY valued 2 Set Database Polling I Enable Update Update Rate sec 1 Reset Add Mew Data Source Properties 67 Data M
89. sentative Sales and Marketing Employees Table in Administration Database Starting the Data Mining Configurator To start the Data Mining Configurator 1 2 From the Windows Start menu select Programs gt Smar ProcessView gt Tools gt Data Mining Configurator This opens the Configurator as shown in the figure below The screen consists of a split window with a tree control view in the left hand pane and a configuration view in the right hand pane The Configurator provides a standard format for the configuration database as well as a 16 Data Mining Configurator sample default configuration project The Configurator also includes a toolbar and menus with many command functions File Edit View Go Tools Help Do Ree A Se gt ea ce 0 9 32 Databases Ready 1 Objects E MER we Configurator Screen Creating a New Configuration Database To create a new configuration database in the Configurator 1 Select New from the File menu as shown in the figure below File Edit View Go Tools Help Open Cbrl 0 Save AS Connection Properties Configuration Wizard Ctrl LS Export 5 Import Creating a Configuration Database 2 Inthe Save New Database dialog box select the database type from the Save As Type drop down list as shown in the figure below Browse for the target directory give the file a name and then click the Save button Save New M5 Acce
90. sing all values from passed array overrides database default values and parameter values 5 Deletes table row with PID 11 Writing VT_EMPTY value into data item used in previous example will delete row with PID 11 8 bytes floating point number 3 14 or 1 82E 3 double Data Mining Configurator Data Items for Data Manipulators There are two types of data items that can access data manipulator objects e Execute data item e Result data item The data manipulator object executes its action asynchronously unique request identification is needed to pair requests and responses as shown in the table below NOTE ProcessView includes two components for accessing data manipulator objects synchronously DBOPCManipulatorWrapper and the Data Manipulator ActiveX control DESCRIPTION Writing to this data item executes a data manipulator action stored procedure or SQL commana The actual value written to this tag must conform to the following rules Execute Write It must be an arrary of VARIANTs The first value in this array is the unique request identification The rest of the values are used as input parameters for data manipulator Returned values represent responses for execute requests All the returned values are stored in an array of VARIANTs Result Read Similar to the Execute data item the first value in this array is the unique request identification used in execute data item The rest of the values are outpu
91. ss Database Save ir E3 Bin Backups d 4 File name DBOPCS erverConfigurator Test mdb Save as type Microsoft Access Databases mdb Lancel Saving the New Configuration Database 17 Data Mining Configurator User s Manual Data Mining Configuration Wizard The Data Mining Configurator provides a Data Mining Configuration Wizard that helps you create and configure your database connections You can also configure your data mining settings in the Database Access tab in the Unified Data Browser For more information please see the Unified Data Browser help documentation To run the Data Mining Configuration Wizard 1 Select Configuration Wizard from the File menu as shown in the figure below File Edit View Go Tools Help Mew Ctrl N Open Ctro Save As Connection Properties Configuration Wizard Ctrl Cov Export LS Import Starting the Data Mining Configuration Wizard 2 The introduction screen for the Data Mining Wizard appears Click the Next button to continue Welcome to the Smar Data Mining Configurator Wizard This wizard helps you to set up the configuration for database mining The wizard allows to create connections data sources data manipulators and data item objects To continue click Next Finish Cancel Help Data Mining Wizard Welcome Screen Back 3 You have two options Create a New Data Source or Create a New Data Manipulator
92. t parameters if there are any DATA ITEM ACCESS NAME RIGHTS Creating a New Data Item To create a new data item 1 Right click on a data source in the tree control of the Configurator and select New gt Data Item from the pop up menu as shown in the figure below Databases E E AdministrationDBE Name Employees_By_PID as tEmployees By PI Rename Multiply Creating a New Data Item 2 The properties dialog box for the new data item appears in the right hand pane of the Configurator as shown in the figure below 79 Data Mining Configurator User s Manual E A Databases 2 12 AadministrationCiB Mame Mn E E Employees_By_PID EN 1 Bind Dataset Parameters Remie Move Up Move Down 2 vee Salary 3 z surnarme_1 2 Specify Dataset Columns po Surname_2 Surname 3 Specific Column Column Mame Mame Complete Rowset returned as an array EL Setting the Properties for the New Data Item In the Name field type a name for the new data item Click the Add button to enter data set parameter values Select the data set columns table for the data item You can select a Specific Column or use a Complete Rowset When you have finished configuring the data item properties click the Apply button The new data item appears under the data source in the tree control SN HRS Data Item Properties 80 1 Configuring a data source requires the
93. ta items from the Employees data source connection Select the FirstName_1 data item Notice that the FirstName_1 item ID e g SMAR DatabaseOPCServer 3 New Database Connection Employees FirstName_1 contains the following information e Server name e g SMAR DatabaseOPCServer 3 e Database connection name e g New Database Connection e Data source name e g Employees e Data item name e g FirstName_1 29 Data Mining Configurator User s Manual 30 MN Unified Data Browser lx A F m e e gt 6 2 SI gly E x Qrep SMAR DatabaseOPCSerrver 3 New Database Connection Employees Firsidame_1 opc DA Database Access Global Aliases Graphworx S Databases Name Use Specific Column Column Name Parameters Hel New Database Connection ES Firstidame_1 True FirstName ME Last5ca 1 Refresh Recordset E Column Wead conce New Database Connection Shown in Unified Data Browser 24 Close out of the Unified Data Browser and the Property Inspector dialog box 25 Enter the GraphWorX display into runtime mode You can see the data coming from the Employees database table The Parameter Value for data item FirstName_1 is resolved as Nancy in the visualization display Basic Steps in the Data Mining Configuration Wizard Using the Data Mining Configuration Wizard to configure your da
94. ta values from the connected database The table in the static definition section contains some column names from the Employees database table as shown in the figure below e Name Employee s first name e Surname Employee s last name e Salary Employee s salary 13 Data Mining Configurator User s Manual e PID Employee s identification number Each column contains process points that correspond to data items e g Name_1 Name_2 Name_3 PID_1 PID 2 etc in the Employees By PID data source from the demo data mining configuration A process point must be configured for each data item We will continue to look at the Name_2 data item as an example 3 Under the Name column in the GraphWorX display double click the second process point under the Name column as shown in the figure below e DBOPC gdf GraphWorX by Smar 3 i ioj xj File Edit View Format Arrange Draw Dynamics Tools Runtime Help asime ttet lA 2 pe 900 a ce i a A GS eG E ee arial Unicode ms Y fio B E u Faoa li e eal ea a 1 24 R Static definition O Name Surname Salary in PID 22 P2229 22 PERO PORE PAPI ic E B ql PPPPO PEPE BE PRPPRERERE PORT PE POP ROR 222 2 om A POP PEE PROPER ERE POPPER PPP PROP RP Pen 4 ee ae 4 Dynamic definition i Name Surname Salary in PID PID _ oO PPP PP PPP POP PPP PP PPPPPEPEP Seibel Sees aces unis PID PID_2 del E as 3 PID FI z E
95. tabase connections involves the following basic steps Step 1 Start the Data Mining Configuration Wizard Step 2 Choose a data object data source or data manipulator you would like to create Step 3 Create a new database connection or choose an existing database connection Step 4 Configure data source or data manipulator properties Step 5 Configure data item properties for a data source only The new configuration that you created using the Wizard appears in the Data Mining Configurator Before you can visualize the data you must activate the data mining configuration database by selecting Make Active from the File menu Step 1 Starting the Data Mining Configuration Wizard To start the Data Mining Configuration Wizard 1 Select Configuration Wizard from the File menu as shown in the figure below File Edit View Go Tools Help Mew Ctrl h Open Chido Save As Connection Properties Configuration Wizard Ctrl CSW Export Coy Import Starting the Data Mining Configuration Wizard Data Mining Configurator 2 The introduction screen for the Data Mining Wizard appears Click the Next button to continue Welcome to the Smar Data Mining Configurator Wizard On This wizard helps you to set up the configuration for database mining The wizard allows to create connections data sources data manipulators and data tem objects To continue click Mert Einst Cancel Help Data Min
96. tem in the ALT Home Moves the cursor up to the first item in the tree Moves the cursor down to the last visible item in the ALT End es Next Pane Moves the cursor to the next pane Previous Pane SHIFT F6 Moves the cursor to the last pane used Tools Menu The Tools menu commands are listed in the table below m 3 o I O 9 Tools Menu Commands COMMAND FUNCTION Launches the Options dialog box Compact Repair MS Opens the Compact Repair MS Access Database dialog box Access Database Options To choose additional settings select Options from the Tools menu This opens the Options dialog box as shown in the figure below The General tab of the Options dialog box contains the following options e Automatically apply changes when selection is changed Checking this option allows changes to the configuration database to be saved each time you switch dialogs without clicking on the Apply button or being shown a message asking if you would like to apply changes e Enable hover selection Checking this option allows you to highlight an item by moving the mouse pointer over that item and keeping it there for a specified amount of time in milliseconds Data Mining Configurator General Workspace settings Automatically apply changes when selection is changed e Enable hover selection Hover Time FOO m Options Dialog Box General Tab Compacting and Repairing Microsoft Access Databases You can co
97. the File menu DBOPC_Excel gdf This GraphWorX display allows you to view and write to Excel cells that are included in the DBOPC PainReliefRECIPE XLS spreadsheet In order to use the Excel example you must open the DBOPC_GlobalAliasCfg mdb database in the Global Aliasing Configurator and activate the database by selecting Make Active from the File menu DBOPC_Access1 gdf This GraphWorX display contains single value database tags extended syntax tags and writeable tags DBOPC_Access2 gdf This GraphWorX display contains tags that return a single dimensional recordset connecting to a database table in the Data Mining Activex DBOPC_Access3 gdf This GraphWorX display contains tags that return a two dimensional recordset connecting to a database table in the Data Mining ActiveX DBOPC_Access4 gdf This GraphWorX display contains tags that return a single dimensional recordset connecting to a database table using a SQL command in the Data Mining ActiveX DBOPC_Access5 gdf This GraphWorX display contains tags that return a two dimensional recordset connecting to a database table using a SQL command in the Data Mining ActiveX DBOPC_GlobalAlias gdf This GraphWorX display contains an example of how to use global aliases as tags in the Data Mining ActiveX To view the example configuration database 1 Open the Data Mining Configurator from the Windows Start menu by selecting Programs gt SMAR PROCESSVIEW gt Tools gt Data Mining Configurator
98. the Name column record number 2 corresponds to the second value in the column Thus the value for record number 2 is Russel which is highlighted in the figure below Thus the Parameter Value for data item Name_2 will be resolved as Russel as shown in the figure below E Employees Table SB TT TT a 1 Bob Richards Applications Engineer Product Development Ss Applications Engineer Product Development 4 Arthor Anderson Sales Representative Sales and Marketing E F Fe J eE Jones GA Engineer Product Development K 0 Record 14 4 z gt er r ar 4 Record Number in Database Table GraphWorX Data Visualization Demo Now that you have seen how the demo database connection demo data source and demo data items are configured in the Data Mining Configurator the next step is to visualize the data in GraphWorX The Data Mining Demo provides an example GraphWorX display that shows how to configure your data mining connections so you can view data coming from the database 1 Go to the C Program Files Smar ProcessView Examples Database Mining Examples directory in the PROCESSVIEW installation and open the DBOPC gdf file 2 The display opens in GraphWorX configuration mode as shown in the figure below The display contains two different examples of data mining visualization static and dynamic A static display simply displays the data values from the connected database A dynamic display allows you to manipulate da
99. the following import settings e Create new items When the import file contains items that are not yet in the configuration database then it creates them Otherwise it skips these items e Update existing items When the import file contains items that are in the configuration database then it updates them using data from the import file Otherwise it skips these items NOTE Either Create new items or Update existing items must be selected Otherwise there is nothing to import e Display errors When this item is checked the Configurator shows a dialog box if an error occurs and then asks you if you want to proceed with the import When it is not checked it skips all items where an error occurred Import configuration data Default MDEs Images Ibi 2 MMYSounds a _ Pager FontInstall JScript J scriptwWizard T SnapShots cece F on t 5 L GenBroker File name Files of type Text files csv Cancel Import settings IW Create new items Insert Missing Parent ltemz Comma Other Update existing items Display errors Importing Configuration Data 53 Data Mining Configurator User s Manual When you have selected a file to import click Open When the import is completed the File Import Results dialog box opens as shown below This shows the import settings including the input file name It also provides a summary of the import including how man
100. tor User s Manual Enter Parameter alue E xi Parameter Value Data Mining Wizard Defining a Value to Read 4 The value is added to the parameters to bind section as shown in the figure below To read the value Nancy enter 1 as the Parameter Value as shown in the figure below Static Tag wizard Please specity the following static tag object properties Specify Static Tag properties 1 State Tag Mame First ame_1 Data Source parameters to bind Remove Static Tag retume complete table rowset as an array Back Mest gt Finish Lancel Help Data Mining Wizard Defining a Value to Read 5 The Wizard displays the data item information that you specified as shown in the figure below You have the option of creating additional data items tags Click the Finish button to complete the configuration 48 Toolbar i O Bes Static Tag object The wizard will create the new Static Tag object with the following attributes Database Mining Wizard creates the following Static Tag for your Attribute Name First ame_ Database Mining Wizard cannot go back after this step The static tag part of the wizard will be finished Cancel Help Data Mining Wizard Confirming Data Item Value The Configurator toolbar shown below contains the following command buttons To show or hide the Standard toolbar select Toolbars gt Standard Buttons from the View menu
101. tput then every column oriented data item will return a single value In this case you can request it as a process point and modify it After check Enable Writes when should I check Enable Inserts In case the related table row is missing from the table then after writing it will create this table row using a combination of known parameters and database default values What is a data manipulator A data manipulator object allows you to execute stored procedures or stand alone SQL queries and get results from them if there are any There are two special data items to manipulate it Execute Results You can execute stored procedures by writing into the Execute data item where the value should be a safe array of VARIANT values The first value is request identification and the rest of the values are input parameters for this procedure You will get results from Result data item The values have a structure similar to the execute data item It is again an array of VARIANT values where the very first value is the request identification and the rest of the values are output parameters What does Bind Results for SQL Command do and why is the Edit button available What should I be able to do with this button The SQL Command section in the data manipulator configuration was designed for databases such as Microsoft Access that do not have stored procedures support implemented or have very poor support Th
102. u LA a E Ui tat a Fa E Datasource06 vie E Tag E DataSourced E En Piee Tag he Tags Click to Advanced button to set other connection Advanced Rite E Tagi0 Apply lesel Add Mew 1 E a lc gu Li o Cc Fan C co wo O E a co cu Eu o la E lt x Ci D AR ie Es Tagli ite Tagi E Datasource12 ae Es Tagl3 E Writevalue ES Writel ee Wribe2 i E DataManipulator e ExcelFile E E Sheeti 1 El I gu cr al i o ci E E Data Mining Example Configuration Database The first is a connection to the Northwind mdb Microsoft Access database as shown in the figure below This database connection reads data from reads data from the Customers table in the Northwind database The figure below shows this table as viewed in Microsoft Access As you can see it has several columns of data e g Customer ID Company Name Contact Name Contact Title etc 89 Data Mining Configurator User s Manual 90 E Customers Table eustomer ID Company Name Alfreds Futterkiste Mara Anders Sales ET Obere str 57 Ana Trujillo Eriparedadc Ana Trujillo Owner Awda de la Constitucic Antonia Moreno Taquerl Antonio Moreno Owner Mataderos 312 Around the Horn Thomas Hardy Sales Representative 120 Hanover Sq Reralunds snahhk e Christina Reralor Order Administrator Ferauvswarian A gee Northwind Database is Create table in Design view
103. u as shown in the figure below Flv Databases agr ArcessConmectio 1 onnection hlarma FA A ee Datasource Ctrl D Data Manipulator Ctrl M Rename Multiply r MS Access Database Delete lesiMicrosork Office Orrice Samples Creating a New Data Manipulator 2 The properties dialog box for the new data manipulator appears in the right hand pane of the Configurator as shown in the figure below E Databases SAri AccessConnection Hame DataManipulator EE Datasource01 EE DataSourced2 FE DataSource E DataSourcel4 Specify Data Manipulator gt E DataSourceds E DataSourced Desc Insert anew row and view the row f SOL Command i EJ DakaSourcet Execute SOL Command Edit E Datasource09 4 Bind Results Ey DataSourcel0 Read SQL Command Edit a Datasourcel 1 E DataSource12 SOL Command Parameters ee ae 6 38 ExcelFil 0 CategoryName YT_ESTR 1 Description VT_BSTR REMOVE Edit Mave Up EER Mave Down Stored Procedure Procedure Wane lt Enter stored procedure name gt ppl Reset Add New Setting the Properties for the New Data Manipulator 3 Inthe Name field tyoe a name for the new data manipulator 4 Specify the type of data manipulator to use SQL Command or Stored Procedure If you use a SQL command click the Edit buttons to create edit your SQL queries If you choose stored procedure click the button to select a stored procedure
104. ures offer wider functionality and because they are precompiled on the server execution is much faster 3 When you have finished configuring the data manipulator properties click the Apply button The new data manipulator appears under the database connection in the tree control SQL Commands The SQL command data manipulators are mainly designed for databases that don t support stored procedures MS Excel partly MS Access etc It allows configuring two SQL commands one for actual data manipulation action the second for getting results if needed Setting the parameters is similar to SQL commands in the data source configuration Result SQL command is using the same set of parameters as Execute SQL command in data manipulation action SQL queries commands in a data manipulator object are mainly designed for databases that do not support stored procedures e g Excel MS Jet You can configure two SQL commands one for the actual data manipulation action the second for getting results if needed Setting the parameters is similar to SQL commands in the data source configuration The Result SQL command uses the same set of parameters as the Execute SQL command in a data manipulation action In all other cases stored procedures functions should be used to manipulate database data The reason for this is that stored procedures offer wider functionality and because they are precompiled on the server execution is much faster To e
105. various delimiters e g TAB instead of commas strings could not accept all characters etc XML is language independent whereas CSV converts date time floats and currency fields according to local settings in Windows For example using CSV you cannot export data on German Windows and import it on English windows without making changes Windows has an installed automation object that has the capability to work with XML Thus programmers can create modify their configurations outside the Configurator using Visual Basic if desired XML supports schemas A schema is a special XML file that specifies the data structure of an XML data file to export the data from your database Click Save You can also export configuration data to an XML Scheme file by selecting XML Export from the File menu NOTE Data Mining Configurator 2 x Save in E Smar da E Ej EEE w UniversalbrowserGul xml File name DBOPCS erer Configurator sm Save as type KML Files nl Cancel Exporting Configuration Data to an XML File Importing Configuration Data Importing Data From a Text or CSV File The Configurator offers the flexibility of importing data from a text txt file or a Microsoft Excel csv file to your configuration database To import data select Import CSV from the File menu This opens the Import Configuration Data From File dialog box shown below You can then specify the delimiters and choose from
106. y items were inserted updated or rejected and shows how many errors occurred Click the button to the right of each field to get the details view of the import results as shown below This view shows the specific items that were inserted updated or rejected as well as a description of any errors that occurred File Import results aq a E x Used Import settings Input file CAD ocuments and Settings 4dministratorhdy Docu Create new items C Update existing items Record Statistics S Read from input file Jap ee Inserted i Sal E Updated ej oco D 14 Errors Wieeured p ea Rejected Po E File Import Results Dialog Box Importing Data From an XML File The Configurator allows you to import data from your configuration database to an XML file The XML export import functionality was mainly developed for Windows platforms that do not support databases e g Windows CE and Windows Embedded XML has the following advantages over the CSV import export function XML has a standardized format unlike the text CSV format which uses various delimiters e g TAB instead of commas strings could not accept all characters etc XML is language independent whereas CSV converts date time floats and currency fields according to local settings in Windows For example using CSV you cannot export data on German Windows and import it on English windows without making changes Wi
107. you change the currently viewed row by clicking the three PID buttons as shown in the figure below The dynamic configuration uses local aliases and global aliases which enable you to switch between rows during runtime mode 15 Data Mining Configurator User s Manual DBOPC gdf GraphWork by Smar File views Tools Configure Help stat definition Names it ae Surname eta aan in hoe Loses PID Bob E Richards nah oS 4500 Dave a Jones tis ue ABOU ae vine O ea ling Dynamic definition _ Name saree gt sumamos aie Salary in eye PID PID_1 Data Mining Demo in GraphWorX Runtime Mode 8 The items displayed in GraphWorX correspond to the information in the Employees database table in the Administration database For example the itemID AdministrationDB Employees By PID Name_2 has the value Russel because Russel is the second name listed in the database table as shown in the figure below la adits tears Database ac BSS ie pen n ed Design Siew ap x e aie ree BE Create table in Design view Tables EE create table by using wizard E Create table by entering data ran Employees Ed See Forms ProjectDetails D Reports Groups E Employees Table E aime pete le Bob Richards Applications Engineer Product Development 2 Russel smith Applications Engineer Product Development 3 Dave Jones DA Engineer Product Development 4 Arthor Anderson Sales Repre
108. ys data values from the connected database A dynamic data item allows you to manipulate data values from the connected database To configure a static data item check the Create Static Tag Under This Data Source check box NOTE You cannot go back after this step Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for pour Object type Data Source Mame Employees Description Update rate 10 Data source type Database table Table name Employees Enable writes FALSE Database Mining Wizard cannot go back after this step The data object part of the wizard will be finished Back Finish Cancel Help Data Mining Wizard Confirming Data Source Connection 25 Data Mining Configurator User s Manual 14 Now you will define a data item value to read from the data source e g the FirstName column Give the data item a name e g FirstName_1 as shown in the figure below Static Tag wizard Please specify the following static tag object properties Specify Static Tag properties 1 Static Tag Mame First ame 1 2 Data Source parameters to bind Add Remove i Static Tag returns complete table rowset as an array Back Next gt Eimer Cancel Help Data Mining Wizard Defining a Value to Read 15 Now you need to specify a value to read from the selected column in the database table The va
109. ys the data source information that you specified as shown in the figure below Click the Next button to complete the data source connection The next step is to define data items values to read from the data source You have the option of creating a static data item or a dynamic data item option depends on the type of database connection A static data item simply displays data values from the connected database A dynamic data item allows you to manipulate data values from the connected database To configure a static data item check the Create Static Tag Under This Data Source check box NOTE You cannot go back after this step Data object The wizard will create the new Data object with the following attributes Database Mining Wizard creates the following Data Object for pour Attribute Object type Data Source Mame Employees DIE aa aa Meee a atest iene eit ee Update rate 10 Data source type SOL command SOL read command Emplopeel COM Create Static Tag under this Data Source object Database Mining Wizard cannot go back after this step The data object part of the wizard will be finished Back Nent gt Finish Cancel Help Data Mining Wizard Confirming Data Source Connection Data Manipulator Wizard SQL Command To configure the data manipulator properties in the Data Mining Wizard Select SQL Command as shown in the figure below Click the Next button NOTE You cannot go back after this ste
110. ze and manipulate the data in the GraphWorX display in runtime The steps listed above are described throughout this document The PROCESSVIEW installation provides a Data Mining Demo configuration that will help you get started with your data mining configurations The following section will guide you through the demo The Data Mining Configurator provides a Data Mining Wizard that helps you create and configure your data source connections You can also configure your data mining settings in the Database Access tab in the Unified Data Browser For more information please see the Unified Data Browser help documentation Data Mining Configuration Demo The PROCESSVIEW installation provides a Data Mining Demo configuration To view the demo files go to the C Program Files Smar ProcessView Examples Database Mining Examples directory in the PROCESSVIEW installation This directory contains the following files e DBOPCServerConfigurator mdb This Microsoft Access file is the default active data mining configuration database e administration mdb This Microsoft Access file is the demo database connection e DBOPC gdf This is a GraphWorX display that allows you to view the data sources and data items specified in the demo database connection To view the DBOPCServerConfigurator mdb demo configuration database 1 Open the Data Mining Configurator from the Windows Start menu by selecting Programs gt Smar ProcessView gt Tools gt Data

Data Mining Configurator

Contents

Download Pdf Manuals

Related Search

Related Contents