# Getting started with PowerPivot



## dugdugdug (Mar 28, 2013)

Can PP be used with VBA or is it only for displaying large (> 1 million rows) data?


----------



## RoryA (Mar 28, 2013)

In 2013 some of the data model is exposed to VBA but PowerPivot itself is not.


----------



## dugdugdug (Mar 28, 2013)

My task is to read csv files containing a few million records, select certain criteria and create a report of the output.

So PP would not be of use, despite it being able to handle very large volumes of data?


----------



## RoryA (Mar 28, 2013)

Why would PP not be of use? What does VBA have to do with it?


----------



## miguel.escobar (Mar 28, 2013)

actually, it could be of great use! shoot some questions on what specifically you're trying to do and I can guarantee that you'll be able to perform those tasks using Powerpivot


----------



## dugdugdug (Apr 3, 2013)

RoryA said:


> Why would PP not be of use? What does VBA have to do with it?





miguel.escobar said:


> actually, it could be of great use! shoot some questions on what specifically you're trying to do and I can guarantee that you'll be able to perform those tasks using Powerpivot



One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.

All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?


----------



## RoryA (Apr 3, 2013)

You never mentioned automating an entire process before. If you can reuse the same csv file names, you could probably set up a PP template to do the extraction you need and then just refresh the model (but you would have to do that manually unless using 2013).


----------



## miguel.escobar (Apr 3, 2013)

dugdugdug said:


> One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.
> 
> All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?




You might want to take a look at Data Explorer for the ETL process.


----------



## dugdugdug (Apr 3, 2013)

RoryA said:


> You never mentioned automating an entire process before. If you can reuse the same csv file names, you could probably set up a PP template to do the extraction you need and then just refresh the model (but you would have to do that manually unless using 2013).



Yes, a very simple example would be to open say 10 large csv files and do something to it.

The first stumbling block is opening it in normal Excel 2010 (because it's large) but PP can manage that except I can't systematically tell it to do 10 times over.

How do I go about setting up a PP template to do the extraction?


----------



## miguel.escobar (Apr 3, 2013)

dugdugdug said:


> One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.
> 
> All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?




You might want to take a look at Data Explorer for the ETL process.


----------



## dugdugdug (Mar 28, 2013)

Can PP be used with VBA or is it only for displaying large (> 1 million rows) data?


----------



## RoryA (Apr 3, 2013)

What are you actually trying to do with these csv files?


----------



## prez02 (Apr 3, 2013)

dugdugdug said:


> Yes, a very simple example would be to open say 10 large csv files and do something to it.
> 
> The first stumbling block is opening it in normal Excel 2010 (because it's large) but PP can manage that except I can't systematically tell it to do 10 times over.
> 
> How do I go about setting up a PP template to do the extraction?



You have to open Excel, open the Powerpivot Window, select the home tab, click on other sources, scroll down to text file, locate the csv file on your computer and make a few choices. If you click ok, the content of the csv is loaded into excel and displayed in the powerpivot window. The table and the csv stay connected to each other, so if you have a newer version of the csv file with fresh data, you change the csv files on your computer and then refresh the powerpivot table. 

In the powerpivot window you can add calculated columns, e.g. adding the values of two columns, etc. A lot of the function you'll  find in excel are also available here.

On the basis of this table, you then can create a pivottable in an excel sheet. You can also create a more "simple" or flat pivot table, which is more like a list, depending on what your report should look like. You can also create measures, or calculated fields in the pivottable. Here lies the real power of powerpivot.

Once you have done this, you can then use a newer/different csv while, provided it has the same columns, refresh the tabel in the powerpivot window, then refresh the pivottabel in Excel. 

This is a bit cumbersome, only in Excel 2013 this can be done via VBA, so there is not really a one-click solution. 

Powerpivot is for creating reports in form of  a pivottable using its own formula language and allowing you to combine data from different sources. I am not sure if this is really what you are looking for. A bit of extra information might be helpful...

Carsten
P.S.:You can connect several csv files to one excel file, creating several tables in the powerpivot window.


----------



## dugdugdug (Apr 3, 2013)

prez02 said:


> You have to open Excel, open the Powerpivot Window, select the home tab, click on other sources, scroll down to text file, locate the csv file on your computer and make a few choices. If you click ok, the content of the csv is loaded into excel and displayed in the powerpivot window. The table and the csv stay connected to each other, so if you have a newer version of the csv file with fresh data, you change the csv files on your computer and then refresh the powerpivot table.
> 
> In the powerpivot window you can add calculated columns, e.g. adding the values of two columns, etc. A lot of the function you'll find in excel are also available here.
> 
> ...



Thanks for the instructions but it doesn't sound like at any stage, it's automatable.


----------



## dugdugdug (Apr 3, 2013)

RoryA said:


> What are you actually trying to do with these csv files?



There are various tasks but a simple one would be to look for identical values.

Assuming there are only 2 fields, I sort by field A, then apply a similar formula to this:


```
=IF(AND(AND(A4=A3,A4=A2),B4=B3,B4=B2),"Same","Not")
```

This checks for a difference of 2 but obviously I need it to be dynamic so the user can choose the desired number.

If 3 was chosen, it would be:


```
=IF(AND(AND(AND(A8=A7,A8=A6,A8=A5),B8=B7,B8=B6,B8=B5)),"Same","Not")
```

I have done all this looping in VBA and have got my desired results. The problem is the first step, reading the csv file when it contains a few million rows.


----------



## prez02 (Apr 3, 2013)

dugdugdug said:


> Thanks for the instructions but it doesn't sound like at any stage, it's automatable.



Well, Powerpivot will not allow you to load more than a million rows in an ordinary excel sheet, and then transform it with your vba code. There is no way to do this.

That said, large tables combined with vba not working well was exactly what drove me to powerpivot, so I am sure it could help you, but only after you spend some time learning new stuff.

Carsten


----------



## miguel.escobar (Apr 4, 2013)

dugdugdug said:


> There are various tasks but a simple one would be to look for identical values.
> 
> Assuming there are only 2 fields, I sort by field A, then apply a similar formula to this:
> 
> ...



you should really try Data Explorer...let's say that you get the csv file from an application or a web location. You can query that data from data explorer, then create a transformation process within that load process and then once you have the data how you want it you can then load it into the data model for its usage with Powerpivot. I can't think of anything more systematically automated.


----------



## RoryA (Apr 4, 2013)

To be honest it sounds as though a database would be a better option here.


----------



## miguel.escobar (Apr 4, 2013)

I second that, Rory.Hopefully dug doesn't have any type of security issues. Many times I've had friends that can't connect to their databases due to corporate security concerns :/


----------



## dugdugdug (Apr 4, 2013)

miguel.escobar said:


> I second that, Rory.Hopefully dug doesn't have any type of security issues. Many times I've had friends that can't connect to their databases due to corporate security concerns :/



Re database, I assume you mean store the data in a db and ADO what you want?

Tried that but other problems exist such as the 2GB limit. Probably have to store it in an SQL Server, though that too has a 10GB limit!

Right now, I think Excel and Access are just about OK for my tasks (for example I've had to write extra code to convert filenames because Access can't handle filenames longer than a certain length) but all this adds to the memory usage and eventually will topple over.

Will try Data Explorer as mentioned (if IT have the budget / security allowance) for it!


----------



## RoryA (Apr 4, 2013)

The database size limitation for SQL Server 2012 is 524,272 terabytes which should be enough...


----------



## dugdugdug (Mar 28, 2013)

Can PP be used with VBA or is it only for displaying large (> 1 million rows) data?


----------



## dugdugdug (Apr 4, 2013)

RoryA said:


> The database size limitation for SQL Server 2012 is 524,272 terabytes which should be enough...



Bloody well hope so!


----------



## dugdugdug (Apr 4, 2013)

RoryA said:


> The database size limitation for SQL Server 2012 is 524,272 terabytes which should be enough...



I'm confused.

This is where I got the 10GB limit:

SQL Server 2008 R2 Express Database Size Limit Increased to 10GB - SQL Server Express WebLog - Site Home - MSDN Blogs


----------



## prez02 (Apr 4, 2013)

I am no expert, but SQL Server 2008 R2 *Express* is probably not the same as SQL Server 2012 without Express in the name.


----------



## RoryA (Apr 4, 2013)

Not even the same version, never mind same SKU.


----------



## dugdugdug (Apr 4, 2013)

RoryA said:


> Not even the same version, never mind same SKU.



I've got SQL Server Man Studio 2008 R2 so what's its limit?


----------



## prez02 (Apr 4, 2013)

dugdugdug said:


> I've got SQL Server Man Studio 2008 R2 so what's its limit?



You are probably right about the file size, Express is a free verison, therefore there will be some limits, if you have a full Version, those will be removed.

Where do you get your csv files from? I might be easier to get odbc access to that database and load data in smaller chunks below a million rows, as you need them, instead of setting up a new database. And even if you have done that, the million row Limit in Excel will still exist.

Also, have you tried your VBA Code on something Close to a million rows? As far as I remember, this could be really slow/not work at all, maybe because VBA does not take advantage of several processor cores. 

Have you ever worked with Excel tables and created your on columns with Excel functions? The if clauses you mentioned above could be created there as well, and whenever the data is refreshed, it would recalculate your columns automatically. If yes, you could do the same with Powerpivot, as I ried to explain above. Maybe you should just give it a try.


----------



## dugdugdug (Apr 5, 2013)

prez02 said:


> You are probably right about the file size, Express is a free verison, therefore there will be some limits, if you have a full Version, those will be removed.
> 
> Where do you get your csv files from? I might be easier to get odbc access to that database and load data in smaller chunks below a million rows, as you need them, instead of setting up a new database. And even if you have done that, the million row Limit in Excel will still exist.
> 
> ...



Thanks for your tips.

My csv files are "floating around" at the moment, ie my programs are based on the assumption that such files exists and are stored somewhere on the network drive (which is slow) somy first step involves copying it to my C drive.

For security reasons, I have no direct access to where the data is stored, possibly in a SQL database.

A million rows is not a problem if I only do a few things to it. My method is involves using arrays so Redim myarray(1 to n, 1 to m) As Variant, where n is 200,000, m is 300 is possible (though I am not sure what the limit is) but as I constantly do my manipulation and create further sub arrays, the memory limit will be eventually be reached and the program crashes.

Isn't Excel tables used for formatting purposes? My tasks include more complicated procedures so not sure if that would work.


----------



## prez02 (Apr 5, 2013)

dugdugdug said:


> Thanks for your tips.
> 
> [...]
> Isn't Excel tables used for formatting purposes?  [...]



Here is another tip, statements like that will not help you to make friends in an Excel forum. 

Excel is used in a lot of different ways and can do a lot of different things. You can use it to accidently bring down a whole investment bank, if youl like that sort of thing. 

I am not sure what you are trying to achieve, why you have to transform your data in the way you do, what the results should be and who is using them for what purpose.. Maybe someone could help you looking at your code and tell you if this can be achieved in another way, e.g via Excel functions or not. Good luck.

Carsten


----------



## prez02 (Apr 5, 2013)

JIC, additional information about SQL Server Express RAM limit: link


----------

