Access Analysis Services Cubes with WCF

WCF Overview

WCF is a service-oriented technology whereby you build programmatic modules that provide business logic and data to consuming applications over HTTP and TCP protocols. WCF unifies many pre-existing Microsoft distributed technologies including ASP.Net Web Services, .Net Remoting, Enterprise Services, Web Service Extensions (WSE), and Microsoft Message Queuing (MSMQ). WCF has the large architectural goal of providing a single service-oriented platform that can be used to build services that will be guaranteed to interoperate with the technological platforms of the future.

WCF’s implementation is found in the System.ServiceModel namespace in the .Net Framework. WCF was first introduced with the .Net Framework v3.0. The latest production version of WCF is found within the .Net Framework v3.5 sp1.

The primary constraint involved with WCF with regard to data access is that the service must send all types back to consuming clients serialized.

Note: Serialization is the process of converting the state of an object into a format that can be persisted or transported. Deserialization is the inverse process of converting a transportable format into the state of an object.

SSAS Overview

SSAS provides both OLAP & Data Mining services for Business Intelligence (BI) and decision support solutions. Developers use Business Intelligence Development Studio (BIDS) to construct and deploy SSAS solutions. SSAS was first introduced as OLAP services with SQL Server 7. The latest production version of SSAS is found within SQL Server 2008 sp1.

SSAS Object Models & Accessibility

SSAS exposes pre-aggregated and predictive analytics that are consumed with programmatic object models that ship with the product. All of the SSAS objects models translate their programmatic requests into corresponding XML for Analysis (XMLA) that is then sent to the targeted SSAS server. Responses from the SSAS server are in turn sent back to the calling entity with XMLA as well. The SSAS object models can be summarized as follows:

Additionally, there is a “back door” approach you can leverage to access Analysis Services content programmatically by leveraging SQL Server’s (RDBMS) native Linked Server capabilities with the associated OPENQUERY syntax. At this point any traditional OLEDD, ODBC, or the composite SQL Server Native Client (SqlClient) data access providers can be used to obtain SSAS analytics. In the sections that follow I will discuss these objects models and methods for accessing SSAS.

AMO

Analysis Management Objects (AMO) is an administrative object model you can use to programmatically manage instances of SSAS. AMO resides in the Microsoft.AnalysisServices namespace. There are a total of 260 classes found in this object model. Like all other SSAS object models, AMO translates programmatic calls into XMLA and sends the XMLA to the SSAS instance. I will not go into detail on all of the classes found in the AMO object model; however there is a single class that is particular interest for executing queries.

The AMO Server object provides an Execute() method for executing XMLA commands. You can leverage the XMLA element with inline MDX/DMX queries to execute queries and received resulting XMLA messages.

WCF Sample Code

string AMO_Execute(){string sXML;XmlaResultCollection oXMLA;string sMDX_XMLA =SELECT [Measures].MEMBERS ON COLUMNS FROM [Sales Quotas]Statement”Server oSrv = Server();oSrv.Connect("Data Source=localhost; Catalog=ssas_AW2008");sXML = oXMLA.Count.ToString();return sXML;}

ADOMD.Net

ADOMD.NET is a Microsoft .NET Framework data provider that is designed to communicate with SSAS. ADOMD.NET uses the XML for Analysis protocol to communicate with analytical data sources by using either TCP/IP or HTTP connections to transmit and receive SOAP requests and responses that are compliant with the XML for Analysis specification. Commands can be sent in Multidimensional Expressions (MDX), Data Mining Extensions (DMX), Analysis Services Scripting Language (ASSL), or even a limited syntax of SQL, and may not return a result. Analytical data, key performance indicators (KPIs), and mining models can be queried and manipulated by using the ADOMD.NET object model. By using ADOMD.NET, you can also view and work with metadata either by retrieving OLE DB-compliant schema rowsets or by using the ADOMD.NET object model.

ADOMD.Net Client Object Model

The client object model resides in the Microsoft.AnalysisServices.AdomdClient namespace. This object model provides client and middle-tier applications the ability to query both data and metadata from SSAS. Version 9 of ADOMD is the build that ships with SSAS 2005 while version 10 is the build that ships with SSAS 2008.

There are a total of 90 classes found in the ADOMD Client object model including classes that are specific to OLAP and Data Mining. I will not review every class as that is beyond the scope of this paper, however I will review the key classes and their associated usage.

WCF Sample Code

 public string ADOMD_XML()        {            string sXML = "";            string sMDX = "SELECT [Dim Date].[Calendar].[Calendar Year] ON 0,[Measures].[Sales Amount Quota] ON 1 FROM [Sales Quotas]";            System.Xml.XmlReader oXMLRead;            //SSAS: Connect            AdomdConnection oConn = new AdomdConnection("Data Source=localhost; Caalog=ssas_AW2008");                   //SSAS: Create Command            AdomdCommand oCmd = new AdomdCommand(sMDX, oConn);            //SSAS: Execute Command            oCmd.Connection.Open();            oXMLRead = oCmd.ExecuteXmlReader();            sXML = oXMLRead.ReadInnerXml();            oCmd.Connection.Close();            return sXML;        }

ADOMD.Net Server Object Model

The server object model resides in the Microsoft.AnalysisServices.AdomdServer namespace. This object model provides the ability to create custom MDX and DMX functions as well as stored procedures that are executed on the SSAS server.  You can then invoke these custom routines with MDX or DMX. Functions are written for either MDX (cube) or DMX (data mining) contexts by leveraging different properties of the Context object.

The server object model is not discussed nor is it a key candidate for SSAS cube consumption because stored procedures and functions must still be called via ADOMD and its constraints regarding serialization of return types.<

Note: You can leverage the AMO object model from within SSAS stored procedures.

SqlClient with Linked Servers & OPENQUERY

SQL Server’s relational database engine (RDBMS) provides a feature called linked servers which allow the RDBMS to access remote data sources with inline-distributed queries as well as direct querying capabilities with the T-SQL OPENQUERY syntax. By leveraging linked servers with SSAS you can create a pass-through MDX query that is executed on the SSAS server using the SqlClient data access provider.

The primary benefit of using this method is that we can leverage a traditional System.Data.DataSet because it is serializable.

Sample Code

Step 1: Creating the Linked Server on the SQL Server RDBMS

/****** Object:  LinkedServer [SSAS]    Script Date: 06/05/2009 08:09:01 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=N'ssas_AW2008'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

Step 2: Test the Linked Server with a T-SQL Connection

USE [Master]

GO

SELECT * FROM OPENQUERY(SSAS, 'SELECT [Dim Date].[Calendar].[Calendar Year] ON 0,[Measures].[Sales Amount Quota] ON 1 FROM [Sales Quotas]')

GO

Step 3: WCF Method

        public DataSet SQLClient_OpenQuery()

        {

            DataSet oDS = new DataSet();

            string sOpenQuery = "SELECT * FROM OPENQUERY(SSAS, 'SELECT [Dim Date].[Calendar].[Calendar Year] ON 0,[Measures].[Sales Amount Quota] ON 1 FROM [Sales Quotas]')";

            //RDBMS: Connect

            System.Data.SqlClient.SqlConnection oConn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;");

            //RDBMS: Command & DataAdapter

            System.Data.SqlClient.SqlCommand oCmd = new System.Data.SqlClient.SqlCommand(sOpenQuery, oConn);

            System.Data.SqlClient.SqlDataAdapter oDA = new System.Data.SqlClient.SqlDataAdapter(oCmd);

            //RDBMS: Execute Command

            oDA.Fill(oDS);

            return oDS;

        }

Step 4: Test Client Code

        private void btnSqlClient_Click(object sender, EventArgs e)

        {

            ServiceReference1.Service1Client oSvc = new ServiceReference1.Service1Client();

            DataSet oDS = oSvc.SQLClient_OpenQuery();

            MessageBox.Show(oDS.Tables[0].Rows.Count.ToString());

        }

Summary

In this blog I have presribed three separate programmatic approaches for accessing SSAS data and metadata with WCF. These methods can be summarized as follows:

1. AMO’s Server.Execute() Method, passing in XMLA

2. ADOMD Client’s AdomdCommand.ExecuteXMLReader() Method, passing in “pure” MDX

3. SQL Server’s Linked Servers feature with pass-through MDX queries