Ready to get started?

Learn more about the CData ADO.NET Provider for SQL Analysis Services or download a free trial:

Download Now

DataBind SQL Analysis Services Data to the DevExpress Data Grid

Use the CData ADO.NET Provider for SQL Analysis Services with the DevExpress Windows Forms and Web controls to provide SQL Analysis Services data to a chart.

The CData ADO.NET Provider for SQL Analysis Services implements standard ADO.NET data access components that can be used with third-party controls. The standard ADO.NET process for databinding can be followed to enable bidirectional access to live data from UI controls. In this article we show how to use the CData components to databind to the DevExpress Windows Forms and Web controls; you will databind to a chart that displays current data.

To connect, provide authentication and set the Url property to a valid SQL Server Analysis Services endpoint. You can connect to SQL Server Analysis Services instances hosted over HTTP with XMLA access. See the Microsoft documentation to configure HTTP access to SQL Server Analysis Services.

To secure connections and authenticate, set the corresponding connection properties, below. The data provider supports the major authentication schemes, including HTTP and Windows, as well as SSL/TLS.

  • HTTP Authentication

    Set AuthScheme to "Basic" or "Digest" and set User and Password. Specify other authentication values in CustomHeaders.

  • Windows (NTLM)

    Set the Windows User and Password and set AuthScheme to "NTLM".

  • Kerberos and Kerberos Delegation

    To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the data provider attempts to communicate with the SPN at the specified Url.

  • SSL/TLS:

    By default, the data provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats.

You can then access any cube as a relational table: When you connect the data provider retrieves SSAS metadata and dynamically updates the table schemas. Instead of retrieving metadata every connection, you can set the CacheLocation property to automatically cache to a simple file-based store.

See the Getting Started section of the CData documentation, under Retrieving Analysis Services Data, to execute SQL-92 queries to the cubes.

Windows Forms Controls

The code below shows how to populate a DevExpress chart with SQL Analysis Services data. The SSASDataAdapter binds to the Series property of the chart control. The Diagram property of the control defines the x- and y-axes as the column names.

using (SSASConnection connection = new SSASConnection( "User=myuseraccount;Password=mypassword;URL=http://localhost/OLAP/msmdpump.dll;")) { SSASDataAdapter dataAdapter = new SSASDataAdapter( "SELECT Fiscal_Year, Sales_Amount FROM Adventure_Works", connection); DataTable table = new DataTable(); dataAdapter.Fill(table); DevExpress.XtraCharts.Series series = new DevExpress.XtraCharts.Series(); chartControl1.Series.Add(series); series.DataSource = table; series.ValueDataMembers.AddRange(new string[] { "Sales_Amount" }); series.ArgumentScaleType = DevExpress.XtraCharts.ScaleType.Qualitative; series.ArgumentDataMember = "Fiscal_Year"; series.ValueScaleType = DevExpress.XtraCharts.ScaleType.Numerical; chartControl1.Legend.Visibility = DevExpress.Utils.DefaultBoolean.False; ((DevExpress.XtraCharts.SideBySideBarSeriesView)series.View).ColorEach = true; }

Web Controls

The code below shows how to populate a DevExpress Web control with SQL Analysis Services data. The SSASDataAdapter binds to the Series property of the chart; the Diagram property defines the x- and y-axes as the column names.

using DevExpress.XtraCharts; using (SSASConnection connection = new SSASConnection( "User=myuseraccount;Password=mypassword;URL=http://localhost/OLAP/msmdpump.dll;")) { SSASDataAdapter SSASDataAdapter1 = new SSASDataAdapter("SELECT Fiscal_Year, Sales_Amount FROM Adventure_Works", connection); DataTable table = new DataTable(); SSASDataAdapter1.Fill(table); DevExpress.XtraCharts.Series series = new Series("Series1", ViewType.Bar); WebChartControl1.Series.Add(series); series.DataSource = table; series.ValueDataMembers.AddRange(new string[] { "Sales_Amount" }); series.ArgumentScaleType = ScaleType.Qualitative; series.ArgumentDataMember = "Fiscal_Year"; series.ValueScaleType = ScaleType.Numerical; ((DevExpress.XtraCharts.SideBySideBarSeriesView)series.View).ColorEach = true; }