Introduction

Introduction & Overview

What is XLCubed?

XLCubed’s products help business users deliver reporting and dashboards and to carry out their own analysis and data exploration without the need for any programming experience. It can also be used in planning and budgeting models. The two core products are based in Excel and Web.

Data Sources

A key aspect of XLCubed is that the data itself is not held in Excel. XLCubed’s primary data source is Microsoft SQL Server Analysis Services. This can be multidimensional cubes, or the Tabular models introduced in SQL 2012. XLCubed can also connect directly to relational databases, with a subset of the overall XLCubed functionality.

How is it used?

In most organisations XLCubed is used to provide a managed self-service business intelligence model, where IT develop and own the cubes or tabular models, and business users are able to develop and maintain their own reports, dashboards and analysis. This gives the business the flexibility and control they need in terms of reporting, but retains IT control of corporate data.

XLCubed consists of two core products: Excel Edition – an add-in for Microsoft Excel (Office 365 – 2003) which provides a powerful and flexible reporting analysis and dashboard environment within Excel. The product extends the presentation and query capabilities of Excel, and offers additional data visualization components. Users can do much more, and more quickly than in a standard pivot table environment, but are still able to leverage their existing Excel skills.

Web Edition – Web server based software, providing intranet or internet access to interactive reports through all standard browsers and with mobile app access. Reports developed in XLCubed Excel Edition can be published to XLCubed Web for a more widespread distribution. XLCubed Web also provide the ability for ad-hoc reporting.

 

XLCubed and Cube Terminology

A cube is a set of data organized into a structure defined by a number of measures and dimensions.

It contains the following:

• Measures
• Dimensions
• Levels
• Members

Measures

Cubes can be structured in different ways, but in simple terms measures are normally the elements you want to add up in reporting. Typical measures might include ‘Sales Volume’, ‘Unit Sales’ and ‘Unit Cost’.

Dimensions, Hierarchies and Levels

Dimensions are the bywords of reporting. For example you may want to report sales by Region, by Period, by Product. Region, Period and Product would each be a dimension within the cube, and XLCubed makes it easy for users to position dimensions within a report and choose the elements they want to see.

Dimensions may consist of one or more hierarchy. Hierarchies are ordered groupings, or drill paths of the data. For example a Time Dimension may consist of both a Fiscal Date and a Calendar Date hierarchy. These are different ways to group the days into months, quarters and years.

A Geography hierarchy may have 3 levels: Country, State, and City. Users can easily select items from any level, or start at a higher level and drill down to detail.

Members

A member is any element, at any level within a hierarchy.

Real world cubes typically have more than 3 dimensions, but to visualise an example cube we will use just three. The diagram above shows a simple sales cube, for a bicycle sales business. The cube contains two measures, Unit Sales and Profit, and three dimensions:

• Product: The product being sold. All products sold are bicycles, and they are categorised as either Road or Mountain bikes, with two model types in each category.

• Region: The region the product is sold in, categorised by Hemisphere

• Time: The (simplified) time period the product was sold in. In this case it goes only to the quarter level, but would typically begin at year level, and go down to month or day.

Each axis of the cube represents one dimension. The texts along the axes of the cube are the members of the dimensions. Example members are m50, Asia, and 2nd quarter. The individual members aggregate into the hierarchy structures of the dimension.

The values within the cube represent the measures, Unit Sales and Profit in this case. Each cell within the cube will contain a value for each of the measures, and is formed by the intersection of the dimension members. For example, the cell shown in red represents the intersection of (Product: m50, Region: Asia, Time: 2nd Quarter), and the measure values are Unit Sales: 600, Profit: $217.

End users of a cube can determine each measure’s values for every intersection between the dimensions by slicing and dicing with a tool such as XLCubed.

Ways to retrieve data in XLCubed

XLCubed offers three ways to bring data into the workbook, which we describe very briefly here, and in more detail in the relevant sections.

Grids

Grids are the most commonly used approach in XLCubed. At the lowest level grids are similar to a pivot table. Each Grid is an individual object, with rows and columns, and they can be drilled down to additional levels of detail and support slice and dice.

Grids remove many of the restrictions of pivot tables, with simple user calculations, rich ranking and filtering, and can fully leverage the cube hierarchies. Grids can be modified or designed using the report designer, through the grid task pane, or simply by direct drag and drop of the hierarchy labels.

Grids can connect to multidimensional cubes or tabular models and use MDX as the query language.

Formulae

XLCubed provides formulae to retrieve values and hierarchy members from the cube. The formula approach gives total control over layout and formatting, and the formulae are easy to understand and have user dialogs.

Formulae are most often used in dashboards and formal financial reports where the layout is key. The simplest approach to create a formula report is to create a grid and then convert to formula.

Formulae can connect to multidimensional cubes or Tabular models and use MDX as the query language.

Grids or Formulae?

User preferences vary, and in many cases either reporting approach can be used, but some considerations are:

• Grids are stronger for slice and dice analytics, supporting drag and drop analysis
• Grids handle dynamic data in terms of changing numbers of rows and columns
• Like for like a grid will be faster than a formula report returning the same data
• Formulae offer total flexibility in terms of layout and formatting possibilities

Tables

Tables can connect to multidimensional cubes using MDX, to tabular models using DAX, or relational databases using SQL.

Tables have similarities to grids, but have no concept of rows – everything is a column.

Tables or Grids?

• If your data is in a multidimensional cube, a grid will generally be a better fit as it is more flexible and dynamic.
• For in-memory tabular models (xVelocity) you can use grids or tables. Grids will give more flexibility, in some cases Tables may have a performance benefit due to running a DAX query.
• For SQL you need to use a table