When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from the database. The problem is that you need to know SQL to be able to do this. So right from the start I decided this second approach wasn’t a very useful tool for the typical Excel user.
Recently though I have come across some real life challenges that I needed to solve. I have a very large “master” workbook that has all my measures and all my data (for several years) loaded – it is almost 400 MB and contains more than 40 million rows of data. I keep a master copy so I don’t have to re-write all these measures every time I have a new request for a report. But on the flip side, if someone wants a report that say contains just 10 products, I don’t want to send them the entire 400 MB workbook. Read More