Archive

Author Archive

Visualize Live CRM Data Using Power View for Microsoft Excel 2013

July 12th, 2013 Abi Shende No comments

The ability to efficiently access and break down data allows businesses to more effectively strategize and implement successful courses of action. Open Data Protocol, also known as OData, is a web protocol for querying and updating data, applying web technologies to provide access to information from a variety of different data sources.

Microsoft Excel 2013 offers a new data source called OData Data Feed. This enables you to import live data feeds from various data sources, including free and subscription-based data sources available on Windows Azure Marketplace. You can then use these live data feeds in Excel for analysis and reporting.

Microsoft Excel 2013 allows users to import live data feeds from different sources into the application for analysis and reporting purposes

Microsoft Excel 2013 allows users to import live data feeds from different sources into the application for analysis and reporting purposes.

Interestingly, Microsoft Dynamics CRM 2011 also offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol. You can find the service endpoint for OData service by navigating to Settings > Customizations > Developer Resources.

Microsoft Dynamics CRM offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol

Microsoft Dynamics CRM offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol.

Using a combination of these features, you can now create a live connection to your Dynamics CRM data and generate interesting reports and dashboards using Excel.

For connecting to Dynamics CRM OData service, follow these steps.

In Excel 2013, click Data > From Other Sources > From OData Data Feed. Enter the service endpoint link above and click “Next”.

The Data Connection Wizard presents the list of data sets available in Dynamics CRM

The Data Connection Wizard presents the list of data sets available in Dynamics CRM. For the purpose of this article, we will select AccountSet (Accounts) and OpportunitySet (Opportunities).

Excel allows you to use this data to populate a Table, PivotTable Report, PivotChart or Power View Report

Excel allows you to use this data to populate a Table, PivotTable Report, PivotChart or Power View Report. We will select Power View.

When you click OK, Excel  retrieves the data from Dynamics CRM and populates the data in PowerPivot Data  Model

When you click OK, Excel retrieves the data from Dynamics CRM and populates the data in PowerPivot Data Model. This can take some time depending on the volume of data being retrieved. There are tools available to limit the data by using the OData query instead of retrieving the full data set. You can view the PowerPivot Data Model by clicking on PowerPivot > Manage Data Model.

The following screenshot shows the data model in the diagram view. Excel does not automatically create the relationship between the tables. However, you can do this manually using the PowerPivot features. This is important when you want to use data from multiple tables in the PivotTable or Power View report.

Once the data model is  populated, the data is available for reporting in Excel

Once the data model is populated, the data is available for reporting in Excel.

The first Power View report shows the Open Opportunities by Account Chart and the Opportunities table. Clicking on the specific bar in the chart automatically filters the Opportunities table based on the relationship we defined earlier.

The second Power View report shows the Opportunities on the map

The second Power View report shows the Opportunities on the map. Maps in Power View display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. Clicking on a dot on the map automatically filters the opportunities in the table below. The size of the dot represents the estimated value of the opportunity.

Because the reports use the data feed from Dynamics CRM, they can be refreshed any time to see the current view of the data

Because the reports use the data feed from Dynamics CRM, they can be refreshed any time to see the current view of the data.

OData helps businesses access valuable information in various applications and thus put that data to good use. In this article, we reviewed how we can take advantage of OData data source and Power View to better analyze live Dynamics CRM data in Microsoft Excel 2013. 

Abi Shende is a Team Lead in Microsoft Dynamics CRM at Ignify. Ignify is a technology provider of ERP, CRM, and eCommerce software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011.

Find Your Most Valuable Customers Using PowerPivot and DAX for Microsoft Excel 2013

June 11th, 2013 Abi Shende No comments

RFM is a method used for analyzing customer behavior and defining market segments. It is commonly used in database marketing and direct marketing and has received particular attention in retail.

RFM stands for:
Recency: How recently did the customer purchase?
Frequency: How often did they purchase?
Monetary Value: How much did they spend?

In this article we will use PowerPivot and DAX (Data Analysis Expression Language) for Microsoft Excel 2013 to rank customers by their monetary value, based on the sales amount in the last 12 months.

We can start this process by populating the PowerPivot data model from the ERP and/or CRM solutions. Our PowerPivot Data Model consists of the following four tables:

PowerPivot Data Model consists of these four tables: Calendar, DimCustomer, FactInternetSales, DimProduct

PowerPivot Data Model consists of these four tables: Calendar, DimCustomer, FactInternetSales, DimProduct.

Next, we will create the following four calculated columns in the DimCustomer table:

Calculated Column DAX Formula Explanation
L12MSales
=CALCULATE(SUM(FactInternetSales [SalesAmount]),
                       DATESBETWEEN (
                           Calendar [FullDate],
                           DATE (2012,6,1),
                           DATE (2013,5,31)
                        )
                      )
This column holds the total sales amount for each customer for the last 12 months.
RunningL12MSales
=SUMX (
     FILTER (
         DimCustomer,
         DimCustomer [L12MSales] >= EARLIER(
     DimCustomer [L12MSales])
                ),
          DimCustomer [L12MSales]
            )
This column calculates the running total of customer sales, which includes the sales for the customer on the current row and all other customers having sales greater than the current customer. This is accomplished using the FILTER function to filter the customers and the EARLIER function, which ignores the filter context and uses the previous row context to compare all other customers’ sales with the current customer’s sales.
RunningPct
=DimCustomer [RunningL12MSales]/
SUM(DimCustomer[L12MSales])
This column calculates the percentage of running total to the total of all customer sales.
SalesABCRank
=IF(DimCustomer[RunningPct]  < 0.7, "A",
    IF(DimCustomer[RunningPct] <  0.9, "B", "C"))
Finally, this column assigns the rank to each customer based on the Running Percentage as follows:
A = Top 70% of Total Sales
B = Middle 20% of Total Sales
C = Bottom 10% of Total Sales

The following screen shot shows the result of these calculations in the PowerPivot data view of DimCustomer table. The DimCustomer table is sorted by L12MSales in the descending order (largest to smallest) to see that top customers are ranked “A”.

After completing calculations in the PowerPivot data view of DimCustomer table, the DimCustomer table is sorted to see the top customers (ranked “A”)

After completing calculations in the PowerPivot data view of DimCustomer table, the DimCustomer table is sorted to see the top customers (ranked “A”).

Now, we can use this data in Microsoft Excel PivotTable for analysis and reporting purposes. The following screenshot shows the list of customers ranked “A” – i.e., top customers that contribute to 70% of sales.

Data can be put in Microsoft Excel Pivot Table for further analysis and reporting

Data can be put in Microsoft Excel Pivot Table for further analysis and reporting.  

The following PivotTable using the same data shows that the top 3,595 customers generate 70% of sales while the bottom 12,465 customers generate only 10% of sales.

This PivotTable shows the percentage of sales generated by customers in different sales ranks

This PivotTable shows the percentage of sales generated by customers in different sales ranks. 

We can use a similar technique to rank customers by recency and frequency and calculate a combined customer rank.

Also, the PowerPivot tables can be refreshed periodically to recalculate the ranks.

This is just one example that illustrates how PowerPivot for Microsoft Excel 2013 provides powerful Business Intelligence capabilities to analyze operational, financial and sales data from ERP and CRM solutions.

If you have any questions, or if you would like more information, please email us at dynamics@ignify.com.

Abi Shende is a Team Lead in Microsoft Dynamics CRM at Ignify. Ignify is a technology provider of ERP, CRM, and eCommerce software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011.

Analyze up to 100 million rows of data from ERP and CRM using PowerPivot for Excel

January 6th, 2012 Abi Shende No comments

Companies often have valuable operational and financial data stored in multiple systems and have to create reports by combining this data. For example, a retailer may have customer demographic data such as customer location, gender, employment status and home ownership stored in Dynamics CRM, and sales data stored in ERP such as Dynamics AX or Dynamics GP. The retailer might then want to create a sales analysis report by combining data from both CRM and ERP. In the past, it has been difficult to create PivotTables based on data from different data sources. PowerPivot is a free add-in for Excel 2010 that allows you to easily create PivotTables based on data from disparate systems, websites, spreadsheets, or databases. Using PowerPivot, you can quickly create PivotTables based on up to 100 million rows of data.

After you install PowerPivot, you will see a PowerPivot tab on the Ribbon in Excel. When you click the PowerPivot tab, you see the following buttons.

PowerPivot tab in Excel

PowerPivot tab in Excel

Clicking on the PowerPivot Window button opens the PowerPivot window and displays the following options.

Home tab after clicking on PowerPivot Window button

Home tab after clicking on PowerPivot Window button

You can now import data from external data sources such as SQL Server, SQL Server Analysis Services, Microsoft Access, Microsoft Excel and text files.

In the following example, the company has the customer, customer address and sales data stored in the ERP system. It maintains sales territories in the Dynamics CRM system. It has also created a custom entity in Dynamics CRM to maintain the mapping from State/Province to sales territory. For example, Alaska (AK) is in the Northwest territory and California (CA) is in the Southwest territory.

The company has imported the customer, customer address and sales data from ERP and Territory data and State/Province to Territory mapping from CRM into PowerPivot. It has also created the following relationships:

  • Customer Address to State/Province (based on State/Province)
  • State/Province to Territory (based on Territory)

PowerPivot enables you to manage relationships between tables in PowerPivot based on common data elements. The following screen shots illustrate this feature.

Relationship between Customer Address and State/Province based on StateProvinceID

Relationship between Customer Address and State/Province based on StateProvinceID.

Relationship between State/Province and Territory based on TerritoryID

Relationship between State/Province and Territory based on TerritoryID.

Now you are ready to do the analysis by combining data from both sources.

PowerPivot for Excel enables you to analyze Sales by Territory by combining data from ERP and CRM

PowerPivot for Excel enables you to analyze Sales by Territory by combining data from ERP and CRM.

In summary, PowerPivot for Excel enables you to create reports based on data residing in multiple systems and tables using the tool you are already familiar with. You can download the PowerPivot for Excel 2010 here.

This post is written by Abi Shende. Abi is a Team Leader in Microsoft Dynamics CRM with Ignify. Ignify is winner of the Microsoft Partner of the Year in 2011 and provides CRM, eCommerce, and ERP software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine, and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011. If you are seeking assistance to maximize your efforts with your CRM project, contact Ignify at 562-219-2001 or email us at crm@ignify.com.

Dynamic Reports with Microsoft Dynamics CRM with Pivot Tables

September 9th, 2011 Abi Shende No comments

Microsoft Dynamics CRM 2011 provides native capability to allow you to export any screen or view into Excel. That export can be a static file with just the data on the screen as a snapshot or as a Dynamic export in the form of an Excel Pivot Table. You have an option to select the fields you want to include in the Pivot Table. Once you have exported the view as a Dynamic Pivot Table, you can then slice and dice the data for further analysis and save the Pivot Table report for future use.

You can open the saved Excel workbook at a later time and refresh it (Data > Refresh from CRM or Refresh All) to update the Pivot Table to reflect the up-to-date information from Dynamics CRM 2011 without having to log into Dynamics CRM. This is incredibly powerful as the report is now on your desktop and every time you open the Excel file, you will have updated numbers.

Here’s an example of a Dynamic report showing the Open Sales Opportunities. The Pivot Table shows open sales opportunities in various pipeline phases by territory. Note the “Refresh from CRM” and “Refresh All” buttons in the Data ribbon. Whenever I want to refresh the data, I would just click the button and my graphs and data will refresh to give me the latest sales pipeline.

The Pivot Table in this Dynamic report shows Open Sales Opportunities in various sales pipeline phases

The Pivot Table in this Dynamic report shows Open Sales Opportunities in various sales pipeline phases. By clicking the “Refresh from CRM” and “Refresh All” buttons in the Data ribbon, the data and graphs refresh to give the latest sales pipeline.

If you want to make this Pivot Table report available to the other CRM users, you can upload the Excel workbook containing your report to Dynamics CRM. You do this by simply going to the reports area in the workplace and start creation of a new report – specify that it is for an existing file and browse to the location where you saved your Excel workbook and your Excel report containing the Dynamic Pivot Table; it is now available to the other users.

The data displayed in the Pivot Table will depend on the user’s privileges set in CRM. For example, when a user opens the Excel workbook created in the example above (open opportunities by territory), the user viewing this report would see only the opportunities that he or she can otherwise see in CRM.

Dynamic Worksheets and Dynamic Pivot Tables require the use of the Excel add-in which is part of the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook client. You can download the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook found here.

You can take advantage of Pivot Table reports using the valuable data in CRM, and find the answers to questions like:

  1. Which territories performed best and what products are they selling?
  2. Which products are selling best over time?
  3. What are the top reasons why we lost opportunities?
  4. What are the Win and Loss ratios by Lead Source?
  5. Which Marketing Campaigns performed well?
  6. Who were the top performing Customer Service Representatives (based on case resolution)?

Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data in your Dynamics CRM system. You can learn how to visualize summary data in a PivotTable report, and to easily see comparisons, patterns, and trends here.

This post is written by Abi Shende. Abi is a Team Leader in Microsoft Dynamics CRM with Ignify. Ignify is winner of the Microsoft Partner of the Year in 2011 and provides CRM, eCommerce and ERP, software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011. If you are seeking assistance to maximize your efforts with your CRM project contact Ignify at 562-219-2001 or email us at crm@ignify.com.