Introduction
Percolator Analysis Services is an ORM for SQL Server Analysis Services that allows programmers to write MDX Queries in LINQ. It makes the code more expressive and easy to understand and maintain than strings of MDX Queries required by ADOMD.NET.
Having worked with ORM mappers such as nHibernate, it was the desired to look for a library/utility that does away with the need to write and maintain strings of MDX Queries, and make the code more understandable to people unfamiliar with SQL Server Analysis Services and MDX.
When I was tasked with finding such alternative, my search revealed Percolator Analysis Services as a pretty viable candidate for an upcoming application. Percolator Analysis Services is currently a Beta, and comes with no warranties of any kind – according to its page in NuGet gallery, but it has great potential and personally I enjoyed working with it. Therefore I decided to write about it as well.
For more details, visit:
- http://www.nuget.org/packages/PercolatorAnalysisServices/
- http://www.coopdigity.com/ssas-orm/
Basically, using Percolator Analysis Services, an MDX Query of the form:
SELECT
{
Measures.[Sales Amount]
} ON 0,
{
[Order Date].[Fiscal Year].[Fiscal Year]
} ON 1
FROM [AdventureWorksCube]
Can be written as
using (var db = new AdventureWorksDB())
{
var mdx = from item in db.AdventureWorksCube
select new
{
item.OrderDate.FiscalYear,
item.SalesAmount
};
var data = mdx.ToList();
}
This article has been written according to version 0.1.4.512 of Percolator Analysis Services, which was the latest version at the time of writing.
So, let’s get started with Percolator Analysis Services.
Setting up the Cube
I am using the Adventure Works DW 2012 example database that can be downloaded from:
http://msftdbprodsamples.codeplex.com/releases/view/105902
And on top of that, I have created and deployed a cube named AdventureWorksCube using some dimensions of the example Adventure Works database. The structure of the cube is:
This cube will serve as an example to explore Percolator Analysis Services.
Initial Project Set-up
Open Visual Studio, and create a new C# console application project named PercolatorSSASDemo. In the solution, open NuGet package manager, and install Percolator Analysis Services:
Alternatively, run the following command to download and include Percolator Analysis Services in the project:
<code>PM> Install-Package PercolatorAnalysisServices</code>
After the installation is complete, following files are added to the project in addition to the reference:
The files include Documentation.txt, which contains detailed documentation related to using Percolator Analysis Services. It is well written, and basic and advanced features, and many examples. It is a must read.
Next is the PercolatorCube.tt file that needs to be filled with connection information, database, and namespace name as:
And then save. Upon saving, the PercolatorCube.cs C# file is generated according to the structure of the cube. Open the file, you will see all the dimensions, measures and the cube. A number of DMX Queries are run to get the structure of the cube, as shown by the SQL Server Profiler:
Next, build the solution. There may be errors upon building, mostly variable names containing characters that are not permitted. Correct the errors, and the project build fine.
In the AdventureWorksCube_DueDate_Dimension class, change the variable names such as:
public Attribute DueDate.CalendarQuarter
to:
public Attribute CalendarQuarter
Do similar corrections in AdventureWorksCube_OrderDate_Dimension and AdventureWorksCube_ShipDate_Dimension as well. If you are following along with a different cube, there may be other errors of the similar nature. Fix them and build the project.
Next, some attributes are incorrectly generated, which will later result in incorrect MDX code, so it is necessary to fix them. If you are following along using a similar cube as I am, you would see Attributes in the AdventureWorksCube_DueDate_Dimension, AdventureWorksCube_OrderDate_Dimension and AdventureWorksCube_ShipDate_Dimension such as:
[Tag("[Order Date].[Order Date.Calendar Quarter]")]
public Attribute CalendarQuarter { get { return new Attribute("[Order Date].[Order Date.Calendar Quarter]"); } }
Change them to
[Tag("[Order Date].[Calendar Quarter]")]
public Attribute CalendarQuarter { get { return new Attribute("[Order Date].[Calendar Quarter]"); } }
It appears that the dimension attributes such as Order Date.Calendar Quarter are being translated into variable names and attribute tags:
As I have mentioned earlier, at the time of writing, Percolator Analysis Services is beta and is being actively corrected and improved, so making such corrections would not be required in the coming versions, and simply saving the PercolatorCube.tt would be enough.
This sets up the IQueryable structures that can be queried to generate and execute MDX queries.
Creating and Running MDX Queries
Next step is to create a basic MDX Query that returns Sales Amount aggregated on Order Date.Fiscal Year dimension. The query can be written in MDX as:
SELECT
[Measures].[Sales Amount] on 0,
[Order Date].[Fiscal Year].[Fiscal year] on 1
FROM [AdventureWorksCube]
The same query can be written in LINQ as:
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();
}
}
And that’s it. Execute the code, and see it run on the profiler, and check the results:
And the list is populated as:
The query has SalesAmount = null for for FiscalYear 2005, 2010 and 2011, and to eliminate these records, we need to run the NonEmpty function. Which is explained next.
MDX Functions, Sets and Members
There are many MDX Functions implemented in Percolator Analysis Services which can be invoked using Mdx.FunctionName(parameters), and the ones that have not yet been implemented, can be invoked with Mdx.MdxFunction<T>() by specifying the name and parameters. The MDX functions can return Member or Set, therefore the generic type T should be set to either Member or Set according to the MDX function being used.
So continuing the previous example, we need to call NonEmpty function on [OrderDate].[FiscalYear].[FiscalYear] set. The required MDX query is:
SELECT
[Measures].[Sales Amount] on 0,
NONEMPTY([Order Date].[Fiscal Year].[Fiscal year]) on 1
FROM [AdventureWorksCube]
The result of the NonEmpty function is a set, which can be then passed to the LINQ query.
While we are at it, let’s create a class to capture the result of the query instead of using anonymous class mapping:
public class SalesAmountOverFiscalyear
{
public object FiscalYear { get; set; }
public object SalesAmount { get; set; }
}
And then the query becomes:
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 supports both anonymous mapping and mapping to a class. For class, the properties should be declared as the expected types, for example double for double values, string for names or text etc. In my experience though, I have found that the best way to avoid type conversion errors is to use properties of object type and then use conversions to properly convert values to individual types. The Sales Amount in the given query contains both numeric values and null values. Null values are returned as string which, on assigning to double property in the class the result is being mapped to, throws type conversion errors.
The NonEmpty function takes two parameters, but for our query, it is safe to pass null for the second parameter. Refer to the description of the MDX NonEmpty function to see when second parameter is needed. Run the query and inspect the MDX that is generated in the SQL Server profiler, the MDX code executed is:
It can be seen that the set is first declared and then selected on Axis 1. This is applicable for the members that are declared before the LINQ Query in the code, they are declared and then selected on Axis 0 in the generated MDX code.
Additionally, the name of the set is the same as the variable name in C# code, and underscore is prepended to it. This information can be useful, as demonstrated later.
Next, let’s implement Order function. Suppose the requirement is to order Fiscal year on Sales Amount in decreasing order. Again using the MDX Function Order. The MDX code that is required for this query is:
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]
This can be achieved by calling Order Mdx function as:
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();
}
This returns the same data, but this time it is in descending order of Sales Amount.
Next, let’s see how to retrieve Rank from the query, and with this, see how Members can be created. Again I will refer to the required MDX, and then translate it to LINQ query. The MDX query is:
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]
In order to achieve this query, create a member instance named SalesOrderRank, and then call the Rank MDX function. But (as of this version of Percolator Analysis Services) the function is not yet available. So the alternative is to use Mdx.MdxFunction<T>(), here is how:
Member SalesOrderRank = (Member)Mdx.MdxFunction<Member>("Rank", new object[] { AdventureWorksCube.Objects.OrderDate.FiscalYear.CurrentMember, filteredFiscalYearSet });
We know that the return will be a measure, therefore we will declare it as a member, and then, after including the new SalesOrderRank member in the select clause, the code becomes:
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();
}
Add the SalesOrderRank member in the SalesAmountOverFiscalYear class as well, and execute the code.
Another exception ……, and this time the error is Query (3, 62) The '[NenEmpty]' function does not exist. Meaning in this version, the NonEmpty function with two arguments has not been mapped properly (this is a beta version anyway). So we are left with no choice but to use Mdx.MdxFunction.
Note: This bug has been fixed in version 0.1.7.103 of Percolator Analysis Services and it no longer occurs.
In addition to that, the Member AdventureWorksCube.Objects.OrderDate.FiscalYear is not translated into three attribute reference similar to [Order Date].[Fiscal Year].[Fiscal Year] when it is referenced outside the LINQ Select clause. The way I handle it is to add a new Attribute in the dimension itself called ThreeAttributeRef. So open the PercolatorCube.cs, and in the AdventureWorksCube_OrderDate_Dimension dimension, add the following Attribute property:
[Tag("[Order Date].[Fiscal Year].[Fiscal Year]")]
public Attribute FiscalYearThreeAttrRef { get { return new Attribute("[Order Date].[Fiscal Year].[Fiscal Year]"); } }
And then the final MDX Code becomes:
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();
}
Execute the code and inspect the MDX, it is:
But as it can be seen here, the set _filteredFiscalYearSet is not referenced by its name, rather the equivalent MDX code is repeated in the Member. If multiple members/sets are being used like this, the resultant MDX code can be very complex, and hard to understand. Currently, the way around this is to use the variable name of the set (name of the variable with underscore appended in the beginning) as a string, since set can be initialized with a string. It is not a very clean way, but in the upcoming versions, I expect that there will be a proper and safe way to get and set the variable name of each Set and Member, and using strings like this will no longer be required.
Another thing to remember is that if a Member or Set does not appear in the final select in LINQ, it does not get included in the generated MDX, so if variable name is being used as a string, it MUST be included in the select clause in LINQ query.
So including the name of the set, the final code becomes:
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();
}
And the MDX generated by this query is:
And for another example, consider a query to return Sales Amount by Year by Sales Territory, and include the results of fiscal year 2006, 2007 and 2008 only. The function will be:
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();
}
This LINQ query demonstrates two things, first, in order to select individual items from a dimension (e.g. Fiscal Year 2008 can be selected as array index [“&2008”] and it is automatically translated into appropriate LINQ clause. Secondly, the MDX Range ( : ) operator can be expressed as | in LINQ. Run the query and inspect that MDX generated.
Creating Where Clause(s)
Next, I will demonstrate the where clause and the way it can be used. As a demonstration, consider an example query that returns the Sales Amount of all territories in Fiscal year 2009. The LINQ query will be:
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();
}
The MDX for this query is:
For using multiple conditions in the where clause, multiple where clauses can be chained. For example to see the sales made in year 2009 by customers working on managerial positions, the LINQ query will be:
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();
}
And the resulting MDX query is:
Creating Sub cubes
Finally, I will briefly explain sub cubes in Percolator Analysis Services. A separate LINQ query can be used to create a new SubCube<T> object, which can then be queried using LINQ in a similar manner as normal cube. The generic type T of the sub cube should be the cube on which the query is desired to be executed on.
Percolator Analysis Services do not run sub-cube query as a separate MDX query, rather the primary query along with the sub cube queries are translated into single MDX query. Sub cubes can be nested as well.
For demonstration, I present the following LINQ query example:
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();
}
And the MDX code of this query is:
Conclusion
There are many other things in Percolator Analysis Services that were beyond the scope of this introduction. The next step is to read the documentation, and read the details of the features explained in this article, especially Sets and Members. And then explore more advanced features such as Using PAS Explicit Syntax, using AdomdDataReader, More MDX functions not presented here, WhereSlicers, Percolator Explicit Methods, and editing the template files instead of editing the generated code directly. Also read the Things to Know section at the end of the document. All of this is enough to put you in a position to make a decision for or against using Percolator Analysis Services in your project, and to understand its features and limitations.
Reiterating the fact that Percolator Analysis Services is Beta at the time of writing, it may be a little rough around the edges, some functionality maybe missing, some may have to be achieved in a not so clean manner, but it is under continuous development, and is being improved very quickly. Overall, I find it to be a rally good ORM mapper, one that is certainly a huge improvement over using ADOMD.NET alone. And I appreciate the efforts of its developers, who created a tool to query SSAS Cubes using clean and simple LINQ code.
This is my first ever technical article, and therefore, any suggestions, corrections, criticisms will be appreciated.
