How is Power BI in Office 365 more than just Excel in the cloud?
Excel has many useful features for the kinds of analysis we do in business intelligence, but when you work with large volumes of data, you need more than standard Excel. Power BI for Office 365 is an Excel add-on that combines two tools currently available for Excel: Power Query and Power Pivot.
Power Query collects data from outside Excel and uses Excel search features to identify relevant subsets of data. One of the useful things about Power Query is non-programmers can now do some of their own extraction, transformation and load (ETL) operations.
Experienced data warehouse professionals know ETL can be a time-consuming and difficult task. But while Power Query cannot change the fundamental nature of ETL, it does allow someone comfortable with some of the more advanced features of Excel to take on basic ETL tasks. This can be especially useful for analysts who may have data in a company relational database and want to integrate it with publically available data.
Power Pivot is an in-memory data management component that allows analysts to work with millions of rows of data at one time. It also has visualization capabilities, and it lets you create tools that others can use, like pivot tables.
Excel without add-ons is a capable tool for analysis, but Power Query and Power Pivot bring some of the features you expect in databases and business intelligence reporting tools. As Power BI rolls out, Office 365 users will have access to more business intelligence capabilities than those in the core Excel base. Power BI should be available in preview later this year.
About the author:
Dan Sullivan, M.Sc., is an author, systems architect and consultant with more than 20 years of IT experience. He has had engagements in advanced analytics, systems architecture, database design, enterprise security and business intelligence. He has worked in a broad range of industries, including financial services, manufacturing, pharmaceuticals, software development, government, retail and education. Dan has written extensively about topics that range from data warehousing, cloud computing and advanced analytics to security management, collaboration and text mining.
This was first published in August 2013