What if only connection to SSAS Cube available and no OLTP equivalent to generate EF Model?

Apr 11, 2013 at 3:35 PM
Hi Agile Design Architects,

I’ve been asked to develop an UX for a B.I. system, and I’m evaluating your SSAS EF Provider to work with it and recommend it to their software maintenance folks after my part of the work becomes completed (the first production version). They provided me with only the SSAS Database backup. I restored it in my local SSAS and it works fine.

But, with your SSAS EF Provider, how can I create an EF model if I have access only to the SSAS Cubes? Even because the final application will connect directly to SSAS, which schema is completely different from the OLTP database, mainly the nomenclature.

A solution is to create an EF like Designer (with Wizard) to generate the Model based on Cube Metadata, but it’s out of my scope and may serve as a suggestion for a future, killer release from you.

In my case, I was thinking in: read the entire cube definitions (including the hierarchies), then create the OLTP equivalent to it (based on the cube reversed engineered), then generate the EF Model from this database. It’s because using Code First to manually map the huge amount of dimensions and measures that those cubes has is quite cumbersome.

I still think that it’s an ugly workaround and not an elegant solution… but, please, tell me if it’s the only way to solve my problem: work with SSAS EF Provider having only SSAS Cube available.

Thanks in advance!
Lucas Ponzo
Coordinator
Apr 13, 2013 at 8:36 PM
Edited Apr 14, 2013 at 5:44 AM
Hi Lucas,

Thank you for your question.

Please note that you need access to the DB which is used as SSAS cube data source - in practice quite often a cube's data source it is not the OLTP database, but some demoralized data warehouse or OLTP replicated copy. Schema of your cube source database is rarely that different from your cube definition.
But in some cases when you have a lot of renames and calculations in DSV itself, or lots of calculated measures in your cube, such cube structure could potentially be quite different from its source database structure, indeed.
Is it your case?

Will it be feasible to ask people with access to your cube data source DB to either:
  • Script their DB schema and give you the generated SQL DDL scripts, so that you could run them and create your own DB copy with no data, but with the same schema?
  • Give you read access to their DB schema only?
  • Run Entity Framework model / class generation from that DB for you, and give you the generated classes/model? (I believe you could automate that process via a one line script and ask people with the access to run that script).
If any of the above is possible, it should solve your problem.

Also entity data model does not have to be generated from DB, e.g. you might create your POCO classes following Entity Framework code first conventions that correspond to names in your cube data source, or using standard Entity Framework mapping approaches coupled with our naming conventions API. But we do not know how much additional work that would be for you (usually it is not that much work, and sometimes it could give you a cleaner design without unused classes and properties at the end).

As for the tool that could generate entity framework classes or data model from SSAS cube definition, we agree that it would be really nice to have it indeed, and we may implement that tool one day, but it is not in our immediate plans yet. If you still believe that you must have such a tool, we could discuss it further by email (our address is info@AgileDesignLlc.com)

Please let us know if you have any additional questions.
Apr 15, 2013 at 4:05 PM
Thank you Grand,

I got the DW schema from my customer and will generate the model to start my testing.

One more question, if the Cube Dimension Attributes differ from the its corresponding schema in DW, I just need to rename that Entity Property (DB Column) in EF Model?

Thanks for your help!!!
Coordinator
Apr 16, 2013 at 11:38 AM
Edited Apr 16, 2013 at 11:43 AM
"If the Cube Dimension Attributes differ from the its corresponding schema in DW, I just need to rename that Entity Property (DB Column) in EF Model?"
-Yes, exactly.
You can also create new properties in EF model for dimension attributes and measures absent in the source database schema but present in your cube.

If the differences in names are common (e.g. spaces between words) you can consider using naming conventions (please see this page for details). Adding spaces between words is a default naming convention.
Apr 16, 2013 at 3:31 PM
Nice, I created a Custom Naming Convencion using your Interface to deal with the underscore ("_"), and it worked in this simple test: context.dw_NFVEIC.Select(f => f.FIN_Qtde).Single();

But, on other simple test (bellow), I got the error: "Specified argument was out of the range of valid values. Parameter name: ordinal"

->>>> LinQ query
var query =
from v in context.dw_VEICULOS
from f in context.dw_NFVEIC
select new { v.VEC_modelo, v.VEC_cor, f.NFV_qtd };

->>>> Resulting MDX (WORKED IN SSAS MDX QUERY EDITOR and in c# program error: "Specified argument was out of the range of valid values. Parameter name: ordinal"):

WITH MEMBER [Measures].[C1] AS 1
SELECT NON EMPTY
( [dw VEICULOS].[VEC modelo], [dw VEICULOS].[VEC cor] ) ON ROWS,
{ [Measures].[C1], [Measures].[NFV qtd] } ON COLUMNS
FROM VEC_FAT

->>>> Single line returned in SSAS MDX query editor:
All All 1 12197

/*************************/
1) I need in this test a List to be returned, and to acomplish that, I must specify the level in the MDX, commonly repeating the Member Name as:
"( [dw VEICULOS].[VEC modelo].[VEC modelo], [dw VEICULOS].[VEC cor].[VEC cor] ) ON ROWS, ... etc..."

3) My first attempt was to modify the Naming Convention GetHierarchyAndColumnName() method to repead the atribute itself and it WORKED FINE when calling .ToList(), even with the "WITH MEMBER [Measures].[C1] AS 1" stuff being generated in the MDX. What is that after all?

4) Is this "repeting pattern" inplemented in the GetHierarchyAndColumnName() actually a good choise? 5) Putting those "repeated atributes" in the EF Model is a better choise? If none of both above, how to get a real list without that using the standad LinQ query pattern demonstrated in your examples?

Thanks in advance!
Coordinator
Apr 18, 2013 at 11:35 AM
Thank you for your question Lucas.
Our engineer will have a look into this issue ASAP, and we will provide an update in this thread.
Apr 18, 2013 at 4:20 PM
OK Grand,

During that I'll try to make the middle tier using ADOMD temporarilly, beucase tomorrow I'll need to show the application working in this first Project Milestone...

But it's completelly temporally and I'll not discuss thechinical detais in such inicial milestone.

I'll awaiting for your response. I'm really interested to use and spread that library.

Thanks.
Apr 19, 2013 at 2:48 PM
Just to complement, I'm using:

1) The version of the Library downloaded from Nuget Package manager.
2) Visual Studio 2012 Update 2 Ultimate Edition
3) MSSQL 2012 Developer Edition
4) Windows 8 Professional x64 based system.
Coordinator
Apr 20, 2013 at 2:14 AM
Edited Apr 20, 2013 at 6:13 PM
We do not have your cube and your entity model to reproduce your case, but most likely this answer will address your question.
Please see inline:

LucasPonzoX wrote:
Nice, I created a Custom Naming Convencion using your Interface to deal with the underscore ("_"), and it worked in this simple test: context.dw_NFVEIC.Select(f => f.FIN_Qtde).Single();

But, on other simple test (bellow), I got the error: "Specified argument was out of the range of valid values. Parameter name: ordinal"

->>>> LinQ query
var query =
from v in context.dw_VEICULOS
from f in context.dw_NFVEIC
select new { v.VEC_modelo, v.VEC_cor, f.NFV_qtd };

->>>> Resulting MDX (WORKED IN SSAS MDX QUERY EDITOR and in c# program error: "Specified argument was out of the range of valid values. Parameter name: ordinal"):

WITH MEMBER [Measures].[C1] AS 1
SELECT NON EMPTY
( [dw VEICULOS].[VEC modelo], [dw VEICULOS].[VEC cor] ) ON ROWS,
{ [Measures].[C1], [Measures].[NFV qtd] } ON COLUMNS
FROM VEC_FAT

->>>> Single line returned in SSAS MDX query editor:
All All 1 12197

/*************************/
1) I need in this test a List to be returned, and to acomplish that, I must specify the level in the MDX, commonly repeating the Member Name as:
"( [dw VEICULOS].[VEC modelo].[VEC modelo], [dw VEICULOS].[VEC cor].[VEC cor] ) ON ROWS, ... etc..."

3) My first attempt was to modify the Naming Convention GetHierarchyAndColumnName() method to repead the atribute itself and it WORKED FINE when calling .ToList(),
-You did the right thing, when you repeated your column name: what is a single column name in SQL syntax becomes 2 names in MDX syntax: 1 name for a dimension hierarchy and 1 name for a dimension level. MDX allows you to omit a level name, in which cases it defaults it to (All) level.
Because LINQ is not multidimensional/hierarchical and Entity Framework always expects a flat table result with leaf level rows back, you should never use (All) level in GetHierarchyAndColumnName() method, and always specify both Hierarchy and Level names.
Please see Debunking the myth of MDX hierarchy, level and member once and for all! for more details.
E.g. this is a fragment from our implementation:
public class AddSpacesToCamelCasingWordsConvention 
    : IMdxNamingConvention
{
    ...
    public string GetHierarchyAndColumnName(string sqlColumnName)
    {
        if (IsStoreFunction(sqlColumnName))
        {
            return sqlColumnName;
        }
        return String.Format("{0}{0}", GetMdxName(sqlColumnName));
    }
    bool IsStoreFunction(string sqlColumnName)
    {
        return sqlColumnName.StartsWith("'<");
    }
    ...
}
We can provide you full sources of AddSpacesToCamelCasingWordsConvention for free, if you wish. Also our engineers could implement any naming conventions by your specifications, please contact us by email for sources or/and prices, if interested.
even with the "WITH MEMBER [Measures].[C1] AS 1" stuff being generated in the MDX. What is that after all?
-In short that is a correct behavior, and you can safely ignore these C1 measures whenever you see them.
Boring technical details: C1 should be there in the query due to Entity Framework tricks. Entity Framework inserts integer constants (named like C1) into generated command trees and SQL for some reason, and expects integer values back (C1 constant value is always 1). To comply with EF expectations we must generate constant integer values in the result. You can take the same LINQ query, run it against some SQL Server, trace SQL generated by Entity Framework and see this fact.
4) Is this "repeting pattern" inplemented in the GetHierarchyAndColumnName() actually a good choise?
-We think so, because we give you a freedom to choose what levels to pick, should you ever need that. Please feel free to give us your reasoning, if you disagree.
5) Putting those "repeated atributes" in the EF Model is a better choise?
If none of both above, how to get a real list without that using the standad LinQ query pattern demonstrated in your examples?
-I am not sure I follow, but if you mean to name you properties this way SameNameSameName, I would rather recommend you to use normal property names without redundancy, and put all work into your naming convention class.
Also, would you consider to use a regular MS naming convention with camel cased words, rather than words separated by an underscore? -In that case you would be able to use our default naming convention and forget about these complexities of mapping between LINQ and MDX.
Thanks in advance!
-Thanks for your questions. Did our information help you?
Apr 24, 2013 at 1:11 AM
Grand,
We can provide you full sources of AddSpacesToCamelCasingWordsConvention for free, if you wish.
This wold help me a lot! I'm willing to write a Custom Naming Convention that includes that kind of Pascal Casing transformation.
In short that is a correct behavior, and you can safely ignore these C1 measures whenever you see them.
Got it.
Also, would you consider to use a regular MS naming convention with camel cased words, rather than words separated by an underscore? -In that case you would be able to use our default naming convention and forget about these complexities of mapping between LINQ and MDX.
I'd renamed my model and removed the underscores and named all entity proterties with PascalCase, and worked fine.

Now, the only problems I'm facing is:

I have an entity named DimData (Date Dimension), but when filtering by Year, for example (DimData.Ano) the resulting MDX plurarizes it, even its not being plurarized in the Model ( [Dim Data__s__].[Mês] ). When I think it was almost done, this unwanted plurarization is out of my control.
var query = from v in context.DimVeiculos
from d in context.DimData
from f in context.MedNfVeic
where f.NfvQtd > 1 && d.Ano == 2012 
select new { v.VecModelo, v.VecCor, f.NfvQtd, f.NfvVltot};
Do you think it would be usefull if I send to you my SSAS Database and VS Project for you to debug it against your current library laboratory version?
Thanks for your questions. Did our information help you?
Helped a lot my friend!!! I'll continue hard testing it and try to cover as many scenarios as possible, from simple to complex ones and I'll providing you with the results. Meanwhile, I'll rename my DimData property to DimDatas temporalily in my development environment to the able continue the tests while you send me a solution for that.

Lets keep working together and I'm sure pretty results will come for both of us.
Coordinator
Apr 24, 2013 at 6:46 PM
LucasPonzoX wrote:
Grand,
We can provide you full sources of AddSpacesToCamelCasingWordsConvention for free, if you wish.
This wold help me a lot! I'm willing to write a Custom Naming Convention that includes that kind of Pascal Casing transformation.
-Could you send us your email address to info@ agiledesignllc .com please, so that we could send you our related sources attached?
In short that is a correct behavior, and you can safely ignore these C1 measures whenever you see them.
Got it.
Also, would you consider to use a regular MS naming convention with camel cased words, rather than words separated by an underscore? -In that case you would be able to use our default naming convention and forget about these complexities of mapping between LINQ and MDX.
I'd renamed my model and removed the underscores and named all entity proterties with PascalCase, and worked fine.

Now, the only problems I'm facing is:

I have an entity named DimData (Date Dimension), but when filtering by Year, for example (DimData.Ano) the resulting MDX plurarizes it, even its not being plurarized in the Model ( [Dim Data__s__].[Mês] ). When I think it was almost done, this unwanted plurarization is out of my control.
var query = from v in context.DimVeiculos
from d in context.DimData
from f in context.MedNfVeic
where f.NfvQtd > 1 && d.Ano == 2012 
select new { v.VecModelo, v.VecCor, f.NfvQtd, f.NfvVltot};
-SSAS Entity Framework Provider does not do any pluralization on its own, but ADO.NET Entity Framework (EF) may do that by default (depending on EF version). Please see this thread for a solution, if you would like to get rid of pluralization:
http://stackoverflow.com/questions/4425027/entity-framework-code-first-naming-conventions-back-to-plural-table-names
Do you think it would be usefull if I send to you my SSAS Database and VS Project for you to debug it against your current library laboratory version?
-We will try to answer all your questions without your project sources first, but in case if the answers we provide do not work for you, we could ask you to send the sources to us. (In such a case some example code reproducing your issue would work even better for us, than the original project code).
Thanks for your questions. Did our information help you?
Helped a lot my friend!!! I'll continue hard testing it and try to cover as many scenarios as possible, from simple to complex ones and I'll providing you with the results. Meanwhile, I'll rename my DimData property to DimDatas temporalily in my development environment to the able continue the tests while you send me a solution for that.
-You have other options to solve this pluralization problem as well. You might disable pluralization in EF itself as per the link above, or you might specify a correct name in EF Column attribute, or via HasColumnName() method of EF fluent configuration API.
Lets keep working together and I'm sure pretty results will come for both of us.
-Absolutely.
Aug 9, 2013 at 8:42 PM
Edited Aug 9, 2013 at 8:49 PM
[deleted]