Data Sources

• Nov 03, 2023
• 9 minutes to read

A data source collects underlying data and passes it to a data engine for aggregation and summarization before data analysis.

The following data source types are supported:

Type	Description	API
SQL Data Source	Provides connections to SQL databases.	DashboardSqlDataSource
OLAP Data Source	Provides connections to an OLAP cube in the Microsoft Analysis Services database.	DashboardOlapDataSource
Microsoft Excel Workbooks/CSV Files	Retrieves data from Microsoft Excel workbooks (XLS, XLSX or XLSM), or from CSV files.	DashboardExcelDataSource
Entity Framework Data Source	You can connect to an Entity Framework data source defined within the current project or you can use an external assembly that contains the data context.	DashboardEFDataSource
Object Data Source	Retrieves data from a class in the current project.	DashboardObjectDataSource
Extract Data Source	A compressed snapshot of data from a regular data source. This data is contained in a local file and can be updated from the original data source at any time. You can also use an external SQL data source or Extract Data Source as an intermediate layer.	DashboardExtractDataSource
XPO Data Source	Allows you to obtain data that XPO (eXpress Persistent Objects) retrieves from a data source.	DashboardXpoDataSource
JSON Data Source	Allows you to extract JSON data from a Web-service endpoint URI, text file, or a string in JSON format.	DashboardJsonDataSource
Federated Data Source	Establishes relationships and combines data sources to provide uniform access. A federated data source does not support OLAP data sources.	DashboardFederationDataSource
MongoDB	Allows you to connect to a MongoDB instance and stores data in JSON-like documents.	DashboardMongoDBDataSource

Note

The DevExpress Dashboard uses database servers that can be configured to use case-sensitive table/column names. For this reason, a database scheme used in dashboards is case-sensitive to prevent any problem when the product is used with such servers.

SQL Data Source

To connect to SQL databases, install the data providers on the client machine or web server. The table below lists the supported SQL databases and data providers.

Relational Database System	Supported Versions	.NET Framework Data Provider	.NET 6+ Data Provider
Microsoft SQL Server	2005 or higher 2005 Express or higher Azure SQL Database	System.Data.SqlClient System.Data.dll (Included in .NET Framework)	System.Data.SqlClient
Microsoft Access	Access 2000 or higher Access 2007 or higher	System.Data.dll	System.Data.OleDb
Microsoft SQL Server CE	3.5, 4.0	System.Data.SqlServerCe.dll (Included in .NET Framework)
Oracle Database	9i or higher	Oracle.ManagedDataAccess System.Data.OracleClient.dll Oracle.DataAccess.dll	Oracle.ManagedDataAccess
Amazon Redshift	n/a	Npgsql	Npgsql
Google BigQuery	Only legacy SQL functions and operations are supported	ODBC driver for BigQuery	ODBC driver for BigQuery
Teradata	13.0 or higher	Teradata.Client.Provider	Teradata.Client.Provider
SAP HANA	2.0 or higher	SAP HANA Client 2.0	SAP HANA Client 2.0 Sap.Data.Hana.Core.v2.1.dll
SAP Sybase Advantage	Advantage Database Server 9.1 or higher	Advantage.Data.Provider.dll
SAP Sybase ASE	Sybase Adaptive Server 12.0 or higher	Sybase.AdoNet4.AseClient
SAP SQL Anywhere	11 or higher	Sap.Data.SQLAnywhere
IBM DB2	9.5 or higher	IBM.Data.DB2.dll	IBM.Data.DB2.Core
Firebird	1.5 or higher, Dialect 3	FirebirdSql.Data.FirebirdClient FirebirdSql.Data.Firebird.dll	FirebirdSql.Data.FirebirdClient
MySQL	4.1 or higher 5.5, 5.6, 5.7, 8.0, 8.3 (for the MySqlConnector provider)	MySqlConnector MySql.Data	MySqlConnector MySql.Data
Pervasive PSQL	9.x or higher	Pervasive.Data.SqlClient.dll
PostgreSQL	7.x or higher	Npgsql	Npgsql
VistaDB	4, 5, 6	VistaDB.6.dll
SQLite	3.x	System.Data.SQLite Microsoft.Data.SQLite	System.Data.SQLite Microsoft.Data.SQLite
XML file	n/a	Built-in support	Built-in support

You can prepare data in the Query Builder in the following ways:

• Join tables/views.
• Create additional columns using expressions.
• Pre-aggregate data.
• Filter data.

You can also use a custom SQL query (WinForms / Web).

You can add calculated fields to this data source.

Create and configure the DashboardSqlDataSource object to bind a dashboard to an SQL database.

Note

If you use a custom connection string, specify the XpoProvider parameter as described in the following topic: Custom Connection Strings for Data Sources.

Tip

Documentation:

• WinForms Dashboard Designer: SQL Data Source
• Connect the ASP.NET Core Dashboard Control to an SQL Data Source
• Connect the ASP.NET MVC Dashboard Extension to an SQL Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an SQL Data Source
• Configure SQL Data Sources in the Web Dashboard

OLAP Data Source

The Microsoft Analysis Services OLE DB and Microsoft ADOMD.NET providers should be installed on the client machine or web server. Refer to the following topic for more information: Data providers used for Analysis Services connections.

The following OLAP servers are supported:

• Microsoft SQL Server 2000 Analysis Services
• Microsoft SQL Server 2005 Analysis Services
• Microsoft SQL Server 2008 Analysis Services
• Microsoft SQL Server 2008 R2 Analysis Services
• Microsoft SQL Server 2012 Analysis Services (Multi-dimensional mode)
• Microsoft SQL Server 2014 Analysis Services (Multi-dimensional mode)
• Microsoft SQL Server 2016 Analysis Services (Multi-dimensional mode)

For .NET Dashboard, OLAP mode supports the following data providers:

• XML for Analysis (XMLA) with MSMDPUMP;
• ADOMD.NET (from v 21.1.4).

You cannot filter the OLAP data source. Calculated fields are also not supported.

Create and configure the DashboardOlapDataSource object to bind a dashboard to an OLAP cube.

Tip

Documentation:

• WinForms Dashboard Designer: OLAP Data Source
• Connect the ASP.NET Core Dashboard Control to an OLAP Data Source
• Connect the ASP.NET MVC Dashboard Extension to an OLAP Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an OLAP Data Source

Microsoft Excel Workbooks/CSV Files

The following Microsoft Excel/text formats are supported:

• XLS
• XLSX
• XLSM
• CSV

You can filter the Excel data source and add calculated fields.

Create and configure the DashboardExcelDataSource object to bind a dashboard to an Excel workbook.

Tip

Documentation:

• WinForms Dashboard Designer: Excel Database
• Connect the ASP.NET Core Dashboard Control to an Excel Database
• Connect the ASP.NET MVC Dashboard Extension to an Excel Database
• Connect the ASP.NET Web Forms Dashboard Control to an Excel Database

Entity Framework Data Source

The following Entity Framework versions are supported:

• Entity Framework 5.0 and higher.
• Entity Framework Core 1.0 and higher.

You can use stored procedures to select only required data from the Entity Framework data source.

Calculated fields are also supported.

Create and configure the DashboardEFDataSource object to bind a dashboard to an Entity Framework data source.

Note

The DashboardEFDataSource should have a data provider to connect to a data source. For instance, install the System.Data.SQLite.EF6 data provider to connect to an SQLite database that uses Entity Framework 6.

Tip

Documentation:

• WinForms Dashboard Designer: Entity Framework Data Source
• Connect the ASP.NET MVC Dashboard Extension to an Entity Framework Data Source
• Connect the ASP.NET Core Dashboard Control to an Entity Framework Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an Entity Framework Data Source

Object Data Source

You can use any object that implements the IEnumerable or IListSource interface as a dashboard’s data source.

You can filter the Object data source and add calculated fields.

Create and configure the DashboardObjectDataSource object to bind a dashboard to an object data source.

Tip

Documentation:

• WinForms Dashboard Designer: Object Data Source
• Connect the ASP.NET Core Dashboard Control to an Object Data Source
• Connect the ASP.NET MVC Dashboard Extension to an Object Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an Object Data Source

Extract Data Source

The DevExpress Dashboard supports a compressed data snapshot from a regular data source (such as DashboardSqlDataSource, DashboardEFDataSource, and DashboardObjectDataSource). You can save a snapshot to a local file and update it from the original data source. An extracted data source can be used as a regular data source.

The data within the extract is optimized for data grouping and reduces the initial load time. You can use an in-memory cache (ExtractCacheOptions) to improve performance when accessing extract data.

You can add calculated fields to this data source.

Create and configure the DashboardExtractDataSource object to bind a dashboard to a data extract.

Tip

Documentation:

• WinForms Dashboard Designer: Extract Data Source
• Connect the ASP.NET Core Dashboard to an Extract Data Source
• Connect the ASP.NET MVC Dashboard Extension to an Extract Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an Extract Data Source

XPO Data Source

The XPO data source gets data from eXpress Persistent Objects.

You can filter the XPO data source and add calculated fields.

Create and configure the DashboardXpoDataSource object to bind a dashboard to eXpress Persistent Objects.

Tip

Documentation:

• WinForms Dashboard Designer: XPO Data Source
• Connect the ASP.NET MVC Dashboard Extension to an XPO Data Source
• Connect the ASP.NET Web Forms Dashboard Control to an XPO Data Source
• Connect the ASP.NET Core Dashboard Control to an XPO Data Source

JSON Data Source

The JSON data source retrieves data from a Web-service endpoint, text file, or string in JSON format.

You can filter the JSON data source and add calculated fields.

Create and configure the DashboardJsonDataSource object to bind a dashboard to JSON-formatted data.

Important

The DashboardJsonDataSource object requires the open source Newtonsoft.Json library. Install the Newtonsoft.Json NuGet package in Visual Studio.

Tip

Documentation:

• WinForms Dashboard Designer: JSON Data Source
• Connect the ASP.NET MVC Dashboard Extension to a JSON Data Source
• Connect the ASP.NET Web Forms Dashboard Control to a JSON Data Source
• Connect the ASP.NET Core Dashboard Control to a JSON Data Source

Federated Data Source

You can create a query that combines data sources to provide uniform access. This query forms the federated data source, which operates with the source-specific query language, translates the queries, and processes the results to supply a dashboard with data. A federated data source does not support OLAP data sources.

You can filter the Federated data source and add calculated fields.

Create and configure the DashboardFederationDataSource object to bind a dashboard to a federated data source.

View Example: How to Bind a Dashboard to a Federated Data Source Created at Runtime

Tip

Documentation:

• WinForms Dashboard Designer: Federated Data Source
• How to: Bind ASP.NET MVC Dashboard to a Federated Data Source

MongoDB

Create and configure a DashboardMongoDBDataSource object to bind a dashboard to MongoDB.

You can use MongoDB as the original data source to create an Extract database or a federated data source.

You can filter the MongoDB data source and add calculated fields.

Note

The MongoDB.Driver package should be installed in your project to supply MongoDB data at runtime.

Tip

Documentation:

• How to: Bind a Dashboard to MongoDB