Friday, July 4, 2014

Drill down Charts in Power View

Yes, we can have drill down charts in Power View.

Suppose we have below data set:



Steps to create drill down Bar chart:

1.       Change type of this table to matrix



2.       On changing type to matrix table is displayed as below:




3.       Now set levels property of matrix to ‘Row – Enable Drill Down One Level at a Time’



4.       This will make table look like:


              
5.       When we double click on any cell in product column then it drill downs to its respective years:


6.       Clicking on up arrow will again take you back to Product Level

7.       Now we can convert this table to either Pie chart, Bar chart or Column chart from standard option given in    Design menu.



8.       Double clicking on any of bar will drill down to year level :




Thursday, June 26, 2014

Power Query Fetching Specific Entity and Columns From Dynamic CRM

We can use Odata query as source in Power Query to fetch data from Dyanamic CRM.

But while fetching data from CRM we face below issues:

  1. All Entities present in that instance of CRM are loaded and then we need to select particular entity to load its data
  2. When we select particular entity all its columns are returned and we need to clean up i.e delete columns not required.  
To overcome first issue we can specify entity name in OData query: 

https://abcd.crm5.dynamics.com/xrmservices/2011/OrganizationData.svc/ContactSet

Above query loads data only from Contacts entity


To overcome 2nd column we can also specify required columns in OData query as below:

https://abcd.crm5.dynamics.com/xrmservices/2011/OrganizationData.svc/ContactSet?$select=FullName,Address1_City

Above query only loads name and city columns from contacts entity


Now sometimes even creating OData with specific Entity and columns name can become a tedious task as names are case sensitive.

To solve this problem we can use below tool which helps to design OData Queries for CRM

CRM 2011 OData Query Designer

Tool can be downloaded from here: http://crm2011odatatool.codeplex.com/

Friday, June 20, 2014

Analyzing Facebook Data using Power BI

Ashish Jagdale: Analyzing Facebook Data using Power BI:  1. We will be using Excel 2013 with Power Query installed. From other sources option click 'From Facebook' to import your Facebo...

Thursday, March 20, 2014

Demo to Model and Analyse data in Excel using Power BI (Movie Ratings)

Use case: To Analyse data collected in survey conducted for movie ratings

Part 1: Pulling, cleaning data from source files using Power Query




Part 2: Creating model and visualizations using Power Pivot and Power View

Monday, March 3, 2014

Microsoft Adds Connectivity for SAP BusinessObjects to Power BI for Office 365

Microsoft added SAP BusinessObjects as new dataSource!!!

With this new solution, users can now seamlessly connect to SAP BusinessObjects BI Universes as another supported data source in Power Query for Excel, enabling them to access and analyze data across the enterprise and share their data and insights in the cloud and any device through Power BI.

Source: Power BI MSDN Blog.

More Details to come soon!!!!

Sunday, February 23, 2014

Introduction to Power BI


Power BI for Office 365 is Microsoft's cloud-based business intelligence (BI) solution that works from within Excel and Office 365 to analyse and visualize data in a self-service way

Components of Power BI:
Excel 2013:
    •Power Query – easily connect to data from public and corporate                                         datasources, transform data and share queries
    •Power Pivot  – create a sophisticated Data Model directly in Excel
    •Power View – create reports and analytical views with interactive data                                visualizations
    •Power Map – visual tool for geospatially based data analysis
   
Office 365:
    •Power BI Sites – share, view, and interact with reports
    Power BI Q&A – use queries to find, explore, and report over your data
    •Power BI Windows APP– view reports on the go, with the Power BI app