what would be a advantage of using sql over built-in Power Pivot?

[cyrilbrd]

As seen here: link.

originally posted by Fazza.

hello, cyril

I am not familiar with Power Pivot, so can't advise on that, sorry. I understand Power Pivot is somehow better suited to large datasets and has a better interface.

Normal pivot tables can do a lot more than we read in normal forum questions & for me can handle all I ever need which is typically 50,000 records and basic summaries - SUM & COUNT functions. By joining multiple tables and a little SQL pivot tables (& query tables) can do wonders. Add a little <acronym>VBA</acronym> with some ADO and even more things. I don't have the need to manipulate existing pivot tables using <acronym>VBA</acronym> as I sometimes see in forum questions: in fact generally I think with appropriate data set up (maybe with more than one table) and pivot table customisation (SQL to define the dataset) the <acronym>VBA</acronym> can be avoided, or a simpler approach taken. But I'm getting away from your question.

Maybe the Power Pivot can do more of the fancier stuff without needing <acronym>VBA</acronym>? Or the user interface makes more complex tasks easier. But again, I don't know. Maybe start a new thread?

regards

In fact I was just curious as I am using Power Pivot but will eventually as well have to learn SQL for Data Mining purpose. I haven't seen any forum related to SQL within Mrexcel, although BI is part of Excel...
Fazza was kind enough to answer my query with extensive examples demonstrating the advantages offered by using SQL, thus extending the capability of the pivot table toward something that reminds me of Power Pivot.

Question, would SQL allow me to work on a data that represent about 40 million cells? Or could ACCESS be sufficient for this? Power Pivot should have been capable but we are running into what we think is hardware limitation.

 

[scottsen]

Both SQL and Access can party on 40 million rows just fine. Power Pivot... it CAN handle 40 million rows, but you are certainly well into the territory of needing to "be careful". Limiting the number of columns will have a big impact, and you will see a big difference between (say) a simple SUM and a SUMX. At that row count, even things like data types on relationships (strings vs int) start to matter.

Depending on your data, your business need maybe the data can be aggregated and still useful. eg: in SQL/Access group by Weeks, and then deal w/ your data at the Week level in Power Pivot (so, 1/7th the size or about 6 million rows).

Aside from the the example of aggregating days to weeks, there is various other things you can do in SQL ... shaping your data to make it happier in power pivot. Say, pivoting lots of columns into less columns, but more rows.

 

[cyrilbrd]

Thanks for your opinion, please do correct me if I am wrong but you would tend to say that SQL (either Microsoft or other) might be better suited for bigger files, or rather that SQL would be the better tool to prepare data for Power Pivot.
Please let's take this as an academic point of view rather than a specific reply to a query. The 40 millions cells being here more of a guideline as to express a bigger file.
Note: ...yes more Rows than Columns but still containing a huge amount of Columns nonetheless...

 

[scottsen]

There is no doubt that sql can handle more data than power pivot (WAY more). However, generally they are used for different purposes. They do work great together; SQL is easily my favorite data source for power pivot.

I read the original thread a bit, and I saw the 50000 rows x 300 columns figures:
* I would be curious to hear if you are actually *using* 300 columns in powerpivot. If some can be calculated based on the other columns... ?
* I would be curious to hear where you are having perf issues. 50k rows is pretty small, regardless of # of columns.

 

[cyrilbrd]

* I would be curious to hear if you are actually *using* 300 columns in powerpivot. If some can be calculated based on the other columns... ?

Yes the 300 Columns are a rather complex array of data including salinity, ph, conductivity, matter in suspension, color shape grade and so on.
No I have no formulae here, hence I am not calculating anything, this is raw data.

* I would be curious to hear where you are having perf issues. 50k rows is pretty small, regardless of # of columns.

Well I got concerns due to the fact that I am using both MAC and PC, This specific file crashed on both, PC 32bits will be upgraded to 64bits.
Performance issues are just that, 'performance issues', right now the file is doing fine and accessibility is not an issue.

As mentioned this is more of an academic question, and as you mentioned:

There is no doubt that sql can handle more data than power pivot

This is something new to me and I am still in the process of understanding how to extract information.
So far I find SQL a bit long to start-up, I am being told that importing directly from Excel isn't feasible, and therefore I should prepare the columns one at the time. Is this confirmed?

Note: Very interesting post...

 

[scottsen]

Thanks for the comment on the guest post Fun project.

You should find 64-bit much better. At least I do.

One thing you could consider is restructuring your table in powerpivot, if possible. (This is actually where it is nice if the data comes from SQL...)

Instead of :
Date, Test#, Salinity, pH, Clarity, Conductivity, Suspension, etc... for 300 columns wide... pivot it so that it is "long". More rows, less columns.

Date, Test#, Value, Type
1/1 123 7 pH
1/1 123 0.12 Salinity
1/1 123 0.5 Clarity

I have done this quite a few times, and the majority of the time this does "good things" to the performance. Power Pivot is happier dealing with lot of rows, than lots of columns. In general.

I'm not sure what you are asking re: "importing directly from excel". From an excel sheet into... power pivot? into sql?

And generally, SQL is "always running", so the "startup time" is not an issue. at all.

 

[cyrilbrd]

Thanks for the comment on the guest post Fun project.

Most deserved.

Instead of :
Date, Test#, Salinity, pH, Clarity, Conductivity, Suspension, etc... for 300 columns wide... pivot it so that it is "long". More rows, less columns.

Date, Test#, Value, Type
1/1 123 7 pH
1/1 123 0.12 Salinity
1/1 123 0.5 Clarity

Interesting alternative, I shall look into that.

Yes 64bits is scheduled for purchase, hopefully it will solve my concerns.
Actually I am running MySQL Workbench, and was able to extract data directly from Excel, so this is covered so far.