none
Accessing 3 million rows of table RRS feed

  • Question

  • Hi All,

    I have a question on accessing a table having around 3 million rows (preferably from Excel 2007) 

    This table is a result of ETL extracted data and want now to enable user to use this data. Therefore, I have proposed a solution to them to access it from excel 2007. So i am trying to generate pivot table which is pointing to this table on our production server

    Since this table is having approximately 3 million of data rows , the excel pivot table is taking a long time to "read" data rows. 

    Can i continue with this solution or can you suggest me some other practical solution so that user can access this data ?

    Thanks 

    Friday, October 3, 2014 5:48 AM

Answers

  • Issue resolved but i had to develop SSAS cube (and no workaround i found for browsing from excel 2007 as data volume is huge)

    Thanks

    • Marked as answer by UltraDev Friday, October 3, 2014 12:30 PM
    Friday, October 3, 2014 12:30 PM

All replies

  • Or is it possible i can access this data in chunk from excel means based on some filter like date range etc. ?

    Friday, October 3, 2014 6:09 AM
  • Or is it possible i can access this data in chunk from excel means based on some filter like date range etc. ?

    Hi,

    You can make use of Power Pivot addins for excel to work with very large dataset, it is a free addin from Microsoft.

    What is Powerpivot?

    5 Cool Things You Can Do With PowerPivot for Excel


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page


    Friday, October 3, 2014 7:00 AM
  • is it there in Excel 2007?  (power pivot)

    can you mention how to use it by giving small example ?

    PS : All i need is to connect to one single table on prod server and rest excel should take care of generating pivot and browsing data 

    Friday, October 3, 2014 7:03 AM
  • Unfortunately power pivot addin is not available in excel 2007, its available in excel 2010 and above.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, October 3, 2014 7:25 AM
  • then there is no solution to the question i have reported ?? any other solution ?
    Friday, October 3, 2014 8:45 AM
  • You shouldn't allow users to access 3 million rows - this can adversely affect the performance. Instead create a view and SELECT TOP few records (say 1,000 or 10,000) from this table. To view the data, users can add their own filters (WHERE clause) and fetch no more than 10,000 records matching their criteria, which should satisfy all their queries. Just grant access to this view to the users

    Satish Kartan www.sqlfood.com


    Friday, October 3, 2014 10:15 AM
  • You shouldn't allow users to access 3 million rows - this can adversely affect the performance. Instead create a view and SELECT TOP few records (say 1,000 or 10,000) from this table. To view the data, users can add their own filters (WHERE clause) and fetch no more than 10,000 records matching their criteria, which should satisfy all their queries. Just grant access to this view to the users

    Satish Kartan www.sqlfood.com


    For example, the view can be defined as below and users have to specify the WHERE clause (select colA, colB from uv_V1 where colA = 'blah')

    create view uv_V1 as 

    select top 10000 colA, colB from TableName


    Satish Kartan www.sqlfood.com

    Friday, October 3, 2014 10:24 AM
  • why does anybody needs to see 3 million data at a time? isnt it better to parameterize queries and filter the data to show only the needed part?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 3, 2014 10:43 AM
  • then there is no solution to the question i have reported ?? any other solution ?

    You mentioned you proposed Excel 2007. Why not propose a later version so that your users can use PowerPivot to analyze a large dataset like this?  This question is really about self-service BI for end users rather than SSIS.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, October 3, 2014 11:48 AM
  • Issue resolved but i had to develop SSAS cube (and no workaround i found for browsing from excel 2007 as data volume is huge)

    Thanks

    • Marked as answer by UltraDev Friday, October 3, 2014 12:30 PM
    Friday, October 3, 2014 12:30 PM
  • So in summary UltraDev,

    If you point your Excel 2007 workbook to a view instead of a table you will prevent 3 million rows being accessed everytime the user of this work book needs to access this work book. Accessing very large data sets over a network is like to have a negative impact on other people accessing the same network.

    http://msdn.microsoft.com/en-gb/library/ms187956.aspx

    Ideally as previously mentioned you need to try and upgrade to Excel 2010 which would enable you to use Power Pivot. This enables you transfer a secure copy of large data sets to your desktop so you don't have the same network traffic issue.

    Getting back to Excel 2007 and SQL views I recommend an array of worksheets within your work book. Each work sheet points to a different view, e.g. UK only customers, US only customers, total sales based on country, etc.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Friday, October 3, 2014 12:37 PM