locked
Power Pivot refresh RRS feed

  • Question

  • Hi

    loving PP - game changer for BI in my opinion..I have built great dashboard but always get the following error

    "Initialization of the data source failed"

    On any intial workBook data refresh-all AFTER a PP data-refresh all back to my server-

    I have about 10pivots in the book - and I get about 3 of these errors

    happens on first refresh only - If I then refresh again its all good - ie no errors - like its now "cleansed"

    please advise how to remove ongoing

    cheers

    Sunday, January 1, 2012 9:24 AM

Answers

  • Hi Scott

    I have seen this error on PowerPivot based workbooks that use lots of Pivot Tables.  I know 'lots' is not a very clear figure, but depending on the available memory, complexity of DAX formulas and amount of data on the pivot tables, I have gotten this error on well designed and DAX error-free expressions.

    In the past I have gotten around the error by reducing the amount of pivot tables on the workbook or by publishing the model to SharePoint and consuming it as an SSAS source; in that way, the 'heavy lifting' is done on the server and Excel can free resources and perform in a more reliable way.




    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, September 19, 2013 2:00 PM
    Answerer

All replies

  • Hi Scott

    Check your data connections. Probably you still have one or more data connections you are no longer using and for which you no longer have authorization to access them. Remove these connections or change the connection string.


    Eddy Nijs
    Sunday, January 1, 2012 12:15 PM
  • Hi Eddy

    I only have one connection to SQL - although lots of local connections are listed - but none of these are used. I have checked each one and I cannot Edit any of them.

    and the error is not happening in my server data  refresh - its happeing in my Pivot refresh - (unless they you knew one affects the other).

     

    thanks...

     

     

    Sunday, January 1, 2012 10:54 PM
  • Scott

    I don't know the reason why you get this error. Next procedure may help you to determine which pivottables is causing troubles so you can focus on that specific PowerPivotTable. The macro can also be handy to disable the refresh of pivottables on file opening.

    The second macro is just listing the pivottables in your workbook and on which worksheet they reside together with their source(table). For this macro you need to add a worksheets with label name 'Docs'.

    What operating system and version of MS Excel (32/64bit) / PowerPivot are you using?

     

    Sub RefreshAllPivotTables()

    ' Refresh all Pivottables in workbook
    ' Disable pivottable refresh on open of workbook

    Dim w As Worksheet, p As PivotTable
    For Each w In ThisWorkbook.Worksheets
        For Each p In w.PivotTables
           
            'p.PivotCache.RefreshOnFileOpen = False
            MsgBox "PivotTable " & w.Name & "." & p.Name & " is going to be refreshed", vbOKOnly
            p.PivotCache.Refresh
        Next
    Next

    End Sub

     


    Sub PrintPivotTables()

    ' Disable pivottable refresh on open
    On Error Resume Next

    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim p As PivotTable
    Dim i As Long


    Set w2 = ActiveWorkbook.Worksheets("Docs")

    i = 5

    For Each w1 In ThisWorkbook.Worksheets
        For Each p In w1.PivotTables
            i = i + 1
            w2.Cells(i, 2) = w1.Name
            w2.Cells(i, 3) = p.Name
            w2.Cells(i, 4) = Mid(p.RowRange.PivotField.Name, 2, InStr(p.RowRange.PivotField.Name, "]") - 2)
           
            p.PivotCache.RefreshOnFileOpen = False
        Next
    Next
    End Sub


    Eddy Nijs
    Monday, January 2, 2012 11:49 PM
  • Hi Eddie

    sorry ive been away over Xmas and New year.

    The macros above suggest about 6/10 of my Pivots act like this - and I can replciate error every time. If  I refresh data IN pivot from server and then refresh worksheet tables the first time I always get the error - and the second time none.

    I am using excel 32 bit.

     

    Thanks

     

     

    Wednesday, January 11, 2012 9:56 PM
  • Is this still an issue? 

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 9:14 PM
  • Hi Scott

    I have seen this error on PowerPivot based workbooks that use lots of Pivot Tables.  I know 'lots' is not a very clear figure, but depending on the available memory, complexity of DAX formulas and amount of data on the pivot tables, I have gotten this error on well designed and DAX error-free expressions.

    In the past I have gotten around the error by reducing the amount of pivot tables on the workbook or by publishing the model to SharePoint and consuming it as an SSAS source; in that way, the 'heavy lifting' is done on the server and Excel can free resources and perform in a more reliable way.




    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, September 19, 2013 2:00 PM
    Answerer