SDX Example

Compare LINQ Simple Dimensional eXtensions (LINQ/SDX) to Regular LINQ and MDX - Examples

All 3 queries return the same results.

SDX LINQ Dialect:

(
from customer in context.Customers
from order in context.OrderDetails //implicit join based on cube metadata
where ( customer.Country == "Italy"
|| customer.Country.Contains( "US" ) )
&& order.Product.ProductName.ToUpper().StartsWith( "P" )
&& order.Discount != 0
&& order.Quantity >= 100
orderby order.Discount descending
select new
{
customer.CustomerID,
customer.CompanyName,
order.Quantity, //Quantity is aggregated using implicit Sum() aggregation function
order.Discount, //Discount is aggregated using implicit Avg() aggregation function
//Sometimes you will specify explicit aggregation functions in MDX style to override cube metadata default aggregation style
MaxDiscount = Mdx.Max(order.Discount, "[Customers].[Customer ID].[Customer ID]" /*granularity dimension(s)*/)
}
)
.Skip(2) //pagination
.Take(3)

Click here to see this query in lambda syntax

Note: Even though the SDX query above does not explicitly join, group or aggregate results, it still does it all, based on meta data in a cube and in an entity model. The idea is to define properties that are grouping keys and those that are values being aggregated in metadata once, and than reuse that information in all queries implicitly, which makes such queries shorter, simpler and more intuitive for a beginner (if you get used to SQL too much it may seem to you initially that old school explicit grouping and joining was simpler).

See How SDX Query Works for more details.

Regular LINQ:

(
from customer in context.Customers
join orderHeader in context.Orders //This line is not needed in LINQ/SDX
on orderHeader.CustomerID equals customer.CustomerID //You have to declare a collection property on Customer or use a join
join order in context.OrderDetails
on order.OrderID equals orderHeader.OrderID //This line is not needed in LINQ/SDX
into joinedOrder //This line is not needed in LINQ/SDX
where (customer.Country == "Italy"
|| customer.Country.Contains("US") )
&& order.Product.ProductName.ToUpper().StartsWith("P")
&& order.Discount != 0
&& order.Quantity >= 100
orderby customer.CustomerID, //This line is not needed in LINQ/SDX
customer.CompanyName, //This line is not needed in LINQ/SDX
order.Discount descending
group joinedOrder by //This line is not needed in LINQ/SDX
new
{
joinedOrder.CustomerID, //This line is not needed in LINQ/SDX
joinedOrder.CompanyName //This line is not needed in LINQ/SDX
}
into groupedOrder //This line is not needed in LINQ/SDX
select new
{
groupedOrder.Key.CustomerID, //more complicated
groupedOrder.Key.CompanyName, //more complicated
groupedOrder.Sum(o => o. Sum(o => o.Quantity), //more complicated
groupedOrder.Average(o => o.Discount) //more complicated
groupedOrder.Max(o => o.Discount) //less complicated
}
)
.Skip(2)
.Take(3)

MDX:

WITH MEMBER [Measures].[Max Discount]
AS MAX([Customers].[Customer ID].[Customer ID], [Measures].[Discount])
SELECT
NON EMPTY
SUBSET
(
ORDER
(
(
FILTER
(
(
[Products].[Product ID].[Product ID],
[Customers].[Customer ID].[Customer ID],
[Customers].[Company Name].[Company Name]
), ((
([Customers].[Country].MemberValue = 'Italy')
OR (INSTR([Customers].[Country].CurrentMember.Name, 'US') > 0)
)
AND ([Measures].[Discount] <> 0)
)
AND ([Measures].[Quantity] >= 100)
AND INSTR(UCASE([Products].[Product Name].CurrentMember.Name), 'P') = 1
)
),
[Measures].[Discount],
BDESC
),
2,
3
)
ON ROWS,
{
[Measures].[Quantity],
[Measures].[Discount],
[Measures].[Max Discount]
}
ON COLUMNS
FROM [NorthwindEF]

How SDX Query Works

Even though the SDX query on top looks like a Cartesian Product, it returns results joined the same way as in a next Regular LINQ query. The query works because the join information is stored in SSAS cube metadata and SSAS Entity Framework Provider (tm) uses it when it generates MDX.

By default entities are treated as Dimensional Entities (A.K.A. dimensions in OLAP world and reference tables in relational databases world), and their properties are treated as Dimensional Properties.
Customer entity is not marked with [MeasureGroup] attribute, so it is considered to be a Dimensional Entity by default, and its customer.CustomerID and customer.CompanyName properties are Dimensional Properties and are used as keys for grouping.

OrderDetails entity is marked with [MeasureGroup] attribute, so it is considered to be a Measure Group Entity (A.K.A. transactional table in a relational database world), and its properties are treated as Measure Properties (A.K.A. measures in OLAP).
SSAS cube defines measure Quantity aggregation type as Sum, and measure Discount aggregation type as Average, that is why order.Quantity is being summed up and order.Discount is being calculated as average by this LINQ/MDX query.

OrderDetail entity also has Dimensional Properties OrderID and ProductID. You can always query for the same information from Order and Product Dimensional Entities, but if you prefer to get them from OrderDetail, you can do that because both OrderDetail.OrderID and OrderDetail.ProductID are marked with [DimensionProperty] attribute and thus will not be summed up, but will be used as grouping keys. Note: you do not need [DimensionProperty] attribute on properties of Dimensional Entities because all properties of Dimensional Entities are Dimensional Properties anyway.

It is technically possible to get all necessary for MDX generation metadata from SSAS cube thus freeing you completely from using .NET attributes like [MeasureGroup] or [DimensionProperty], but it would lead to some performance impact because of an additional roundtrip to SSAS cube. And because you would need very few of [MeasureGroup] and [DimensionProperty] attributes defined in your classes, this seems like a pragmatic trade-off.