Usage Reports will not open in SharePoint 2013

Question

All of my analytic's are running in my farm, but when I open the Usage reports I am getting "we found a problem with some content ins usage.xlsx, Do you want us to try and recover..."

So I cant see any of my popularity trends and it is vexing me to why. If I go to popularity reports and look up top queries by day and by month... all of those excel files open fine. Number of queries opens fine as well. Only the Usage.xlsx file is not opening.

Is it the table that SharePoint creates that is causing the issue? How do I change this?

I've followed all of the suggested actions from technet

• the logs show recent files
• the event logs have recent files
• Reports feature is activate
• the timer jobs are running fine
• i tried downloading the file and changing the '.xls' instead of '.xlsx' to no avail
• the databases appear to have data.
• I have restarted the timer jobs repeatedly to get them to attempt more
• I actually downloaded a program that opens excel files that are corrupted and it was able to open the file and it showed there was data in the file but the charts did not work.

still when I open the file it says that it is corrupted. What interests me about the issue is that all of the other reports work but the usage report does not.

I think I am getting close. It must be something with the compression of the file, so I am trying to reset that compression by using the anaylitic's plug in using the following code:

#Set up the Anaytics powershell environment
Add-PSSnapin Microsoft.SharePoint.PowerShell
Add-PSSnapin hostcontrollerpssnapin
Add-pssnapin junopssnapin
Add-pssnapin searchcorepssnapin
Add-pssnapin enginepssnapin
Add-pssnapin analysisenginepssnapin
$env:CERES_REGISTRY_PRODUCT_NAME = "Office Server\15.0\Search\Ceres"
Connect-System -Uri (Get-      SPEnterpriseSearchServiceApplication).SystemManagerLocations[0] -ServiceIdentity     (Get-SPEnterpriseSearchService).ProcessIdentity
Connect-AnalysisEngine -NodeName AdminComponent1


Get-Command -Module analysisenginepssnapin

Start-Analysis UsageAnalytics

Start-Analysis SearchReports



Set-AnalysisConfiguration

this is where I run into trouble, there is disable binary format and disable compressed format that I both want to set to "true" from their current state "false". This may or may not fix the issue, but I am hoping. The trouble I run into is that we cant figure out how to set it to false using the set command above

update: So I tried deleting the service application than provisioning it, I then ran the reports again using the analytic's engine and still came up with no change. The search reports still work but when I click on the usage reports they come up with this excel file is corrupted. Now I did find the second part of the It Unity article which was short and did not cover the changing of thecompressed format but that is just a shot in the dark to wheter it works anyways.

Update:

So I deleted the usageapplication and turned off reporting on every single site in the portal. Still didnt work. The excel file still opens corrupted. I even changed the location of the log files from \usagelogs to \usagelogs2, so that I would have fresh data

A couple of interesting points from it though

1. with the usageapplication deleted and reporting turned on the excel file is still corrupted 2.reinstated usagepplication with new DB and then enabled reporting. First attempt on excel... still corrupted.

I recently created a QA enviroment using the same databases and the excel file downloads fine and can be opened.

Below is the first bunch of lines from the hex to show what is going on with the excel file:

Below is the configuration of usageanalytics that I got from using the analysis snapin:

Below is a image of the exiftool results:

Answer 1

So.. fixed the problem. Metalogix was broken. I recently got a QA environment set up where we had the confidence to repair Metalogix. Magically, usage reports were working again.