Power Pivot

• Question

• When using power pivot do you need to export all the transactions into a table first then use the pivots to create the reports?

Or can the pivot link directly to the power pivot query code?

It's possible that table could be >1Million records which I believe would be a problem in excel.

Thanks

D

• Moved by Thursday, September 29, 2016 1:36 AM PowerPivot
Wednesday, September 28, 2016 3:47 PM

• That limitation of 1,0458,576 rows applies to excel worksheets.  Power Pivot is not bound by that limitation as the storage mechanism is completely different.  Although they look similar, an Excel Worksheet is not the same thing as a Power Pivot or Tabular table. I'm not sure of the upper bound but the limitation is really dictated by how much on-board memory you have.  You should be able to import 10's of millions of rows into a power pivot table (via direct sql query) quite easily if you have enough memory.

 1,048,576 rows
Thursday, September 29, 2016 11:54 AM
• Hello,

Your question is not clear for me, may can you rephrase it, please?

Power Pivot has a highly compression algorithm and kann easily handle a few million fact data, as Long as your Client machine have enough memory and you are using 64 bit MS Excel; see About the Relativity of Large Data Volumes

Olaf Helper

[ Blog] [ Xing] [ MVP]

Thursday, September 29, 2016 6:33 AM
• Hi David,

When creating a Pivot table from a PowerPivot data model, the data was load to the worksheet first, and then you can create table or chart from it.

For the row limitation, the max row numbers in the table is 1,999,999,997.

Object

Specification / Limit

Object name length

100 characters

Invalid characters in a Name

. , ; ' ` : / \ * | ? " & % \$ ! + = () [] {} < >

Number of tables per PowerPivot database

(2^31) - 1 = 2,147,483,647

Number of columns and calculated columns per table

(2^31) - 1 = 2,147,483,647

Number of calculated measures in a table

(2^31) - 1 = 2,147,483,647

PowerPivot memory size for saving a workbook

4GB = 4,294,967,296 bytes

Concurrent requests per workbook

6

Local cubes connections

5

Number of distinct values in a column

1,999,999,997

Number of rows in a table

1,999,999,997

String length

536,870,912 bytes (512 MB), equivalent to 268,435,456                       Unicode characters (256 mega characters)

Regards,

Charlie Liao
TechNet Community Support

Thursday, September 29, 2016 7:05 AM
• Hello David,

If the data comes from a supported data source, like a SQL Server database, then you can load the data directly into the Power Pivot data model without a Loop way over an Excel sheet; an Power Pivot can load this amount of rows.

Olaf Helper

[ Blog] [ Xing] [ MVP]

Thursday, September 29, 2016 10:51 AM

All replies

• Hi,

Since the issue is more related to PowerPivot, I would move this thread into the following forum,

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel

Sorry for any inconvenience and have a nice day!

Regards,

Celeste

Thursday, September 29, 2016 1:35 AM
• Hello,

Your question is not clear for me, may can you rephrase it, please?

Power Pivot has a highly compression algorithm and kann easily handle a few million fact data, as Long as your Client machine have enough memory and you are using 64 bit MS Excel; see About the Relativity of Large Data Volumes

Olaf Helper

[ Blog] [ Xing] [ MVP]

Thursday, September 29, 2016 6:33 AM
• Hi David,

When creating a Pivot table from a PowerPivot data model, the data was load to the worksheet first, and then you can create table or chart from it.

For the row limitation, the max row numbers in the table is 1,999,999,997.

Object

Specification / Limit

Object name length

100 characters

Invalid characters in a Name

. , ; ' ` : / \ * | ? " & % \$ ! + = () [] {} < >

Number of tables per PowerPivot database

(2^31) - 1 = 2,147,483,647

Number of columns and calculated columns per table

(2^31) - 1 = 2,147,483,647

Number of calculated measures in a table

(2^31) - 1 = 2,147,483,647

PowerPivot memory size for saving a workbook

4GB = 4,294,967,296 bytes

Concurrent requests per workbook

6

Local cubes connections

5

Number of distinct values in a column

1,999,999,997

Number of rows in a table

1,999,999,997

String length

536,870,912 bytes (512 MB), equivalent to 268,435,456                       Unicode characters (256 mega characters)

Regards,

Charlie Liao
TechNet Community Support

Thursday, September 29, 2016 7:05 AM
• Thats really why I wanted to know does the data needed to be renderd onto an excel table first as excel tables have limitations

 Feature Maximum limit Worksheet size 1,048,576 rows

how can a table of 1,999,999,997 fit on a sheet of 1,048,576 ?

Does that mean that pivot can link directly to the sql query?

Thanks

D

Thursday, September 29, 2016 7:28 AM
• Hello David,

If the data comes from a supported data source, like a SQL Server database, then you can load the data directly into the Power Pivot data model without a Loop way over an Excel sheet; an Power Pivot can load this amount of rows.

Olaf Helper

[ Blog] [ Xing] [ MVP]

Thursday, September 29, 2016 10:51 AM
• That limitation of 1,0458,576 rows applies to excel worksheets.  Power Pivot is not bound by that limitation as the storage mechanism is completely different.  Although they look similar, an Excel Worksheet is not the same thing as a Power Pivot or Tabular table. I'm not sure of the upper bound but the limitation is really dictated by how much on-board memory you have.  You should be able to import 10's of millions of rows into a power pivot table (via direct sql query) quite easily if you have enough memory.

 1,048,576 rows
Thursday, September 29, 2016 11:54 AM