Update 20th Feb 2016:
Original Post:
What is Power BI?
Power BI is a cloud-based service that works together with Excel to provide a complete self-service analytic solution. With both Excel to author reports and Power BI for Office 365 to share them, you can give everyone in your organization a powerful new way to work with data.
Power BI Excel graphic
In Excel, you can now discover, combine, model, analyze, and visualize data like never before.
With Power BI for Office 365 you can easily setup an online gallery for users to share insights, collaborate and access reports created in Excel, from anywhere on any device while asking questions in natural language to get live interactive answers through data visualizations.
Self-Service BI in Excel
Self-service BI features in Excel 2013 make discovering and visualizing data easy. These four features – Power Query, Power Pivot, Power View, and Power Map – work seamlessly together.
With Power Query, you can search the entire cloud for data, both public and private. Using multiple data sources, you can filter, shape, merge, and append that data using Power Query… without bringing any of it into Excel. You get a view into the data, shape it how you want, and can then send it directly to the Data Model in Power Pivot. And even then, you can create new calculations and fields in Power Pivot to get just the dataset you want.
Power View uses the Data Model to bring that data to life. Create reports, then let others interact and drill into them, changing visualizations on the fly as they select report elements.
Connecting CRM 2015 to Power BI Excel
Power Query for Excel uses OData feeds to get the data from CRM using OData Organizational Data Services. Using this one can get all the data from CRM into Excel and use the power of Excel for analyzing data in depth.
To start, one need to have Microsoft Power Query for Excel add-on installed. This can be download at below link.
http://www.microsoft.com/en-us/download/details.aspx?id=39379
Navigate to your CRM organization and copy the Organizational Data Service URL from Setting > Customizations >Developer Resources.
Open Excel and go to POWER QUERY tab and select From Other Sources button in Get External Data section. Here we have multiple data sources options to get data from to Excel like SharePoint, Azure, Web Search, Active Directory, Exchange, OData, etc.
For connecting to CRM, we will use From Odata Feed.
Paste the OData URL into URL field on OData Feed dialog
Go to Organizational account tab and select the OData URL to use.
Click Sign in to connect to CRM instance.
On successful sign in, click on save. This will open a query window where one can select the CRM table which you want to query data and apply different filters.
The filtered query can be saved for future use and the data returned will be displayed in Excel worksheet.
Once the data is in excel, your open to use all Excel features to work with the data outside CRM and build the report as you wish with few clicks.
Other sources that Power BI supports for getting data into Excel with few configurations are Web (search), File (Excel, CSV, XML, Text, Folder), Database (SQL Server, Access, SQL Server Analysis Services, Oracle, IBM DB2, MySql, PostgreSQL, Sybase, Teradata), Azure, SharePoint, OData Feed, Hadoop File, Active Directory, Microsoft Exchange, Facebook, SAP BusinessObject BI Universe, Salesforce Objects, Salesforce Reports, ODBC.
crm backlog 