Archive

Posts Tagged ‘Data Analysis in CRM’

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.