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 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:
- AMO (Managed)
- Management Object Model
- ADOMD.Net (Managed)
- Client Object Model
- Server Object Model
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.
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 <Execute> 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\]
- Server oSrv = Server();
- oSrv.Connect("Data Source=localhost; Catalog=ssas_AW2008");
- sXML = oXMLA.Count.ToString();
- return sXML;\}
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.
Establishes connections to SSAS
Executes MDX and DMX Queries
This class provides 6 key methods of query execution:
Only the ExecuteXMLReader() method returns a serializable object
An in-memory representation of a MDX/DMX query response. This object is not serializable
Contains OLAP cube metadata
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
- oXMLRead = oCmd.ExecuteXmlReader();
- sXML = oXMLRead.ReadInnerXml();
- 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.
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
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
Step 2: Test the Linked Server with a T-SQL Connection
SELECT * FROM OPENQUERY(SSAS, 'SELECT \[Dim Date\].\[Calendar\].\[Calendar Year\] ON 0,\[Measures\].\[Sales Amount Quota\] ON 1 FROM \[Sales Quotas\]')
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\]')";
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
Step 4: Test Client Code
private void btnSqlClient_Click(object sender, EventArgs e)
ServiceReference1.Service1Client oSvc = new ServiceReference1.Service1Client();
DataSet oDS = oSvc.SQLClient_OpenQuery();
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