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.

Custom drill paths for Analysis Services reporting

Any business report will answer a predefined set of questions, but it will often give rise to many additional queries and chains of thought as the user wants to explore any oddities or anomalies in more depth.

As a report designer you want to provide flexibility in your reports. You may want to create a customised drill down behaviour which you know is how the report users naturally think of the data. Similarly giving users the ability to ‘drill across’ into other hierarchies is a great way of providing chain of thought interactivity, but the sheer complexity and number of hierarchies in many corporate cubes these days means a vanilla ‘drill across’ may be problematic. In the hands of users relatively unfamiliar with the data model drill across can be a shortcut to a support ticket:

  • What is the difference between hierarchy ‘a’ & hierarchy ‘b’?
  • What is hierarchy ‘c’?
  • When I drill across into hierarchy ‘d’ it hangs?
    • that’ll be the 8 million skus in the attribute hierarchy you’ve picked…

We recently worked closely with a customer to implement a solution in XLCubed v7.6 for exactly these types of issue. Many thanks to Thomas Zeutschler at Henkel AG for the inspiration!

Flex Report extends a concept which Henkel had developed in-house to deliver report level flexibility of the drill path, while retaining control over what the user can do. Non-technical report developers can easily define the drilling behaviour for a report (for example from Country -> Promotion -> Product Category), and also to provide controlled ‘Drill Across’ options where the users have a choice of 5 or 6 meaningful levels to drill into, rather than the 200 which may exist in the cube. The difference in usability can be stark, as in the example below.


The end result is report consumers with guided and controlled flexibility in data exploration. This type of reporting can be delivered from a potentially broad group of business users who have the flexibility to develop sophisticated reporting applications without the need to go back to IT each time they want to create a slightly modified drill path. We are finding that both the deliverable reports and the report development process itself resonates with many businesses. Take a look at this video for an introduction: Flex Reporting