Ask SWC: What’s XLCubed?

June 23, 2014   //   Business Intelligence, , , , , , ,

I’ve been working in business intelligence and data warehousing environments for more than 20 years and over the last couple of weeks I have been evaluating a tool called XLCubed. Watch my “Ask SWC” video to learn more about XLCubed and its ability to create powerful reports through Microsoft Excel.

SQL Server 2014 New BI Features

XLCubed is an add-in tool for Microsoft Excel that allows you to point to analysis and services cubes or any other relational databases so that you can create ad hoc analysis, reporting and even dashboards that can be published to the web with a single click. Once you put items on the web, the environments are the same, which is really cool because the user interactivity and use of the report on the web and in Microsoft Excel is the same.

Another reason I like XLCubed is because the tool extends the normal analysis services add-in that users get with Microsoft Excel, allowing you to create your own custom reporting. This adds a lot of features and functions and even visualizations to your business intelligence environment. XLCubed also removes some of the limitations and restrictions that are often encountered in Microsoft Excel, so that you can create what you want as you need it.

If you watch my “Ask SWC” video, you will see a demo of XLCubed. The first feature I show in the demo is cube aware calculations. Typically when I am creating a report in Microsoft Excel using an analysis services cube and I put a calculation at the end, once I drill into my data, chances are it would remove and overwrite that calculation. In the video, you will see that I created a normal Microsoft Excel calculation with an IF Statement and a little subtraction and then when I drill down into it, it copies that profit calculation all the way down and even across. As I drill into years, you can see that profit is brought over into each one of these pieces. This is the cube aware piece and if you drill into it, it will show you how the calculation is created in the MDX.

The second thing I want to show you, which I think is really cool, is if I have a bunch of things like the plants I am selling in the demo, I can create a graph that includes a four year trend of each type of plant. As you can see at the bottom of my demo screen, the graph quickly becomes very crowded. What XLCubed allows is a visualization where you can create an in-cell chart or spark line that shows you at a glance how each individual plant is doing over the last four years. I can now show you which ones are doing well and which ones aren’t doing so well.

Next I want to chat about small multiples, which is another cool thing that I really like. XLCubed allows you to plot multiple measures over different axises and dimensions. On the left hand side of my demo screen, I have an all dimension. If I drill into this dimension, it breaks my data down into all of the details of how each student group sold each plant.

Finally, when I publish this report to the web, there are a number of different visualizations that XLCubed offers, such as the slicers where you can pick the states or quarters or even the categories by clicking on these little radio buttons here and as you do, it updates all of the graphs and charts that go with it. In my demo, if I change from New York to Florida, you can see that the chart over on the right hand side has changed and now we have a graph of Florida. These charts are built on Google maps, so you can zoom in and zoom out. You can even change the view to a satellite view. The report itself, then draws these little bubbles there, so you can see the relative importance of the sales in each of these bubbles. The bigger the bubble, the more sales are there. If you’re wondering why I am talking about plants and bands, I will tell you that this data comes from a plant sale that my wife and I orchestrated for our high school band.

Download our guide, The Essential Guide to Data-Driven Decision Making, to explore real-world examples of how modern organizations are overcoming the barriers to becoming a data-driven business and how you can be leveraging data analytics to grow.

Data Analytics Guide