MDSCHEMA_CUBES DMV not returning all cubes

Categories: Blog Business Intelligence Microsoft SQL Server SSIS SSRS

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentation to feed the foreach loop.

However there is a major problem with the DMVs. They only list processed cubes (and their dimensions). So how do you get a list of unprocessed cubes (and their dimensions)?

Answer? Using the Analysis Management Objects (AMO). This will return the same list as SQL Server Management Studio (SSMS), rather then just the processed, like the DMV or Excel lists. I’m currently trying out a few ideas to find the best solution – so as a script component (source) or a stored procedure. As the saying goes, it’s production ready, but its not GitHub ready – not yet anyway.

Thanks again to Chris Webb for pointing me in the right direction

SSMS might well be using AMO or something similar from the admin interface

— Christopher Webb (@Technitrain) May 18, 2016