Wijmo Enterprise includes an OLAP module for creating web-based pivot tables and charts with all data aggregation performed on the client side. While this approach is suitable for handling thousands of records, it's not practical for large data sets containing hundreds of millions of records. Specifically, if your data is stored in SQL Server Analysis Services (SSAS) cubes, you will want to take advantage of the server-side processing that is already in place. Fortunately, as of the 2017 v1 release, Wijmo's pivot controls can now consume SSAS cubes when used in conjunction with the ASP.NET MVC Edition of ComponentOne Studio.
Since the server-side data access assemblies depend on the full .NET Framework, you will need to use a Windows-based Microsoft development environment to achieve the functionality described here. This blog post uses Visual Studio 2017 running on Windows 10, version 1703. See Part 2 for guidance on how to implement the same functionality using Visual Studio Code, Microsoft's free, open-source editor.
Prerequisites
If you haven't already, download and install a version of Visual Studio 2017. If you normally use Wijmo outside of Visual Studio, you can get started with the free community edition. Next, 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 don't 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 Visual Studio 2017 and select File > New > Project. When the New Project dialog opens, you will see a tree control on the left side. Select the node Installed > Templates > Visual C# > Web. You will see a list of templates with similar names.
At first glance, it's not at all obvious which template you should choose. Just trust me on this one and select the template named ASP.NET Core Web Application (.NET Framework). Note that the equivalent template based on .NET Core will not work because the ComponentOne Web API assemblies currently depend on the full .NET Framework. Give the project a meaningful name (CubeDemo, in this example), and press OK. After the next dialog opens, choose the Web Application template and press OK. In my experience, it's easier to add Web API functionality to an MVC application rather than the other way around.
Press F5 to build and run the application, which will open the home page in a browser.
Make note of the localhost port number assigned by Visual Studio (53217, in this example), as you will need it later to test your data connection. Close the browser, then return to Visual Studio and press Shift + F5 to stop the web server process.
Add NuGet Packages
Since you created the project from a standard Microsoft template, you will need to add the ComponentOne packages manually. In Solution Explorer, right-click the project node and select Manage NuGet Packages. Change the NuGet package source to GrapeCity, click the Browse link, and then type api into the search box. Install the package named C1.AspNetCore.Api.DataEngine.
If prompted to review changes, click OK. Note that you do not need to install the package named C1.AspNetCore.Api separately, as it will be installed as a dependency of the data engine package.
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. On the Visual Studio Tools menu, click GrapeCity License Manager, then enter the email address and password for your GrapeCity account. When the following screen opens, click the button named Generate App (runtime) Licenses.
On the next screen, select Eval from the Serial Number dropdown, then check the App Name box.
To complete the process, click the button named Generate App (runtime) Licenses. If all goes well, you should see a screen like the one below. Click Done to dismiss the dialog.
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:
- The name of the cube that you will use later when specifying data service URL strings.
- 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.
- The name of the cube as defined on the server.
To test the connection to the cube, press F5 to run the application, then enter the following URL in a new tab, replacing 53217 with the port number noted earlier:
http://localhost:53217/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. At this point, you are 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 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.
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. If you're familiar with Wijmo, but not web development with Visual Studio, this post should give you a sense of what you can accomplish on that platform. If you already know how to develop MVC applications using the Microsoft stack, but aren't familiar with ComponentOne Studio, this post provides a concise set of steps for adding Web API data providers capable of serving SSAS cubes.
John Juback