locked
Power Pivot RRS feed

  • 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 Chenchen Li Thursday, September 29, 2016 1:36 AM PowerPivot
    Wednesday, September 28, 2016 3:47 PM

Answers

  • 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)

    Reference
    https://msdn.microsoft.com/en-us/library/gg413465%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    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)

    Reference
    https://msdn.microsoft.com/en-us/library/gg413465%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    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 ?

    source https://support.office.com/en-us/article/Excel-specifications-and-limits-CA36E2DC-1F09-4620-B726-67C00B05040F

    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