Archive for Business Intelligence

Excel is the Swiss Army Knife of Business Intelligence

I was recently asked by an Industry Excel expert what made Excel such a compelling product for Business Intelligence, Reporting and Analysis.  As many people know, I have worked in IT as well as commercial business roles over the years, so I have seen ‘both sides’ of the story and hence I am qualified to understand the things that can go wrong between these departments.

Big complex software projects (like an SAP implementation) necessarily require specialist IT staff to work with commercial business staff to develop people, process and technology solutions that will solve business problems.  These projects are highly complex, expensive, time consuming and risky by their very nature.  It is not unusual for such a project to last 3-5 years and cost 10s of millions of dollars – more in very large organisations.  Traditional BI projects are a bit like this.  They are complex, expensive, and often fail to deliver on the user expectations.

Why Big BI Projects are so hard

When business people work with specialist IT people, there is an enormous communication overhead between them. I have seen plenty of great people in IT and in commercial roles, and they almost always want the same thing – a great outcome for the business.  But the problem is that people are different; especially IT and commercial people are often very different.   There is a very good reason why some people are successful working in Sales and other people are successful working as a programmer.  The skill set between these people are fundamentally different.  So it is inevitable that when these people work together on a project, there will be misunderstandings, mistakes and rework.  All of this comes at a cost of time and money, and in the fast paced business world some things are just not viable if they have to go through this cycle.  Unfortunately in traditional BI projects, there is no choice but for IT people to work for commercial people, because the technical side of the projects are so complex that there is no other way.

Often it is not worth the cost

Business people will always have things that are a priority to them but are subordinate to the greater needs of the business/others/competing departments etc. So when a business person says  “I need ABC report to analyse this business opportunity by no later than XYZ”, sometimes/often the timeline simply can’t be met or the cost of doing so is prohibitive.

In a world of competing business priorities, successful business people find ways to get the important things done – they don’t sit around discussing why things could not be done due to lack of resources, competing priorities etc.

Enter Excel – the Swiss Army Knife of Business

I think about Excel as being the Swiss Army Knife of business.   Excel is one of those universal tools that allows any business person to get on with important priorities without having to call in IT to do the work.  In fact in my experience IT people know very little about Excel – the skill is all with the business users.

modern excel sml

Excel can be learnt and used by almost anyone for any purpose, and often the cost/benefit ratio is completely unsurpassed by any alternative (eg long slow expensive IT lead BI project) to the point where it is not worth taking your Excel built solution to the next level. In a world of rapidly changing business challenges, it is the speed to value that counts.  If you can’t turn around the analysis quickly and affordably, then the opportunity will most likely simply pass you by.

And then there is Modern Excel

Rob Collie uses the term “Modern Excel” to describe the new features that Excel has delivered over the last 5 or so years.  These new capabilities include Power Pivot, Power Query, but also the evolution of Pivot Tables, Slicers and into the future will include the still developing tools like Power Map, Power View etc. There is no other BI tool that can deliver a speed to value ratio that Modern Excel can deliver.

If you want to take advantage of Modern Excel, you will definitely need to learn some new skills such as how to write DAX, or you may have to bring in some people for a short period of time to break the back of the work to get you going, but these are small one off investments that have immediate payback in a rapidly changing business environment. Once you have these new skills dialled up, you will deliver speed to value like never before.

Fill Table with Last Survey Result

I have come across this problem a few times recently. There was no immediately obvious solution that worked at first. I knew what I wanted to do but I couldn’t get it to work – but now I have a solution and I want to share it for the benefit of others.

First, let me explain what I mean by “Last Survey Result”

The scenario I am talking about here is one where there is an event that occurs infrequently in time – say once every couple of months, and you want to refer to the value of the last event in your DAX formulae until there is a new event to replace it. So one example is an “Audit” or “Survey” which was a case with Satoshi (participant at my recent Brisbane training course) or it could also be the “last handicap the golfer had” which was the problem that Michael had (participant at my last Sydney Power Pivot course). In both of these scenarios, we want to be able to refer to the “last value” of the event (survey or golf handicap) and keep referring to that value from the last recorded event until that value is replaced with a new value at some point in the future. So time marches on, but I still want to see that last value in my pivot tables for the current date. This is also referred to as “events in progress” by The Italians.

Why is this such a hard problem?

Before I answer this, let’s talk about a scenario that IS NOT a problem. Let’s take a simple Adventure Works data model. In this case you have a number of lookup tables sitting up top, and each lookup table is connected to the data table below (in this case the data table is sales)

aw dm

Read More

I’m Speaking at the PASS BA Conference

PASS_BAC15_Speaking_250x250I’m really excited to be speaking at this year’s PASS Business Analyst Conference in San Jose California.  I will be sharing what I have learnt in my time working as BI Director at The Coca-Cola Company about how to share Power Pivot (and other Excel) Workbooks with people that simply want to consume the data.

Part of the problem of many workbook sharing tools is (in my view) the default settings are far too focused on giving a “Full Excel” experience instead of providing a “Web Browsing” experience.  People that want to consume the labours of our Power Pivot Workbooks don’t care about Power Pivot or Excel, they care about the numbers and the insights.

The techniques I have learnt and will share at the conference will show how any Excel user with access to SharePoint Enterprise (or a cloud hosted equivalent) can deliver a positive web browsing experience while still having the full Power Pivot interactive capabilities.  When you configure your shared workbooks for the consumer of your data rather than an Excel User, they can simply focus on the numbers and insights and get on with business.

Why you should attend this year’s conference

Read More

Power Query Issues with Power Pivot

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here

PhilC at PowerPivotForum.com.au asked me for advice on the best way to use Power Query with Power Pivot.  If you have come to this post via Google, it is probably because you have had issues and are looking for answers.  If you are reading this post for any other reason, then hopefully it will save you some pain by following my advice on how to minimise these Power Query issues with Power Pivot.

Quickly – what is Power Query

Power Query is the best thing to happen to Excel since Power Pivot!  Basically it is a lite extract, transform and load (ETL) tool for Excel users.  It allows us Excel users to grab data from almost anywhere, shape it into a form that can be used in Excel (increasingly Power Pivot), load it into your workbook and then away we go.

So what’s the problem?  Let me explain

Here is the scenario.  You grab some data using Power Query and then load it into Power Pivot.  You start building out your measures and adding value to your Power Pivot data model.  After investing many hours, you realise that you really need 1 more column of data from your Power Query table.  You go back to Power Query, add the new column of data, save the workbook and “BAM” you get the following error message.

We couldn’t refresh the table YourTableName from connection YourPQConnection. Here’s the error message we got: OLE DB or ODBC error: The query YourPowerQuery or one of its inputs was modified in Power Query after this connection was added.

error

When you first see this message, you can feel your stomach sink through the floor.  You have put hours into your data model and how you have to “start again”.

How to fix this problem after it happens

Read More

What is Power Pivot and Why You Should Care

I have been meaning to write this post for a long time – today is the day!  The answer to the question “What is Power Pivot?“.  You may also have landed here by searching for “Use this workbook’s Data Model”.  Often the first time people discover Power Pivot is when they see this message in Excel 2016.

this-workbook

Let me start with what Power Pivot is not

I have heard lots of people describe Power Pivot as “Pivot Tables on steroids”.  This is not a helpful description because it suggests that Pivot Tables are somehow changed in the process – this is not true – there is no change to how a Pivot Table works.  If you want to find out what Power Pivot really is, then read on.  But first let me clarify what a Pivot Table is.

What is a Pivot Table

A Pivot Table is a visualisation tool that can aggregate data from a data source (historically a single Excel data table) and display that data in a way that helps the reader make sense of the data.  Let’s look at an example

Source Data Table

Here are a few rows of a simple Excel Table (ie a data source).  Note there are more rows in this table that are not shown here to save space.  This is my data source.

table

The trouble with source data like this is it is hard to find insights.  Eg which country has the highest sales this year?  It is hard to read typically because there is so much data and it is at a level of detail that makes it hard to consume.  So we need a way to aggregate, summarise and display the data so we can make sense of it.

Pivot Table

Here is a Pivot Table that uses the above source data.  The Pivot Table is used to summarise and present the data to the user in a way that makes sense.

pivot

Further, you can use inbuilt Excel visualisation tools to make the data even easier to read, as can be seen below.

pivot visual

In fact Microsoft Excel is the world’s most commonly used tool for visualising data.  It has had years of development and is a very mature, flexible and stable product that EVERYONE knows how to use.   Excel is a GREAT TOOL for visualisation.

So what’s the problem?

Read More

How to Bulk Refresh PowerPivot Workbooks on your PC

There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are:

  1. Using Power BI
  2. Using SharePoint Auto Refresh
  3. Using some sort of custom built desktop automation tool

The problem with Power BI and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap. I cover the downsides of Power BI in this post about how to access PowerPivot on iPad. SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that brings me to option 3 – desktop automation.

How to bulk refresh powerpivot workbooks on your PC

I have been a closet VBA coder for many years. In fact I first taught myself “really bad coding” using Lotus 123 Macros. Over the years I have accumulated code that I reuse for new purposes. At some stage about 10 years ago, I created a VBA workbook that will list the contents of a folder into a workbook. As I was thinking about the need to refresh PowerPivot Workbooks on a PC, I realised that I could reuse this code as the core of the project. Now I don’t profess this to be the best code, or even good code for that matter. But I can tell you this has been working successfully for me for some time.

How the refresh process works

Read More

How to access PowerPivot on iPad

ipad_screen

PowerPivot on iPad – great experience!

One thing I find when working with my customers and meeting PowerPivot users is that a lot of people want to know how to access PowerPivot when they are mobile.  Most frequently people want to access PowerPivot reports on iPad, however in many cases just presenting data for PC users in an ‘easy to consume’ and ‘easy to control’ manner is also important.  Well there are 3 ways that you can set up to allow iPad (and PC users for that matter) to access PowerPivot reports from a web browser, and it doesn’t have to be difficult or expensive if you do it the right way.  The three options are:

  1. Microsoft Power BI in the Cloud
  2. An On Premise instance of SharePoint Enterprise Edition
  3. A Cloud based SharePoint Enterprise Service purchased as Software as a Service (SaaS).

I will cover each of these in turn leaving what I consider to be the best until last.

 

First of all, a definition of the term Power BI

Read More

PivotTables for iPad

Microsoft has released an upgrade to its Office Apps for iPad (version 1.1) that now includes – wait for it…. PivotTables for iPad Support!  Wooohoo.

whats_new

But unfortunately there is good news and bad news.

First the good news.

You can indeed open a workbook that has PivotTables and you can interact with them on screen.

pivot_working

Now the bad news.

Unfortunately what is missing on the screen shot above is the “Slicer” that is in the workbook in the blank space in columns F:H.  I can see it when I open it in Excel 2013, but it is missing in the iPad version.  And there is more bad news too – no support for PowerPivot.

no_PP

Well I guess Rome wasn’t built in a day, and this is definite progress.  However I will be waiting for PivotTables for iPad to include slicer and PowerPivot support before declaring this a game changer.

However all is not lost.  If you are looking to enable PivotTables and PowerPivot on iPad and other mobile devices, I can help you do that now – quickly and affordably with a cloud based hosted solution.  Most people don’t realise what a positive experience can be delivered if you do it right.  Just send me an email if you would like more information.

Rob Collie’s powerpivot(pro) University coming to Australia

Next week is going to be a really exciting week for me – I am off to Cleveland Ohio to participate in this powerpivot(pro) University training course hosted by Rob Collie.  The primary purpose of my trip is to become accredited in the delivery of Rob’s world-renowned approach to teaching PowerPivot for Excel.

I first stumbled upon PowerPivot when working as the BI Director for The Coca-Cola Company in Asia Pacific.  I was looking for ways to deliver fast and cheap dashboards for my user community.  I discovered PowerPivot for Excel and I couldn’t believe the fabulous capabilities and its tight integration with SharePoint. Since then I have learnt A LOT about how to get the best out of the tools from Rob’s online training, Rob’s book DAX Formulas for PowerPivot, and most importantly from experience with working at The Coca-Cola Company, with my clients and their real world problems.  But I have also learnt that it can be hard to get your head around some of the concepts, and that learning from instructor lead training can be a real accelerator of skill development and knowledge retention.

I am a true believer in the fundamentals of Rob’s approach to using PowerPivot.  As a Self Service BI evangelist it is natural for me to want to share what I have learnt from Rob with others about the best way to get value from these tools.  And what better way to do that than to become accredited in Rob’s material and then deliver this powerpivot(pro) University training here in Australia.  After all, unfortunately most people can’t travel to the USA to do the course live with Rob.

PowerPivot Training Australia

If you would like to see the course outline for what is offered next week in Ohio and get a preview of what is coming to Australia, you can take a look at the course outline.

powerpivotuni

If you would like to register your interest to attend this training in Sydney around Sep/Oct 2014 you can use the form below.  If you live in another Australian city and are interested in the course, please let me know that too.  If there is enough interest, I will extend the training to other cities.

[contact-form-7 404 "Not Found"]

 

 

Chris Webb’s Power Query Book Review

I have been waiting for this book Power Query for Power BI and Excel by Chris Webb for some time and I am pleased to advise it is a great book for a broad range of people with anything from zero to medium skills in Power Query. I fall into the later category and I found some value in every chapter, with increasing value per chapter as the book progressed.Power Query for Power BI and Excel

Chris has written this book in a thoughtful manner. He starts with the basics and then layers concepts one on one until he culminates with a chapter of real world examples of easy to understand use cases. These use cases leverage the skills learnt in the book and give you something meaningful to work on to absorb how the concepts are used in real life. I am confident that with these new skills I will be able to accelerate my self learning using the book as a reference along with the other resources provided by Chris as links in the book.

Chapter 1 is a good overview for people that are new to Power Query. Anyone that has used the tool a bit won’t find much here. There is however a good section on licensing that clearly describes what products come with each licensing purchase.

Chapter 2 takes you through each data source format one at a time, and gives examples of each. You could do this yourself with a bit of trial and error, but it is very convenient to have this as a reference. There is also an excellent reference about advance search functions to improve the quality of your searches over the web, and a great tip on referencing other Power Queries as a data source.

Chapter 3 is a comprehensive overview of the transform capabilities of Power Query. This would be very useful for newbies and a useful section to skim for those with a reasonable working knowledge to validate complete breadth of knowledge.

Chapter 4 focuses on what happens to the data when you return it to Excel and/or the data model including some tips on refreshing data.

Chapter 5: the M language! Chris writes this chapter to help you get the most out of these documents by explaining concepts not covered so well in the tech documents themselves, such as the Let statement.  You are not going to be an M Language wizard after reading this chapter, but you can expect to have a much better understanding of the structure of the language, and how to make sense of the output generated from the user interface.  With this base to build upon, you can learn more about M yourself much in the way that you can learn to program VBA from the Excel Macro Recorder.

Chapter 6 covers multiple tables. Building on what you have learnt about lists and the M language, the concepts of joining tables somehow doesn’t seem so hard now. I always struggled to get my head around the syntax before reading this chapter. The incremental build up to chapter 6 and the content of this chapter have sorted that out for me.

Chapter 7 extensively covers Power BI cloud integration, including sharing queries in the cloud, how to manage the quality of queries through certification, and some tips on how to manage version control. If you are using Power BI in your organisation, these will indeed be valuable information to get off on the right track.

Finally chapter 8 is the culmination of everything taught in the book. Chris gives step by step instructions on how to create specific transformations to data in real world use cases. He takes you through each step so that you learn what to do, then explains anything that needs further clarification. I would highly recommend actually doing each of the recipes on your own PC as a learning experience to cement your new knowledge.

All in all, a great purchase at under $20 for the eBook.

PowerPivot Forum Recruitment Drive

Back in early May I realised that there was not much in the way of a dedicated forum for PowerPivot users, especially for the general user.  There are some specialised forums out there for Microsoft Developers but not much for the average user.  As a result I went ahead and set up a new PowerPivot Forum and introduced it to the Web community.

PowerPivotForum.pngSo far so good – there are 30 members and growing slowly.  But any forum is only as good as its member community (size and participation level).  It doesn’t matter if you are a beginner or an experienced PowerPivot, Power Query, Power View or Power Map user – this is the place for you to come and ask questions or find answers.

If this is not you but you know someone that fits the bill, please share the link  with them.

http://powerpivotforum.com.au/

How much does a BI Project Cost?

string_smlThis is a very interesting question “How much does a BI Project cost?”, and one that many people want the answer to.  The standard response from IT is “how long is a piece of string?”, and that is a valid response in many senses.  ie how would you know how much it will cost without understanding the detail?

The trouble is that this response doesn’t help anyone.  I saw a post on LinkedIn about this very topic, and I thought it was a very good read – hence I am sharing the original blog post by SolidQ here, and I have been prompted to write this post.

The synopsis of the post above is that a BI project costs between $400k and $2m, and the biggest drivers of the cost are the experience of your BI implementation team, and the size of the user community.  It is very difficult to estimate the cost, and a project can over run by 100% – 200% quite easily.

From my perspective, there is another layer of important detail to answer this question.  A BI Project that uses PowerPivot and DAX will be “orders of magnitude” cheaper than a traditional BI project that uses traditional SQL Server Analysis Services.

The advantage of PowerPivot and DAX is that it is integrated on the desktop (via Excel), and can be used as a prototyping tool to build a BI solution in a much more agile way by actual users of the data.  It my view, it is not unreasonable to expect that a PowerPivot BI solution would cost 1/10th of the cost of a traditional BI project.   This may mean that the PowerPivot solution is less robust and less automated than a traditional BI solution (not always, but it can be true) and in some cases this may be an issue.  But in many other cases, a $40k – $200k project is much more affordable, compelling and ‘fit for purpose’ than traditional BI.  Some simple desktop BI solutions using PowerPivot are much cheaper even than this and can be built for as little as $10k with external assistance or can be built without any incremental cost by skilled internal staff (if they have the skill) but that does depend on the length of that string, of course!

What do others think about my ‘order of magnitude’ cost estimates for PowerPivot projects vs Traditional BI?

Deep Dive into Power Query M Language

Matt's curated best self serve BI content

In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the way, which came out in a light sarcastic way at times, but I think this reflects the state of maturity of this tool. Not too many people have deep knowledge since it is so new. I am waiting for Chris Webb to release his new book so I can get in some late night reading. Here is the agenda – mainly demos which is good.

M language agenda

and here is the relevant content

Time Stamp Description
0:00 Intros
1:55 Demo/Overview of the Power Query product, tab etc. Not too much new here.
2:50 A good lesson about the SQL Statement window. Don’t use it.
3:15 A great 2 min overview of what Power Query does, how the formula bar works, how to access the advanced editor, and good advice to use the UI first, then edit formulae.
5:00 Intro to M Language, why Microsoft selected it, why DAX is not up to the job.
7:30 Introduction to the concept of “Query Folding” – how Power Query pushes the work back to the database when it can. Good to know.
12:15 Some information about Library Functions. Here is a link with a PDF download that has a formula library reference that they talk to on this slide. http://go.microsoft.com/fwlink/?LinkID=320634
13:30 Demo time.

  • Explains the new features to change the default load behaviour to Excel and/or data model.
  • How to get inline help for the M language
  • Shows making changes to the advanced editor to bring in a 3 way join of tables from a DB.
17:45 Language Flow explanation – how the language passes on transformations from one step to another.
19:45 Type System. Explains the different data types, and how to define them, with a demo
29:30 Demo: Import and combine multiple CSV files into one table
32:40 Demo: Import a single file (Excel), edit the query to create a function, then import multiple versions of the same file and combine them. Matt shows you how to edit the regular query to a function.
41:30 Demo: Download complex data from Web. Good example of the breadth of the language using functions to download data.
48:15 Demo: Another web demo downloading multiple pages using a function.
55:40 An example to extract data from Binary objects. Far too deep for me!
61:00 Tips and Tricks. Worth watching. #shared in the formula bar will give you a list of functions and keywords.
64:10 Links Page
Language Reference: http://go.microsoft.com/fwlink/?LindID=235475
Library Referenece: http://go.microsoft.com/fwlink/?LindID=320634
Office Help: http://go.microsoft.com/fwlink/?LindID=398594

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

Solving Complex Business Problems with DAX – TechEd 2014

Matt's curated best self serve BI content

This is a continuation of my series on “best of” from TechEd 2014. This session by Kasper de Jonge makes my best of list. It is quite technical in places and digs in deep. I particularly like when he talks about “evaluation context”. Erik explains that there are 2 types of context (row context and filter context) and why it matters. The other thing really interesting in this session is that Erik describes how the development team uses (what he calls) ‘syntax sugar’ to create new functions that the user sees, but under the hood DAX is actually using a more complex calculation using other existing functions.

ErikDeJoneAgenda.png

 

Time Stamp Description
0:00 Introductions and background
2:28 Agenda
3:40 What is evaluation context? A great bit of information at the 20 min mark about how “nested” measures are calculated – innermost
6:00 Demo: Running Totals
21:00 Demo: Tool Table (disconnected table example). He uses Values() to detect the “type” of data, and then uses a switch statement to select which measure to display in the pivot table, and explains how to prevent errors in Grand Total columns.
35:00 Demo: Time Intelligence example where you can create a “current month” calculated column that automatically updates with each new month, meaning you don’t have to change your pivot table each month.
40:00 Demo: Several row context examples for both columns and measures. He explains how using ‘calculate’ in a calc column will give you filter context, and now row context
48:00 Demo: Time Intelligence year on year growth.
57:30 Demo: Time Intelligence with a 445 calendar.
63:40 Q&A

Here is the full session

Self Service BI from TechEd 2014 cont…

Those that didn't make my list

This post is a continuation of my last post where I started to provide a curated view of content from TechEd 2014 from the perspective of a Self Service BI professional.  Inevitably if I am to share with you the best of the sessions, there are going to be some that don’t make my list.  Rather than ignore those sessions on my blog, I have decided to create a list of the sessions that I have viewed that don’t make my “best of” list.  Rather than post each time I find one of these, I will instead come back to this post and edit it with the latest “didn’t make it” sessions.

Session Why it didn’t make my “best of” list.
All you need to know about Microsoft Power BI for Office 365 – Michael Tejedor When Michael starts this session, his first comment was that the session title had changed from his brief.  The session is really a “Power BI Overview”, and for this reason it doesn’t make my list.  It was VERY high level, more like a show and tell rather than a learning experience.  If you have never seen Power BI for Office 365 or Excel, then this is a good intro session.  If you already know what these tools are, there won’t be much for you in this session.
Interactive Data Visualization with Power View – Lukasz Pawlowski I watched this session and went in with very low expectations. My expectations were met. You see I have a low opinion of the current (and every previous) release of Power View. I am not saying Microsoft wont get there, but I think it is a long way off where it needs to be if it wants to be competitive with products like Tableau and native Excel – yes I think native Excel is better.Having said that, if you know nothing about Power View, this demo will show you what it can do. The biggest repeating disappointment I have with Power View presentations is that they are all a “dog and pony show”. They show tricks with their toys, but I simply can’t see the business value in the demo’s. Take my advice Microsoft – develop better, more meaningful demos of real business scenarios with your Power View demos.
BI Power Hour I actually think this would have been quite fun to attend if you were at the conference.  It was presented by an awesome bunch of MS BI gurus, but somehow watching a slide version after the conference didn’t really deliver the same experience.  It doesn’t make my list.

Here is a link to all the BI Track sessions at TechEd 2014.

A smorgasbord of Microsoft Self Serve BI at TechEd 2014

Matt's curated best self serve BI content

Last week in Houston Texas, Microsoft held TechEd 2014 (North America, 12-15 May). I was browsing the videos from this conference and wanted to share the wealth of material that is available for anyone to view. If you just filter on the “Data Platform and Business Intelligence stream“, there are over 40 sessions focussed on everything from Power Query, PowerPivot, SQL Server and more.

One of the things I really love about my new career as a freelance Self Service BI expert is that I can consume content like this, and actually get value that helps me be successful in my job. I plan to watch a lot of these videos over the coming weeks, and I will share back here a brief summary of those that I find most useful.

What’s New in Power Query for Excel

Let me start with this beauty of a session. It runs for 1 hour 15 mins and it gives an excellent overview of why Microsoft has decided to build Power Query, as well as a number of excellent demos of the power of this great new tool. Towards the end of the video (at the 45 min mark) they start to show some of the new features that will be released in the near future. If you even wanted to answer the question “What is Power Query and why should I care?”, then this is the video for you.

Here are the highlight points in case you don’t want to watch it all, with the video below.

Video Topics
Time Description
0:00 Intros
2:20 Why they have built Power Query
11:15 Demo – pull data from web search transform and produce a Power Map chart
17:50 Demo – pull data from web URL transform and produce a Power View chart
23:20 Demo – data mashup pulling data from different data sources combining and producing a Power View time series chart
32:10 Demo – multi dimensional data demo (Business Objects Universe)
40:30 Demo – pull data from web page URL where data is spread over multiple pages. Uses advanced Power Query technique using a function written in the "M" language.
45:25 The start of all new stuff not currently released.
47:30 Demo – demo of new tool to "transpose" data direct in Power Query
49:30 Demo – demo connecting to Microsoft Exchange to analyse emails over time (very interesting) – already available.
60:20 Demo – recent data sources feature that keeps a list of previous data sources
64:00 Q&A

Here is the link to all the videos: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014#fbid=

PowerPivot Forum Open for Business

As I spend more and more of my time working full time with PowerPivot and associated Excel products, I have been looking for suitable forums that are focused around PowerPivot. I have not found anything out there that I really like. So I spent some time over the weekend setting up a brand new PowerPivot Forum. In fact it is sooo new that it only has 4 members as of the time of this post.

It is my intention to grow this forum over time into a community of people helping each other to solve real world PowerPivot problems. While the forum is in its infancy, I will reply to every post to make sure that people that ask a question get a response. Hopefully others with the right skills will join and help out too, and it will hit critical mass in the not too distant future.

If you are looking for a PowerPivot Forum, then here is your opportunity to sign up. http://powerpivotforum.com.au/

PowerPivot Forum

Why Excel will SAVE you millions

Yesterday I read this post called “Why Excel is costing you millions” by Gayle Ryan.  I have never seen a more misleading and ill informed post about Excel, and I was motivated to provide a response to this article here.

Ryan starts out by talking about static reporting, and how that is no longer enough for business – this is definitely true.  She correctly points out that businesses need more data discovery, user focused, dynamic reports.  But from that point on, Ryan completely fails to recognise Excel for its strengths, and she is completely misleading in attributing problem after problem to Excel.  Let me share just a few of these incorrect statements along with my experience.

Ryan incorrect statement: “… spreadsheets don’t allow decision makers to see the big picture as effectively as a dashboard”.
What a load of rubbish.  Excel is a very powerful tool with fantastic dashboard capabilities built in, specifically PowerPivot, Pivot Tables,  Slicers and conditional formatting.  Excel has an enviable suite of visualisation capabilities and can easily be combined with tools like SharePoint Enterprise to provide an enterprise strength dashboard tool at a fraction of the cost of many other tools.  I personally built such a solution at Coca-Cola when I worked there.  Excel has a distinct advantage over many other dashboard tools – that is most business users know how to use Excel, and they can participate in the development of the dashboards themselves without having to rely solely on user requirements and specifications handed over to a developer.

Ryan incorrect statement: “Excel reports are static, and take time to produce”.
Absolute garbage!  Of course some reports are static, but to attribute Excel as being a tool that can only be used for static reporting is so wrong that I wonder what her motivation is for making such a statement.  Modern Excel has a powerful suite of capabilities including PowerPivot, Power Query and Power View that allow users to connect to almost any data source, produce a data model, and then analyse the data using discovery techniques.  When the user has created a report that has the potential for longevity, it can be polished into a user friendly reusable report with drill, slice and dice capabilities using Pivot Tables, Slicers and conditional formatting.  All this at a fraction of the cost to to the same with many other data visualisation tools.  And much of this capability is not new – Pivot Tables have been included in Excel since 2000.

Ryan incorrect statement: “Dashboards are also interactive, unlike Excel, which means that if a certain data point is troubling, dashboard users can drill down to get more information”.
Excel is a very powerful desktop or dashboard interactive tool using standard Pivot Tables, Slicers and Conditional Formatting.  Excel has taken further big steps forward with the release of PowerPivot and Power View (although in my opinion Microsoft has a lot of work to do to make Power View a real competitor to other more developed BI visualisation tools).

Ryan incorrect statement: “Excel spreadsheets … are actually hindering our ability to understand critical information by being difficult to read”.
You have got to be joking – are you real!? Excel is the most ubiquitous data analysis tool in use today, and probably over the last 20 years.  Excel is a flexible, cheap and agile tool that can present your data almost any way you want to see it.  If Excel is difficult to read, then it has more to do with the report developer than Excel itself.

Why Excel will save you millions

Excel will save you millions

In my view, one of the best ways to burn money is to believe that your problems are all about your tools, and that if you could just buy “a shiny new tool” that all your problems will go away.  Nothing is further from the truth.  My experience is that problems with existing BI solutions are related to “how” the tools are used and deployed as well as problems with master data standards and reporting processes.  Any business that reads Ryan’s article may be tempted to think that they should abandon Excel as a BI tool and instead go out and buy some new BI “discovery” tool.  It is definitely true that high end BI tools have a significant and growing role in business, but they are not the only tools.  My formula for how Excel will save you millions is as follows:

  1. Understand your business needs of data and reporting.  If you don’t know this, then you need to work it out BEFORE you spend a bucket of money on a new tool.
  2. Determine why your current tools/processes are not delivering on those needs.  Is it really the tools, or is it the way the tools are used, the process and the lack of data standards?
  3. Use Excel as a prototyping tool and determine “how” you can make your data work for you – learn by doing.  If you have a data warehouse, use PowerPivot to build on the fly data models that deliver real time, dynamic reporting that you can slice and dice to provide insights into your business operations.
  4. Iteratively improve your PowerPivot Excel tools until you get to a point that you can do no more.
  5. Then and only then should you ask yourself “do I need to replace what I have got with something better”.  In many cases the answer will be no.

The prototyping process will force you to think about your real problems (eg availability and cleanliness of data, knowing what you really need) and help you solve these problems as part of the prototyping process.  The further you go through the journey, the better your data and tools will become, and the more you will learn about what you actually need.  At the end of this journey you are highly likely to discover that you know what you need, and that you already have robust tools that can be published in the organisation using tools like SharePoint Enterprise (Excel Services, PowerPivot Galleries etc).

Compared with the risk of failure by believing that somehow some new shiny BI tool will solve your data woes, my approach above using Excel with PowerPivot is highly likely to save you many millions of dollars.