Intellect Integration: External Data Sources User`s Manual
Contents
1. amp INterneer PROCESS DRIVEN SOLUTIONS Intellect Integration External Data Sources User s Manual David Mines Interneer Inc 9 11 2012 External data sources This feature allows external data sources such as databases Excel files SalesForce etc to be accessed as if they were created in Intellect Viewing external data sources To view all external data sources 1 Goto Administrative Tools 2 Goto External Data Sources exeral Date Source TWP een aan eee uesieoeat FEE Adding changing external data sources 1 To add an external data source click the Insert External Data Source button To edit an existing external data source click the external data source s name 2 The following page appears New External Data Source External Data Source Name External Data Source Type Tables Show Tables Cancel 3 Fill out these fields Field Description S External Data Source Name A unique name you give describing the data source External Data Source Type The type of data source such as a database Excel file SalesForce etc If the type of data source isn t listed then you must add a new external data source type see External Data Source Types below Additional Parameters Additional pieces of information needed to access this data source such as username password etc The additional parameters vary depending on the type of data source2. Tables The data tables that this data source contains Check off any data tables that you want to access in Intellect Some data tables may ask for a unique ID field This is the field that uniquely identifies an instance within the data table Click OK or Show Tables If the data source can be accessed successfully one table is created in Intellect under the Administrative Tools External Tables category for each table checked off in the Tables field For example if you checked off the Payroll and Employees tables then Payroll and Employees tables would be created in the External Tables category Since tables in Intellect require an associated template a template is also created to match each new table A template category with the external data source s name is created under the Administrative Tools Templates External Templates category For example if you created an external data source called Accounting then a template category called Accounting is created Then one template is created in the new template category for each table that was checked off For example if you checked off the tables Payroll and Employees in the Accounting external data source then Payroll and Employees templates are created under the Accounting template category Troubleshooting If the external data source cannot be accessed successfully then an error message appears in red If the message doesn t clearly indicate the problem here are some trouble
3. column s name as recognized by the external data source and stripped of spaces and punctuation The following sample XML shows the first and last name of two employees returned as a data result lt Employees gt lt Employee gt lt FirstName gt John lt FirstName gt lt LastName gt John lt LastName gt lt Employee gt lt Employee gt lt FirstName gt Jane lt FirstName gt lt LastName gt Doe lt LastName gt lt Employee gt lt Employees gt Deleting an external data source type To delete an external data source type Go to Administrative Tools Go to External Data Sources Right click the external data source you want to delete Click the Delete option and confirm the deletion E ee ce
4. the Parameters section of the ReadTables method TableName A string containing the table s name as recognized by the external data source Returns This method returns a string of XML which contains general information about all the external table s columns The XML contains one sub tag for each column in the external table For each table tag there are three sub tags Tag Value Label The column s label VariableName The column s name as recognized the external data source Type The type of column This type matches the name Intellect uses in its toolbox For example a number field would be Number and a date time field would be Date Time Additional properties Depending on the type of column additional properties can be set These property names are the same as the internal names that Intellect uses for its properties ReadIndexes method This method reads the available indexes of an external table Currently this method is not needed for web services but will eventually be used to determine which queries will run quickly ReadData method This method reads data from an external table Parameters This method has three parameters AdditionalParametersXML See the description for AdditionalParametersXML inthe Parameters section of the ReadTables method TableName A string containing the table s name as recognized by the external data source Query A string containing the query for accessing the data
5. For the format of a query refer to the Query format section below Query format For the Query parameter listed above XML is passed There are four possible types of tags in this XML e lt Sort ID Column Name gt where Column Name is the name of the column to sort If multiple Sort tags are specified the resulting data set should be sorted by all the columns specified in the order that the tags appear e lt Column ID Column Name gt where Column Name is the name of the column to return If multiple Column tags are specified the resulting data set should return all the columns specified in the order that the tags appear e lt Select ID Column Name Comparison Comparison AndOr AndOr gt lt Value gt Expression1 lt Value gt lt Value gt Expression2 lt Value gt lt Value gt Expression3 lt Value gt lt Value gt Expression4 lt Value gt lt Select gt Each Select tag specifies a comparison between a database column and one or more values Multiple Select tags can be specified in a query This tag contains the following attributes e ID Column Name Column Name is the name of the column to compare e Comparison Comparison ts I dl yy te yy lt Comparison is the type of comparison to make such as is is not is between is greater etc The column specified in the ID attribute will be compared to the columns value and or expressions specifi
6. Goto Administrative Tools 2 Goto External Data Source Types 3 The following page appears Adding changing external data source types 1 To add an external data source type click the Insert External Data Source Type button To edit an existing external data source type click the external data source type s name 2 The following page appears New External Data Source Type External Data Source Type Name Web Service C Yes G No Provider Same for all data sources of this Different for each data source of this needed type type eee el ee el em Cvs ne ee el Se ee RER un R ote 3 Fill out these fields Field 0 Description S External Data Source Type Name Product Name Data source A unique name you give describing the data source type Is the data source accessed using a web service Yes It is accessed using a web service No Itis either a direct database access or access through a linked server e g Excel The unique programmatic identifier of the OLE DB provider that corresponds to this data source This field only appears if the external data source is not a web service The product name of the OLE DB data source to add as a linked server If the product name is not needed for this data source type then select the Not Needed radio button If the product name is the same for all data sources of this type then select the
7. Same for all data sources of this type radio button Then indicate the product name in the Value text box If the product name is different for each data source of this type then select the Different for each data source of this type radio button When a new external data source is created for this type the configurer will be asked fora product name In the Label text box indicate a more appropriate label that the configurer will understand when specifying the product name This field only appears if the external data source is not a web service The name of the data source as interpreted by the OLE DB provider For instructions on how to fill out this field see Provider string Catalog Use Self Local Login the Product Name field instructions This field only appears if the external data source is not a web service The OLE DB provider specific connection string that identifies a unique data source For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service The location of the database as interpreted by the OLE DB provider For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service The catalog to be used when a connection is made to the OLE DB provider For instructions on how to fill out this field
8. ed in the Value sub tags The possible values for Comparison are The column s value must be equal to one of the values specified in the Va ue sub tags e is not The column s value may not be equal to any of the values specified in the Va ue sub tags e is less than The column s value is less than or equal to the value specified in the Value sub tag Only one Value tag is specified This comparison applies only to numbers e is greater than The column s value is greater than or equal to the value specified in the Value sub tag Only one Value tag is specified This comparison applies only to numbers e is between The column s value is between the value specified in the first Va ue sub tag and the second Value sub tag Two Value tags are specified This comparison applies only to numbers and date times e is earlier than The column s value is earlier than or same as the date time specified in the Value sub tag Only one Value tag is specified This comparison applies only to date times e is later than The column s value is later than or same as the date time specified in the Value sub tag Only one Value tag is specified This comparison applies only to date times e starts with The column s value starts with the text specified in the Va ue sub tag Only one Value tag is specified This comparison applies only to text and memo fields e
9. ends with The column s value ends with the text specified in the Value sub tag Only one Value tag is specified This comparison applies only to text and memo fields e contains The column s value contains the text specified in the Value sub tag Only one Value tag is specified This comparison applies only to text and memo fields e AndOr AndOr This parameter is ignored for the first Select tag Subsequent Select tag comparisons are appended to the previous Select tag s comparison with an And or an Or which is specified in this parameter e lt Parenthesis AndOr AndOr gt lt Select gt lt Select gt lt Select gt lt Select gt lt Parenthesis gt lt Parenthesis gt lt Parenthesis gt This tag allows parenthesis to be specified in the query Parenthesis tags can contain Select tags and or other Parenthesis tags The Parenthesis tag contains the following attribute e AndOr AndOr This parameter is ignored for the first Parenthesis tag Subsequent Parenthesis tag conditions are appended to the previous Parenthesis tag s condition with an And or an Or which is specified in this parameter Returns This method returns a string of XML which contains the data results from the query The XML contains one sub tag for each instance returned For each instance tag there is one sub tag for each data column returned The column s tag name is the same as the
10. h the remote user For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service The URL of the web service that accesses the external data source This web service will have to be specially programmed to work with Intellect and use the methods outlined in the Web Service Methods section below This option only appears if the external data source type is a web service These are all the additional parameters needed to access any external data sources for this type At this point just specify the labels indicating what additional parameters are needed For example if the additional parameters are a username and password then specify Username and Password as two additional parameter labels The actual parameter values will be supplied when any external data sources are created of this type In the example above when someone creates this type of external data source he will be asked for a username and password This option only appears if the external data source type is a web service 4 Click the OK button to save the external data source type Web service methods This section is for software developers only This section shows how to create a web service to access a type of external data source There are four methods e ReadTables e ReadColumns e Readindexes e ReadData ReadTables method This method reads the a
11. see the Product Name field instructions This field only appears if the external data source is not a web service A value of true specifies that logins use their own credentials to connect to the remote server with the Remote User and Remote Password fields being ignored A value of false specifies that the Remote User and Remote Password fields are used to connect to the remote server for the specified Local Login If the Remote User field is blank no login or password is used to connect to the remote server For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service A login on the local server If not blank the Local Login can be a SQL Server login or a Windows login The Windows login must have been granted access to SQL Server either directly or through its membership in a Remote User Remote Password Web Service URL Additional Parameter Labels Windows group granted access For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service The user name used to connect to the remote server when the Use Self field is false For instructions on how to fill out this field see the Product Name field instructions This field only appears if the external data source is not a web service The password associated wit
12. shooting tips e f the data source requires a username and password double check these to make sure they are correct and not expired e fthe data source accesses a file make sure the file path can be recognized by the web server Also make sure that the file can be accessed by Everyone in the file security Finally if it is an Excel or Access file make sure that it is in either Excel 2000 or Access 2000 format Deleting an external data source To delete an external data source Go to Administrative Tools Go to External Data Sources Right click the external data source you want to delete A 2 GES Click the Delete option and confirm the deletion Using external data sources External tables and templates can be used just like any other table or template They can even be accessed from the properties of internal fields For example you can set the Data List property of an internally created drop down to an external table Restrictions e External data is read only You cannot add edit or delete instances e Youcan move fields and set most field properties but you cannot add or delete fields External data source types These are the types of external data sources The most often used types are already included in Intellect however if a new type of web service or database is being accessed then a new external data source type will need to be created Viewing external data source types To view all external data sources 1
13. vailable tables from the external data source Parameters This method has one parameter called AdditionalParametersXML which indicates all the additional parameters to be passed to the web service This parameter is a string which contains XML The XML contains a main tag called AdditionalParameters Each sub tag represents one parameter The tag s name is the additional parameter s label without any punctuation or spaces The text inside the tag is the additional parameter s value For example assume there are two additional parameters User Name which is jsmith and Password which is abcde The XML would be lt AdditionalParameters gt lt UserName gt jsmith lt UserName gt lt Password gt abcde lt Password gt lt AdditionalParameters gt Returns This method returns a string of XML which contains general information about all the external data source s tables The XML contains one sub tag for each table in the external data source For each table tag there are three sub tags Tag Name Tag Value Label The table s label VariableName The table s name as recognized by the external data source UniquelDColumn The name as recognized by the external data source of the Unique ID field within the table ReadColumns method This method reads the available columns of an external table Parameters This method has two parameters AdditionalParametersXML See the description for AdditionalParametersXML in
Download Pdf Manuals
Related Search