Executing Standard MDX / Connection String Problems

Aug 22, 2013 at 4:35 PM
On the Agile Design Linked In page, there is a discussion around MDX coverage from the SSAS Entity Framework Provider. Agile states that 80% of all MDX queries can be generated and executed successfully with the remaining 20% having a complexity so grand it is better left to use straight MDX syntax.

Source : http://www.linkedin.com/groups/New-members-can-find-official-4141102.S.76779809

I am trying to figure out some code coverage in the instance we hit the 20% and wondering if it is possible to reuse the context (and the adodm client connection) to execute MDX queries through the SSAS Entity Framework Provider directly.

The reason I am asking this is a journey in itself, but if I could explain some issues I am having perhaps you could shed some light on what exactly the problem is.

Typically, to issue the MDX query, I would initialize an instance of the ADODM.NET client. This works flawlessly when connection to an Microsoft Analysis Server . However, when working with offline data cubes (.cub file) I start running into a very specific error message.

Now your product does not seem to have an issue opening up and executing data on an offline cube. However, if I use the ADODM.NET client directly, I get a failure with the following error message ( the values contained in << >> are based upon the current computer / cube file )
Either the user, <<domain user>,> does not have access to the <<cube>> database, or the database does not exist.
I am using the exact connection string the SSAS Entity Framework Provider uses. I have even generated a context and utilized
context.Database.Connection.ConnectionString;
to see if somehow the connection string was changing. The cube file is someway being touched, as if I try to open the cube file by another process it throws an error (as offline cube files can only be opened by one process at a time).

As I mentioned before, using a connection to a SSAS server allows me to execute a MDX query through the connection but I am having zero luck getting the offline cube to work. This offline cube works with excel and tableau software products as well, and works with your SSAS Entity Framework.

Coming full circle,
  • is there a way to reuse the SSAS Entity Framework ADODM client connection to query the local cube (or SSAS server)?
  • is there some magic that is being performed on the connection string / impersonation to allow the ADODM client to connect to the offline cube?
Any help would be appreciated in trying to cover the 20%.

Thanks
Aug 22, 2013 at 5:46 PM
After some experimentation I am able to get the local cube to work by using the correct ADOMD.NET reference. I was originally referencing the 100 version, but switching to the 110 version allows me to connect to the local cube (as the cube was generated in SQL SERVER 2012).

However, that still does not help me to understand how the SSAS Entity Framework is accessing the local cube as the reference it uses is the 100 version. Any light you could shed on this would be helpful, in addition to understanding if it is possible to re-use the connection the SSAS Entity Framework opens.
Coordinator
Aug 25, 2013 at 4:58 AM
Edited Aug 25, 2013 at 5:06 AM
lbrewington wrote:
After some experimentation I am able to get the local cube to work by using the correct ADOMD.NET reference. I was originally referencing the 100 version, but switching to the 110 version allows me to connect to the local cube (as the cube was generated in SQL SERVER 2012).

However, that still does not help me to understand how the SSAS Entity Framework is accessing the local cube as the reference it uses is the 100 version. Any light you could shed on this would be helpful,
-SSAS Entity Framework Provider does not manipulate connection strings in any way, it just creates ADOMD.NET AdomdConnection object and passes the connection string to it as is, preliminary removing "Cube=..." and "provider connection string=..." parts from it.
We can guess that Entity Framework and ADOMD.NET could read you offline cube file due to the fact that SSAS Entity Framework Provider has the next line of code:
        protected override string GetDbProviderManifestToken(DbConnection connection)
        {
            return "2008";
        }
But working with raw MDX and ADOMD.NET is not directly related to our product support, and we will not go into further details here. We recommend to contact Microsoft if you need further assistance here, or contact us by email for our paid services rates.
in addition to understanding if it is possible to re-use the connection the SSAS Entity Framework opens.
-Yes, you can re-use the connection string or AdomdConnection object by taking them from DbContext.Database.Connection.ConnectionString or
DbContext.Database.Connection.StoreConnection property respectively.
But please note that if you start to manually manage the connection state by opening and/or closing it explicitly, you will become responsible to make sure that the connection is open before running a LINQ query and closing the connection when not needed. Entity Framework will not open/close connections opened or closed manually.