Category Archives: BI

DAX Performance tips– lessons from the field

XLCubed has supported a drag/drop interface for creating reports against Tabular Analysis Services since the first release of the new engine. It lets users easily create reports which run DAX queries on the cube, and we’ve often seen very good performance at customers when MDX against Tabular was a cause of long running reports.

So when we were approached at SQL Pass in Seattle by some attendees who had a SSAS Tabular performance issue we were optimistic we’d be able to help.

In this case the business wanted to retrieve thousands of rows from the cube at the transactional level, and the first approach had been to use PivotTables in Excel. To get to the lowest level they cross-joined the lowest levels of all the hierarchies on the rows section which would give the right result, but performance was terrible, with several queries taking 20 minutes or more and others not returning at all.

We hoped using an XLCubed table running DAX would be the solution and created the same report in the designer. Sadly while performance was a little better it was still far from acceptable; the model was large, and the number or columns combined with their cardinality meant that a lot of work was being done on the server.

XLCubed’s DAX generator was trying to cross-join all the values from each column, which had worked well for our other customers. But when there are a dozen columns including the transaction ID things do not go so well. DAX in itself is not a magic bullet and SSAS Tabular models can hit performance problems on low level data – we needed a new approach.

After some investigation we discussed the issue and our thinking with our friends at SQLBI and determined that instead of cross-join we wanted an option to use Summarize() instead as this only uses the rows in the database, and it can access columns related to the summarized table which were required for the report.

As the customer’s report had the transaction ID in it the result wasn’t aggregated, even though we were using summarize. But we wanted to add true transactional reporting too, using the Related() function.

Finally, SQL 2016 adds a couple of new functions, SummarizeColumns() and SelectColumns(), both of which are useful for this type of reporting, but offer better performance than the older equivalents.

The end result in XLCubed is a new option for DAX tables to allow users to set the type of report they want to run, and some internal changes so that XLCubed will automatically use the most efficient DAX function where they are available.

A beta was sent to the business users and the results were fantastic. The report which had run for several minutes now completed in a few seconds, and 20 minutes was down to 15 seconds – we had some very happy users!

The changes will be in the next release of XLCubed so that all our customers can benefit from the improvements. It’s always nice when a customer request helps improve the product for everyone.

A sample of the syntax change is included below

Before:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            CROSSJOIN ( VALUES ( 'Customer'[Education] ), VALUES ( 'Product'[Color] ) )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

After:

 

EVALUATE
FILTER (
    ADDCOLUMNS (
        KEEPFILTERS (
            SUMMARIZE ( 'Internet Sales', 'Customer'[Education], 'Product'[Color] )
        ),
        "Internet Total Units", 'Internet Sales'[Internet Total Units],
        "Internet Total Sales", 'Internet Sales'[Internet Total Sales]
    ),
    NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] )
)
ORDER BY
    'Customer'[Education],
    'Product'[Color]

Cell-Linked Decomposition view

In displaying the key and often high level information required, many reports raise lots of additional questions in terms of why a specific number is ‘too high’ or ‘too low’, or how the number is comprised. XLCubed provides lots of ways for power users to further explore and visualise this, but in this blog we’ll take a look at one of the simplest and easiest to implement within a report – a cell-linked decomposition view, or a dynamic breakout.

The scenario below is a fairly straightforward Regional Sales report which will be deployed to management through XLCubed Web. When users click on a sales number for a specific region and month we want to provide them with a ranking to show the top 10 products which were contributing to that revenue. We provide a number of ways to achieve that but this is by far the simplest:

Select any of the values returned in the Grid and then either use the ‘Breakout’ option on the Grid Ribbon, or right click and choose XLCubed – Breakout Value. You now specify where to position the resulting breakout, which hierarchy to decompose the number by, at what level, and how many ranked items to show.

BODialog

 

In this example we’ve chosen the top 10 Products at the SubCategory level. The result is shown below. The green cell in the original grid is the cell the user has selected and is ‘tracked’, so when another cell in the grid is selected the breakout updates accordingly, meaning users can easily explore the makeup of their data.

 

BO Result1

 

The breakout result can be further decomposed by running another breakout, this time on the product subcategory. In the published example below we’ve run an additional breakout showing the top ranked products in the selected subcategory.

WebBO

 

The report user can now simply click on the values which are of interest to see the associated product sales breakdown, and it’s something which can be built in minutes by the report designer.

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

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:

ipxl

…to web…

ipwb

…to mobile.

ipip

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.

ExpandTo

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: #3 – limited dashboards

#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.

FinanceSShot

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.

RampMap

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

#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.

waterqualitysmalt

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.

DrillIncell

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

“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).

Current and Previous month reporting made easy

We’ve all been there. Our shiny new dashboard or report pack is finished and ready to go meet its users.  We’ve presented the key information clearly, we’ve followed all the data vis guidelines on effective charting and use of colour. We like it a lot, and we’ve thought ahead and built in lots of flexibility with slicers and managed drill paths so it can already help answer some of the questions it will doubtless raise.

It’s a little disappointing then that one of the first pieces of feedback is that the senior execs don’t actually want to use the interactivity much. They want to open the dashboard and see the current month picture (or previous month), and don’t want to waste valuable seconds selecting the month in a slicer…

Joking aside, in lots of situations it’s a sensible request, and there are various different ways to handle it in an XLCubed / Analysis Services world. Often this will be for a multi-sheet report incorporating grids, formulae, and charting elements and we need a centralised point to handle the month selection – enter the XL3MemberNavigate() formula.

MemberNav

This lets you pick a hierarchy and a level, and you can specify that you want the Last member (first and previous / next are also available). It’s available in the XLCubed Insert Formula dialog. In our case we’d pick the date hierarchy, the month level and choose Last, generating a formula as below:

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”)

The issue is that at this point it has no concept of data, it will give you the last month available in the hierarchy, not the last month with data. However that’s just another parameter away, we can add dimension member pairs to force a data check, as below, where we are checking that data exists for the “Reseller Sales Amount” measure.

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,0,”[Measures]”,”Reseller Sales Amount”)

So that will return the last month with data, and as we know in XLCubed all grids, formulae and XLCubed charts can be based off a cell. The Xl3MemberNavigate() cell becomes the driver for all time selections in the report. Job done. Or is it? What if you actually wanted the last complete month? :

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,2,“[Measures]”,”Reseller Sales Amount”)

Adding the addional ‘2’ parameter means it will go back an additional month, hence giving you the last completed month.

In our experience this is far and away the easiest way to handle current or Previous month reporting, and we hope you find it useful if it’s new to you. For more information on XL3MemberNavigate check our wiki.

 

XLCubed as an alternative to ProClarity

With the launch of 7.1 of XLCubed Excel Edition we introduced the ability to import ProClarity Briefing Books – with support for ProClarity ending this year and many customers looking for a replacement, now is a great time for us to show you how the import works to help move users from ProClarity to an alternate solution.

Importing

Let’s start with importing from ProClarity, we’ve built a simple example briefing book based on the usual AdventureWorks sample cube, it includes a sample grid:

 

a performance map:

 

and a chart:

 

To get to the import option we load Excel and select XLCubed -> Extras -> Import -> Import ProClarity Briefing book. After selecting the file to import we are given a summary of each item that is going to be imported:

 

At this point you can control the resulting worksheet name, as well as switching the type of XLCubed object you’ll end up with. Clicking “Import” will now give us 1 sheet for each briefing page:

 

You’ll notice that the import process has created any required slicers so the report is good to go. You could now also spend a bit more time adding any extra XLCubed functionality to the report such as Incell charts or Excel calculations to leverage the power of Excel or publish to XLCubedWeb for consumption by a wider audience.

The import process is very straight forward and we have some great feedback from our customers regarding the speed and ease that they have been able to migrate users’ reports into XLCubed.

Look out for some more blogs showing other features of XLCubed that will help users transition from ProClarity!

Excluding members in XLCubed

So today we are going to show you how you can easily exclude members from your XLCubed reports.  Here we have a simple grid which shows lowest level descendants of Promotions on rows and Geography on columns.

 

 

 

 

 

 

 

We would like to rank this report and also exclude the Promotion No Discount which is not really adding any value to the report.

So let’s edit the Promotions hierarchy and set up the exclusion of the No Discount Promotion.

 Click the Advanced tab and then the Add Member List icon:

You will see a window as below:

Now click the drop-down on the right-hand side member list and select Edit.  This will allow us to edit the member set:

 

We are going to exclude No Discount so select it and drag it across.

Next we need to choose one of the following operations to perform on our two member lists:

Add – left and right sides combined

Common – must exist on left and right side

Subtract – left side minus right side

 

We will select the subtract operator and click OK.  We will also click this icon to rank the result:

 

 

Let’s rank these Promotions based on the current measure, Reseller Sales Amount:

 

The Promotions hierarchy has now been edited to exclude No Discount and then ranked.

 

 

Our report now looks like this:

 

 

 

 

 

 

 

 

As you can see the report now excludes No Discount row and has been ranked to show the top 10 Promotions across All Geographies.