www.robkerr.com
www.robkerr.com

mobile development, cloud computing and building great software

Rob Kerr
Author

Addicted to coding since writing my first programs for the Commodore computers in the 1980s. Currently working as an independent contractor focused on native iOS development.

Share


Tags


Twitter


www.robkerr.com

Connecting Native iOS apps to to SQL Server Analysis Services Cubes

Rob KerrRob Kerr

Using a web service pattern, we can connect a native mobile application with just about any data source. SSAS is most often used as a backend database for Microsoft Excel and out-of-the-box BI Platforms to implement slice-and-dice ad-hoc query use cases. But could we connect a native mobile application to SSAS to provide a high-performance analytical database? Absolutely! Let's look at how we can architect a solid solution connecting a Swift-based iOS native application with an on-premises deployed Analysis Services cube.

Architecture

Since there's no direct client framework for iOS (or Android) to connect to SSAS, we'll use the familiar pattern of placing a JSON web service in front of the SSAS database. Client drivers for SSAS provided by Microsoft are supported on Windows, so the web service I'll create will use .NET, and the C# code will leverage the standard ADOMD client drivers provided by Microsoft in the SQL Server Feature Pack Download.

I'll develop my client application using Swift and XCode, targeting iOS natively. The client could of course be developed in many different ways -- Native iOS, Native Android, Xamarin, Ionic, etc.

The final architecture looks as follows:

SSAS for Mobile Architecture Diagram

iOS Application design

Before going further, let's look at the design of the iOS application that will be the result of the development steps. Here's the UI we'll be creating -- a pretty basic KPI Scorecard displayed on an iPhone, with data served up by our .NET web service.

iOS UI

The key of the data integration within the iOS app is swift code in the ViewController that contains the KPI list in the screen shot. Here's an abbreviated version (error handling and some complexity removed). Note that there's nothing really different about this compared with any other data source -- all the complexity is encapsulated in the web service.

// excerpt from DashDetailViewController.swift
func getWebServiceData() {
    // Setup headers
    var headers : [String : String] = [
        "Content-Type": "application/json"
    ]

    // Add authorization
    let loginData: NSData = auth.dataUsingEncoding(NSUTF8StringEncoding)!
    let base64AuthString = loginData.base64EncodedStringWithOptions([])
    headers["Authorization"] = "Basic " + base64AuthString

    // Request to .NET + SSAS web service endpoint
    if let url = dashParameters?.urlEndpoint, parms = dashParameters?.parameters {

        Alamofire.request(.POST, url, headers: headers, parameters: parms, encoding: .JSON)
            .responseJSON { response in

                let resultNSString = NSString(data: response.data!, encoding: NSUTF8StringEncoding)!

                // Tear down the package and iterate over the objects found inside
                if let package = response.result.value as? [String:AnyObject] {

                    if let arr = package["data"] as? [AnyObject] {
                        for (_, item) in arr.enumerate() {

                            if let viz = item as? [String:AnyObject] {
                                if let vizType = viz["vizType"] as? String {

                                    if vizType == "LineChart" {
                                        print("Calling addVizTrendLineChart")
                                        self.addVizTrendLineChart(viz)
                                    }

                                    if vizType == "KPIItem" {
                                        self.addVizKpi(viz)
                                    }
                                }
                            }
                        }
                    }

                self.collectionView?.reloadData()
        }
    }
}

Write the MDX (or DAX) query

To create the data displayed in the iOS application, we need to query the Analysis Services cube. Like any database - SQL Server, Oracle, MongoDB, DocumentDB, etc., SSAS Cubes have a query language. And actually, they have two languages, depending on whether Analysis Services uses Multidimensional or Tabular modes. In this example, I've used MDX -- but this technique works just as well with DAX.

Here's the MDX to create the data set displayed above:

// TerritoryKpiList.mdx
WITH 
    MEMBER [Measures].[TerritoryLabel] AS [Sales Territory].[Sales Territory].CurrentMember.UniqueName
    MEMBER [Measures].[TerritoryKey] AS [Sales Territory].[Sales Territory].CurrentMember.Name
    MEMBER [Measures].[ChildCount] AS [Sales Territory].[Sales Territory].CurrentMember.Children.Count
SELECT 
{
    [Measures].[TerritoryLabel],
    [Measures].[TerritoryKey],
    [Measures].[ChildCount],
    KPIValue("Revenue"), 
    KPIGoal("Revenue"), 
    KPIStatus("Revenue"), 
    KPITrend("Revenue")
} ON COLUMNS,
NON EMPTY
{
    (STRTOMEMBER(@TerritoryKey).Children)
} ON ROWS
FROM [Adventure Works]
WHERE (STRTOMEMBER("[Date].[Fiscal].[Fiscal Year].&[" + @Year + "]")) 

Note there are two bound parameters in the MDX query: TerritoryKey and Year. The iOS application passes these parameters in via a JSON object called dashParameters in the Swift code above. The web service retrieves them from the JSON parameter object, and injects them into the query via AdomdCommand.

Implementing the .NET Web Service

The core of delivering the SSAS data to the mobile application is a .NET web service, in this case developed with Visual Studio 2015 and C#. Implementing a .NET web service is straightforward and I won't cover it step-by-step. But let's look at some important concepts that enable connectivity to SSAS.

Adding the ADOMD reference

Just as with any database access from a web service, a database driver is needed. SSAS supports a few different options: OLEDB, XML/A, and ADOMD. I recommend ADOMD in almost every programmatic use case, and for this web service this is what I've used.

The first step to adding the ADOMD reference is to obtain and install the driver software from Microsoft. The best way to get this is to download it from the Microsoft SQL Feature Pack for the version of SQL Server you'll be using. As of this writing, the latest production version is SQL Server 2014, and the feature pack is located here. SQL Server Feature Pack Download.

You'll need to install ADOMD on your production web front-end, as well as on your .NET development workstation or VM.

After installing ADOMD on the web-front end on your development workstation, add a reference to it, so you your references look similar to this:

ADOMD Reference

Implementing the ApiController

The key step is implementing the .NET controller. Again, this is straightforward and not really different than what you'd do with any kind of database backend -- I won't go through this step-by-step. But let's look at the pieces of my controller that are relevant to retrieving and returning data from SSAS.

// excerpt from CorporateSalesController.cs
namespace AdventureWorksAPI.Controllers
{
    public class CorporateSalesController : ApiController
    {

        [HttpPost]
        public IHttpActionResult TerritoryKpiList([FromBody]WebServiceParameters jsonData)
        {
            string queryName = @"CorporateSales\TerritoryKpiList";

            try
            {
                AdomdCommand cmd = ControllerHelper.setupMDXQuery(queryName);

                cmd.Parameters.Add("TerritoryKey", jsonData.territoryKey);  
                cmd.Parameters.Add("Year", jsonData.year.ToString()); 

                using (var reader = cmd.ExecuteReader())
                {
                    List<KpiItem> items = new List<KpiItem>();

                    while (reader.Read())
                    {
                        Util.MdxUtils.debugDumpRow(reader);

                        KpiItem item = new KpiItem();

                        int n = reader.FieldCount - 1;

                        item.key = MdxUtils.readString(reader[n - 6]);
                        item.name = MdxUtils.readString(reader[n - 5]);
                        item.childCount = MdxUtils.readInt(reader[n - 4]);
                        item.value = MdxUtils.readDouble(reader[n - 3]);
                        item.goal = MdxUtils.readDouble(reader[n - 2]);
                        item.status = MdxUtils.readInt(reader[n - 1]);
                        item.trend = MdxUtils.readInt(reader[n - 0]);

                        items.Add(item);
                    }

                    List<VizBase> data = new List<VizBase>();
                    data.Add(new KpiItemPackage(
                                    "TerritoryPerformance",
                                    "Territory Performance",
                                    items,
                                    "$#,###,###",
                                    "($#,###,###)",
                                    "api/CorporateSales/TerritoryDetailViews"));

                    return Ok(new VisualizationPackageObject(data));
                }
         }
        catch (Exception ex)
        {
            log.Error("*** ERROR: " + this.GetType().Name + "." +  
                  System.Reflection.MethodBase.GetCurrentMethod().Name + ": " + ex.Message);
            return InternalServerError(ex);
        }
    }

The controller is derived from the standard .NET ApiController base class, and uses an AdomdCommand to execute an MDX query against the SSAS cube. As a note -- ADOMD can process MDX or DAX, so you can use either query language variant within this type of web service.

Note in the controller method above, you don't actually see the MDX query as text. Since I don't want to mix the MDX query with my C# source code, instead I put it on the file system of the web server, and use the method setupMDXQuery(queryName) to load it from disk and return it as an AdomdCommand. I find this much cleaner, and it allows the query strings to be maintained atomically and updated without changing the web service source code.

The code for this method is as follows:

// excerpt from ControllerHelper.cs
public class ControllerHelper
{
    public static AdomdCommand setupMDXQuery(string queryName)
    {
        string connString = Util.MdxUtils.getConnectionString();
        string query = Util.MdxUtils.fetchNamedMDXQuery(queryName);

        // Trap for named query missing from file system
        if (query == null)
        {
            throw new Exception("Could not find named query " + queryName);
        }

        AdomdConnection conn = new AdomdConnection();
        conn.ConnectionString = connString;

        conn.Open();

        AdomdCommand cmd = new AdomdCommand(query);
        cmd.Connection = conn;

        return cmd;
    }
}

The fetchNamedMDXQuery(queryName) is just a helper routine to provide I/O to the file system on the web server. That code is as follows:

// excerpt from MdxUtils.cs
public static string fetchNamedMDXQuery(string queryName)
{
    string cwd = HttpContext.Current.Server.MapPath("~");
    string filePath = cwd + @"\MDX\" + queryName + ".mdx";
    return File.ReadAllText(filePath);
}
Rob Kerr
Author

Rob Kerr

Addicted to coding since writing my first programs for the Commodore computers in the 1980s. Currently working as an independent contractor focused on native iOS development.

Comments