65.9K
CodeProject 正在变化。 阅读更多。
Home

Analysis Services LINQ to MDX ORM 的介绍

Suffyan Asad
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.91/5 (9投票s)

2015年1月4日

CPOL

11分钟阅读

viewsIcon

27783

Percolator Analysis Services 是一个 LINQ to MDX ORM，它允许使用 LINQ 编写 MDX 查询，而不是使用硬编码的 MDX 代码字符串，从而使代码更简洁、易于阅读和维护。

引言

Percolator Analysis Services 是 SQL Server Analysis Services 的一个 ORM，它允许程序员使用 LINQ 编写 MDX 查询。与 ADOMD.NET 所需的 MDX 查询字符串相比，它使代码更具表现力，易于理解和维护。

在使用了 nHibernate 等 ORM 映射器之后，人们期望找到一个库/工具，可以避免编写和维护 MDX 查询字符串，并使不熟悉 SQL Server Analysis Services 和 MDX 的人更容易理解代码。

当我被 T-ask 寻找这样一个替代方案时，我的搜索显示 Percolator Analysis Services 是一个很有潜力的选择，适用于即将推出的应用程序。Percolator Analysis Services 目前处于 Beta 阶段，并且根据其在 NuGet gallery 上的页面，不附带任何形式的保修——但它具有巨大的潜力，我个人也很喜欢使用它。因此，我也决定写一篇关于它的文章。

更多详情，请访问

  1. https://nuget.net.cn/packages/PercolatorAnalysisServices/
  2. http://www.coopdigity.com/ssas-orm/

基本上，使用 Percolator Analysis Services，形式为

SELECT
{
       Measures.[Sales Amount]
} ON 0,
{
       [Order Date].[Fiscal Year].[Fiscal Year]
} ON 1
FROM [AdventureWorksCube]

的 MDX 查询可以写成

using (var db = new AdventureWorksDB())
{
    var mdx = from item in db.AdventureWorksCube
              select new
              {
                  item.OrderDate.FiscalYear,
                  item.SalesAmount
              };
    var data = mdx.ToList();
}

本文是根据 Percolator Analysis Services 的版本 0.1.4.512 撰写的，这是写作时最新的版本。

那么，让我们开始使用 Percolator Analysis Services 吧。

设置 Cube

我正在使用 Adventure Works DW 2012 示例数据库，可以从以下网址下载：

http://msftdbprodsamples.codeplex.com/releases/view/105902

在此基础上，我使用示例 Adventure Works 数据库中的一些维度创建并部署了一个名为 AdventureWorksCube 的 Cube。Cube 的结构如下：

这个 Cube 将作为探索 Percolator Analysis Services 的示例。

初始项目设置

打开 Visual Studio，创建一个名为 PercolatorSSASDemo 的新 C# 控制台应用程序项目。在解决方案中，打开 NuGet 包管理器，然后安装 Percolator Analysis Services。

或者，运行以下命令以下载并包含 Percolator Analysis Services 到项目中：

PM> Install-Package PercolatorAnalysisServices

安装完成后，除了引用之外，项目中还添加了以下文件：

这些文件包括 Documentation.txt，其中包含有关使用 Percolator Analysis Services 的详细文档。它写得很好，涵盖了基本和高级功能，以及许多示例。这是必读的。

接下来是 PercolatorCube.tt 文件，需要填写连接信息、数据库和命名空间名称，如下所示：

然后保存。保存后，将根据 Cube 的结构生成 PercolatorCube.cs C# 文件。打开该文件，您将看到所有的维度、度量和 Cube。如 SQL Server Profiler 所示，会运行一些 DMX 查询来获取 Cube 的结构。

接下来，构建解决方案。构建时可能会出现错误，主要是变量名包含不允许的字符。纠正错误后，项目即可成功构建。

在 AdventureWorksCube_DueDate_Dimension 类中，更改变量名，例如：

public Attribute DueDate.CalendarQuarter

to

public Attribute CalendarQuarter

在 AdventureWorksCube_OrderDate_Dimension 和 AdventureWorksCube_ShipDate_Dimension 中也进行类似的更正。如果您使用不同的 Cube 进行操作，可能会出现类似的错误。修复它们并构建项目。

接下来，一些属性生成不正确，这将在以后导致错误的 MDX 代码，因此有必要修复它们。如果您像我一样使用类似的 Cube 进行操作，您会在 AdventureWorksCube_DueDate_Dimension、AdventureWorksCube_OrderDate_Dimension 和 AdventureWorksCube_ShipDate_Dimension 中看到类似以下的属性：

[Tag("[Order Date].[Order Date.Calendar Quarter]")]

public Attribute CalendarQuarter { get { return new Attribute("[Order Date].[Order Date.Calendar Quarter]"); } }

将其更改为：

[Tag("[Order Date].[Calendar Quarter]")]

public Attribute CalendarQuarter { get { return new Attribute("[Order Date].[Calendar Quarter]"); } }

看起来像 Order Date.Calendar Quarter 这样的维度属性被翻译成了变量名和属性标签。

正如我之前提到的，在写作时，Percolator Analysis Services 仍处于 Beta 阶段，并且正在积极纠正和改进中，因此在未来的版本中可能不需要进行此类更正，只需保存 PercolatorCube.tt 即可。

这设置了可以查询以生成和执行 MDX 查询的 IQueryable 结构。

创建和运行 MDX 查询

下一步是创建一个基本的 MDX 查询，该查询返回按 Order Date.Fiscal Year 维度聚合的 Sales Amount。查询可以用 MDX 编写为：

SELECT
[Measures].[Sales Amount] on 0,
[Order Date].[Fiscal Year].[Fiscal year] on 1
FROM [AdventureWorksCube]

相同的查询可以用 LINQ 编写为：

static void FirstLinqToMdxQuery()
{
    using (var db = new AdventureWorksDB())
    {
        var mdx = from item in db.AdventureWorksCube
                  select new
                  {
                      item.OrderDate.FiscalYear,
                      item.SalesAmount
                  };
        var data = mdx.ToList();
    }
}

就是这样。执行代码，在 profiler 上查看其运行情况，并检查结果：

列表填充如下：

对于 2005、2010 和 2011 年的 FiscalYear，查询的 SalesAmount 为 null。为了消除这些记录，我们需要运行 NonEmpty 函数。这将在下一节中进行说明。

MDX 函数、集合和成员

Percolator Analysis Services 中实现了许多 MDX 函数，可以通过 Mdx.FunctionName(parameters) 调用。尚未实现的函数可以通过指定名称和参数的 Mdx.MdxFunction<T>() 来调用。MDX 函数可以返回 Member 或 Set，因此泛型类型 T 应根据所使用的 MDX 函数设置为 Member 或 Set。

因此，继续前面的示例，我们需要在 [OrderDate].[FiscalYear].[FiscalYear] 集合上调用 NonEmpty 函数。所需的 MDX 查询是：

SELECT
[Measures].[Sales Amount] on 0,
NONEMPTY([Order Date].[Fiscal Year].[Fiscal year]) on 1
FROM [AdventureWorksCube]

NonEmpty 函数的结果是一个集合，然后可以将其传递给 LINQ 查询。

同时，让我们创建一个类来捕获查询结果，而不是使用匿名类映射：

public class SalesAmountOverFiscalyear
{
    public object FiscalYear { get; set; }
    public object SalesAmount { get; set; }
}

然后查询变为：

static void FirstLinqToMdxQuery()
{
    using (var db = new AdventureWorksDB())
    {
        var filteredFiscalYearSet = Mdx.NonEmpty(AdventureWorksCube.Objects.OrderDate.FiscalYear.Children, null);
        var mdx = from item in db.AdventureWorksCube
                  select new SalesAmountOverFiscalyear
                  {
                      FiscalYear = filteredFiscalYearSet,
                      SalesAmount = item.SalesAmount
                  };
        var data = mdx.ToList();
    }
}

Percolator Analysis Services 支持匿名映射和映射到类。对于类，属性应声明为预期的类型，例如，double 用于双精度值，string 用于名称或文本等。然而，根据我的经验，避免类型转换错误的最佳方法是使用 object 类型的属性，然后使用转换将值正确转换为individual 类型。给定查询中的 Sales Amount 包含数值和 null 值。Null 值以字符串形式返回，当将其分配给类中的 double 属性（用于映射）时，会引发类型转换错误。

NonEmpty 函数接受两个参数，但对于我们的查询，将第二个参数传递为 null 是安全的。请参阅 MDX NonEmpty 函数的描述，了解何时需要第二个参数。运行查询并检查 SQL Server profiler 中生成的 MDX，执行的 MDX 代码是：

可以看到，集合首先被声明，然后在 Axis 1 上进行选择。这适用于在代码中声明的成员之前声明的成员，它们在生成的 MDX 代码中被声明并在 Axis 0 上进行选择。

此外，集合的名称与 C# 代码中的变量名相同，并且前面加上下划线。此信息可能很有用，如下文所示。

接下来，让我们实现 Order 函数。假设需求是按 Sales Amount 的降序对 Fiscal Year 进行排序。再次使用 MDX 函数 Order。此查询所需的 MDX 代码是：

WITH
SET _filteredFiscalYearSet AS
 Order(NonEmpty( [Order Date].[Fiscal Year].Children ), Measures.[Sales Amount])
SELECT
{
       Measures.[Sales Amount]
} ON 0,
{
       _filteredFiscalYearSet
} ON 1
FROM [AdventureWorksCube]

这可以通过调用 Order Mdx 函数来实现，如下所示：

using (var db = new AdventureWorksDB())
{
    var filteredFiscalYearSet = Mdx.Order(Mdx.NonEmpty(AdventureWorksCube.Objects.OrderDate.FiscalYear.Children, null), AdventureWorksCube.Objects.SalesAmount, OrderType.DESC);
    var mdx = from item in db.AdventureWorksCube
              select new SalesAmountOverFiscalyear
              {
                  FiscalYear = filteredFiscalYearSet,
                  SalesAmount = item.SalesAmount
              };
    var data = mdx.ToList();
}

这返回相同的数据，但这次是按 Sales Amount 的降序排列。

接下来，让我们看看如何从查询中检索 Rank，并以此来创建 Members。我将再次参考所需的 MDX，然后将其翻译成 LINQ 查询。MDX 查询是：

WITH SET _filteredFiscalYearSet AS
Order(  NonEmpty([Order Date].[Fiscal Year].[Fiscal Year],  Measures.[Sales Amount] ), Measures.[Sales Amount], BDESC )
MEMBER Measures.SalesOrderRank AS
Rank(   [Order Date].[Fiscal Year].CurrentMember ,  _filteredFiscalYearSet)
SELECT
{
       Measures.SalesOrderRank
,      Measures.[Sales Amount]
} ON 0,
{
       _filteredFiscalYearSet
} ON 1
FROM [AdventureWorksCube]

为了实现此查询，创建一个名为 SalesOrderRank 的成员实例，然后调用 Rank MDX 函数。但是（截至此版本的 Percolator Analysis Services），该函数尚未可用。因此，替代方法是使用 Mdx.MdxFunction<T>()，方法如下：

Member SalesOrderRank = (Member)Mdx.MdxFunction<Member>("Rank", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYear.CurrentMember, filteredFiscalYearSet });

我们知道返回的是一个度量，因此我们将其声明为一个成员，然后在 Select 子句中包含新的 SalesOrderRank 成员后，代码变为：

using (var db = new AdventureWorksDB())
{
    var filteredFiscalYearSet = Mdx.Order(Mdx.NonEmpty(AdventureWorksCube.Objects.OrderDate.FiscalYear.Children, new Set(new [] {AdventureWorksCube.Objects.SalesAmount})), AdventureWorksCube.Objects.SalesAmount, OrderType.DESC);
    Member SalesOrderRank = (Member)Mdx.MdxFunction<Member>("Rank", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYear.CurrentMember, filteredFiscalYearSet });

    var mdx = from item in db.AdventureWorksCube
              select new SalesAmountOverFiscalyear
              {
                  SalesOrderRank = SalesOrderRank,
                  SalesAmount = item.SalesAmount,
                  FiscalYear = filteredFiscalYearSet,
              };
    var data = mdx.ToList();
}

还将 SalesOrderRank 成员添加到 SalesAmountOverFiscalYear 类中，并执行代码。

又一个异常……这次的错误是 Query (3, 62) The '[NenEmpty]' function does not exist。这意味着在此版本中，带有两个参数的 NonEmpty 函数尚未正确映射（毕竟这是一个 Beta 版本）。因此，我们别无选择，只能使用 Mdx.MdxFunction。

注意：此 bug 已在 Percolator Analysis Services 的版本 0.1.7.103 中修复，不再出现。

此外，当成员 AdventureWorksCube.Objects.OrderDate.FiscalYear 在 LINQ Select 子句之外引用时，它不会被翻译成类似于 [Order Date].[Fiscal Year].[Fiscal Year] 的三属性引用。我处理此问题的方法是，在维度本身中添加一个名为 ThreeAttributeRef 的新属性。因此，打开 PercolatorCube.cs，在 AdventureWorksCube_OrderDate_Dimension 维度中，添加以下 Attribute 属性：

[Tag("[Order Date].[Fiscal Year].[Fiscal Year]")]
public Attribute FiscalYearThreeAttrRef { get { return new Attribute("[Order Date].[Fiscal Year].[Fiscal Year]"); } }

然后最终的 MDX 代码变为：

using (var db = new AdventureWorksDB())
{
    var NonEmptySet = (Set)Mdx.MdxFunction<Set>("NonEmpty", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYearThreeAttrRef, AdventureWorksCube.Objects.SalesAmount });
    var filteredFiscalYearSet = Mdx.Order(NonEmptySet, AdventureWorksCube.Objects.SalesAmount, OrderType.DESC);

    Member SalesOrderRank = (Member)Mdx.MdxFunction<Member>("Rank", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYear.CurrentMember, filteredFiscalYearSet });
    
    var mdx = from item in db.AdventureWorksCube
              select new SalesAmountOverFiscalyear
              {
                  SalesOrderRank = SalesOrderRank,
                  SalesAmount = item.SalesAmount,
                  FiscalYear = filteredFiscalYearSet,
              };
    var data = mdx.ToList();
}

执行代码并检查 MDX，它是：

但正如这里所见，集合 _filteredFiscalYearSet 没有通过其名称引用，而是将等效的 MDX 代码重复在成员中。如果以这种方式使用多个成员/集合，生成的 MDX 代码可能会非常复杂且难以理解。目前，解决方法是使用集合的变量名（变量名加上下划线前缀）作为字符串，因为集合可以用字符串初始化。这不是一个很好的方法，但我预计在即将发布的版本中，将有一种正确且安全的方法来获取和设置每个 Set 和 Member 的变量名，并且不再需要像这样使用字符串。

另一个需要记住的是，如果一个 Member 或 Set 没有出现在 LINQ 的最终 Select 中，它就不会包含在生成的 MDX 中。因此，如果变量名被用作字符串，它必须包含在 LINQ 查询的 Select 子句中。

因此，包括集合的名称，最终的代码变为：

using (var db = new AdventureWorksDB())
{
    var NonEmptySet = (Set)Mdx.MdxFunction<Set>("NonEmpty", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYearThreeAttrRef, AdventureWorksCube.Objects.SalesAmount });
    var filteredFiscalYearSet = Mdx.Order(NonEmptySet, AdventureWorksCube.Objects.SalesAmount, OrderType.DESC);

    Member SalesOrderRank = (Member)Mdx.MdxFunction<Member>("Rank", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYear.CurrentMember, "_filteredFiscalYearSet" });

    var mdx = from item in db.AdventureWorksCube
              select new SalesAmountOverFiscalyear
              {
                  SalesOrderRank = SalesOrderRank,
                  SalesAmount = item.SalesAmount,
                  FiscalYear = filteredFiscalYearSet,
              };
    var data = mdx.ToList();
}

此查询生成的 MDX 是：

再举一个例子，考虑一个查询，该查询按 Sales Territory 返回按年份划分的 Sales Amount，并且仅包含 fiscal year 2006、2007 和 2008 的结果。该函数将是：

using (var db = new AdventureWorksDB())
{
    var fiscalYearSet = AdventureWorksCube.Objects.OrderDate.FiscalYear["&2006"] | AdventureWorksCube.Objects.OrderDate.FiscalYear["&2008"];
    
    var mdx = from item in db.AdventureWorksCube
              select new
              {
                  SalesAmount = item.SalesAmount,
                  FiscalYear = fiscalYearSet,
                  SalesTerritory = item.DimSalesTerritory.SalesTerritoryRegion.Children
              };
    var data = mdx.ToList();
}

此 LINQ 查询演示了两件事：首先，为了选择维度中的单个项（例如，Fiscal Year 2008 可以选择为数组索引 [“&2008”]，它会自动转换为相应的 LINQ 子句）。其次，MDX Range ( : ) 运算符在 LINQ 中可以表示为 |。运行查询并检查生成的 MDX。

创建 Where 子句

接下来，我将演示 where 子句及其用法。作为演示，考虑一个返回 2009 年所有地区的 Sales Amount 的示例查询。LINQ 查询将是：

using (var db = new AdventureWorksDB())
{
    var mdx = from item in db.AdventureWorksCube
              where item.OrderDate.FiscalYear["&2009"]
              select new
              {
                  SalesAmount = item.SalesAmount,
                  SalesTerritory = item.DimSalesTerritory.SalesTerritoryRegion
              };
    var data = mdx.ToList();
}

此查询的 MDX 是：

要使用 Where 子句中的多个条件，可以链式调用多个 where 子句。例如，要查看 2009 年担任管理职位的客户的销售额，LINQ 查询将是：

using (var db = new AdventureWorksDB())
{
    var mdx = from item in db.AdventureWorksCube
              where item.OrderDate.FiscalYear["&2009"]
              where item.DimCustomer.EnglishOccupation["Management"]
              select new
              {
                  SalesAmount = item.SalesAmount,
                  SalesTerritory = item.DimSalesTerritory.SalesTerritoryRegion
              };
    var data = mdx.ToList();
}

生成的 MDX 查询是：

创建子 Cube

最后，我将简要解释 Percolator Analysis Services 中的子 Cube。可以使用一个单独的 LINQ 查询来创建一个新的 SubCube<T> 对象，然后可以使用 LINQ 以与常规 Cube 类似的方式对其进行查询。子 Cube 的泛型类型 T 应该是希望在其上执行查询的 Cube。

Percolator Analysis Services 不会单独运行子 Cube 查询，而是将主查询与子 Cube 查询一起转换为单个 MDX 查询。子 Cube 也可以嵌套。

作为演示，我提供以下 LINQ 查询示例：

using (var db = new AdventureWorksDB())
{
    var subCubeMdx = from item in db.AdventureWorksCube
                     select new
                     {
                         FiscalYear = item.OrderDate.FiscalYear["&2008"]
                     };
    var subCube = new SubCube<AdventureWorksCube>(subCubeMdx);

    var mdx = from item in subCube
              select new {
                  SalesTerritory = item.DimSalesTerritory.SalesTerritoryRegion,
                  SalesAmount = item.SalesAmount
              };
    var data = mdx.ToList();
}

此查询的 MDX 代码是：

结论

Percolator Analysis Services 中还有许多其他内容超出了本简介的范围。下一步是阅读文档，并详细了解本文中解释的各项功能，特别是 Sets 和 Members。然后探索更高级的功能，例如使用 PAS Explicit Syntax、使用 AdomdDataReader、未在此处介绍的更多 MDX 函数、WhereSlicers、Percolator Explicit Methods，以及编辑模板文件而不是直接编辑生成的代码。还请阅读文档末尾的“需要了解的事项”部分。所有这些都足以让您决定是否在您的项目中使用 Percolator Analysis Services，并了解其功能和局限性。

重申 Percolator Analysis Services 在写作时仍处于 Beta 阶段，可能还有一些不完善之处，某些功能可能缺失，有些可能需要以不那么优雅的方式实现，但它正在持续开发中，并且改进速度非常快。总的来说，我认为它是一个非常好的 ORM 映射器，与仅使用 ADOMD.NET 相比有了巨大的改进。我非常赞赏其开发者的努力，他们创建了一个使用简洁易懂的 LINQ 代码来查询 SSAS Cube 的工具。

这是我的第一篇技术文章，因此，任何建议、更正、批评都将不胜感激。

© . All rights reserved.