ADOMD.NET (Microsoft .NET Framework data provider )

ADOMD.NET is a Microsoft .NET Framework data provider that is designed to communicate with Microsoft SQL Server 2005 Analysis Services (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 Architecture 

Microsoft SQL Server 2005 Analysis Services (SSAS) uses both server and client components to supply online analytical processing (OLAP) and data mining functionality for business intelligence applications:

The server component of Analysis Services is implemented as a Microsoft Windows service. SQL Server 2005 Analysis Services supports multiple instances on the same computer, with each instance of Analysis Services implemented as a separate instance of the Windows service.

Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol for issuing commands and receiving responses, exposed as a Web service. Client object models are also provided over XMLA, and can be accessed either by using a managed provider, such as ADOMD.NET, or a native OLE DB provider.

Query commands can be issued using the following languages: SQL; Multidimensional Expressions (MDX), an industry standard query language for analysis; or Data Mining Extensions (DMX), an industry standard query language oriented toward data mining. Analysis Services Scripting Language (ASSL) can also be used to manage Analysis Services database objects

Analysis Services Concepts

Microsoft SQL Server 2005 Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining functionality for business intelligence solutions. Before designing a business intelligence solution using Analysis Services, you should familiarize yourself with the OLAP and data mining concepts required for a successful solution.

Analysis Services combines the best aspects of traditional OLAP-based analysis and relational-based reporting by enabling developers to define a single data model, called a Unified Dimensional Model (UDM) over one or more physical data sources. All end user queries from OLAP, reporting, and custom BI applications access the data in the underlying data sources through the UDM, which provides a single business view of this relational data.

Analysis Services provides a rich set of data mining algorithms to enable business users to mine their data looking for specific patterns and trends. These data mining algorithms can be used to analyze data through a UDM or directly from a physical data store.

ADOMD.NET source code for querying KPIs in Analysis Services 2005 in ASP.NET

One of the more publicized features of Analysis Services 2005 is the intrinsic support for the KPIs . KPIs are defined by cube designer and any client application can programmatically get the rich metadata about them (i.e. Goals, Trends, Graphics etc) and query them. Support for programmability of KPIs is built-in into all layers of Analysis Services APIs – schema rowset in XML for Analysis, OLEDB for OLAP, MDX functions, objects in ADOMD.NET object model etc. Another interesting thing about that source code is that it makes use of parametric queries in ADOMD.NET. Parametric queries are something that application developers have asked for and now Analysis Services 2005 supports them. One additional comment about Olivier’s code – when he wrote it, the MDX functions KPIValue, KPIGoal, KPITrend etc didn’t accept KPI name as a string, and a result he was forced to use StrToMember functions in the MDX query generation. In the more recent Yukon builds it changed, so the code now will be simplified – i.e. the following snippet

 myKPICommand.CommandText = @"     SELECT { strtomember(@Value), strtomember(@Goal), strtomember(@Status), strtomember(@Trend) }      ON COLUMNS FROM [" +myCubeDef.Name + "]";  myKPICommand.Parameters.Clear();  myKPICommand.Parameters.Add(new AdomdParameter("Value", "KPIValue([" + k.Name + "])"));  myKPICommand.Parameters.Add(new AdomdParameter("Goal", "KPIGoal([" + k.Name + "])"));  myKPICommand.Parameters.Add(new AdomdParameter("Status", "KPIStatus([" + k.Name + "])"));  myKPICommand.Parameters.Add(new AdomdParameter("Trend", "KPITrend([" + k.Name + "])"));

would become

 myKPICommand.CommandText = @"     SELECT { KPIValue(@Value), KPIGoal(@Goal), KPIStatus(@Status), KPITrend(@Trend) }      ON COLUMNS FROM [" +myCubeDef.Name + "]";  myKPICommand.Parameters.Clear();  myKPICommand.Parameters.Add(new AdomdParameter("Value", k.Name));  myKPICommand.Parameters.Add(new AdomdParameter("Goal",  k.Name));  myKPICommand.Parameters.Add(new AdomdParameter("Status",k.Name));   myKPICommand.Parameters.Add(new AdomdParameter("Trend", k.Name)); 

Finally, below is the sample screenshot of how it would look like: 

KPIViewer

Advertisements

One thought on “ADOMD.NET (Microsoft .NET Framework data provider )

  1. Pingback: ADOMD.NET (Microsoft .NET Framework data provider )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s