I have several SSAS cubes which are processed daily. This is done with a SSIS project which is scheduled to run daily at a set time. Sometimes we have ETL issues and my processing job chugs ahead without knowing that the underlying tables are incomplete. Depending on the nature of the ETL error, sometimes processing completes successfully, but with stale data; sometimes I get a SSAS error.
I want to optimize the processing jobs so that it will check/wait until the objects that a given cube is dependent on are done extracting before sending the process command to the cube.
SSAS is 2016, SQL server is 2014. The cubes are using Query binding in the data source view, and I am extracting metadata from the XMLA script that is generated during the deployment.
A potential solution I thought of is to parse out the query information in the XMLA's data source view node. That would complicate my deployment/metadata extraction process quite a bit, but would be worth it if there is no other way. I have also searched through the DISCOVER_SCHEMA_ROWSETS DMV, and I could not find a DMV that would return these DSV queries, although I am not very familiar with the DMVs, so I may have missed one.
It also should be noted that I am lazy and do not want to duplicate development work by maintaining a index of dependencies separate from my SSAS projects. This solution needs to programmatically pull the data, and not rely on me keeping something updated because I am a unreliable human.
How do you ensure your cube processing starts after the dependent objects are extracted? Do you programmatically identify the SQL objects that a SSAS 2016 cube is dependent on? If so, how do you do it, and what are the pros and cons to your approach?
