Filter on Date range

Sep 6, 2013 at 5:16 PM
Hello

Is it possible (Using LINQ) to create query to filter on Date dimension?
I have tried something using calculated members, but can't make it work.
Could you provide an example using NorthwindEF cube and Order Date Time dimension?
Or do I have to use ExecuteMdxCollection for this?

Thanks in advance,
Tom
Coordinator
Sep 9, 2013 at 4:49 AM
Edited Sep 9, 2013 at 11:02 AM
Hello Tom,

It looks like you have run into a bug - we are sorry for your trouble.
Our testing shows that wrapping date literals into CDate() function fixes this issue, and we are working on a patch (which is expected to be released quite soon).

Mdx.CalculatedMemberAsString() may be your temporal work-around.

Thank you for bringing this problem up.
Coordinator
Sep 10, 2013 at 4:23 AM
The issue with date filtering has been fixed in SSAS Entity Framework Provider ver. 2.5.4.0.

The fixed setup project is available at http://agiledesignllc.com/download/SsasEntityFrameworkProviderSetup.msi
The updated libraries are available on Nuget.org.
Sep 17, 2013 at 2:58 PM
Hello

Sorry for the late reply, I've been away for a while...
Thanks for the updated libraries!
I have, however, a few questions about that.
  1. The cube I use has Date dimension with YMD hierarchy but it doesn't have a full Date property, only Year/Month/Day. What would be the best way use this to select in date range?
  2. Is there a way to create a MDX Where clause using LINQ. For example, I'd like to have something like this:
SELECT NON EMPTY
FILTER
((
[AnalyticalAccount].[AnalyticalAccountDbID].[AnalyticalAccountDbID],
[ReceiptDate].[Month].[Month], [ReceiptDate].[Year].[Year]
)
,
([AnalyticalAccount].[AnalyticalAccountDbID].MemberValue = 392)
)ON ROWS,
{
[Measures].[Sum Amount]
}
ON COLUMNS
FROM [fInvoice]
WHERE 
( [ReceiptDate].[MQYD].[Month].&[6]&[2013] : [ReceiptDate].[MQYD].[Month].&[6]&[2014] ) 
while LINQ query gives me this:
SELECT NON EMPTY
FILTER
((
[AnalyticalAccount].[AnalyticalAccountDbID].[AnalyticalAccountDbID],
[ReceiptDate].[Month].[Month], [ReceiptDate].[Year].[Year]
)
,
([AnalyticalAccount].[AnalyticalAccountDbID].MemberValue = 392)
 AND (([ReceiptDate].[Date].MemberValue >= CDate('1.6.2013. 0:00:00'))
 AND ([ReceiptDate].[Date].MemberValue < CDate('1.7.2014. 0:00:00')))
)ON ROWS,
{
[Measures].[Sum Amount]
}
ON COLUMNS
FROM [fInvoice]
None of these are great issues, but I'd like to know if and what is possible.

Thanks in advance,
Tom
Coordinator
Sep 22, 2013 at 2:52 AM
Edited Sep 23, 2013 at 4:04 AM
ttustonic wrote:
Hello

Sorry for the late reply, I've been away for a while...
Thanks for the updated libraries!
I have, however, a few questions about that.
  1. The cube I use has Date dimension with YMD hierarchy but it doesn't have a full Date property, only Year/Month/Day. What would be the best way use this to select in date range?
-Maybe using something like DateTime.Now.Date would work for you? -I am not quite sure I understand your question though. Could you give me an MDX query example for the filter by Year/Month/Day that you are trying to apply, please? What exactly is it that you cannot get working? Do you mean that an expression like CDate('1.6.2013. 0:00:00') does not work for you because you do not have a time portion in your date?
  1. Is there a way to create a MDX Where clause using LINQ. For example, I'd like to have something like this:
SELECT NON EMPTY
FILTER
((
[AnalyticalAccount].[AnalyticalAccountDbID].[AnalyticalAccountDbID],
[ReceiptDate].[Month].[Month], [ReceiptDate].[Year].[Year]
)
,
([AnalyticalAccount].[AnalyticalAccountDbID].MemberValue = 392)
)ON ROWS,
{
[Measures].[Sum Amount]
}
ON COLUMNS
FROM [fInvoice]
WHERE 
( [ReceiptDate].[MQYD].[Month].&[6]&[2013] : [ReceiptDate].[MQYD].[Month].&[6]&[2014] ) 
-Yes, you can put a condition into WHERE MDX axis using SDX Member() extension method:

SDX example that generates MDX with a single member in WHERE axis:
from orderHeader in context.Orders
from order in context.OrderDetails
where orderHeader.ShipCountry.Member("Italy")
select new
{
    order.Quantity
}

Generated MDX:

SELECT {
[Measures].[Quantity]
}
ON COLUMNS
FROM [NorthwindEF]
WHERE 
{
[Orders].[Ship Country].[Ship Country].[Italy]
}
SDX example that generates MDX with a set in WHERE axis:
from orderHeader in context.Orders
from order in context.OrderDetails
where orderHeader.ShipCountry.Member("Italy")
  || orderHeader.ShipCountry.Member("USA")
select new
{
   order.Quantity
}

Generated MDX:

SELECT {
[Measures].[Quantity]
}
ON COLUMNS
FROM [NorthwindEF]
WHERE 
{
[Orders].[Ship Country].[Ship Country].[Italy],
[Orders].[Ship Country].[Ship Country].[USA]
}
You can even combine MDX FILTER and WHERE:
from orderHeader in context.Orders
from order in context.OrderDetails
where orderHeader.ShipCountry.Member("Italy")
  && orderHeader.OrderID >= 10300
select new
{
    orderHeader.OrderDate,
    order.Quantity
}

Generated MDX:

SELECT 
NON EMPTY
FILTER
(
(
[Orders].[Order ID].[Order ID], 
[Orders].[Order Date].[Order Date]
)
,
[Orders].[Order ID].MemberValue >= 10300
)ON ROWS,
{
[Measures].[Quantity]
}
ON COLUMNS
FROM [NorthwindEF]
WHERE 
{
[Orders].[Ship Country].[Ship Country].[Italy]
}
But there is no support for range sets like a date range in your MDX example yet.
Sep 27, 2013 at 12:44 PM
Hello

Thanks for the reply.
I have tried your SDX examples for WHERE and they work OK, but, when using variable instead of fixed string value, like this:
void TestQuery1()
 {
   var member = "Italy";
   var qq = from orderHeader in context.Orders
                     from order in context.OrderDetails
                     where orderHeader.ShipCountry.Member(member)
                     select new { order.Quantity };
  var qql = qq.ToList();
  foreach (var q in qql)
     Console.WriteLine("{0} ", q.Quantity);
}
I receive EntityCommandCompilationException. Here is a strack trace (here Ii put COMPANYNAME and PERSONEMAIL instead of the company name and person who bought the licence):

Unhandled Exception: System.Data.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.InvalidCastException: Unable to cast object of type 'System.Data.Common.CommandTrees.DbParameterReferenceExpression' to type 
    'System.Data.Common.CommandTrees.DbConstantExpression'.System.Linq.Enumerable+<RepeatIterator>d__bc`1[System.String]COMPANYNAME#COMPANYNAME#PERSONEMAIL ---> System.InvalidCastException: Unable to cast object of type 'System.Data.Common.CommandTrees.DbParameterReferenceExpression' to type 'System.Data.Common.CommandTrees.DbConstantExpression'.
   at A.FP.GC(DbComparisonExpression )
   at A.FP.GC(DbComparisonExpression )
   at A.FP.Visit(DbComparisonExpression comparisonExpression)
   at System.Data.Common.CommandTrees.DbComparisonExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at A.FP.FC(EO , DbExpression )
   at A.ZO.GC(DbExpression , DbExpressionBinding , Boolean )
   at A.ZO.Visit(DbFilterExpression e)
   at (Object , DbFilterExpression )
   at A.LSB.B(Object , DbFilterExpression )
   at A.FP.Visit(DbFilterExpression e)
   at System.Data.Common.CommandTrees.DbFilterExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at A.ZO.GC(XO , AO& )
   at A.FP.GC(DbProjectExpression )
   at A.FP.Visit(DbProjectExpression e)
   at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at A.ZO.GC(DbExpression , Boolean )
   at A.ZO.GC(DbQueryCommandTree )
   at A.FP.GC(DbCommandTree , AN , IDictionary`2& )
   at AgileDesign.SsasEntityFrameworkProvider.SsasProvider.B(DbProviderManifest , DbCommandTree )
   at AgileDesign.SsasEntityFrameworkProvider.SsasProvider.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   --- End of inner exception stack trace ---
   at AgileDesign.SsasEntityFrameworkProvider.SsasProvider.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   --- End of inner exception stack trace ---
   at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   at System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   at System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
   at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at UsageExample.QueryRunner.TestQuery1() in c:\Users\ttustonic\Documents\Visual Studio 2012\Projects\CubeTest1\ExampleLibrary\QueryRunner.cs:line 65
   at UsageExample.QueryRunner.RunQuery() in c:\Users\ttustonic\Documents\Visual Studio 2012\Projects\CubeTest1\ExampleLibrary\QueryRunner.cs:line 45
   at UsageExample.Program.Main() in c:\Users\ttustonic\Documents\Visual Studio 2012\Projects\CubeTest1\UsageExample\Program.cs:line 15
I am using SsasEntityFrameworkProvider ver. 2.5.4

Thanks,
Tom
Coordinator
Oct 12, 2013 at 10:05 AM
The issue with parametrized Member() is fixed in ver. 2.5.5 and answered here: http://ssasefprovider.codeplex.com/discussions/461196