Connecting to Data

Before designing a dashboard, it is necessary to decide the data that you are going to visualize, to pick the right widgets for visualization.

Data Connection Types

Dashboard Designer supports various data connection types such that the structured data can be in any specific format to consider for processing.

• Microsoft Excel

• CSV

• JSON

• Text Document

• Microsoft SQL Server

• Microsoft SQL Server Analysis Services

• ODBC

      1.  Microsoft SQL Server

      2.  MySQL

      3.  Oracle

      4.  Microsoft Access

      5.  Hive

      6.  Others (ANSI SQL)

• PostgreSQL

• SQLite

• Microsoft Azure Table Storage

• Salesforce

• Spark SQL

• Web Data Source

• Hive

Connecting to SQL Server Database

With Microsoft SQL Server data connection type, you can connect to a database hosted in Microsoft SQL Server whose version should be 2005 and above. Also you can connect to SQL Azure hosted in Azure cloud.

SQLWindow

Set Connection type as Microsoft SQL Server.

Set the Server name. You may either select the server existing in the local network from the dropdown list or specifying the specific remote server name like myserver.domain.com, myserver.domain.com,port, ipaddress and ipaddress,port.

Set the Authentication type. For SQL Server Authentication, credentials are required.

Set the Database that you need to connect and test its connection. If it succeeds, proceed to connect it through clicking the Connect button.

Now you will get into the data design view window.

DataDesignView

You can also connect SQL Views in design view just like tables. Simply drag and drop any SQL View under Views section in tree-view to design view.

Views

NOTE

If you have only one table in the bounded database, the data design view will have that one table added by default.

Connecting to SQL Server Analysis Services Cube

With Microsoft SQL Server Analysis Services data connection type, you can connect to a cube hosted in Microsoft SQL Server Analysis Services whose version should be 2012 and above. Also you can connect to a cube hosted in Azure Analysis Services using Server Authentication type alone.

SSASWindow

In the New Connection dialog, enter a data source name and select connection type as Microsoft SQL Server Analysis Services.

Set the name of the server. You may either select the server existing in the local network from the dropdown list or specify the remote server name like myserver.domain.com, myserver.domain.com:port, ipaddress and ipaddress:port.

NOTE

For more SQL Server name formats, please refer here.

Set the authentication type. For Server Authentication, user credentials are required.

Select the Database that you need to connect and test its connection. If it succeeds, proceed to connect it through clicking the Connect button.

Now you will get into the data design view window

SSASDataDesignview

Connecting to PostgreSQL Database

With PostgreSQL connection type, you can connect to a PostgreSQL database hosted in local or remote machine whose version should be 9.x and above.

PostgresWindow

In the New Connection dialog, enter a data source name and select connection type as PostgreSQL from the drop down list.

Set the server name and port number where the PostgreSQL service is running.

Enter the user name and password to connect to the PostgreSQL server.

Select the database that you need to connect and test its connection. If it succeeds, proceed to connect it through clicking the Connect button.

Now you will get into the data design view window.

PostgresDataDesignView

NOTE

If you have only one table in the bounded database, the data design view will have that one table added by default.

Connecting to an Excel workbook

With Microsoft Excel data connection type, you can connect to an Excel workbook either from Local Machine or Cloud File Storage, whose format can be XLS or XLSX.

ExcelWindow

Set Connection type as Microsoft Excel.

Local

Through Local storage you can connect the Excel workbook which is existing in your local machine.

Set the File Path through locating the Excel workbook accessible location and click Connect.

Now you will get into data design view window.

ExcelDataDesignView

File Storage

Through File Storage connection you can connect Excel workbook from cloud service using either Dropbox or Google Drive account.

ExcelOnlineOption

Procedure to get file URL from cloud service:

Dropbox

Sign in to your Dropbox account and choose the file to upload. Once the file is uploaded, click the Share option available next to it.

ShareButton

Then choose Copy Link option to copy the URL link.

copyLink

Now you will get the File URL to connect the selected file in dashboard designer.

URLWindow

Paste the link in File URL box and click Connect button. Now you will get data design view window.

DropBoxOption

Google Drive

Sign in to your Google Drive account and choose the file to upload. Once the file is uploaded, right-click on it.

DriveShare

Now choose Get Shareable Link option to get the file URL.

ShareableLink

Now you will get the link and use Copy Link option to copy the URL link.

DriveCopyLink

Paste the respective link in File URL box and click Connect button. Now you will get data design view window.

DriveConnection

Dashboard Server

Before connecting a file from the Dashboard Server, you must upload a file in the Dashboard Server.

Refer here for file uploading in the Dashboard Server.

When the file uploading is completed, you can connect those files from the Dashboard Designer.

Log on your Dashboard Server account from the Dashboard Designer.

ServerOption

Select the respective file from the drop-down list and click the Connect button. Now, you will get the data design view window.

ServerDashboardList

NOTE

You can also connect CSV, JSON, Text document, SQLite and Microsoft Access connection via cloud file storage using the above mentioned procedures.
If you have only one worksheet in the excel workbook, the data design view will have that one table added by default.

IMPORTANT

The Excel workbook that need to be connected, should have column names at first row of sheet followed by data rows.

Connecting to a CSV file

With CSV data connection type, you can connect to any comma separated value formatted file (CSV). Apart from Comma Separator, Semicolon, Space and Tab (*.tsv files) Separated files are also supported.

CSVWindow

Set Connection type as CSV.

Set the File Path through locating the CSV file existing in your machine accessible location and click Connect.

Select a Separator value by which the respective file values are separated.

Now you will get into data design view window.

CSVDataDesignView

NOTE

The data design view will have that only table added by default.

Connecting to a Text Document

With Text Document data connection type, you can connect to any text document (*.txt) whose values are separated by any one of the separators like Comma, Semicolon, Space and Tab.

TextWindow

Set Connection type as Text Document.

Set the File Path through locating the Text Document file existing in your machine accessible location and click Connect.

Select a Separator value by which the respective file values are separated.

Now you will get into data design view window.

TextDataDesignView

NOTE

The data design view will have that only table added by default.

Connecting to a XER File

Text Document option now establishes connection with *.xer extension file types which comes with multiple tables along with tab-separated values.

XERWindow

Set Connection type as Text Document.

Set the File Path through locating the XER file existing in your machine accessible location.

By default Tab Separator will set automatically and finally click Connect.

Now you will get into data design view window.

XERDataDesignView

Connecting to Spark SQL Data

With Spark SQL data connection type, you can connect to data placed in HDFS processed by Spark on Hive Server.

SparkWindow

Set Connection type as Spark SQL.

Set the Server name which can be either IP address or the host name of the server where the data resides in HDFS and Spark is running.

The Port number and the Type will be filled by default as 10001 and HiveServer2 respectively.

Select the Database to connect to and test the connection. If it succeeds, you will get into the data design view like below.

SparkDataDesignView

NOTE

If you have only one table in the bounded database, the data design view will have that one table added by default.

Connecting to Microsoft SQL Server Database through ODBC

Through Microsoft SQL Server (in ODBC category) data connection type, you can connect to ODBC enabled SQL database hosted in Microsoft SQL Server 2005 & above through the following ODBC driver.

• SQL Server

ODBCWindow

Setting up an ODBC-enabled SQL Server database

To setup a new data source with SQL database, add the installed driver through the following procedure.

  1. Navigate to the Control Panel -> System and Security -> Administrative Tools folder location.

  2. Double click the ODBC Data Sources (32-bit) tool to open the ODBC Data Source Administrator (32-bit) dialog.

  3. Select the System DSN or User DSN tab where the data sources already available get listed in the list. Click Add to select the specific driver and bind the preferred database to create a new ODBC data source.

Connecting to ODBC SQL Server data source

  1. Select Microsoft SQL Server in ODBC category as Connection type in the New Connection wizard.

  2. Select Data Source Name (DSN) or the SQL ODBC driver that you added, and bind the SQL Server database path and connect, to create a new data source for dashboard.

Now you will get into the data design view window.

SQLODBCDataView

NOTE

For connecting SQL Server Database through ODBC, ensure both SQL driver and Syncfusion Dashboard Designer are installed on the same machine.

IMPORTANT

We recommend to use direct connection of Microsoft SQL Server in database category instead of going to connect a Microsoft SQL Database through ODBC.

Connecting to MySQL Database through ODBC

Through MySQL data connection type, you can connect to ODBC enabled MySQL database.

MYSQLODBCWindow

Setting up an ODBC-enabled MySQL database

  1. Download & Install the MySQL ODBC 5.3 Unicode driver from here

  2. Add the installed driver through the following procedure to setup a new data source with MySQL database:

a. Navigate to the Control Panel -> System and Security -> Administrative Tools folder location.

b. Double click the ODBC Data Sources (32-bit) tool to open the ODBC Data Source Administrator (32-bit) dialog.

c. Select the User DSN tab. In this tab, the user data sources already available get listed in the list. Click Add to select the specific driver and bind the preferred database to create a new ODBC data source.

Connecting to ODBC MySQL data source

  1. Select MySQL as Connection type in the New Connection wizard.

  2. Select Data Source Name (DSN) or the MySQL driver that you added, and select the MySQL database by providing the Server Name,Port,User Name,Password and connect, to create a new data source for dashboard.

Now you will get into data design view window.

MYSQLDataView

NOTE

For connecting MySQL Database through ODBC, ensure both MySQL driver and Syncfusion Dashboard Designer are installed on the same machine.

IMPORTANT

We can follow the same connecting procedure to connect MariaDB database as well.

Connecting to Oracle Database through ODBC

Through Oracle data connection type, you can connect to ODBC enabled Oracle database through any of the following ODBC drivers.

• Microsoft ODBC for Oracle - This comes with Office installation. Need to install Oracle Client software separately to use this driver.

• Oracle in OraClient 11g_home1 – This can be downloaded from here – v11.2.0.2.1

Setting up an ODBC-enabled Oracle database

To setup a new data source with Oracle database, add the installed driver through the following procedure.

  1. Navigate to the Control Panel -> System and Security -> Administrative Tools folder location.

  2. Double click the ODBC Data Sources (32-bit) tool to open the ODBC Data Source Administrator (32-bit) dialog.

  3. Select the System DSN or User DSN tab where the data sources already available get listed in the list. Click Add to select the specific driver and bind the preferred database to create a new ODBC data source.

OracleList

OracleDriveWindow

Connecting to ODBC Oracle data source

To establish Oracle connection, click on add data source which displays New Connection window as shown below.

OracleWindow

  1. Select Oracle as Connection type in the New Connection wizard.

  2. Select Data Source Name (DSN) or the Oracle ODBC driver that you added, and bind the Oracle database path and Connect, to create a new data source for dashboard.

We can also test the connection by clicking on Test Connection button.

Click Connect to navigate to the data design view. It holds the tables, views, etc. available under the connected data source. Now, a new oracle data source was created.

OracleDataView

NOTE

For connecting Oracle Database through ODBC, ensure both Oracle driver and Syncfusion Dashboard Designer are installed on the same machine.
Using Oracle data connection type, you can connect Oracle 11g or Oracle 12c database.

Connecting to Microsoft Access Database through ODBC

Through Microsoft Access data connection type, you can connect to ODBC enabled Microsoft Access database file whose format can be either MDB or ACCDB.

MicrosoftAccessWindow

Setting up an ODBC-enabled Access database

  1. Download & Install the redistributable driver from here

  2. Add the installed driver through the following procedure to setup a new data source with Access database:

a. Navigate to the Control Panel -> System and Security -> Administrative Tools folder location.

b. Double click the ODBC Data Sources (32-bit) tool to open the ODBC Data Source Administrator (32-bit) dialog.

c. Select the User DSN tab. In this tab, the user data sources already available get listed in the list. Click Add to select the specific driver and bind the preferred database to create a new ODBC data source.

Connecting to ODBC Access data source

  1. Select Microsoft Access as Connection type in the New Connection wizard.

  2. Select Data Source Name (DSN) or the Access driver that you added, and bind the Access database file path and connect, to create a new data source for dashboard.

Now you will get into data design view window.

AccessDataView

NOTE

For connecting Microsoft Access Database through ODBC, ensure both Access driver and Syncfusion Dashboard Designer are installed on the same machine.

Connecting to Salesforce

With Salesforce data connection type, you can connect the objects from your Salesforce account.

SalesforceWindow

Set Connection type as Salesforce.

Set the URL for the Salesforce server you are connecting to. By default, the server is here

Fill your user name and password for Salesforce.com.

In order to Salesforce login, you may need to append a special security token to the end of your password.

To get the security token, refer How to obtain my security token.

Click Connect, now you will get into data design view window.

SalesforceDataView

Salesforce Account Configuration

In order to connect Salesforce data, all of the following must be enabled on your Salesforce account.

• SOAP API – To sign in and get Salesforce objects information.

• BULK API – To retrieve data.

• REST API – To retrieve data that BULK API doesn’t support.

Connecting to RESTful Web Services

With RESTful Web Services connection type, you can connected to data from web which is accessible through RESTful APIs.

RestfulWebWindow

Set Connection type as Web Data Source.

Enter the API in URL text box which must be a valid REST API.

Choose an appropriate Method for the REST API; it can be either GET or POST in the type combo box.

When you choose POST method, Request Body section will be displayed.

Request Body allows to send data request to the server to update it: as often the case with POST requests. It can be chosen either Raw or Parameter(s).

RequestBodyOption

For Example, Consider the table below,

Raw Parameter(s)
{ “status”: “Hello Everyone,” } Status: Hello Everyone,

Header(s) allows the client and the server to pass additional information with the request or the response.

Example: Content-Type: application/json

You can choose your response Data Format. We have supported the JSON and CSV data format.

You can choose your Authentication type for the REST API from drop down list,it can be either Basic HTTP Authentication or No Authentication or OAuth.

After filling required details, click Next button in the New Connection window as shown below.

NextButton

OAuth

Through OAuth authentication type, you can get Web API from some providers like Facebook, Twitter, LinkedIn, Google, Yahoo, Instagram, GitHub and Custom.

NOTE

You can click information icon shown below to know more details about OAuth.

OAuthIcon

Here, we have taken Dropbox as example to explain OAuth Authentication type.

You can choose Provider from drop down list.

You can choose Token Type, we have provided the three kinds of Token type listed in below,

  1. New Access Token.
  2. Existing Access Token.
  3. Direct Access Token.

New Access Token

Enter Token Name, Client ID, Client secret and Redirect URL for selected provider type.

Click Get Access button to get access token.

GetAccessButton

NOTE

After getting the Access Token, the alert message shown in bottom and the connect button will enable.

Existing Access Token

You can reuse the token by choosing Existing Access Token, which is already created by using the New Token type.

You can choose created token from Select Token drop down list.

Click Connect button to get the data.

Direct Access Token

We have provided the support to get data from your own access token. If you have direct access token for your registered application, you can avoid the process for getting the access token with our application. You can connect directly through choosing the Direct Access Token option which is available in Token Type selection radio button.

Enter valid Access Token for the provider type.

Here, the Refresh Token, Client ID, Client secret and Token Endpoint URL are optional parameters which used only for refreshing the access token.

DirectWindow

Click the Connect button.

If your URL returned data in JSON format, then JSON Schema Designer window will open.

Custom OAuth

Custom option available in our web data source OAuth provider selection drop down list shown below.

CustomOAuthWindow

Here, we have provided four kinds of Authorization Flow (Grant Type) for OAuth Custom provider type.

  1. Authorization Code.
  2. Implicit.
  3. Password.
  4. Client Credential.

Authorization Endpoint URL is the endpoint for authorization server, which retrieves the authorization.

Token Endpoint URL is the endpoint for the resource server, which exchanges the authorization code for an access token.

Enter your registered ClientID and Client Secret.

Redirect URL allows to redirect which you provided in your application registration process.

Enter User Name and Password for your web service.

After filling the all required fields, you can click the Get Access button

CustomOAuthDetails

It will navigate to your service login window. Login your account with credentials.

ServiceLogin

The authorization server will present them with a prompt asking if they would like to authorize this application’s request.

AuthorizationServer

Here, the application required the permission to get your basic profile information. It can be defined through the Authorization URL’s Scope section.

After getting access token , Connect button will be enabled and you can get data from your provided Rest API.

JSON Schema Designer

The JSON Schema Designer is used to design one or more than one table based on JSON schemas in your JSON string.

JSONWindow

In the JSON schema designer, all JSON schema information are fetched from the JSON string and all JSON schema are listed in hierarchical order to design one or more than one table. To design a table, you can select any schema which you want to use in that table.

JSONTable

If you have multiple tables in DBMS (Database Management System) structure in the JSON string, then you can use Add option to design the multiple tables as follows.

JSONMultiTable

After designing the table(s) using the JSON schema, click OK.

Now the designed table(s) in JSON Schema Designer will generate table(s) which will have columns and rows as follows.

DataDesignView

TableDropped

NOTE

  1. With our Web Data Source connection type, you can also interact with Entity Framework data layer through RESTful HTTP services that were built using ASP.NET Web API.
  2. Currently, our dashboard designer supports HTTP Get requests and does not have support for HTTP Post requests since it requests additional data from client to server in the message body like JSON, XML, TEXT etc.
  3. The Column names in the REST API response must be unique.

Connecting to Microsoft Azure Table Storage

To create a new data source select the Microsoft Azure Table Storage option from the connection type.

AzureWindow

Provide the Account Name and Account Key about the data source and then proceed to test the connection. Finally, on clicking the Connect button a new Data source with the given name will be created.

AzureDataView

Dragging and dropping tables listed on the left pane to the center pane creates a de-normalized virtual table that will be used for creating your dashboard. The dashboard designer application joins multiple tables by automatically detecting the related fields but it can be manually defined. A preview of the transformed data is shown in the data grid.

AzureDropTable

Connecting to JSON

With JSON data connection type, you can connect to JSON file whose format will be JSON.

JSONWindow

Set Connection type as JSON.

Set the Database Path by locating the JSON file existing in your machine accessible location.

Click the Connect button, the JSON Schema Designer window will open.

JSON Schema Designer

The JSON Schema Designer is used to design one or more than one table based on JSON schemas in your JSON string.

JSONOption

In the JSON schema designer, all JSON schema information are fetched from the JSON string and all JSON schema are listed in hierarchical order to design one or more than one table. To design a table, you can select any schema which you want to use in that table.

JSONTable

If you have multiple tables in DBMS (Database Management System) structure in your JSON string, then you can use Add option to design the multiple tables as follows.

JSONMultiTable

After designing the table(s) using the JSON schema, click OK.

Now the designed table(s) in the JSON Schema Designer will generate table(s) which will have columns and rows as follows.

DataDesignView

TableDrop

Connecting to SQLite Database

With SQLite data connection type, you can connect to SQLite database whose format will be in DB.

SQLiteWindow

Set Connection type as SQLite.

Set the Database through locating the SQLite Database file existing in your machine accessible location and click Connect.

Now you will get into data design view window.

SQliteDataView

NOTE

If you have only one table in the bounded database, the data design view will have that one table added by default.

Connecting to Hive Data

With Hive data connection type, you can connect to data placed in Hive Server.

HiveWindow

Set Connection type as Hive.

Set the Server name which can be either IP address or the host name of the hive server.

The Port number and the Type will be filled by default as 10000 and HiveServer2 respectively.

Select the Database to connect to and test the connection. If it succeeds, you will get into the data design view like below.

HiveDataView