The second day of the Australian SharePoint Conference 2014 is kicking off. John White (as he said, “a Canadian from Canadia”) is talking about Power BI (Business Intelligence).
– classic architecture – from source data, extract / transform / load, into a storage alternative, and then ready for design and visualization. In a Microsoft world, ETL is done by SQL Server Integration Services (SSIS), storage in SQL Server DB with SQL Server Analysis Services, and design and visualization through Reporting Services (SRSS, on SQL Server), SharePoint (Excel Services, PowerPivot for SharePoint, SSRS SharePoint Mode, and PerformancePoint), or Excel. This is classic BI. However, for the past few years, there has been an emerging area – “personal BI.”
– Excel is the client of choice from Microsoft for personal BI. Why? List based data, powerful features, massive adoption, and good usability.
– However, there are problems with Excel (because it is so powerful, easy to use, and can do some many things). It’s not a database, it’s not relational, there are capacity limits, and it’s not authoritative.
– John’s recommendation – keep everything in SQL. Don’t use Excel to store authoritative data. Just use it as a client.
– Microsoft’s Power Query offers ETL, whereas Power Pivot Import only offers EL.
– In Excel, you have various tools to visualize data – Pivot Charts and Tables (standard Excel), Power View (analytic reports – not “structured organization reports” which is done by SRSS), Power Map (geospatial and time series data), and Power Pivot (Model design). Power View and Power Map ship with Excel 2013, and you don’t need a separate license to use.
– What are the components in Power BI – client side (Excel, Power Pivot, etc) and cloud side (Power BI sites, Power Q&A, shared queries, etc). In between is a data management gateway (for exposing on-premise data through an OData feed into Office 365), plus native apps and browser support.
– John gave a demo of the various BI tools using a public source on recorded shark attacks around the world.