How to connect to Analysis Services from the iPad with ReportPlus

Guillermo Lopez / Wednesday, January 23, 2013

When we built SharePlus, we saw an opportunity in bridging one massively used server side technology such as Microsoft SharePoint, with a new family of technologies, such as the iPad and iPhone.

We saw a similar opportunity with Microsoft SQL Server Analysis Services, a platform that it’s among the most widely adopted business intelligence OLAP technologies in the corporate world. Our goal was simple, to allow anyone with an iPad to establish a connection to a SSAS cube without the need for any additional middleware, something we believe it’s key to enable true self service business intelligence (required, not sufficient).

That’s how & why ReportPlus shipped support to connect directly to Analysis Services with v1.0, allowing the user to query SSAS cubes and use them as input when authoring dashboards.

Creating the SSAS connection

Creating a connection to a SSAS server with ReportPlus is a similar process to configuring connections to other data sources, you must enter server host name, and account credentials. For the host name, ReportPlus has an option to discover SQL Server instances in the local area network. Tapping the blue disclosure button displays this dialog. For servers to appear in this listing they must have up & running the SQL Server Browser service

In SSAS connections you may also choose whether to create a native TCP based connection, or a connection to an HTTP SSAS endpoint. HTTP based access to Analysis Services is not something enabled by default, but it can be configured following the guidelines in this article from Microsoft.

ReportPlus SSAS Connection

Querying the cube

Once a connection to SSAS has been configured, it can be navigated to list the cubes available in that server. Querying a cube is as simple as dropping it on the dashboard canvas. This will open the pivot editor view, which lists dimensions, and measures on the left hand pane. Dimensions’ hierarchies can be navigated by tapping.

 ReportPlus SSAS pivoteditor

 There’s no default view of data, to create a query you must drop measures in the Values placeholder, and dimensions’ in the rows and columns placeholders.

Applying filters

To define filters on the defined query there are two options: either configuring the filter in the dropped dimensions, or dropping a dimension in the Filters placeholder.

ReportPlus SSAS Filters

Configuring the dimension to filter values can be achieved by tapping the dropped element in the rows or column placeholder. This will display the configuration settings pane on the right hand side, and from there you can enable filters by rule, or by value, as with other data sources in ReportPlus. Filter’s will depend on the data type, for instance Date dimensions will display Date filters.  

There’s a sort by caption option, which defines whether the filter will be applied to the dimension’s labels or to the corresponding values.

On the other hand, dropping in the Filters placeholder will display filter buttons on top of the data. Unlike the filters defined on the dimension’s settings, dropping dimensions in the filters placeholder allows the user viewing the dashboard to modify it’s values.

We’ve have received some very good feedback on our SSAS implementation, such as the following review:

Finally a way to access Analysis Services from the iPad

“I was waiting for an app like this, now I can connect to SQL Server and Analysis Services from the iPad.
Very useful app.” 
By blue-hawk USA

We’ve also received lots of feedback on ways to improve it, and we are working on it, so stay tuned.