SharePoint 2013 and PowerPivot 2012: why are there so many data model databases?

Problem: My customer sent me a screenshot of their SQL Server Management studio Analysis Services database listing. They were concerned with one of the database model tables appearing multiple times, in this case around 20 times. They asked me how can they determine which of these copies is the one that is active?

Just so we are on the same page, when using PowerPivot with SharePoint, a workbook will generate a unique data model database in SQL when the workbook is refreshed, for example, in the browser.  The DB I am referencing looks something like this:

So in my customer's case they saw something that looked like this in SQL Server Management Studio:

my_ExcelWorkbook_xyz5625496587561e052f83b_15a8aa37cc3asdf92e9db_SSPM

my_MyStatistics_abc2568941254471411e052f83b_56b98euicc3ckju56bcpw_SSPM

my_MyStatistics_abc2568941254471411e052f83b_99avlo3cc3lloik8767s77v_SSPM

my_MyStatistics_abc2568941254471411e052f83b_65h6567aloim5625s97vx_SSPM

my_MyStatistics_abc2568941254471411e052f83b_115685djxytm324t652jjw_SSPM

etc

so they were concerned with the my_MyStatistics_GUID_GUID_SSPM appearing so many times

Explanation: I have to give a shout out to Rick Andring who explained this phenomenon in detail. The answer to the customer was that ALL of the copies are active. The databases are based on user sessions and will be purged from the system once the user session expires. Each one of the copies is literally a separate model for the same workbook. The first GUID is the model GUID and the second is the session GUID for the user. He explained that he sees this happen when workbooks are configured to refresh when they open:

In short, these models clean themselves up eventually.

This can also happen on heavily used workbooks where users are refreshing workbooks manually (not just refresh on open), or workbooks that are set to refresh on open and are imbedded into Excel Services web parts.

If this is a concern, you can check the workbook to see if it is indeed configured to refresh when it opens and disable it. Then you can schedule the workbook to refresh daily via PowerPivot Scheduled Data Refresh. If people are refreshing this manually, then another strategy may be needed.

If you need a quick way to flush the models, simply restart the SSAS instance and the models will rebuild when the workbook is opened. But remember that new models will be rebuilt for every user that refreshes the workbook and you will end up in this same situation.