Steps to use Upload Tool for uploading Data from any DB:
1. Download and extract the uploadtool.zip from this link
2. Configure the Database connection details:
Open the file 'database_connection_params.conf' located under <Uploadtool>\conf\ folder, using wordpad and specify the connection settings about your in-house database behind firewall in this file. The following screenshot illustrates a typical database_connection_params.conf.
The example in the following screenshot illustrates the connection settings for a MySQL database.
The following are the parameters that are to be given in database_connection_params.conf file:
Once all required DB settings are updated, save the file.
|DBTYPE||Specify your local database type. e.g: MySQL, SQL Server, Oracle, Sybase etc.,. (Refer to the next section which contains the list of possible values)|
|HOSTNAME||Host name of your computer in which the database is running, i.e., the name of the computer where your database is running.|
|PORT||The port number used by the database server. (Refer to the next section to know the default port number for different databases).|
|USERNAME||Your local database login name.|
|PASSWORSD||Password for accessing your local database.|
|DBNAME||Your local database name from which the data is to be uploaded.|
|MDBFILENAME||In case you are going to upload data from MS Access database, then specify the complete path of MS Access database file name which contains the data. |
e.g., MDBFILENAME=C:MSACCESSmdbfiles est.mdb.
|SQLITEFILENAME||In case you are going to upload data from SQLite database, then specify the complete path of SQLite database file name which contains the data.|
|FIREBIRDFILENAME||In case you are going to upload data from Firebird database, then specify the complete path of Firebird database file name which contains the data.|
e.g., FIREBIRDFILENAME=C:FIREBIRD irebirdfilesmydb.fdb
3. Specify Queries to be Executed on Relational databases
The database_sql_queries.xml file located under <Uploadtool>\conf\ folder, contains all the settings related to the SQL SELECT Queries that are to be executed in the database for fetching the required data, along with settings about the Analytics Plus database and table names into which the data is to be uploaded. To fetch data from your relational databases, you need to specify SQL SELECT query. These queries will be executed by the upload tool in your local database to fetch the necessary records and upload the same into Analytics Plus according to the settings provided.
You can provide any number of SQL SELECT queries to execute. Each query is to be provided in a <Query> <Query> XML element. Multiple queries can be grouped within the <Queries> <Queries> element.
The following screenshot illustrates a sample SQL SELECT queries in the database_sql_queries.xml file.
The above illustrated queries will fetch data from two tables and import the same into Analytics Plus reporting database ReportsDBName1.
Data from the local_db_table1 will be appended into ReportsTableName1 in Analytics Plus. And data from local_db_table2, where the employee number is less than 1000, will replace the existing data inReportsTableName2.
The following is the SQL SELECT query format to be followed to fetch data from your relational database.
<Query dbname="Analytics_Plus_dbname" tablename="Analytics_Plustablename" importtype="APPEND / TRUNCATEADD / UPDATEADD" matchingcols="matching_cols_for_updateadd" selectcols="columns_to_import_from_csv" skiptop="number_of_rows_to_be_skipped"batchsize="number of lines to send per request" queuesize="number of temporary files can be created at a time">sql_query_ to_be_executed</Query>
Make sure that the 'dbname' mentioned in the query is already created in Analytics Plus.
Save the file once the queries and details are updated.
4. Execute the Uploadfromdb:
Once both the files are updated, Open a Command prompt and access the location <Uploadtool>\bin\.
Execute the batch file UploadFromDB.bat with the below parameters.
Example: <UploadTool>\AnalyticsPlus\UploadTool>UploadFromDB.bat localhost 443 XXXXXX@zohocorp.com admin123
"Usage : UploadFromDB.bat [Analyticsplus-host] [Analyticsplus-port] [userEmailAddress] [<-A authtoken>/<password>] [-D dbowner]"
" UploadFromDB.bat <Analyticsplus-host> <Analyticsplus-port> <userEmailAddress> <password>"
" UploadFromDB.bat <Analyticsplus-host> <Analyticsplus-port> <userEmailAddress> <password> -D <dbowner>" (If uploading to a shared DB, provide DB owners email ID under 'dbowner')
" UploadFromDB.bat <Analyticsplus-host> <Analyticsplus-port> <userEmailAddress> -A <authtoken>"
" UploadFromDB.bat <Analyticsplus-host> <Analyticsplus-port> <userEmailAddress> -A <authtoken> -D <dbowner>"
Once the above batch file is executed, the data will be fetched from local database and will be uploaded to Analytics Plus DB and will be shown a success message as below.
Note: For each DB type the parameters updated in the database_connection_params.conf, differs. Please refer this link to get the right set of parameters.