Richard Edgerton | 2 September 2013

Excel has become a de facto place for organisations to analyse their data. How can you keep up with BI requirements when data volumes are growing exponentially?

Microsoft recognised that the file formats supported in Excel up to Office 2003 were stuck in a backwards capability mode. The sheer volume of data generated by andavailable to organisations continues to grow and storage locations are becoming increasingly diverse. The challenge is to maintain the required levels of analysis and as well as improve what can be offered to the organisation in terms of its relevance and also to be able to deliver across different platforms to meet the needs of the end consumer.

With the introduction of the XML file formats in Office 2007 and beyond, great steps were taken to enhance the physical capacity of Excel in conjunction with the ability to connect to a wide variety of data sources. Pretty soon though the application itself was hitting limits and in many cases cannot store the quantity of data generated by a business. Along came PowerPivot in Excel 2010 and the extension of Excel Services as part of the application out of the box. This was good news and provided a platform to build upon.

The next challenge was to allow us as end users and consumers of our data to connect, analyse and present the material in different ways and not necessarily sitting on a high spec desktop PC.

Where might your data be stored? Traditional database applications have either morphed, migrated or been replaced by the connected world we live and work in. SharePoint looms large in the data sphere but it has many rivals as well as colleagues. CRM applications, SAP, the Web, Azure or maybe Facebook can all act as data aggregators and storage points which we need to get into to work with the information that keeps us that vital BI step ahead.

It is not just a question of where the data is stored but also how we can access it. Are you a mobile worker and what determines the level of mobility? Laptops, notebooks, smart phones, iPads and tablets all figure in the mix.  Getting to the data via a browser from anywhere using a service like Office 365 is becoming more prevalent.

As BI needs grow Microsoft have introduced further evolutions into the mix. Excel 2013 now has the beta of a tool called Power Query . This allows you to connect to any of the data stores noted above and more in a similar way to PowerPivot.

Once you have connected to the data source it is loaded into a data model which can also be downloaded if required. Different data sources can be appended or merged together - think working with database tables and queries. Change the shape and view of the data using filters and queries as required. Slice it or use a Timeline then Pivot or add a Power View Report.

Whilst Power Query is still in early rushes downloading and evaluating it reveals some exciting possibilities to enhance how we work in BI environments. What makes it truly exciting is that the functionality is also being extend to Office 365 .

Read the MSDN blog here to get more information. For an amazing visual demonstration of the capabilities of this technology watch the recent Microsoft Partner video and make sure you stay for the last five minutes!

QA Training | Richard Edgerton

Richard Edgerton

Office Applications Principal Consultant - Microsoft

Richard is the Principal Technologist for Microsoft Business Applications responsible for overseeing the development and production of new courses for QA and its clients. He has over 25 years’ experience of working with Office Applications and in client business application transformation projects, gaining a wide variety of experience across all sectors both commercial and not for profit. QA lead on Office 365 working closely with Microsoft since its launch in 2011. As a qualified PRINCE2® Practitioner, Richard has undertaken all project roles and is fully conversant with the PRINCE2® methodologies and their application. Equally happy in the classroom, delivering seminars, one to one or distance learning, Richard’s main focus is always on providing innovative and best value solutions to the client. Richard is based in the Midlands (Coventry).
Talk to our learning experts

Talk to our team of learning experts

Every business has different learning needs. QA has over 30 years of experience in combining the highest quality training with the most comprehensive range of learning services, ensuring the very best fit for your organisation.

Get in touch with our learning experts to talk about how we can help.