locked
Analysis Features Within PowerPovit add-in for Excel RRS feed

  • Question

  • I am trying to find features other than pivot table and pivot charts within PowerPivot add-in for Excel.  Can someone point me to those features?  Like more analytic analysis based on the worksheets that I have created in my powerpivot.

    Your help is appreciated!

    Thursday, February 10, 2011 7:45 PM

Answers

  • Hi LAWSQL,

    It sounds like you need Data Mining :) Check out the Excel data mining add-in. Latest version is for Excel 2007 32bit, but should work with 2010 32bit. Definitely no point in trying with Excel 2010 64bit - it will not work (I tried and nope...).

    It can do lots of analysis like the ones you described - correlation, segmentation, regression, etc...


    Boyan Penev --- http://www.bp-msbi.com
    Saturday, February 12, 2011 12:10 AM
  • You can use Microsoft SQL Server Data Mining Add-ins for Office 2007 to perform more complex analysis of the data. The add-ins only work with 32-bit version of Office 2010. More information can be found here:

    http://social.technet.microsoft.com/wiki/contents/articles/using-the-sql-server-data-mining-add-ins-with-powerpivot-for-excel.aspx

    According to the article above "There are no current plans to release a separate 64-bit version of the Data Mining Add-ins, because of a dependency on the 32-bit Office Web controls."

     

    One of Microsoft partners (Predixion Software) provides similar add-ins that work with both 32 and 64-bit version of Office 2010. You might like "Profile Data" feature of the add-ins. It shows you what numerical columns are correlated without performing data mining. Some information can be found here

    http://www.bogdancrivat.net/dm/archives/76

     


    Tatyana Yakushev [PredixionSoftware.com]
    Monday, February 14, 2011 6:32 PM

All replies

  • Hi LAWSQL,

    There is not much more you can do. However, you can go to the PivotTable Tools ribbon group, then to OLAP Tools drop-down and select Convert To Formulas. Then you can cut/paste around the pivot table and you can get a better layout (through the use of CUBEVALUE and CUBEMEMBER formulas).

    You can also create custom DAX measures in the pivot tables - right click on the table name in the pivot table field list and select Add New Measure. Then you can specify a DAX expression to calculate something - like TableName[ColumnName1]/TableName[ColumnName2], etc..


     

    Boyan Penev --- http://www.bp-msbi.com
    Friday, February 11, 2011 2:03 AM
  • Thanks.  So what tools do you guys recommend that will provide additional functionalities (such as ignore outliers) analytical correlation (such as 80% of my cases are related to what Age range, or between attributes such as how Age correlate to Product purchased)? 

    Seems like PowerPivot is good at combining multiple data sources, but not providing additional correlations.

    Your suggestions are much appreciated!

    Friday, February 11, 2011 7:06 PM
  • Hi LAWSQL,

    It sounds like you need Data Mining :) Check out the Excel data mining add-in. Latest version is for Excel 2007 32bit, but should work with 2010 32bit. Definitely no point in trying with Excel 2010 64bit - it will not work (I tried and nope...).

    It can do lots of analysis like the ones you described - correlation, segmentation, regression, etc...


    Boyan Penev --- http://www.bp-msbi.com
    Saturday, February 12, 2011 12:10 AM
  • You can use Microsoft SQL Server Data Mining Add-ins for Office 2007 to perform more complex analysis of the data. The add-ins only work with 32-bit version of Office 2010. More information can be found here:

    http://social.technet.microsoft.com/wiki/contents/articles/using-the-sql-server-data-mining-add-ins-with-powerpivot-for-excel.aspx

    According to the article above "There are no current plans to release a separate 64-bit version of the Data Mining Add-ins, because of a dependency on the 32-bit Office Web controls."

     

    One of Microsoft partners (Predixion Software) provides similar add-ins that work with both 32 and 64-bit version of Office 2010. You might like "Profile Data" feature of the add-ins. It shows you what numerical columns are correlated without performing data mining. Some information can be found here

    http://www.bogdancrivat.net/dm/archives/76

     


    Tatyana Yakushev [PredixionSoftware.com]
    Monday, February 14, 2011 6:32 PM