Best approach for data analysis / BI

Hi there,
I do usually extract data from poorly managed databases (never more than 500k rows) and run some analysis for Business Intelligence purpose generating KPIs and dashboard.
I have got Office 365 Home. Yesterday I discovered Excel Data Model and PowerPivot. I also discovered... that PowerPivot is not supported in my version and i should buy Office 365 ProPlus.

I would like to have your opinion on which is for you the best approach for analyze data with the Office Suite.

From what I have poorly understood I should use:

• Excel for data quality analysis and data edit (e.g. concatenate to create an interface key between sheets);
  PowerPivot for Business Intelligence Analysis and visualization.

Since I don't feel confident on any of these, I would like to have your opinion on which approach, tools and maybe plugin should I use for better and faster results!

Thanks
CLF

Originally Posted by noboffinme

Hi,

Excel can cope with 500K rows & apply formulas, calculations to tell you a lot about your data.

Powerpivot can summarise this data to display trends or progress of any particular value in your data.

It's really up to you to try them & create a real world example with your data that gives value to you.

Seems to me that Microsoft intend PowerPivot, Excel Data Model and DAX as the right approach to it - e.g forget VLOOKUP.

I wonder which are the best practices and, based on your personal experience, which approach would you recommend?
tks

Originally Posted by noboffinme

2. If you're familiar with DAX etc, you may get better performance etc, but there's nothing inherently wrong with using a VLOOKUP or other Functions.

Same applies to the use of Paper spreadsheets and desk calculator...
I'd like to know the opinion of people that are actually using PowerPivot!
Thks