Accessing Cubes in Wijmo with Basic Authentication

Wijmo's OLAP module provides a mechanism for connecting directly to SQL Server Analysis Services (SSAS) cubes. It includes the following components:

• PivotEngine: A non-visual component that takes in raw data via its itemsSource property and builds output views exposed by the pivotView property.
• PivotPanel: A control that allows users to dynamically build and modify views using drag-and-drop and easy to use context menus.
• PivotGrid: A data grid that extends our FlexGrid to show pivot data, including collapsible rows and columns, subtotals, grand totals, and custom cell formatting.
• PivotChart: A chart control that extends our FlexChart control to show pivot data, including multiple chart types and hierarchical axes.

An earlier post outlined the steps needed to configure a cube server for HTTP access. However, at that time, the Wijmo API did not provide a straightforward method for supplying Basic Authentication credentials. The 2020 v1 release addresses this issue, allowing you to provide credentials when creating a PivotEngine component.

This post illustrates the additional steps needed for Basic Authentication, both on the server and in client-side code.

Server Configuration for HTTP Access

If you have not already done so, configure your Analysis Services server for HTTP access, as described in this article. Make sure you follow the instructions for Basic Authentication.

To enable cross-domain requests, install the IIS CORS Module on the server. Earlier instructions suggested configuring HTTP Response Headers, but at the time of this writing, installing the IIS CORS Module appears to be sufficient (and easier).

You will still need to configure the OPTIONSVerbHandler mapping, available under Handler Mappings in the IIS Manager application.

To fully configure the IIS CORS Module, you will need to manually edit the web.config file for the OLAP service (located in the same folder as MSMDPUMP.DLL). Specifically, you cannot use the wildcard character "*" as an origin; you have to include a domain, even if it's localhost.

Here is a typical web.config file, copied from my development server:

<?xml version="1.0" encoding="UTF-8"?>  
<configuration>  
    <system.webServer>  
        <handlers accessPolicy="Read, Script">  
            <clear />  
            <add name="OPTIONSVerbHandler" path="*" verb="OPTIONS" type="" modules="ProtocolSupportModule" scriptProcessor="" resourceType="Unspecified" requireAccess="Read" allowPathInfo="false" preCondition="" responseBufferLimit="4194304" />  
            <add name="OLAP" path="*.dll" verb="*" type="" modules="IsapiModule" scriptProcessor="C:\inetpub\wwwroot\OLAP\msmdpump.dll" resourceType="Unspecified" requireAccess="Script" allowPathInfo="false" preCondition="bitness64" responseBufferLimit="4194304" />  
            <add name="TRACEVerbHandler" path="*" verb="TRACE" type="" modules="ProtocolSupportModule" scriptProcessor="" resourceType="Unspecified" requireAccess="None" allowPathInfo="false" preCondition="" responseBufferLimit="4194304" />  
            <add name="StaticFile" path="*" verb="*" type="" modules="StaticFileModule,DefaultDocumentModule,DirectoryListingModule" scriptProcessor="" resourceType="Either" requireAccess="Read" allowPathInfo="false" preCondition="" responseBufferLimit="4194304" />  
        </handlers>  
        <cors enabled="true">  
            <add origin="http://localhost:8080" allowCredentials="true">  
                <allowHeaders>  
                    <add header="Origin" />  
                    <add header="Content-Type" />  
                    <add header="Accept" />  
                    <add header="Authorization" />  
                </allowHeaders>  
                <allowMethods>  
                    <add method="GET" />  
                    <add method="POST" />  
                    <add method="OPTIONS" />  
                </allowMethods>  
            </add>  
        </cors>  
    </system.webServer>  
</configuration>

Note that in this example, I used localhost:8080 to match my development process. In the real world, this would be an actual domain or IP number. This completes the IIS portion of the server configuration.

Next, create an ordinary non-administrator local machine account on the server. This account is the account that you will use to access SSAS cubes in Wijmo applications.

To complete the server configuration, open SQL Server Management Studio and connect to your Analysis Services instance. Expand the Databases node and its children as in the following illustration. (Here the database and its cube share the same name, but this is typically not the case.)

Right-click the Roles folder and select New Role to open the Role Properties dialog. Specify a role name and optional description string. Leave the database permission boxes unchecked.

In this example, the role is named Cube Reader.

Select the Membership page, then click Add. Select the local machine user you created earlier for this purpose (in this example, DemoUser).

Select the Data Sources page, then grant read access to the desired data source (in this example, Adventure Works DW 2012).

Select the Cubes page, then grant read access to the desired cube (in this example, Analysis Services Tutorial).

Click OK to dismiss the Role Properties dialog and finish the server configuration. If you create additional cubes at a later date, you will need to revisit the last screen to grant read access to the newly created cubes, if desired.

Client Access to Cube Server

Earlier Wijmo documentation and samples used a public cube server that supports anonymous access, as in the following example:

var ng = new wijmo.olap.PivotEngine({  
    itemsSource: {  
        url: 'http://ssrs.componentone.com/OLAP/msmdpump.dll',  
        cube: 'AdventureWorks'  
    }  
});  

With the 2020 v1 release, the itemsSource property now supports three additional parameters:

• catalog is the name of the Analysis Services database that contains the cube in question. Depending upon the server configuration, it may be required to disambiguate the name of the cube.
• user is the name of the local Windows user on the server.
• password is the password assigned to this user.

In my development environment, here is how I authenticate a connection to the Analysis Services Tutorial cube depicted earlier:

var ng = new wijmo.olap.PivotEngine({  
    itemsSource: {  
        url: 'http://10.41.0.88/OLAP/msmdpump.dll',  
        cube: 'Analysis Services Tutorial',  
        catalog: 'Analysis Services Tutorial',  
        user: 'DemoUser',  
        password: '********'  
    }  
});  

Note that Wijmo automatically converts the credentials to base64 format before sending them to the server.

Conclusion: Basic Authentication for Wijmo's OLAP Components

The 2020 v1 release of Wijmo now supports Basic Authentication for its OLAP components when connecting directly to SQL Server Analysis Services cubes. This release also adds support for server-defined attribute orderings and includes performance enhancements for direct cube connections.