Learn more
 

Ready to get started?

Download a free trial of the SQL Analysis Services Data Provider to get started:

 Download Now

Learn more:

SQL Server Analysis Services Icon SQL Analysis Services ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with SQL Analysis Services.

In this article

Related articles

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 ADO.NET Provider for SQL Analysis Services by CData incorporates conventional ADO.NET data access components compatible with third-party controls. You can adhere to the standard ADO.NET data binding procedures to establish two-way access to real-time data through UI controls. This article will demonstrate the utilization of CData components for data binding with DevExpress UI Controls (Windows Forms and Web controls), specifically binding to a chart that visualizes live 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; }