【ADOMD.NET】一、使用ADOMD.NET建立与Analysis Services的连接

C#操作Analysis Services，AMO是Analysis Services的管理类的完整集合，可在托管环境中，在Microsoft.AnalysisServices命名空间下以编程方式使用。

这些类包含在 AnalysisServices.dll文件中，该文件通常位于SQL Server安装目录下的\100\SDK\Assemblies\文件夹中。使用AMO可以创建、修改和删除对象，如多维数据集、维度、挖掘结构以及Analysis Services数据库。

但需要注意的是：无法通过 AMO 来查询数据，若要查询数据，请使用ADOMD.NET，ADOMD.NET使用XMLA（XML for Analysis)协议与服务器进行通讯。

Adomd.net SDK下载地址

安装了对应的MSI包之后，Microsoft.AnalysisServices.AdomdClient.dll文件通常位于x:\Program Files\Microsoft.NET\Adomd.NET\下。

• 添加对Microsoft.AnalysisServices.AdomdClient.dll的引用

using Microsoft.AnalysisServices.AdomdClient;

• 建立连接

string connectionString = "Data Source=JINGXIAO;Catalog=FoodMart2000;ConnectTo=8.0;Integrated Security=SSPI";
AdomdConnection conn = new AdomdConnection();
conn.Open();
conn.Close();

【注】如果没有安装MSXML4.0或者更高的版本，则运行上述代码的时候，会显示【无法与服务器建立连接的报错框】。

官方的建立连接语句：

AdomdConnection advwrksConnection = new AdomdConnection("Data Source=localhost;Catalog=AdventureWorksAS");

再来一个网友写的：

AdomdConnection conn = new AdomdConnection(@"Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorks Tabular Model SQL 2012;Data Source=.\MSBI");

conn.Open();

• 获取数据

private DataTable tabularQueryExecute(string qry, ADOMD.AdomdConnection cnx)
{
ADOMD.AdomdDataAdapter currentDataAdapter = new ADOMD.AdomdDataAdapter(qry, cnx);
DataTable tabularResults = new DataTable();
currentDataAdapter.Fill(tabularResults);
return tabularResults;
}

• 模拟一段DAX语句

DEFINE 
    MEASURE 'Product Category'[Internet Sales] 
　　　　　　= SUM( 'Internet Sales'[Sales Amount]) 
    MEASURE 'Product Category'[Reseller Sales] 
　　　　　　= SUM('Reseller Sales'[Sales Amount]) \n" 

    EVALUATE ADDCOLUMNS(
　　　　'Product Category', 
　　　　\"Internet Sales - All Years\", 
　　　　'Product Category'[Internet Sales], 
　　　　\"Reseller Sales - All Years\", 
　　　　'Product Category'[Reseller Sales])

上面这段语句产生的结果应该是：

• 为了更好的了解DAX语句，下面抛出几段对比

 第一组：

SQL:

SELECT    
　　pc.EnglishProductCategoryName,    
　　SUM(SalesAmount) AS [Internet Total Sales]
FROM 
　　dbo.FactInternetSales s
LEFT JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey
LEFT JOIN DimProductSubcategory ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
LEFT JOIN dbo.DimProductCategory pc ON ps.ProductCategoryKey = pc.ProductCategoryKey
GROUP BY pc.EnglishProductCategoryName
ORDER BY EnglishProductCategoryName ASC  

DAX:

EVALUATESUMMARIZE( 
    'Internet Sales',           
    'Product Category'[Product Category Name],
    "Internet Total Sales", 
    'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Product Category'[Product Category Name]  

 第二组：

MDX:

WITH    
    MEMBER Measures.[Sales 2003]AS ([Measures].[Internet Total Sales], [Date].[Calendar Year].&[2003] )
SELECT {
    Measures.[Internet Total Sales], 
    Measures.[Sales 2003]
} ON COLUMNS,
NON EMPTY [Product Category].[Product Category Name].[Product Category Name] ON ROWS
FROM [Internet Sales]
WHERE ([Date].[Calendar Year].&[2002])  

DAX:

DEFINE
    MEASURE 'Internet Sales'[Sales 2003] 
        = CALCULATE(
            SUM('Internet Sales'[Sales Amount])
            ,'Date'[Calendar Year]=2003
        )
EVALUATEFILTER(
    CALCULATETABLE(
        ADDCOLUMNS(
            ALL('Product Category'[Product Category Name]),
            "Internet Total Sales", 'Internet Sales'[Internet Total Sales],
            "Sales 2003", 'Internet Sales'[Sales 2003]
        )
        ,'Date'[Calendar Year] = 2002
    )
    ,[Internet Total Sales] <> 0 || [Sales 2003] <> 0
)
ORDER BY 'Product Category'[Product Category Name]  

第三组，来一把CROSSJOIN(笛卡儿积)

MDX:

WITH    
    MEMBER Measures.[Sales 2003]AS ([Measures].[Internet Total Sales], [Date].[Calendar Year].&[2003] )
SELECT
{
    Measures.[Internet Total Sales], 
    Measures.[Sales 2003]
} ON COLUMNS,
NON EMPTY [Product Category].[Product Category Name].[Product Category Name]
    *[Product Sub-Category].[Product Subcategory Name].[Product Subcategory Name] ON ROWS
FROM [Internet Sales]
WHERE ([Date].[Calendar Year].&[2002])  

DAX:

DEFINE 
    MEASURE 'Internet Sales'[Sales 2003] = CALCULATE(
        SUM('Internet Sales'[Sales Amount])
        ,'Date'[Calendar Year] = 2003
)
EVALUATEFILTER(
    CALCULATETABLE(
        ADDCOLUMNS(CROSSJOIN(ALL('Product Category'[Product Category Name]),ALL( 'Product Sub-Category'[Product Subcategory Name]))
            ,"Internet Total Sales", 'Internet Sales'[Internet Total Sales]
            ,"Sales 2003", 'Internet Sales'[Sales 2003]),
        'Date'[Calendar Year] = 2002)
    ,[Internet Total Sales] <> 0 || [Sales 2003] <> 0
)
ORDER BY 'Product Category'[Product Category Name], 'Product Sub-Category'[Product Subcategory Name]