Answered by:
Power Pivot refresh

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/- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, September 25, 2013 12:52 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, November 25, 2013 7:08 AM
Thursday, September 19, 2013 2:00 PMAnswerer
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 NijsSunday, 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 workbookDim 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
NextEnd Sub
Sub PrintPivotTables()' Disable pivottable refresh on open
On Error Resume NextDim 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- Proposed as answer by Challen Fu Tuesday, January 3, 2012 5:53 AM
- Marked as answer by Challen Fu Monday, January 9, 2012 3:01 AM
- Unmarked as answer by ScottfromVendorMAX Wednesday, January 11, 2012 9:54 PM
- Unproposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, September 18, 2013 9:14 PM
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/- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, September 25, 2013 12:52 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, November 25, 2013 7:08 AM
Thursday, September 19, 2013 2:00 PMAnswerer