• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot - To use or not to use?

V

Vivek D

Member
Looking for some suggestions...

I have to generate a dashboard which might need 1/2 million to 1 million records of data in total with around 20 columns in each row coming from 3-4 files.

Given the size of the data I guess Power Pivot is the best option. However, one major limitation is that everyone would need to have Power Pivot installed. Almost all users are on 2010 so I guess I can ask them to get it installed so that is manageable.

However, I need to use Power Query to transform the data before I load it into Power Pivot but it looks like there is no direct load option from Power Query to Power Pivot like there is in Excel 2013.

There is a method outlined here. However, looks like there is a pretty significant limitation for this approach and it's that when you make any change to the Power Query steps (pretty much any change) then you have to reload the data into Power Pivot and redo anything that you may have done within Power Pivot and the entire dashboard.

This is a deal breaker and I know there are some workarounds suggested by folks (here) but don't seem like they will reliably work so don't want to depend on them.

So I guess these are my my options:-
1. Transform the data and store it in a separate excel file and then import those into Power Pivot?
2. Use Excel 2013 and get everyone to install it as that does not have the limitation
3. Any others?
 
To be honest.

I'd recommend using free DB (MySQL and such) to store data and using Access as front end. Failing that, Excel as front end to query specific part of data to report on.
 
Hello,

I have the same problem (users, (around the world) have excel 2010 (or 2007) and that is why I still cannot use power pivot. :(

Another option would be to use qlikview to merge and transform excel data.
I often use it and it works great.
Qlikview is free for a personal use.

Then you can export result in a csv file and use it as source in powerpivot
 
@Chihiro,
I don't understand your proposal, why not using access as db too? Why only front end?

I would say use access or MySQL as db and excel/powerpivot as frontend.
 
Performance wise MySql is much better.
Access, unless you have Office licence that came with it, you may need to purchase.
Access is not meant for network/server deployment and only for local machine.

Overall, for any mid to large size business that has DB type need, you'd want to go with MySQL, Postgre SQL etc.
 
I bump into this a lot:
mysql isn't a bad idea, firebird works with a little bit tweaking.
 
You must log in or register to reply here.
Back
Top