Blog – Page 3 – XLCubed Blog

Bump Charts in XLCubed

9th December 20148th November 2016/ Blog, Charts, Dashboard, Reporting, Uncategorized, Visualization

So today’s blog is about adding Bump Charts in Excel using v8 XLCubed.

Initially a Bump Chart looks the same as a line chart – the difference is they plot the rank position rather than the actual value.

Let’s imagine that I sell a product in a marketplace with 10 other competitors. I may like to see how the rank position of my product and the competition changes over time to check if I’m gaining or losing market position. It’s a common scenario in pharma, where we have a good customer base.

You will usually want dates on the category axis so the trends are shown across time. The series then holds the items to be compared, in this case the products.

Our example has been set up with Measures on Headers, Product Categories on Series and Date Calendar on Categories. For more information on using Small Multiples in XLCubed please visit Small Multiple Charts.

The currently selected measure is Reseller Order Quantities (selected though the Measures slicer)

for the eleven months prior to April 2008 (selected through the Date slicer)

for a subset of products.

Looking at the bump chart you can see that I’ve selected Road Bikes and Mountain Bikes for easy comparison. You can quickly see that the rank position for Road Bikes dropped quite dramatically from May 2007, picked up again in September before dropping again in November and rising in December through to February 2008. The change for Mountain Bikes, on the other hand, was less dramatic, rising and falling slightly, steadying in February 2008 before dropping again the following month.

To create a bump chart just select Line – Bump as the Chart Type on your Small Multiple chart. The neat part is that all the rankings are worked out for you behind the scenes, without the need for lots of complex Excel gymnastics trying to work through the full result set month by month.

Workbook slicers – all for one and one for all!

28th October 201421st September 2016/ Blog, General, Reporting, Uncategorized

So this is our second blog on the new features of XLCubed v8 – today we’re going to run through workbook slicers.

Workbook slicers allow the user to create the slicers at the workbook level so that they can be displayed for any/all sheets.

There’s a slicer pane which can be arranged horizontally or vertically and stays in place when you navigate to another sheet. This means that if you have a multi-sheet workbook you only need to define one set of slicers. These can then configured to be shown or hidden for individual sheets as required.

Turn the slicer pane on by selecting Workbook slicers from the XLCubed ribbon, Slicers tab:

Within the slicer pane there’s an Add Slicer button – this brings up the standard design form for adding slicers.

The Edit layout button brings up the window below. It allows you to configure the order in which slicers will appear on the pane, which sheets they will be visible on and the padding between individual slicers. You can also set a background fill colour from here.

The screenshot above shows that the Date.Calendar slicer is available on a number of sheets. Selecting a slicer choice on one sheet will refresh the other sheets where the slicer is also available:

Once added, you link workbook slicers to your report in the same way as embedded slicers. You can link directly to grids and other XLCubed objects and output their selection to Excel cell locations for use by formulae.

Their positioning on the web is fixed but if you find the slicers are taking up too much screen space you can make your slicer selections and then use this icon to toggle the Slicer Pane off:

Excel heat maps made easy!

22nd October 2014/ Charts, General, Reporting, Uncategorized, Visualization

With the recent release of version 8 we’re going to blog about a number of the new features, starting with how to create a heat map in Excel.

Here’s a fairly large table showing sales for thirty six products across twenty six US states:

There’s a lot of data here but it’s not giving us any helpful information as the table is too large to see any pattern or comparison.

A heat map could be a useful way to give a quick visual picture of the spread of the sales volume. Let’s add a simple heat map, new in version 8 of XLCubed.

Select the data area in the table, and then from the XLCubed ribbon select the InCell-Chart group, and heat map:

As we have already selected the data area to be charted this prompt is already showing the correct cell locations.

Choose the formula destination (where the formula controlling the chart will be located), and the Chart destination (where the top left cell in the chart area will be located).

We can now define the look of the heat map in the Chart Format dialog:

We have set the low and high colours to define a blue colour gradient.

Outlying values could potentially skew the chart so you have the option to exclude these by setting minimum and maximum values. Select the icon to use, squares in our case, and the number of steps or bands to split the range of values into.

We have pre-arranged the Excel cell sizes to be squares, and this is the resulting heat map:

You can now quickly assimilate the spread of values in a glance, and note the higher sales volumes in Maine, Michigan and Missouri for Road, Touring and Mountain Bikes.

To alter the formatting of the chart simply double click on any one of the squares in the heat map, or on the chart formula to bring up the formatting dialog.

If you are not already a user of XLCubed you can get started with an evaluation of XLCubed by going to our registration page.

Excel BI myths debunked – #6: No report sharing & distribution

13th May 2014/ Analytics, BI, Charts, Dashboard, Excel Tricks, Reporting, Sparklines, Table Design, UI Design, Uncategorized, Visualization

Here we continue our theme on the myths which get propagated about Excel based BI. The next argument is that Excel BI cannot handle widespread report sharing and distribution. Base case we actually agree with this one, and that’s why we invested in developing XLCubed Web Edition specifically to address it.

Understandably, sharing an Excel workbook around hundreds or thousands of users is not something which many companies will consider. A web based distribution approach is much lighter and easier to manage. The drawback is that most web based report design environments lack the flexibility and latent user skill base of Excel. XLCubed provides a simple way to push data-connected reports developed in Excel to a portal based environment, where report consumers don’t require any software installed locally, other than a browser. The reports can also be accessed interactively through our native mobile apps for Apple, Android and Windows phone 8.

XLCubed Web is self-sufficient and does not require SharePoint. For customers with SharePoint and keen to retain it as a centralised environment – no problem, XLCubed Web can integrate so tightly within SharePoint the end users won’t even know it’s there.

Excel based users can become web and mobile report designers in minutes. XLCubed uses Excel as a key part of the BI solution rather than as the entire BI solution, and it’s that which allows us to address the sharing problem, along with the other myths we have identified in this blog series.

from any version of Excel:

…to web…

…to mobile.

Custom drill paths for Analysis Services reporting

3rd December 2013/ Analytics, BI, Reporting, UI Design

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

Some Excel BI myths debunked – #5 Real-time data exploration

11th September 2013/ Uncategorized

#5: Lack of Real-time data exploration

The argument is often made that Excel is too inflexible to answer spur of the moment questions quickly and effectively. The scenario given is that you’re in a meeting with your Excel workbook, and someone asks a related question not currently accounted for. How embarrassing to have to look at your back up folder of printed Excel workbooks… Really? It may escape the attention of some, but Excel is actually an electronic product too so as a first point you wouldn’t need to dust off an enormous binder of printed reports.

That aside, the overall argument has some merit in specific cases. If Excel is acting as both the datastore and the presentation tool you have a problem. If the data you need isn’t in the workbook, you’re bang out of luck.

There are two key requirements to address the issue in Excel. Firstly the data needs to be stored outside the workbook; in the case of XLCubed that’s in AS cubes or tabular models. This means when the data isn’t currently visible in the workbook it can still easily be queried and brought into play.

Secondly, while it’s a huge step having the data in a cube, that in itself isn’t enough. You need to be able to get it out quickly, easily and flexibly and to display it as information rather than just data. There are significant limitations with pivot tables when used to report on a cube and XLCubed addresses these while adding a lot more capability on top. The additional data you need to answer the question is readily available, and you have tools to do something meaningful with it using slice and dice tabular reporting, interactive charting and straightforward user calculations.

So when someone asks the question in meeting, you can explore it interactively and on the spot. And in Excel.

Some Excel BI myths debunked: #4 – no maps

3rd September 2013/ Uncategorized

#4: no Maps

The next commonly listed criticism of Excel BI is the lack of integrated geospatial mapping. While for some reports maps remain an irrelevance, for others they can make a huge difference, with targeted advertising or awareness campaigns being the most obvious. While you can now use mapping in Power View, for most corporate office users that isn’t an option yet. XLCubed brings both point and shape based mapping to any version of Excel.

The example report below mixes mapping with the Small Multiples concept. The approach means you can have multiple identical maps which vary only by the time period. This helps you see change over time regionally, and also to make comparisons between the direction and pace of change by region.

You can zoom in and pan the maps as needed and they’ll stay in sync, and individual points on the map can also be set up as report selectors which update the rest of the report in sync.

So, mapping in Excel? – absolutely, right now and in any version.

Some Excel BI myths debunked: #3 – limited dashboards

21st August 2013/ Analytics, BI, Dashboard, Excel Tricks, Reporting, Sparklines, UI Design, Uncategorized, Visualization

#3: Limited and difficult to Maintain Dashboards

Third on our list of common criticisms of Excel focused BI, is the limitations of Excel Dashboards.

“Excel dashboards are ugly, limited, and inflexible…”

It’s possible to build a truly awful dashboard in pretty much any dashboard tool. No tool is magic, ignoring the Doctor’s Sonic Screwdriver of course, and if you make bad design choices when building a dashboard the end result can be a mess. Similarly you can build a pretty decent dashboard in most tools. So even in base Excel with no additional software you can build a dashboard which looks good, and many people do.

In native Excel there are undoubtedly some limitations around the available chart types, and the handling of dynamic charting. However you do have the benefit of very fine grain control over the layout and positioning of tables and charts. The camera object also lets you break out of the fixed column width which is sometimes seen as a limitation.

XLCubed extends the core charts available in Excel with a rich library of in-cell charts, small multiple/trellis charting, mapping and TreeMaps. It means you can deliver more in Excel visually, rather than have to leave the environment totally. Dashboards mean different things to different people, for some a dashboard can be a table with a chart, but most contain significantly more than that. The example below uses a mixture of native Excel charting and XLCubed in-cell charts.

It’s based around a sample personal finance data set, and brings a lot of information together in hopefully a visually appealing and effective way. If you want to build a highly formatted and relatively densely populated dashboard like this, it’s going to take more than a few minutes in any tool, no matter what the marketing says. In reality you’ll most likely struggle to get the exact layout in a widget based dashboard tool as you lose some of the fine-grain control over table and chart sizing which you have in Excel.

Dashboards can be fundamentally simpler than the first example, but require more specialised chart types like the example below. In this case it’s a dashboard built in XLCubed Excel Edition and published to the Web, looking at fuel pricing for a downstream oil company (fictitious data). It’s a ranked table of data for a selected county in Florida, and is then using an extended boxplot to display the price distribution in the market, and a map to show the Revenues and Volumes geographically.

One major issue with Excel dashboards can be the maintenance. If it’s an Excel-only dashboard, bringing in the new data, and checking all the links can be a time consuming process. In an XLCubed environment the cube is updated behind the scenes and the next time you open the report you’ll get the updated data, the ongoing burden of maintenance is largely removed.

So in summary, Excel when well used, is a very good dashboard tool, and XLCubed extends the capability further still in terms of available chart types, flexibility and maintenance.

Some Excel BI myths debunked #2: Inflexible Charting

13th August 2013/ Analytics, BI, Charts, Dashboard, Excel Tricks, Reporting, Sparklines, UI Design, Uncategorized, Visualization

#2: Inflexible Charting

Continuing our discussion of common criticisms of Excel focused BI, let’s take a look at charting.

“Excel charts are static, inflexible and you need to start from scratch if you want to change them.“

The flipside is that everyone knows how to use them, and in reality many charts in business reporting are in effect static – the numbers being charted change, but the chart layout and number of elements being charted stays the same.

Of course there are cases when charts can vary considerably with the data, or perhaps you would like to be able to drill into more detail on the chart, or to quickly display multiple charts split by one variable. Excel charting can’t handle those scenarios, but XLCubed caters for it through Small Multiples. The example below depicts river water quality in different regions of England. It could be built in native Excel, but would be a painful and time consuming process. With XLCubed it’s a drag and drop process in our small multiple designer.

If the number of regions being reported changes, the number of charts being plotted will automatically stay in sync, and there is a direct data connection rather than having to maintain Excel ranges etc.

Sometimes with charting small is beautiful. Perhaps we just want the key numbers with a Sparkline alongside, or a bullet graph or bar chart to display actual to target. Native Excel 2010 and 2013 can handle the Sparkline, but not the ability to then drill the report and have the Sparklines extend, and there is also the issue of needing to bring the data itself into Excel before charting it.

XLCubed Grids can contain dynamic in-cell charts which build the charts as part of the query, and as such are drillable and remove the need to maintain a data range in Excel as shown below.

So XLCubed brings the type of dynamic charting being described to Excel, and provides a simple web and mobile deployment option.

Some Excel BI myths debunked

8th August 2013/ Analytics, BI, Excel Tricks, Uncategorized

“Excel: Great hammer, lousy screwdriver’”

When evaluating BI tools, many of our customers are hit by marketing messages about the limitations and woes of Excel. One white paper we were pointed to is Tableau’s ‘Excel: Great hammer, lousy screwdriver’. It contains 5 key points concerning Excel limitations for BI which we’ll take a look at over the next few weeks, along with a few others which we hear frequently.

“Don’t throw the baby out with the bathwater”

We fully appreciate that Excel isn’t perfect for all needs, but XLCubed chooses to address the weaknesses and embrace the very significant strengths, rather than throwing everything away.

XLCubed helps users get most value and benefit from Microsoft’s Analysis Services platform by taking the best of Excel, and extending that with an optimised query and reporting environment which lets them do more, and do it more quickly. Excel becomes a very flexible presentation layer, and Analysis Services removes the scalability and data integrity issues.

Let’s take a look at some of the key Excel objections raised, with #2 to follow next week:

#1) Limited data volumes

“Excel only handles 1 million rows – that’s not nearly enough for my business”

The advent of Big data makes everyone think of huge data volumes. In reality if you’re looking at core Financial reporting a million rows may well be more than enough but that’s not the point: for sales and operational reporting over several years a million rows won’t come close. Big Data is partly around volumes, but also concerns the data structure. These days the challenge of big data isn’t the ability to store it, it’s the ability to do something useful with it. And doing something useful with it isn’t creating reports which run to a million rows.

We see Excel as a presentation layer, not as a database. While Power Pivot muddies that argument a little, very few people see Power Pivot models as a central repository for Corporate data. XLCubed is a client front end tool for SQL Server Analysis Services (which laughs at 1 million rows). 1 million new rows per day over several years is starting to ramp up the volume, but the technology is designed to scale, and to scale on significantly less expensive hardware than in-memory technologies (of which Analysis Services 2012 of course now has its own player with xVelocity).

So while Excel and hence XLCubed can only display 1 million rows at a time, the underlying cubes can run to billions of rows. XLCubed gives the user flexible and fast filtering and ranking capabilities, simple ways to leverage the cube hierarchies, and effective data visualisation techniques to let you work with these large volumes of data.

Aside from that, if someone wants a report (a report!) which is a million rows long, our first question is always ‘and can you show me how you use that report?’. If you print it you’ll get around 25,000 pages of deforestation. By comparison Tolstoy’s War and Peace is around 1,400 pages in most print editions… We believe there is a lot to be said for a combination of top-down reporting, and ranking and filtering to make that type of data volume useful rather than burdensome.

So in summary, when you’re using XLCubed and the Microsoft BI stack, more than a million rows of data is really not a limitation (though if you put a million rows in the report you’re creating your own limitation in terms of its usefulness).