Data Mashups 101

In an ideal world for report designers, all the data required for a report is available in one data source, and the structure and hierarchies perfectly match the reporting needs. Sadly, the world isn’t always ideal (just ask Theresa May…).

We often see scenarios where users are reporting numerical data from Analysis Services but want to include descriptive or textual information held elsewhere. People have approached this in different ways, but XLCubed’s mashup capability can make it a much more streamlined and maintainable process.

Mashups can be used to build analytic models, but in this simplest, 101 case, they give an easy way to build list type reports across two or more data sources.

Take the example below based on Reseller sales, where the cells in blue are from Analysis Services, and those in green are from SQL Server. The SQL data relates to Reseller Banking details in this case but could be credit history or any other required information. The report is filtered by a slicer allowing users to show only Resellers in the selected country.

The process of setting this up is straightforward. Firstly you’ll need to add a connection to the Analysis Services model, and one to the SQL database. With that done you need to then add a third connection, a ‘Data Mashup’:

On choosing Data Mashup you’ll be asked to select 2 or more connections to use – so choose the two existing connections.

As this is a simple list report, we’ll use an XLCubed Table. When you add the Table and choose the Data Mashup Connection, you’ll be asked to define the 2 queries, and then how they join together. To create the join you’ll need to be able to match on one or more fields, in this case the Reseller Name (yes, a key would be better…).

The query from Analysis Services will be in the form of a Grid, and you’ll need the column you want to join on to be positioned in rows, with the other data elements required on columns. The query from SQL will be a standard SQL query.

The screenshot below shows the Mashup dialog with the two data sources and their query definitions. When you select the second connection you can define the join by selecting the two (or more) joining fields as shown circled in green.

With that done, you’re good to go.

By default, the join is an ‘inner join’ which means only data which exists in both data sets will be returned. This can be changed to a left or right outer join where required.

With the steps above, we have a mashup report, but it’s returning all matching rows – we likely want to add some filters for a specific country or month. Here you can use a Slicer, or an Excel cell as preferred. While the join type is an inner join, the filter can be applied to either of the data sources – it’s normally easiest on the Analysis Services side, which also has the benefit that you can potentially filter by data groupings which do not exist in the SQL source.

To do this, simply add an XLCubed slicer as normal, then go to the Table Design, edit the Analysis Services connection and in the Designer add the slicer to the selection for the required hierarchy.

We’ve seen this approach save users a lot of time and maintenance effort – hopefully it helps streamline your reporting process.

No comment? We’ve got plenty to say!

Today’s blog will run through XLCubed’s commentary functionality.

At XLCubed we have seen a lot of customer interest in commentary and collaboration in the last couple of years.

We’re all familiar with the standard comments functionality in Excel where you add a comment to an Excel cell.

However, in a dynamic BI environment it can be limiting.  For example, say I add an Excel comment to cell $C$10, then the comment is tied to that specific cell.  If a user changes a filter selection, the numbers change but the comment does not and may not now be relevant.

This is where XLCubed commentary shines through.

By default, XLCubed comments are tied to a specific value and are made at a cell/datapoint level (or higher).  XLCubed comments are dynamic and tied to the data so if the data moves to a different location in the grid then so does the associated comment.  XLCubed comments are not workbook-specific and allow commentary to be viewed across reports.

So where could you use comments?

Potential use cases could be:

  • To warn of a temporary operational issue – for example, a failed data load
  • An at-source explanation of why a value is higher/lower than expected
  • A discussion thread where users can share insight or raise/answer questions in-context (that’s what our first screenshot shows)

To reassure all our users who might worry about permissions and the ability to see information that should be limited, XLCubed commentary access is controlled at a user/group level from the XLCubed control app.

Also, by default all grids have commentary disabled – you have to go into the grid property and enable comments

Comments for a particular datapoint eg Germany, Bikes, January 2015 can potentially be viewed in all reports that refer to that datapoint (as long as the destination grid is enabled to display comments).

Comments can be viewed and added both in the Excel Client and through the XLCubed Web Portal.

Commentary access is configured in the Web Admin tool below:

The options available are to enter comments, view comments, full control or none.

You also need to enable the grid property to allow comments:

Access comments by using XLCubed’s right-click menu > Comments.  Alternatively, comments are available from the XLCubed Grid ribbon.

XLCubed commentary supports highly-formatted text, which can be set directly in the dialog, or copied from Word.

You can include files attachments and images from an Excel range.  Both of these can be useful if you want to bring something in particular to the attention of other team members.

After adding your comments hovering over the cell will display the comment – just like standard Excel as below:

Selecting XLCubed > Comment will show the comments in an XLCubed dialog:

Comment Frames allow you to have a permanent on-report display of the comments rather than needing to hover over or select the specific cell.

These can be tied to a particular grid or to slicer selections.

The grid below has separate comments for France for January 2015 and June 2015 – the comment frame shows both comments at once:

This screenshot shows me all the comments held for the KPIs grid based on current slicer selections:

As you can see from the comment history, there’s been quite a discussion amongst the team regarding the Margin value for May 2012 for all PoS.

Remember, XLCubed commentary is fully-governed with strict control over who can add and who can view comments.

All in all, XLCubed commentary can help add context to and share insight on the numeric data shown in reporting. It can prevent many users investigating the same ‘known’ issues and provides a forum to help team members understand the data and decide on future steps to make better decisions.

We hope you’ve found this blog helpful.

Here’s a couple of useful links – the first is to the v9.1 commentary tutorial:

This is a link to our YouTube video commentary webinar: