天天看點

SSAS : 資料通路接口整理彙總

Microsoft SQL Server 2005 Analysis Services (SSAS) supports several technologies that can be used to access data and metadata stored on an instance of Analysis Services. Using the Analysis Services data access technologies, you can do the following:

Retrieve Schema Rowsets

Schema rowsets are one way in which client applications can examine metadata stored on, and retrieve support and monitoring information from, an Analysis Services instance. You can retrieve OLE DB, OLE DB for Online Analytical Processing (OLAP), OLE DB for Data Mining, or XML for Analysis (XMLA) schema rowsets.

To retrieve schema rowsets:

  • You can use OLE DB commands with the Analysis Services 9.0 OLE DB provider. For more information, see Analysis Services 9.0 OLE DB Provider (SSAS).這是一個非托管的提供程式,msolap90.dll
  • You can run the XMLA Discover method directly against an Analysis Services instance to retrieve schema rowset information. For more information, see Discover Method (XMLA).

Retrieve Data and Metadata

You can retrieve data and metadata, in either tabular or multidimensional formats, from cubes and mining models.

To retrieve data and metadata:

  • You can use ADOMD.NET in a managed client application to retrieve Analysis Services information using common Microsoft .NET Framework data access interfaces. For more information, see ADOMD.NET.
  • You can use the Analysis Services 9.0 OLE DB provider in unmanaged client applications to support OLE DB access. For more information, see Analysis Services 9.0 OLE DB Provider (SSAS).
  • You can use XMLA to retrieve data directly from an Analysis Services instance in an XML-based format. For more information, see XML for Analysis (XMLA).

Manipulate Analysis Services Objects

You can manipulate Analysis Services objects, such as databases, cubes, dimensions, and mining structures, using Multidimensional Expressions (MDX), Data Mining Extensions (DMX), or XMLA commands.

To manipulate Analysis Services objects:

  • You can use ADOMD.NET in a managed client application to send commands using common .NET Framework data access interfaces. For more information, see ADOMD.NET.
  • You can use the Analysis Services 9.0 OLE DB provider in unmanaged client applications to send commands using OLE DB access. For more information, see Analysis Services 9.0 OLE DB Provider (SSAS).
  • You can use XMLA to send commands directly to an Analysis Services instance. For more information, see XML for Analysis (XMLA).

何時使用OLEDB Provider?

在非托管環境中,例如在Excel,就是使用OLEDB 的方式

如何使用OLEDB Provider?

​​

SSAS : 資料通路接口整理彙總

​​

它的連接配接字元串,大緻如下

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventureworks;Data Source=(local);MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

如何在VB或者VBA代碼中使用,則需要先添加引用

SSAS : 資料通路接口整理彙總
SSAS : 資料通路接口整理彙總

何時使用ADOMD.NET ?

在托管環境中。例如.NET編寫的程式。

如何使用ADOMD.NET?

SSAS : 資料通路接口整理彙總

何時使用XMLA?

在沒有辦法使用OLEDB以及ADOMD.NET的時候。例如在java中直接通過XML的方式來操作

通常都是發送下面這樣的消息到伺服器。當然,前提是伺服器已經配置為可以使用http通路

<Envelope xmlns="​​http://schemas.xmlsoap.org/soap/envelope/"​​>

  <Header>

    <XA:Session soap:mustUnderstand="1"  SessionId="$sessionId$" xmlns:soap="​​http://schemas.xmlsoap.org/soap/envelope/"​​ xmlns:XA="urn:schemas-microsoft-com:xml-analysis" />

  </Header>

  <Body>

    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">

      <Command>

        <Statement>     

        SELECT [Customer].[Customer Geography].[Country-Region] ON 0,

[Measures].[Internet Sales-Sales Amount] ON 1

FROM [Analysis Services Tutorial]</Statement>

      </Command>

      <Properties>

        <PropertyList>

        <Catalog>Adventureworks</Catalog>

          <LocaleIdentifier>2052</LocaleIdentifier>

          <DataSourceInfo />

          <Format>Multidimensional</Format>

          <AxisFormat>TupleFormat</AxisFormat>

          <Content>SchemaData</Content>

        </PropertyList>

      </Properties>

    </Execute>

  </Body>

</Envelope>