I am trying to implement a form of versioning for our company's SQL Analysis Services Databases.
At the moment we have a very simple drop and recreate each time we actually deploy using the SQLPS PowerShell module and XMLA, but this causes hindrances when having to reprocess large measure groups because of the database being recreated and we would like to where possible reduce the deployment window since this can impact reprocessing backlog of transactions after deployment is finished since the system needs to catchup again.
So thus i am trying to implement a form of versioning so that only when there is actually a schema or model change would the objects need to be dropped and recreated since our entire deployment process is automated then in those cases we will book a longer deployment slot.
I am trying to find out if there is any functionality that exists within SSAS itself which allows you to maybe store some text value perhaps like a version number of the databases which can then be correlated to whether we need to drop and recreate the SSAS database.
At the moment I could not find anything so my best bet so far is to rely on managing the version number of the database via its related sql database instance so then I use a tracking table within the SQL instance to check if the latest version of this release has already been deployed.
Does anyone know of any method where such custom metadata might be added to the SSAS objects other that trying to modify their names which i would like to avoid.
Does anyone know of something like this perhaps or has anyone dealt with a similar scenario and if yes, how did you approach it?
I would use Annotations. Almost all components of SSAS cube have Annotations property which is a collection of Annotation structures. It is a structure with Name property which is a key and string Value storing arbitrary string.
Good thing about annotations that its are stored in SSAS metadata and can be retrieved back from server once cube is deployed.