Analytics-Plus Help

Import data from Local and Cloud databases

This page describes the steps to set up import from local and cloud databases but before you begin the integration, please make sure that you have met the prerequisites found here.

Importing data from Cloud Databases

Importing data from Local databases

Importing data from Cloud Services

Log in to Analytics Plus, click on the Import your data button found at the top right corner of the screen, and select the Local & Cloud Databases option.

Amazon RDS: MySQL

Follow the steps below to import data from Amazon RDS cloud databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.

  • Database Type: Choose MySQL as the database type.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.

  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to pull data from different tables at once.  

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported.  

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

 

 Amazon RDS: SQL Server

 Follow the steps below to import data from Amazon RDS cloud databases.

 Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.
  • Database Type: Choose the database type as SQL Server.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • SQL Server Instance Name: This identifies the SQL Server Instance to connect to among all running instances on the endpoint. If not specified, connection is made to the default instance.

  • Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  •  Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.
  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

 Step 2: Select data to import

  •  Select database: Select the database from which data is to be imported.
  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from different tables at once.  

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

 Step 3: ManageEngine Analytics Plus database setup

 You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

 

Amazon RDS: Oracle

Follow the steps below to import data from Amazon RDS cloud databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.

  • Database Type: Choose Oracle as the database type.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Database Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Oracle SID/Service Name: Oracle databases requires SID to connect with the data source. The default SID would be ORCL. If you have changed the SID name in your data source, specify the changed value or specify the default value.

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from different tables at once.  

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported.  

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

 

Amazon RDS: PostgreSQL

Follow the steps below to import data from Amazon RDS cloud databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.

  • Database Type: Choose PostgreSQL as the database type.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Database Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.

  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from different tables at once.

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Amazon RDS: Amazon Aurora

Follow the steps below to import data from Amazon RDS cloud databases.

Step 1: Local and Cloud Database Connection Settings 

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.

  • Database Type: Choose Amazon Aurora as the database type.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.

  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from different tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Amazon RDS: Maria DB

Follow the steps below to import data from Amazon RDS cloud databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon RDS as the service name, this identifies the database service on which the Database is running.

  • Database Type: Choose Maria DB as the database type.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.

  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Amazon Redshift

Follow the steps below to import data from Amazon Redshift cloud database.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Amazon Redshift as the service name, this identifies the database service on which the Database is running.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Database Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables optin to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

    Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

 

Microsoft Azure SQL

Follow the steps below to import data from Microsoft Azure SQL cloud database.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Microsoft Azure SQL as the service name, this identifies the database service on which the Database is running.

  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.

  • Database Port: Enter the port on which the database instance is running.

Note: The database must publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables option to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Import Summary

Analytics Plus imports the input data provided, it will bring up a Import Summary dialog as shown below, capturing the status of the Import process just completed.

The details that would be shown include:

  • Column Details: Total number of columns in the input data and the total number of columns that where actually selected for import.

  • Row Details: Total number of rows in the input data and the total number of successfully imported rows in it.

  • Scheduling Options: If you wish to schedule imports, click on the "Schedule This Import" option

Schedule Settings 

How do you want to import?

Select your preference from a drop down list on the available options to add new records into your database.

  • Add records at the end: This option adds the new columns towards the end of the existing database table

  • Delete existing records and add: This option deletes the existing records and adds the new records into the database table

  • Add Records and replace if already exists: This option adds the new columns and overwrites records with same column name.

  • Add new, replace existing and delete missing records: This option adds the new records, overwrites the existing records with same column name and deletes the missing records.

Note

  • Check the "include new column added in your Cloud Database automatically into ManageEngine Analytics Plus table", to add the new columns into your Analytics Plus table automatically

  • When Add records and replace if already exists or Add records, replace existing and delete missing record is selected, it is mandatory to specify matching columns using the Select the column(s) to match existing records option. You can choose one or more columns as matching columns. Analytics Plus will use the values in these columns as the key to match the existing records with the new records being imported. Hence the matching column(s) combination should uniquely identify each record in the table. Once a match is identified, then Analytics Plus will update that existing record in the table with the new record values present in the data being Imported. Incase there is no match found, those records will be appended to the end of the table.

You can specify the schedule interval from the available options such as:

  • Repeat- Specify the schedule interval from the drop-down list. Available options are.

    • Every 'N' hours - Choose this option to schedule the import every 'N' hours i.e., every 2 hour or 6 hours. On choosing this option, specify the time interval to import the data.

    • Every Day - Choose this option to schedule the import at a particular time everyday. On choosing this option, specify the time to import data.

    • Weekly Once - Choose this option to schedule the import once in a week on a particular day. On choosing this option, specify the day of the week and the time to import the data.

    • Monthly Once - Choose this option if you want to schedule the import once in a month on a particular day. On choosing this option, specify the day of the month and the time to import data.

On closing the Summary dialog, Analytics Plus will display the newly created table with the data loaded in the same.

Follow the steps below to import data from Google Cloud SQL databases.

Google Cloud

Follow the steps below to import data from Google Cloud SQL databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Google Cloud SQL as the service name, this identifies the database service on which the Database is running.
  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.
  • Port: Enter the port on which the database instance is running.

Note: The database must be publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.
  • Password: Enter the password that is associated with the username specified in the database username text box.
  • Database Name: Enter the database name to connect with the data source instance.
  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.
  • Select table: Select the required radio button to import data
    • Single table: Select the table from which the data is to be imported.
    • Multiple tables: Select multiple tables option to import data from multiple tables at once. 
    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 
  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Heroku PostgreSQL Cloud

Follow the steps below to import data from Heroku PostgreSQL Cloud databases.

Step 1: Local and Cloud Database Connection Settings

  • Service Name: Choose Heroku PostgreSQL Cloud as the service name, this identifies the database service on which the Database is running.
  • Endpoint: Enter the endpoint host name. Endpoint is the host name that uniquely identifies the cloud database instance. If the endpoint is unknown, contact your database administrator.
  • Port: Enter the port on which the database instance is running.

Note: The database must be publicly accessible. If the Database is protected by firewall, firewall rules must allow public inbound connections to the port on which the Database is running.

  • Username: Enter the username that is used to access the database. It is recommended to provide the read-only user name.
  • Password: Enter the password that is associated with the username specified in the database username text box.
  • Database Name: Enter the database name to connect with the data source instance.
  • Click on "Next" to proceed with the import process.

Step 2: Select data to import

  • Select database: Select the database from which data is to be imported.
  • Select table: Select the required radio button to import data
    • Single table: Select the table from which the data is to be imported.
    • Multiple tables: Select multiple tables option to import data from multiple tables at once. 
    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 
  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

Importing data from Local Databases

Log in to Analytics Plus, click on the Import your data button found at the top right corner of the screen, and select the Local & Cloud Databases option

MySQL 

Before you start importing from MySQL, make sure these prerequisites are met. Follow the steps below to import data from MySQL local database. 

Step 1: Local and Cloud Database Connection Settings 

  • Service Name: Choose the service name, this identifies the database service on which the Database is Runnning.

  • Database Type: This is the type of database running on the Server.

  • Database Host Name: Database Host Name uniquely identifies the local database instance.This can be obtained from the management console provided by the respective database services. 

  • Database Port: This is the Port on which the database instance is running

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process. 

 Step 2: Select data to import 

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

SQL Server

Before you start importing from SQL server, make sure these prerequisites are met. Analytics Plus supports both Windows as well as SQL Server authentication modes for importing data from MSSQL servers. Choose your preferred authentication mode from the Database Type drop-down in the Connect to Local and Cloud Databases page. 

Step 1: Local and Cloud Database Connection Settings 

  • Service Name: Choose the service name, this identifies the database service on which the Database is Runnning.

  • Database Type: This is the type of database running on the Server.

  • Database Host Name: Database Host Name uniquely identifies the local database instance.This can be obtained from the management console provided by the respective database services. For Windows authentication, enter the domain name/host name of your database. 

  • SQL Server Instance Name: This identifies the SQL Server Instance to connect to among all running instances on the endpoint. If not specified, connection is made to the default instance.

  • Database Port: This is the Port on which the database instance is running

  • Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

Step 2: Select data to import 

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

 

Step 3: ManageEngine Analytics Plus database setup 

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.   

 

Note: Data is not imported immediately after the import is started, rather an empty table is created with the schema provided in the data preview page. The data import begins in a few minutes and then filles the table. 

Oracle 

Before you start importing from Oracle, make sure these prerequisites are met. Follow the steps below to import data from Oracle local database. 

Step 1: Local and Cloud Database Connection Settings 

  • Service Name: Choose the service name, this identifies the database service on which the Database is Runnning.

  • Database Type: This is the type of database running on the Server.

  • Database Host Name: Database Host Name uniquely identifies the local database instance.This can be obtained from the management console provided by the respective database services.

  • Database Port: This is the Port on which the database instance is running

  • Oracle SID/Service Name: Oracle databases requires SID to connect with the data source. The default SID would be ORCL. If you have changed the SID name in your data source, specify the changed value or specify the default value.

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Click on "Next" to proceed with the import process.

 

Step 2: Select data to import 

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

Step 3: ManageEngine Analytics Plus database setup 

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.  

 

 

PostgreSQL

Before you being importing from PostgreSQL, make sure these prerequisites are met. Follow the steps below to import data from PostgreSQL local database.

Step 1: Local and Cloud Database Connection Settings 

  • Service Name: Choose the service name, this identifies the database service on which the Database is Runnning.

  • Database Type: This is the type of database running on the Server.

  • Database Host Name: Database Host Name uniquely identifies the local database instance.This can be obtained from the management console provided by the respective database services.

  • Database Port: This is the Port on which the database instance is running

  • Database Username: This is the username used to access the database. It is recommended to provide read-only user name.

  • Database Password: Enter the password that is associated with the username specified in the database username text box.

  • Database Name: Enter the database name to connect with the data source instance.

  • Click on "Next" to proceed with the import process.

 

Step 2: Select data to import 

  • Select database: Select the database from which data is to be imported.

  • Select table: Select the required radio button to import data

    • Single table: Select the table from which the data is to be imported.

    • Multiple tables: Select multiple tables to import data from multiple tables at once. 

    • Custom query: Enter your custom query in the respective database query format from which data is to be imported. 

  • Click on "Next" to proceed with the import process.

 

Step 3: ManageEngine Analytics Plus database setup 

You can change the title of the columns, choose to exclude some and change their data types. Choose what the application should do when an error occurs during import.   

 

Import Summary 

Analytics Plus imports the input data provided, it will bring up a Import Summary dialog as shown below, capturing the status of the Import process just completed. 

The details that would be shown include:

  • Column Details: Total number of columns in the input data and the total number of columns that where actually selected for import.

  • Row Details: Total number of rows in the input data and the total number of successfully imported rows in it.

  • Scheduling Options: If you wish to schedule imports, click on the "Schedule This Import" option

Schedule Settings 

How do you want to import? 

Select your preference from a drop down list on the available options to add new records into your database. 

  • Add records at the end: This option adds the new columns towards the end of the existing database table

  • Delete existing records and add: This option deletes the existing records and adds the new records into the database table

  • Add Records and replace if already exists: This option adds the new columns and overwrites records with same column name.

  • Add new, replace existing and delete missing records: This option adds the new records, overwrites the existing records with same column name and deletes the missing records. 

Note:

  • Check the "include new column added in your Cloud Database automatically into ManageEngine Analytics Plus table", to add the new columns into your Analytics Plus table automatically

  • When Add records and replace if already exists or Add records, replace existing and delete missing record is selected, it is mandatory to specify matching columns using the Select the column(s) to match existing records option. You can choose one or more columns as matching columns. Analytics Plus will use the values in these columns as the key to match the existing records with the new records being imported. Hence the matching column(s) combination should uniquely identify each record in the table. Once a match is identified, then Analytics Plus will update that existing record in the table with the new record values present in the data being Imported. Incase there is no match found, those records will be appended to the end of the table.

You can specify the schedule interval from the available options such as:

  • Repeat- Specify the schedule interval from the drop-down list. Available options are.

    • Every 'N' hours - Choose this option to schedule the import every 'N' hours i.e., every 2 hour or 6 hours. On choosing this option, specify the time interval to import the data.

    • Every Day - Choose this option to schedule the import at a particular time everyday. On choosing this option, specify the time to import data.

    • Weekly Once - Choose this option to schedule the import once in a week on a particular day. On choosing this option, specify the day of the week and the time to import the data.

    • Monthly Once - Choose this option if you want to schedule the import once in a month on a particular day. On choosing this option, specify the day of the month and the time to import data 

On closing the Summary dialog, Analytics Plus will display the newly created table with the data loaded in the same. 

Share this post : FacebookTwitter