Microsoft Gold Partner Renewal, & resulting questions answered

We’ve just completed the process of renewing our Gold partner status with Microsoft. Among other things, this ensures we have early access to upcoming software through the CTP and beta programs, as has been the case with office 2010 over the last year.

The Gold level now has a requirement for completion of an independently run Customer Satisfaction survey, and thanks very much to everyone who completed this. The scoring and comments are much appreciated, and it was good to see the consistently high scores. We’re of course reviewing the areas where we didn’t score perfectly as we strive to further improve the service we offer.

The survey also highlighted a number of questions which appeared a few times:

1) Relational Database support

Our prime focus is on cube based reporting and analytics, but we do also support querying of relational databases in both Excel and on the Web, and have a number of customers using the product purely for relational reporting.

We extend the native Microsoft functionality in excel, and add support for this in the Web product. The connection string and query string can both be formula driven so you can construct parameter driven reports with ease.

– Search for ‘relational database access’ in the help file for an overview.

2) Writeback & Planning applications

XLCubed supports both grid and formula based writeback against Analysis Services and PowerOLAP platforms, in Excel and on the Web. As such it lends itself well to planning and budgeting applications, and it’s an arena in which we have a lot of experience, from the straightforward through to the highly complex.

3) Documentation and User Guides

Documentation is being revised for V6, which will release in the summer. If you need the current user guide for v5 please contact us direct at support@xlcubed.com and we’ll be able to assist.

4) YouTube videos

We had two streams of comment here, broadly summarised as:

a) They’re really useful – thanks!

b) Youtube access is blocked by our corporate internet policy!

If your access is blocked, you can download the videos as mp4 from

http://www.xlcubed.com/XLCubedv5VideoMP4.zip

Posted in BI, General, Uncategorized | Leave a comment

PowerPivot, SQL R2, Sharepoint 2010, Office 2010.

So we’ve been using PowerPivot for a while now, and Office 2010 has been part of our lives for some time. I’ll use this blog to answer some of the questions that keep cropping up in conversation with our customers:

1. Does XLCubed work with Excel (Office 14) 2010?

a. Yes, we’ve been using it since the first CTP release and each release since then.

2. Can I use XLCubed Web with SharePoint 2010?

a. Yes, publishing to the web and embedding the reports within your SharePoint site works in exactly the same way as with previous versions.

3. Does XLCubed connect to PowerPivot?

a. Yes, XLCubed connects to the PowerPivot published cubes, and our client tools can be used to build reports and dashboards from them.

4. Can I build reports from SQL Server R2 using XLCubed?

a. Yes this will work just fine, just as you can build reports from previous version of SQL or other relational sources. (here is an example)

PowerPivot in the real world

The services team have been working on migrating some of our internal models and sample databases across to a PowerPivot environment – looking at the pros and cons, using DAX rather than MDX to perform some calculations. Results have been varied, its been interesting to see some features that we’ve had for a while (like cube formulas, slicers and web parameters) appear in a similar way in PowerPivot.

Quite clearly PowerPivot isn’t the be all and end all or anything like a replacement for Analysis Services, but it certainly has a role for tactical solutions, some power user analysis, and we think likely also for RAD prototypes of larger scale AS implementations. It doesn’t venture into the gap left by PerformancePoint Planning (as many thought it would in early 2009) – we’ve moved to address this area with the XLCubed PM suite that uses in memory OLAP cubes and/or Analysis Services.

Trying out some of the tools

Here’s a few download sets for you to try, take careful note of the hardware spec and requirements for the MS ones though:

The 2010 Information Worker Virtual machine

Register and Download Office 2010

PowerPivot 32Bit, 64Bit

XLCubed and MicroCharts Evaluation

If you would like to evaluate against your own data – contact the XLCubed Product team for evaluation editions or if you want to try a no risk proof of concept or prototype contact the XLCubed consulting team.

Posted in Analytics, BI, Reporting, Uncategorized | Tagged , , , | Leave a comment

Data Visualization – a real world example

In the following example we work through a real world example of a data visualization. We’ve chosen an example that involves Operations data – this is fairly non-domain specific so hopefully it can demonstrate some important points. The first, and most important point is that you have to define your audience.

We receive many questions about “what is the best chart for this situation” or “what colour should I use for emphasis”. These questions are usually attacking the problem from the wrong angle. The one question you need to ask before anything else is “who is this visualization going to be seen by and how?” Is it in a boardroom on a printed sheet or across a trading floor on a plasma screen. Are the consumers domain experts?

This example features data about an investment bank’s operations processing, the audience being the clients of the Operations department.

Starting Point

Initially the project started out as simply trying to record what operational problems were encountered on a daily basis across different product lines. A reporting system was built and various generic reports produced:

original charts

Unfortunately the reports either didn’t contain data at a granular enough level or it was difficult for the product managers to see where the issues were occurring and what the trends were. In reality the report showed what the major problems had been – unfortunately this was already known, as when something major goes wrong you remember getting shouted out!

What was requested

The client wanted a report that showed where the problems were occurring across business lines (rather than operational units) and how they were doing historically in a single page that could be included in a weekly MIS pack (they currently had four pages per product line (8) so a total of 32 page. As a first pass they simply wanted an Excel worksheet they could update manually:

proposed solution

We felt this solution lacked clarity and it was very difficult to spot trends across products.

What we proposed

We designed a solution using MicroCharts to allow small multiples of charts to show a variety of views:

final version

This solution allowed the user to view the data simply as a cumulative set of data by Product (top line) or by Root Cause (vertically) and then look deeper into historical trends in the centre of the chart. For example, its fairly easy to see spikes in the Root Cause data historically and see that the overall trend has improved over time. By ranking the Products and Root Causes you immediately give some sense of scale to the data. For example you can see that there are many more Application failures than any other type of problem, but the majority of root causes are otherwise fairly evenly distributed.

One other point worth noting was that the original colour scheme was much more muted, but the client got very upset that it looked like a competitors corporate colour and wanted it to be “louder”.

What was the user reaction…

Ecstatic, 1 page replaced 34 and they could see at a glance how the entire (large) organisation was working but also quickly find out detail for a particular area and identify trends.

Posted in Analytics, BI, Case Studies, Charts, Dashboard, Reporting, Sparklines, Visualization | 3 Comments

Cube Design – meeting the business needs

 

Following on from our previous blog post on a couple of the common cube performance issues we’ve seen this last month, I thought I’d mention some of the non-technical issues we see quite often. In one case, once we’d made a few teaks and sorted out the cube performance issues we had to ask – Is the cube doing what it needs to? (Of course we did ask this first but the priority was sorting out the current cube performance!) Does it meet the business requirement? There’s no point in having the most complex cube that uses all the greatest features if it can’t answer the users queries.

In reports, we’ve seen examples where clients have nested four or five attributes to build up the effects of a hierarchy or run huge queries then vlookups on them to get the data they need, or bring back 12 columns of data and manually work out year to date, or not have any hierarchies that reflected commonly used groupings of members, or not have member names formatted in the way the business needs. To us this just isn’t right.

The users might not seem to care too much if they don’t know how the cube could work or if it runs fast enough to bring back huge result sets they can manipulate themselves – but doesn’t that negate the point of having a cube and your investment in it? Consumers of the cube should have fast, timely, accurate and importantly appropriate data made available to them in a manner that makes sense.

Cube design and build is about understanding the business and users needs and then building the cube and associated processes, that’s before even starting to build the reports and conveying the information using good data visualisation practices.

All too often we’re seeing a drive to use the latest tech, the flashiest widgets, cool looking 3D and shading effects on reports through to cubes and databases with every conceivable hierarchy or type of measure thought possible but not bearing much resemblance to what the users need to see.

I won’t hide the fact that we’re very proud of our skills and experience in ensuring our clients get not just a technically excellent system but also one that fits their needs. If you want to talk to one of the team about how they can help, you can find our contact details here.

Posted in Analytics, BI, Case Studies, Reporting | 1 Comment

Common Analysis Services Performance Issues

A quick blog post from the Services team here at XLCubed on some performance problems with SSAS that we’ve seen again recently. With the processing power and memory available it’s pretty easy to build a fast cube – both for query performance and processing time. It is also easy to be lax in cube design, ignore the warnings and best practice guidelines, and end up with a cube that’s looks concise, is neat and clever but performs terribly for end users.

We’ve come across a couple of examples of this at client sites in the last month, and there are some common issues that always seem to jump out – rectifying these normally has a very positive impact. The three most common culprits we see are:

Parent-Child dimensions – Parent-Child dimensions are nice and easy to build and use. However, as you can’t build aggregations that include a parent-child dimension it can make for a badly performing cube! Try to flatten dimensions out and evaluate exactly why a parent-child dimension is required and being used. They are not the only option..

Unary operators, Custom-roll ups – we’ve seen cases where these have been included in every dimension in a cube by default. If there isn’t a need for them – leave them out! If you can get around using a custom rollup or unary operator by some simple work in the ETL process it may be better to do that first.

If your query performance is bad – try removing all unary operators and custom rollups then re-test the cube. How’s the performance now? It should be significantly faster – evaluate and review the need for the unary operators and custom rollups and see if the same effect can be achieved differently (e.g. in the ETL layer)

Cache vs. Non-Cache Data – Basically is the cube recalculating and re-querying numbers over and over again or can it re-use results? Use profiler to check for cache or non-cache data when your queries are running. So many times we’ve seen all queries not using the cache because AS hasn’t been given enough available memory or volatile operators such as now() have been used in mdx calcs.

Resolving the issues above had a massive impact – reports taking up to 3 minutes to run were down to a few seconds, users could begin to use the application properly for the first time, however fixing the performance may be only part of the task. The cube of course needs to have been designed to meet the business requirements, but that’s another blog..

Posted in BI, Case Studies, Uncategorized | Leave a comment

2009 Excel Dashboard Competition Winners

Thanks to everyone who entered this years competition, again the standard was very high, and it’s always great to see the product being used so effectively. The entrants were extremely varied in both their style and subject matter, and made for a difficult decision. However I’m pleased to be able to announce the winners:

1) Ajay V Singh – Operations Dashboard for a Debt Collections Company.

The target audience are the CXO level execs of the business, aiming to provide a view of all the nerve points of the organization in a single unified interface that is portable and yet comprehensive.

The dashboard layout is dense but uncluttered and well thought through. Colours are well balanced, and allow the reds to draw the reader’s attention as intended.

Ajay’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

Collections Dashboard Screenshot

 

 

 

 

 

 

 

 

 

 

 

 

 

2) John Munoz – Insights into Unemployment in the United States.

Using data from the bureau of Labor statistics, the dashboard gives a deep glimpse into the unemployment situation in the US. A large volume of disparate and tabular information is brought together in a single concise view, which aids understanding and adds real insight. The trends and demographic splits come through very well, and make for easy comparison.

John’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

unemployment_dashboard_munoz

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3) Lisa Cunningham – Anti-Social Behaviour Dashboard

The dashboard is produced by the Research and Information Team at Leicestershire County Council as part of a suite of dashboards produced for the Crime and Disorder Reduction Partnerships. It is available to the public through the local web portal, which makes readability, and also the contact information provided vital. The dashboard aims to provide an at a glance view of the level and trend of ASB, and does an excellent job.

Lisa’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

ASBDashboard

Posted in Uncategorized | 4 Comments

Excel Dashboard Competition – deadline extended

We have decided to extend the entry deadline through the holiday period, to 28th August.

As a reminder, the competition is for real world solutions (no sample data set), and judging criteria include:

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

See the competition page for more detail.

-Thanks to all of you who have already entered, the quality has again been good, and will doubtless lead to an interesting debate when it comes to choosing the winners. As we’ve extended the deadline if there are any additional tweaks you’d like to incorporate you can of course send revised versions.

Posted in Uncategorized | Leave a comment