Dimension Name Pluralizes In MDX

Aug 9, 2013 at 8:34 PM
I am trying to evaluate the SSAS Entity Framework Provider, and have followed the online guide for the Provider to work against my OLAP cube. However I am encountering a very basic problem revolving around pluralization of the dimensions when translated to MDX.

I have on one hand successfully executed this query on my cube with the expected results:
 Lambda 
      var result = context.Transaction.Select(x => x.Amount);

 SDX
        var result = (

                       from transaction in context.Transaction
                       select transaction.Amount

                       );

 MDX Translation : 
       SELECT {
                  [Measures].[Amount]
                   }
                    ON COLUMNS
                     FROM [CUBENAMEREDACTED]

Perfect. This is exactly what I expected. Now let's add some dimensions and see what happens.


SDX:
var result = (
                       from channelType in context.ChannelType
                       from transaction in context.Transaction
                       where channelType.Name == "Field"
                       select new
                       {
                           transaction.Amount,
                           channelType.Name
                       }
                       );
MDX Translated :


WITH MEMBER [Measures].[C1]
        AS 1
SELECT
NON EMPTY
HEAD
(
FILTER
(
(
[Channel Types].[Name].[Name]
)

,
[Channel Types].[Name].MemberValue = 'Field'
),
2
)
ON ROWS,
{
[Measures].[C1],
[Measures].[Amount]
}
ON COLUMNS
FROM [CUBENAMEREDACTED]
YIKES! Error. Well let's explore some more and see what the problems is. Right off the bat I can see that "Channel Types" is incorrect. By copying this MDX statement and executing it directly in SSMS I get the same error that Visual Studio throws. However, simply removing the plural and changing the instance to "Channel Type", the query executes flawlessly and returns the expected results.

This should be an easy fix, or at least that was what I have was thinking to myself. I have scoured the code base looking for instances of the string Channel Types, and have found nothing. Nothing I changed in code appears to effect the MDX generated to not make the dimension plural.

I did find some discussion revolving around naming conventions and using IMdxNamingConvention , primarily located at this website. However, this seems like a lot of work for something that should be relatively straightforward especially when you consider that neither the RDMS that holds the cube data nor the cube itself every once uses the ChannelTypes convention.

I created an entire new project and made sure to check the box when generating the edmx to not create the plural objects. Still same error.

So I attempted to explore that faq entry a bit more. I added the
 Mdx.NamingConvention = new PreserveSpecifiedNameConvention()

This simply changed the generated MDX to be ChannelTypes (without the space). I am going to go try to implement IMdxNamingConvention to strip out the plural addition to dimensions. I seriously hope that I find this not necessary. I guess the other method would be to rename my dimensions columns within the cubes to be plural. Currently my mind is being blown by this pluralization and am at wit's end.


Can someone chip in on this and tell me if I am doing something wrong and help to resolve this plural error. If I can get the MDX generation to not pluralize the dimension, the query works perfectly.
Aug 9, 2013 at 8:44 PM
Edited Aug 9, 2013 at 8:45 PM
Here to answer my own question:

Digging around in the forum and found a reference to this site:

EF PLURALITY REMOVAL


using System.Data.Entity.ModelConfiguration.Conventions;

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.Add(new TransactionConfiguration()); // <--- this should reflect your cube configuration
        }
This code follows closely with the example setup on the agile website. So just add the extra line about removing the plural names bit and you should be good to go.
Coordinator
Aug 13, 2013 at 4:13 AM
Edited Aug 13, 2013 at 4:14 AM
Thank you for your post lbrewington.

In general we do not document Entity Framework API as it is already done by Microsoft. But in this case this is a frequent problem and your solution is basically the same that we referred to in this thread: http://ssasefprovider.codeplex.com/discussions/439951 .

We do not put
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 
into our walkthrough, because we ourselves use pluralized dimension names in our example cube and our example would not work without PluralizingTableNameConvention.

But we added that PluralizingTableNameConvention convention removal line as a comment into our Getting Started Guide: http://agiledesignllc.com/GettingStartedGuide as per your feedback.
Aug 14, 2013 at 5:51 PM
I did want to point out another small problem with the GettingStartedGuide, and hopefully this is not a problem isolated to me. While working through the getting started guide I ran into a compile issue with the image located at step 7c : http://agiledesignllc.com/images/guide/image040.jpg (oddly enough the same place you added the above comment) . The public partial class NorthWindEFEntities does not inherit the dbContext class. However, if you open the ExampleLibrary.sln and look at the same file (NorthWindEFModel.cs) , the partial class correctly inherits the dbContext class. Adding this class to the partial class fixed the error. Just a tiny problem that might throw some people if they are unfamiliar with your UsageExamples / ExampleLibrary files you make available to developers getting started with your product.

Other than that, I would mention that the GettingStartedGuide appeared to be a little overwhelming at first, but after working through it I can say that it was a great resource for being able to quickly incorporate your product and be able to evaluate it.
Coordinator
Aug 18, 2013 at 3:23 AM
Edited Aug 18, 2013 at 3:25 AM
-That is correct, NorthwindEFEntities class must inherit from DbContext indeed, but please note that there are 2 partial classes for NorthwindEFEntities: one is generated by T4 template, and the other one is being added by hand on step 7c. Because the generated partial class already inherits from DbContext, the custom partial class being added on step 7c does not have to, but if you inherit both of NorthwindEFEntities partial classes from DbContext, the code will still compile and work.

We are glad that our guide helped you. Please feel free to contact us directly with your feedback and/or questions at info@Agiledesignllc.com .