Using ASP.NET Core MVC and Web API to Render SSAS Cubes (Part 2)

In Part 1 of this blog post, you learned how to use Wijmo Enterprise pivot controls in conjunction with the ASP.NET MVC Edition of ComponentOne Studio to consume and render SQL Server Analysis Services (SSAS) cubes. Part 1 used Microsoft's full-featured, professional development environment, Visual Studio 2017. In Part 2, you will learn how to implement the same functionality using Visual Studio Code, Microsoft's free, open-source editor.

Although there are versions of Visual Studio Code available for Linux and Mac OS X, the ComponentOne server-side data access assemblies depend on the full .NET Framework. Therefore, the steps in this tutorial will only work on a Windows machine. Windows 10 is assumed, although earlier versions should also work with the appropriate framework downloads.

Prerequisites

If you haven't already, download and install the Windows version of Visual Studio Code.

If you have Visual Studio 2017 installed, you should already have the necessary components for .NET Core as well as reference assemblies for the .NET Framework. If you do not have Visual Studio 2017 installed, do the following:

  1. Visit the download page for .NET Core. Make sure that you choose the SDK download, not the runtime, and click the link for the Windows (x64) Installer.
  2. Determine which version of the .NET Framework you will target. Run the winver command to see which version of Windows 10 you are running. For version 1703, the framework version is 4.7. For version 1607, the framework version is 4.6.2.
  3. Visit http://microsoft.com/net/targeting and download the appropriate .NET Framework Developer Pack. (This may require a restart first.)

Finally, download and install ComponentOne Studio. Create a free GrapeCity account if you haven't already done so, as you will need it later to generate a runtime license key. If prompted, download and install .NET Framework 3.5. During installation of ComponentOne Studio, make sure that you check the boxes labeled ASP.NET MVC and WebApi. You do not need to perform a separate installation of Wijmo, as the necessary files are copied to your machine when you install the ASP.NET MVC Edition.

If you're not familiar with the MVC (Model-View-Controller) pattern, check out this overview, which covers MVC design principles as well as the specifics of the ASP.NET Core implementation.

Create a New Project

To get started, open a command prompt and change the current directory, if desired. Execute the following commands:

mkdir CubeDemo  
cd CubeDemo  
dotnet new mvc

The last line creates a new ASP.NET Core MVC project. The first time you run this command, it may take a few minutes to populate your local package cache. To open the newly created project in Visual Studio Code, run this command:

code .

When the editor has finished loading, its left pane will display the files in your project.

Click the main project file, CubeDemo.csproj, to open it for editing. If this is the first time you opened a .NET Core project in Visual Studio Code, you will see the following prompt:

Click Show Recommendations to switch to the Extensions pane:

Click the Install button for C#, then click Reload to activate it. Click Reload Window when prompted to complete the installation.

By default, the newly created project targets .NET Core 1.1. However, the ComponentOne data access assemblies require the full .NET Framework, so you will need to modify the project file. Go back to the CubeDemo.csproj tab and change the target framework from netcoreapp1.1 to net47 (or net462, depending upon which version of Windows 10 you are running), and then add the win7-x64 runtime identifier:

<PropertyGroup>  
  <TargetFramework>net47</TargetFramework>  
  <RuntimeIdentifier>win7-x64</RuntimeIdentifier>  
</PropertyGroup>

Next, open the integrated terminal in Visual Studio Code (Ctrl + `). If this is first time you executed this command, you will see the following prompt:

Click OK, Never Show Again to keep using Windows PowerShell, then type the command dotnet restore to update the NuGet packages for the new target framework.

Create a Debug Configuration

Open the Debug pane by clicking its icon. The dropdown control in the toolbar shows No Configurations, so you will need to create one.

Click the gear icon to the right of the dropdown. When prompted, select the .NET Core environment. Edit the file launch.json and replace both occurrences of:

${workspaceRoot}/bin/Debug/<target-framework>/<project-name.dll>

with the following, where the last two path fragments must match the contents of CubeDemo.csproj, which you edited earlier:

${workspaceRoot}/bin/Debug/net47/win7-x64/CubeDemo.exe

Save all open files, then press F1 to open the command palette. Type the word task, then select the command Tasks: Configure Task Runner.

A list of environments will appear. Select .NET Core from the list. Return to the integrated terminal and re-enter the command dotnet restore.

On the Debug toolbar, switch the launch target to .NET Core Launch (web), then press F5. If you see the following error message, click Close to dismiss it.

After dismissing the error, press F1 to open the command palette, then type debug, then select the command Debug: Download .NET Core Debugger. If you see the following error message, you can click Close and ignore it:

Note that the .NET Core debugger only works if the target framework is netcoreapp1.x, so you will not be able to use it with this project. However, it is necessary to download and install it to perform builds. To run your project without debugging, press Ctrl + F5. The default browser will open on the site http://localhost:5000/.

Close the browser, then return to Visual Studio Code and click Shift + F5 to stop the web server process.

Add NuGet Packages

Since Visual Studio Code doesn't provide an integrated NuGet package manager, you will need to edit the project file directly to specify external package references. Open CubeDemo.csproj and add the following lines to the end of the existing <ItemGroup> tag:

<PackageReference Include="C1.AspNetCore.Api" Version="1.0.20171.91" />  
<PackageReference Include="C1.AspNetCore.Api.DataEngine" Version="1.0.20171.91" />

After you save the project file, you should see the following prompt:

Click Restore, or return to the integrated terminal and run the dotnet restore command again.

Generate GrapeCity Runtime Licenses

Before you can use the ComponentOne packages you added in the previous section, you will need to generate runtime licenses for your application. Visit http://www.componentone.com/MyAccount/MyASPNet.aspx and enter the email address and password for your GrapeCity account. On the next screen, select Evaluation from the Serial Number dropdown, then enter CubeDemo in the App Name box.

Click the Generate button to emit a class declaration that looks something like this:

public static class License  
{  
    public const string Key =   
        "ABgBGAIYB4BDAHUAYgBlAEQAZQBtAG8ApRLl1hi2W7vtfkFDXJboBWYdrfKOfg5G" +  
        "3TsNjAbp0UoNuYaZBgAO57j/Q6f+0aIbGMn7q4V0pdIy2Nr0fy2Y9B7RU782QCbJ" +  
        "xRtMaeu4PUQLj+VaOdMJ3M2IHRMZ4qGl1NG7YXAeUtmMBUZWK6/ySM5TSgruyqlN" +  
        "<<lines deleted>>" +  
        "EpNEiC0XFqTaOG2cIOQPeDuF57WNJ4kFeNx4HnEkQ6bWin5spSGIEg==";  
}

Copy all code from the multiline text box to the clipboard. Go back to Visual Studio Code and switch to Explorer view. Add a new file named Licenses.cs to the project, and insert the following contents:

namespace CubeDemo  
{  
    // paste generated License class here  
}

Replace the comment line with the contents of the clipboard. Next, open the file Startup.cs and locate the ConfigureServices method. Add these lines to the beginning of the method:

C1.Web.Api.LicenseManager.Key = License.Key;  
C1.Web.Api.DataEngine.LicenseManager.Key = License.Key;

At runtime, this code will apply your license key to the appropriate ComponentOne artifacts.

Register a Cube Provider

After installing the required NuGet packages and generating runtime licenses, you're ready to register a cube data provider. Open the file Startup.cs and insert the following code before the call to app.UseStaticFiles:

app.UseDataEngineProviders().AddCube(  
    "cube",  
    @"Data Source=http://ssrs.componentone.com/OLAP/msmdpump.dll;Provider=msolap;Initial Catalog=AdventureWorksDW2012Multidimensional",  
    "Adventure Works"  
);

The AddCube method takes three parameters:

  1. The name of the cube that you will use later when specifying data service URL strings.
  2. The connection string for the cube on the server. This example uses the standard Adventure Works cube hosted on the componentone.com domain, which is accessible via anonymous authentication.
  3. The name of the cube as defined on the server.

To test the connection to the cube, press Ctrl + F5 to run the application, then enter the following URL in a new tab:

http://localhost:5000/api/dataengine/cube/fields

In this URL, the api/dataengine fragment denotes a data engine provider implemented by the C1.AspNetCore.Api.DataEngine package. The cube fragment matches the first parameter in the call to AddCube. The fields fragment directs the data provider to return the cube's schema in JSON format. If the connection is successful, the browser should display the following content:

Close the browser and stop debugging by pressing Shift + F5 in Visual Studio Code. At this point, you're ready to create a client test page that interacts with the server to visually render the cube.

Add a Client Test Page

Now that you have a functional server application that can access cube data, you can write a client test page containing data visualization controls that communicate with the server. Note that the project contains a web root folder named wwwroot. Any assets that you place in this folder can be accessed with a relative path to that root. This means that you can add a standard HTML page to your project and bypass the MVC conventions for client pages.

Right-click the wwwroot folder, then select New Folder. Name the new folder cube. Add a new file named index.html to this folder, and insert the following content:

<!DOCTYPE html>  
<html>  
<head>  
    <link rel="stylesheet" href="/lib/bootstrap/dist/css/bootstrap.min.css" />  
    <link rel="stylesheet" href="/css/site.min.css" />  
    <link rel="stylesheet" href="http://cdn.wijmo.com/5.20171.300/styles/wijmo.min.css" />  
    <script src="http://cdn.wijmo.com/5.20171.300/controls/wijmo.min.js"></script>  
    <script src="http://cdn.wijmo.com/5.20171.300/controls/wijmo.input.min.js"></script>  
    <script src="http://cdn.wijmo.com/5.20171.300/controls/wijmo.grid.min.js"></script>  
    <script src="http://cdn.wijmo.com/5.20171.300/controls/wijmo.grid.filter.min.js"></script>  
    <script src="http://cdn.wijmo.com/5.20171.300/controls/wijmo.olap.min.js"></script>  
</head>  
<body>  
    <div class="container body-content">  
        <div class="row">  
            <div class="col-sm-3">  
                <div id="pivotPanel">  
                </div>  
            </div>  
            <div class="col-sm-9">  
                <div id="pivotGrid">  
                </div>  
            </div>  
        </div>  
    </div>  
    <script type="text/javascript">  
        var panel = new wijmo.olap.PivotPanel("#pivotPanel");  
        var grid = new wijmo.olap.PivotGrid("#pivotGrid");  
        var engine = new wijmo.olap.PivotEngine();  
        engine.itemsSource = "http://localhost:5000/api/dataengine/cube";  
        engine.rowFields.push(new wijmo.olap.CubePivotField(engine, "[Customer].[Country]", "Country"));  
        engine.columnFields.push(new wijmo.olap.CubePivotField(engine, "[Customer].[Occupation]", "Occupation"));  
        engine.valueFields.push(new wijmo.olap.CubePivotField(engine, "[Measures].[Customer Count]", "Customer Count"));  
        panel.engine = engine;  
        grid.itemsSource = panel;  
    </script>  
</body>  
</html>

The <head> tag includes local references to Bootstrap stylesheets, followed by Wijmo CDN references to stylesheets and scripts. The <body> tag defines a two-column layout using Bootstrap styles, with <div> placeholders named pivotPanel and pivotGrid. The <script> block defines the behavior of the page. First, the PivotPanel and PivotGrid data visualization controls are instantiated:

var panel = new wijmo.olap.PivotPanel("#pivotPanel");  
var grid = new wijmo.olap.PivotGrid("#pivotGrid");

Next, a PivotEngine is created and bound to the server instance by setting its itemsSource property to the appropriate URL:

var engine = new wijmo.olap.PivotEngine();  
engine.itemsSource = "http://localhost:5000/api/dataengine/cube";

The initial settings of the row, column, and value fields are specified at the pivot engine level:

engine.rowFields.push(new wijmo.olap.CubePivotField(engine, "[Customer].[Country]", "Country"));  
engine.columnFields.push(new wijmo.olap.CubePivotField(engine, "[Customer].[Occupation]", "Occupation"));  
engine.valueFields.push(new wijmo.olap.CubePivotField(engine, "[Measures].[Customer Count]", "Customer Count"));

Finally, the pivot panel is bound to the pivot engine, and the pivot grid is bound to the pivot panel:

panel.engine = engine;  
grid.itemsSource = panel;

One minor server tweak remains. Open the file Startup.cs and insert the following line of code before the call to app.UseStaticFiles:

app.UseDefaultFiles();

This lets you specify the path to your test page in the browser without explicitly appending the index.html filename.

Run the Project

Save all files, press Ctrl + F5 to run the project, and then enter the following URL in the browser:

http://localhost:5000/cube

You should see the pivot panel populated with the available fields, and the pivot grid should display rows of countries and columns of occupations.

View Results in Visual Studio Code

As an alternative to opening a separate browser window, you can configure your project to display the target web site within Visual Studio Code itself. First, add a new file named test.md to the project folder, and insert the following content:

<div style="border:1px solid #777;position:absolute;left:10px;top:10px;bottom:10px;right:10px;">   
<iframe src="http://localhost:5000/cube" width="100%" height="100%" frameborder="0" />   
</div>

Next, open the Debug pane and use the toolbar to change the debug target to .NET Core Launch (console). Press Ctrl + F5 to start the web server. With test.md open, press Ctrl + Shift + V to open markdown preview in an adjacent tab. If you see a button labeled Scripts have been disabled in this document, click it, then choose Enable script execution in markdown previews for this workspace. Close the preview tab, reopen test.md, then press Ctrl + Shift + V again.

Conclusion

In this blog post, you learned how to use ASP.NET Core MVC in conjunction with ComponentOne Studio to render SSAS cubes in Wijmo pivot controls. Moreover, you learned how to accomplish this with Microsoft's free, lightweight editor, Visual Studio Code.

Download Now!<%/if%>

MESCIUS inc.

comments powered by Disqus