Getting Started Walkthrough

The purpose of this walkthrough is to query SSAS OLAP cube using SDX dialect of LINQ and SSAS Entity Framework Provider.

Prerequisites

SSAS Entity Framework Provider 2.5.2, .NET Framework 4.0 - 4.5, ADO.NET Entity Framework 4.1 - 6.0, Visual Studio 2010 - 2013, connection to SQL Server Analysis Services 2008 or 2012 (SSAS)
(Note: you can use Visual Studio 2008 or any other IDE with SSAS Entity Framework Provider as well, but steps in this walkthrough are specific to VS 2010 and VS 2012).

If you have installed NuGet package with SSAS Entity Framework Provider, please make sure that you have the latest version. If you would like to run this example using our SSAS cube rather than our offline cube file, please make sure you followed Installation Instructions.

Steps:

  • Create project, add references

    • Select File > New > Project...
      Select Visual C# > Console Application and name the project SdxWalkthrough

    • Right click the project, select Properties

    • ! Go to Application tab, change '.Net Framework 4 Client Profile' to '.Net Framework 4:'

      Note: SSAS Entity Framework Provider uses ADOMD.NET under the hood, and ADOMD.NET is not compatible with the client profile. But because SSAS Entity Framework Provider should never be put into client side assemblies in well architected real world applications, it should not be an issue.

    • Optional, but recommended: go to Build > Configuration Manager... and make sure that x86 is selected in Active Solution Platform (Note: x86 is a Visual Studio 2010 default, see here why (look for answer # 21 - Jon Skeet comment). SSAS Entity Framework Provider is compiled for AnyCPU platform, so it can be used for AnyCPU or for x64 as well, but it is not an optimal choice for performance and we recommend to run NGEN update if you choose a platform other than x86 - see this article for details):

      Than go to Build tab > Platform Target and make sure that 'x86' is selected.:

    • Right click the project, select Manage NuGet Packages..., search for "SSAS" click on SSAS Entity Framework Provider (tm) and click Install button.

  • Generate entity model from NorthwindEF relational database.

    • If you do not have NorthwindEF relational database deployed, download its creation script from http://archive.msdn.microsoft.com/EFSampleProvider/Release/ProjectReleases.aspx?ReleaseId=4406, unzip, open the unzipped folder, than open and run in SQL Server Management Studio <YourPath>\EFSampleMsSqServerlProvider\EFSampleProvider\NorthwindEFModel\Database\CreateNorthwindEFDB.sql , after this step you should have NorthwindEF relational database created.

    • Select Project > Add New Item...

    • Select Data > 'ADO.NET Entity Data Model' from the list of available items, name your model 'NorthwindEF.edmx' and click Add:

    • Select 'Generate from database' and follow the prompts. When prompted for connection string select the one where your NorthwindEF database resides. On the next step expand Tables node and select OrderDetails, Orders and Products tables, then click Finish:

  • (Skip this step if you use VS2012 or if you already have got NorthwindEFModel.tt or NorthwindEF.tt T4 template under NorthwindEF.edmx node in a solution explorer).
    Swap to DbContext Code Generation. The NorthwindEFContext is currently generating a derived ObjectContext and entity classes that derive from EntityObject, we want to make use of the simplified EF 4.1 DbContext API:

    • On the design surface; Right Click > Add Code Generation Item...

    • Select 'Code' from the left menu

    • Select 'ADO.NET DbContext Generator'

    • Name the item 'NorthwindEFModel.tt' :

    • Click 'Add'

    • You'll notice that two items are added to your project:

      NorthwindEFModel.tt - This template generates very simple POCO classes for each entity in your model.
      NorthwindEFModel.Context.tt - This template generates a derived DbContext to use for querying and persisting data:

  • Configure SSAS connection

    • Open the App.config, clear its content and paste the following XML:

      <?xml version="1.0" encoding="utf-8"?>
      <configuration>
      <system.data>
      <DbProviderFactories>
      <clear />
      <add name="SSAS Entity Framework Provider"
      invariant="AgileDesign.SsasEntityFrameworkProvider"
      description="Entity Framework Provider for SSAS"
      type="AgileDesign.SsasEntityFrameworkProvider.SsasProviderFactory, SsasEntityFrameworkProvider, Version=2.5.2.0, Culture=neutral, PublicKeyToken=0c609c2d7c233e82" />
      </DbProviderFactories>
      </system.data>
      <connectionStrings>
      <add name="NorthwindEFContextOlap"
      connectionString="Provider=MSOLAP.4;Data Source=.;Initial Catalog=NorthwindEfOlap;Integrated Security=SSPI;Cube=NorthwindEF"
      providerName="AgileDesign.SsasEntityFrameworkProvider"
      />
      <add name="NorthwindEFEntities"
      connectionString="metadata=res://*/NorthwindEF.csdl|res://*/NorthwindEF.ssdl|
      res://*/NorthwindEF.msl;provider=System.Data.SqlClient;provider connection string=&quot; data source=.\sqlExpress;initial catalog=NorthwindEF;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;
      "
      providerName="System.Data.EntityClient"
      />
      </connectionStrings>
      <startup>
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
      </startup>
      </configuration>
      Note: we added "Cube=NorthwindEF" to the right of a normal SSAS connection string, so that to know a cube name. Use one DbContext per a cube. If you did not install NorthwindEF SSAS database yet, please follow "Installation Instructions.rtf" document located in the folder where you installed SSAS Entity Framework Provider. (It is "%ProgramFiles%\Agile Design LLC\SSAS Entity Framework Provider \" by default.)
      If you do not have SSAS installed and you do not want to install it, you can use a local offline cube file instead. -In such a case type a full path to the example local cube file in your Data Source, e.g.: "C:\Program Files (x86)\Agile Design LLC\SSAS Entity Framework Provider\UsageExample\Bin\Debug\NorthwindEF.cub". Please keep in mind that the example local cube does not contain most of the dimensions and measures used in our examples.

    • Please make sure that connection strings correspond to your environment. If SSAS is not deployed on your local machine, replace Data Source=. with a name of your own SSAS instance.

  • Add a creation method for SSAS OLAP connection into context

    • Right click the project, select Add > New Folder and name it "Custom"

    • Right click on the Custom project folder, select Add > Class..., name the class NorthwindEFEntities.cs:

    • Copy and paste the following code:

      using System.Data.Entity;
      namespace SdxWalkthrough
      {
      public partial class NorthwindEFEntities
      {
      private NorthwindEFEntities(string nameOrConnectionString)
      : base(nameOrConnectionString)
      {
      }
      private NorthwindEFEntities()
      : base( "name=NorthwindEFEntities" )
      {
      }
      public static NorthwindEFEntities CreateForOlap()
      {
      Database.SetInitializer<NorthwindEFEntities>(null);
      return new NorthwindEFEntities("Name=NorthwindEFContextOlap");
      //"NorthwindEFContextOlap" is a name of your connection string to SSAS.
      }
      public static NorthwindEFEntities CreateForSql()
      {
      return new NorthwindEFEntities();
      }
      }
      }

      Note: SSAS Entity Framework Provider ver. 2.5.2 does not support cube generation from EF model definition, that is why we need to disable database initializer with this line in the code above:
      Database.SetInitializer<NorthwindEFEntities> (null);

  • Add metadata to the entity model to identify model assembly and measure groups

    • Open 'Properties\AssemblyInfo.cs' file and add
      [assembly: AgileDesign.SsasEntityFrameworkProvider.Attributes.ModelAssembly] attribute in it:

      (Alternatively you could install SsasEntityFrameworkProvider.Attributes package from NuGet.org into your model assembly).

    • Right click Custom folder, select Add > Class..., name the new class OrderDetail, change its namespace to SdxWalthrough and make it public partial , add [MeasureGroup] attribute on top of its definition. Then open an auto-generated OrderDetail partial class-pair and change the type of the property Quantity from short to int , move this property into a hand made OrderDetail partial class in Custom folder, so that your change is not overridden during a next generation. (Note: the type change is needed because 'short' type would cause a data overflow exception when dealing with aggregated values like Sum(Quantity)):

  • Configure DbContext with keys:

    • Right click the project and select Add > Class... and name the class OrderDetailConfiguration.cs

    • Copy-paste the following code:

      namespace SdxWalkthrough
      {
      public class OrderDetailConfiguration
      : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<OrderDetail>
      {
      public OrderDetailConfiguration()
      {
      HasKey
      (
      od => new
      {
      od.OrderID,
      od.ProductID
      }
      );
      }
      }
      }

    • Open NorthwindEFModel.Context.cs (in VS2012 it will be named NorthwindEF.Context.tt) and delete the constructor.
      Find OnModelCreating method and move it into its partial pair NorthwindEFModel in Custom folder.
      Replace OnModelCreating method body with the following line of code:

      modelBuilder.Configurations.Add(new OrderDetailConfiguration());
      //Note: use the following line if needed by your cube, our examples do not need this:
      //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

      - now you are set to create and run LINQ / SDX queries against NorthwindEF OLAP cube

  • Create a LINQ / SDX query

    • Open Program.cs and copy-paste the following code:

      using System;
      using System.Linq;
      namespace SdxWalkthrough
      {
      class Program
      {
      static void Main()
      {
      var context = NorthwindEFEntities.CreateForOlap();
      var result = context.OrderDetails.Select(o => o.Quantity);
      Console.WriteLine(result.Single());
      }
      }
      }

  • Run the query: Press F5 to run the application and verify the result (50119):