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!