none
PowerPivot Management Dashboard not updating (workbook activity chart shows "X") RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hi,

    I am facing a problem on my test machine concerning to PowerPivot. Everything seems to work ok:

    • Using the PowerPivot gallery.
    • Interacting with a PowerPivot Excel file in Excel services.
    • Using data refresh.
    • Seeing average instance CPU and memory in the PowerPivot management dashboard.

    But I am not able to get the workbook activity chart working. I see some usage records inside the WSS_Logging database in the views dbo.AnalysisServicesConnections, dbo.AnalysisServicesRequests etc. but no data can be found in the PowerPivot database. The Usage.Requests, Usage.Connections etc. tables are empty. As far as I understand Microsoft SharePoint Foundation Usage Data Processing timer job is responsible for moving the data around. I get an error SharePoint Foundation 8075 (The Usage Data Processing timer job failed...) every time the job runs.

    In the ULS logs I see the following exception logging stating that the UNION ALL dbo.AnalysisServices* views could not be updated due to a missing primary key on the respective partition tables (message is partly German as I am running a German machine (OS, SharePoint, SQL):

    EXCEPTION: System.Data.SqlClient.SqlException: Die UNION ALL-Sicht 'WSS_LoggingDB.dbo.AnalysisServicesConnections' kann nicht aktualisiert werden, da kein Primärschlüssel für die [WSS_LoggingDB].[dbo].[AnalysisServicesConnections_Partition0]-Tabelle gefunden wurde.     bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)     bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)     bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)     bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()     bei Microsoft.AnalysisServices.SharePoint.Integration.AnalysisServicesUsageDefinition.MoveRows(String selectCommandText, String insertCommandText, SqlParameter[] insertCommandParameters, String deleteCommandText, String trimCommandText)

     

    Displaying the PowerPivot Management Dashboard I get an "Error loading history for workbook history bubble chart" followed by another exception. I believe this errors is caused by the error above and hopefully vanishes as soon as I get the first error fixed:

    EXCEPTION: System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.     bei Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.GetString(Int32 ordinal)     bei Microsoft.AnalysisServices.SharePoint.Integration.WorkbookHistoryDataProvider.GetHistory(WorkbookHistoryDataSet& historyDataSet)     bei Microsoft.AnalysisServices.SharePoint.Integration.WebServices.PowerPivotOperationsServiceImpl.GetWorkbookHistory(WorkbookHistoryDataSet& history)

    Things I have done so far:

    • Checked version of ADOMD.Net
    • Reinstallation of PowerPivot
    • checked lots of log files
    • tried many suggestions on http://powerpivotgeek.com

    Now I am out of ideas. I hope someone out there can help?

     

    Regards,

    Peter

    Montag, 15. November 2010 15:36
    |

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    I have a little follow-up:

    I tried to find out what is happening when running Microsoft SharePoint Foundation Usage Data Processing timer job with the SQL profiler and saw lots of calls to the Usage.AddConnection procedure in my PowerPivot database. I copied and pasted one of the calls into SQL Server Management Studio using my SharePoint farm account for the database connection. The calls failed due to date formatting.

    Usage.AddConnection has a parameter "LogTime" that is specified as datetime. It is called with a string in ymd format (verified in SQL profiler output). Default language setting for the SQL server is German with a dateformat of dmy. So this call fails. I double checked it setting dateformat to ymd in SSMS and re-executing the call pasted from SQL profiler. This time an entry was written to the PowerPivot database.

    So this seems to be an internationalization issue? Is there any possibility to set the dateformat to ymd on connection to the PowerPivot database? Any other workaround?

    Regards,

    Peter

    Montag, 15. November 2010 17:30
    |
  • Question
    Anmelden
    0
    Anmelden

    Hi Peter,

    intresting observation! If this is reproducable my feeling is that's a bug in the PowerPivotDB Stored Proc parameter handling!?

    As you working in  testenvironment...could you temporarly change the default language of sql server (SSMS>Server>Properties>Erweitert>Standardsprache)  , restart the instance and try if usage processing works with the changed language?

    I don't know of a way to change default dateformat used in a database, but you can change it for a specific login:

    SP_DEFAULTLANGUAGE @loginame = 'LoginName', @LANGUAGE = 'Language'

    so you could change this setting for the powerpivot/ssas serviceaccount this way?

    viele Grüße aus München,

    Jochen

    Dienstag, 16. November 2010 17:15
    |
  • Question
    Anmelden
    0
    Anmelden

    Hi Jochen,

    I changed language options as proposed but that didn't fix the error. I changed lots of other settings like the LCID for PowerPivot's SSAS instance. The default region settings for the users but to no avail.

    Digging deeper into the profiler output I found the statement which causes the 4440 error:

    exec sp_executesql N'DELETE FROM [dbo].[AnalysisServicesRequests] WHERE ServiceApplicationId=@ServiceApplicationId',N'@ServiceApplicationId uniqueidentifier',@ServiceApplicationId='869E4F0D-2F2B-4F18-8AC9-85E7F1EEC343'

    This happens after a lot of calls to [Usage].[AddRequest]. If I copy & paste the delete statement in SSMS I get the aforementioned 4440 error. The tables dbo.AnalysisServicesRequests_Partition<Number> don't have a primary key and there is no instead of delete trigger. So I understand why this fails. The delete statement makes sense taking the AddRequest statements into account: After importing all requests to the PowerPivot database the requests should be deleted from the usage database. That sounds reasonable.

    Can anyone with a working PowerPivot for SharePoint installation please verify if there are primary keys on the tables (dbo.AnalysisServicesRequests_Partition<Number>) or if there is an instead of trigger on the view  dbo.AnalysisServicesRequests? Thanks!

    Peter

    Sonntag, 21. November 2010 14:41
    |
  • Question
    Anmelden
    0
    Anmelden
    I got the same issue, so if you found a resolution i'm listening :)
    Emmanuel ISSALY - Sharepoint MCTS, MCNEXT (FR).
    • Als Antwort vorgeschlagen zceer Samstag, 30. Juli 2011 20:06
    Dienstag, 26. Juli 2011 13:06
    |
  • Question
    Anmelden
    0
    Anmelden

    I ran into this as well.  I got so fed up, I un-installed Sharepoint and re-installed, thinking I had an "order of installation issue".  I re-installed everything in the right order (according to service pack release dates and SP1 install best practices), and after all that, I still got the same error on the Usage Data Processing timer job that moves data from the usage table to the powerpivot table.

    I had another Sharepoint 2010 installation that wasn't having this problem, so I ran a schema compare on the TimerJobUsage_PartitionXX tables.  And voila!  2 of the tables had constraints with "NO CHECK" specified, which was breaking the updateable partitioned views.

    Run this sql against your Sharepoint Usage database (WSS_Logging by default):

    select name, definition, is_not_trusted from sys.check_constraints where is_not_trusted = 1

    Then, run this to change all of the constraints with "WITH NOCHECK" over to "WITH CHECK":

    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    And yes, you need BOTH "CHECK" keywords in there :-).

    BTW - the tables with constraint issues seemed to be the ones WITH DATA IN THEM.  This probably indicates that one of the service packs, either SP1 for SQL Server 2008 R2 (for PowerPivot), or SP1 for Sharepoint 2010, didn't do a good enough job with the SQL and left the modified tables un-verified.  Of course, it could also have something to do with my nightly maintenance plan.  I'll have to wait until tomorrow and see if the issues magically re-appears.

    Here are the results from the above query, before I ran the second statement to "fix" the constraints:

    CK__NTEventLo__Parti__002AF460 ([PartitionId]=(21)) 1
    CK__RequestUs__Parti__00AA174D ([PartitionId]=(23)) 1
    CK__ULSTraceL__Parti__00EA0E6F ([PartitionId]=(22)) 1
    CK__SQLDMVQue__Parti__01A9287E ([PartitionId]=(23)) 1
    CK__SQLMemory__Parti__01BE3717 ([PartitionId]=(23)) 1
    CK__NTEventLo__Parti__03FB8544 ([PartitionId]=(22)) 1
    CK__ULSTraceL__Parti__04BA9F53 ([PartitionId]=(23)) 1
    CK__NTEventLo__Parti__07CC1628 ([PartitionId]=(23)) 1
    CK__AnalysisS__Parti__4E3F5E68 ([PartitionId]=(21)) 1
    CK__AnalysisS__Parti__5150D53D ([PartitionId]=(21)) 1
    CK__AnalysisS__Parti__520FEF4C ([PartitionId]=(22)) 1
    CK__AnalysisS__Parti__55216621 ([PartitionId]=(22)) 1
    CK__AnalysisS__Parti__55E08030 ([PartitionId]=(23)) 1
    CK__AnalysisS__Parti__5773C2E7 ([PartitionId]=(21)) 1
    CK__AnalysisS__Parti__58F1F705 ([PartitionId]=(23)) 1
    CK__AnalysisS__Parti__5B4453CB ([PartitionId]=(22)) 1
    CK__AnalysisS__Parti__5F14E4AF ([PartitionId]=(23)) 1
    CK__TimerJobU__Parti__6C040022 ([PartitionId]=(20)) 1
    CK__TimerJobU__Parti__6FD49106 ([PartitionId]=(21)) 1
    CK__Performan__Parti__702996C1 ([PartitionId]=(20)) 1
    CK__FeatureUs__Parti__7226EDCC ([PartitionId]=(20)) 1
    CK__TimerJobU__Parti__73A521EA ([PartitionId]=(22)) 1
    CK__Performan__Parti__73FA27A5 ([PartitionId]=(21)) 1
    CK__RequestUs__Parti__753864A1 ([PartitionId]=(20)) 1
    CK__FeatureUs__Parti__75F77EB0 ([PartitionId]=(21)) 1
    CK__SQLDMVQue__Parti__763775D2 ([PartitionId]=(20)) 1
    CK__SQLMemory__Parti__764C846B ([PartitionId]=(20)) 1
    CK__BlockingQ__Parti__770B9E7A ([PartitionId]=(21)) 1
    CK__TimerJobU__Parti__7775B2CE ([PartitionId]=(23)) 1
    CK__Performan__Parti__77CAB889 ([PartitionId]=(22)) 1
    CK__RequestUs__Parti__7908F585 ([PartitionId]=(21)) 1
    CK__ULSTraceL__Parti__7948ECA7 ([PartitionId]=(20)) 1
    CK__FeatureUs__Parti__79C80F94 ([PartitionId]=(22)) 1
    CK__SQLDMVQue__Parti__7A0806B6 ([PartitionId]=(21)) 1
    CK__SQLMemory__Parti__7A1D154F ([PartitionId]=(21)) 1
    CK__BlockingQ__Parti__7ADC2F5E ([PartitionId]=(22)) 1
    CK__Performan__Parti__7B9B496D ([PartitionId]=(23)) 1
    CK__NTEventLo__Parti__7C5A637C ([PartitionId]=(20)) 1
    CK__RequestUs__Parti__7CD98669 ([PartitionId]=(22)) 1
    CK__ULSTraceL__Parti__7D197D8B ([PartitionId]=(21)) 1
    CK__FeatureUs__Parti__7D98A078 ([PartitionId]=(23)) 1
    CK__SQLDMVQue__Parti__7DD8979A ([PartitionId]=(22)) 1
    CK__SQLMemory__Parti__7DEDA633 ([PartitionId]=(22)) 1
    CK__BlockingQ__Parti__7EACC042 ([PartitionId]=(23)) 1

    Samstag, 30. Juli 2011 20:20
    |
  • Question
    Anmelden
    0
    Anmelden

    I noticed my PowerPivot Management Dashboard data was stale today, so I dug through the logs and found that the UNION ALL error had returned. As it turns out, the Microsoft SharePoint Foundation Usage Data Import timer job is actually the culprit. It’s using bulk insert to get the data into the usage tables, and by default, bulk insert will set constraints to “not trusted” (http://msdn.microsoft.com/en-us/library/ms186247.aspx). Here’s an example from Profiler, captured during the execution of this job:

    insert bulk FeatureUsage_Partition26 ([PartitionId] TinyInt, [LogTime] DateTime, [MachineName] NVarChar(128) COLLATE Latin1_General_CI_AS_KS_WS, [FarmId] UniqueIdentifier, [SiteSubscriptionId] UniqueIdentifier, [UserLogin] NVarChar(300) COLLATE Latin1_General_CI_AS_KS_WS, [CorrelationId] UniqueIdentifier, [FeatureId] UniqueIdentifier, [SiteUrl] NVarChar(260) COLLATE Latin1_General_CI_AS_KS_WS, [RowCreatedTime] DateTime) with (TABLOCK)

    After this job runs, any tables that it added data to have their constraints set to not trusted, and not trusted (i.e. NOCHECK) constraints will prevent updates and deletes through the associated UNION ALL view.

    For now, I’m scheduling a SQLAgent job to run every night, following the execution of this job, to run “EXEC sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all’” against my usage database.

    Is nobody else having this problem?!?!

    Dienstag, 2. August 2011 19:21
    |
  • Question
    Anmelden
    0
    Anmelden

    At the moment, i did a fresh install with powerpivot on the same node as the central admin, which solved most issues i had before.

    I have no errors, but i get only system values in the dashboard (no response times / activity, and the activity chart is empty with a "X")

    All the excel files are empty, dunno if it's normal. Since i don't have much activity yet, it's perhaps normal.

     

    Configuration is SP1 (no june CU), will update.

    Emmanuel ISSALY - Sharepoint MCTS, MCNEXT (FR).
    Mittwoch, 3. August 2011 08:26
    |
  • Question
    Anmelden
    0
    Anmelden
    I don't think that's normal.  And the red X is definitely a "red flag". :-)

    Check out my post here for what I hope is the last installment of bug fixing on my PowerPivot installation. If you've taken the time to configure Kerberos authentication on your farm, then you've probably got the same "401 Unauthorized" issue I ran into with using PowerPivot workbooks as data sources.
    Joe Cole
    Donnerstag, 4. August 2011 21:54
    |
  • Question
    Anmelden
    0
    Anmelden

    I also got this error:

    EXCEPTION: System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.GetString(Int32 ordinal)     at Microsoft.AnalysisServices.SharePoint.Integration.WorkbookHistoryDataProvider.GetHistory(WorkbookHistoryDataSet& historyDataSet)     at Microsoft.AnalysisServices.SharePoint.Integration.WebServices.PowerPivotOperationsServiceImpl.GetWorkbookHistory(WorkbookHistoryDataSet& history) fd729718-53a9-4577-879b-0fe8d1742049

    I rate PowerPivot on the same lvl as SharePoint designer 2007

    Mittwoch, 7. März 2012 15:15
    |
  • Question
    Anmelden
    0
    Anmelden

    Had the same problem.
    Was resolved by installing SQL 2008R2 SP1(PowerPivot feature was not updated).http://www.microsoft.com/en-us/download/details.aspx?id=26727
    Then running the timerjobs.

    Hope this helps.

    Montag, 11. Juni 2012 12:36
    |