Connect to data and view it on a dashboard

Contents[Hide]

1. Overview

This walkthrough shows you how to connect to a SQL Server data source as an example, and display some data on a dashboard.

The figures in the article use the Adventure Works for SQL Server 2012 database.

2. Connecting to data

In order to create a data connector, you generally need to have some knowledge about the type of data source as well as the necessary permissions to connect and perform queries.

You also need to be a user with at least power user access.

2.1. Create a new data connector

A data connector lets you connect to a data source by choosing a data provider and setting various fields that are specific to that provider.

You can create a new data connector in Dundas BI from the main menu.

Create a new data connector
Create a new data connector

Click the Data Connector item to open the New Data Connector dialog. This is a scrollable dialog with a number of fields to set depending on the type of data provider you want to use.

New Data Connector dialog
New Data Connector dialog

2.2. Choose a file name and folder location

Items within your Dundas BI project are generally referred to as files. When you create a new data connector, the first thing you need to do is choose a folder location and give it a file name.

Click inside the File Name box to open the Save File dialog.

Enter a name for your data connector and select a folder location within your Dundas BI project. Typically, this will be the Data Connectors folder, but you can also choose a subfolder if one is available.

Save File dialog
Save File dialog

2.3. Select a data provider

You can connect to a wide range of data sources with Dundas BI. By default, the data provider dropdown is set to Microsoft SQL Server.

Data provider choices
Data provider choices

Depending on the data provider selected, the remaining fields may be different.

2.4. Settings for SQL Server

This section details some specific settings that are available based on choosing SQL Server.

2.4.1. Authentication

There are a number of different authentication methods to choose from:

  • Server Windows credentials
  • Use SQL Server authentication
  • Specified Windows credentials
  • Impersonated Windows credentials

Server Windows credentials is the default setting.

SQL Server settings
SQL Server settings

2.4.2. Data Source

This is the name of your SQL Server instance (e.g., the server name).

2.4.3. Initial Catalog

This is the name of  the database from your SQL Server instance that you want to connect to.

2.4.4. Advanced settings

For SQL Server, there are other optional and advanced settings which you can configure. Hover with your mouse over a field value to see a tooltip describing what the field is for.

2.5. Test the connection

Once you have set the required data connector fields, click Test connection as a quick way of verifying the settings you just entered.

Test connection
Test connection

2.6. Submit

Finally, click Submit (checkmark) at the bottom of the New Data Connector dialog to create the connector.

Submit button
Submit button

You'll see a message telling you the data connector was created and that a discovery operation was started. Discovery retrieves information about native data structures such as tables, and programmatic elements such as stored procedures and functions. You don't need to wait for the discovery operation to complete because it runs in the background.

Discovery started
Discovery started

Note
Stored Procedures that don't output a value are not supported.

3. Viewing data

Now that you have a data connector providing access to a data source, you can display some data using a metric set or dashboard.

3.1. Create a new dashboard

Use the main menu to create a new dashboard.

Create a new dashboard
Create a new dashboard

Select the Blank template for your new dashboard.

Blank dashboard template
Blank dashboard template

The dashboard editor is displayed. It consists of a main menu on the left, a toolbar along the top, a status bar along the bottom, and the actual design surface with horizontal and vertical rulers. The white area within the design surface represents your dashboard and is generally referred to as the canvas. You can drag and drop items onto the canvas in order to compose your dashboard.

Dashboard Designer screen
Dashboard Designer screen

3.2. Find your data connector

Click the EXPLORE window, which is docked on the right, to see all the items within the current project.

Expand the Data Connectors folder to see the data connector created previously. Further expand the data connector itself to see the native data structures discovered, including database tables and programmatic structures such as stored procedures and functions.

Data connector native structures
Data connector native structures

3.3. Drag a database table to the canvas

Under your data connector, scroll down and locate a specific database table you want to see. Drag the database table and drop it onto the canvas. The database table will appear as a table visualization (i.e., data grid) that displays raw data. In this mode, the records are displayed as they exist in the native table, without aggregation.

Drag database table to canvas
Drag database table to canvas

Note
Dragging an entire native table (as opposed to a single column) to the dashboard canvas is one of the simplest operations you can do and is primarily used just to preview the data.

3.4. View the dashboard

Click View in the toolbar to switch to View mode. This lets you interact with your dashboard, such as using the scrollbars on the table visualization. Click Edit in the toolbar to go back to Edit mode.

4. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri