November 7, 2018 at 1:20 pm
Our auditors want to see a yearly list of everything that has changes about how our data warehouse works. This is NOT about data changes. This is about schema changes, job changes, cube changes, etc. For example, they want to be able to see when a change was made to a certain calculation and who made the change. I've set up Audit Logging to track changes to database objects, permissions, etc. But how do I audit changes to SSAS cubes? Can I use the same Audit Log technique and have it audit changes to all database objects on my SSAS server? Is there a better way?
For reference: We are using SQL Server 2016, Enterprise Edition.
Thanks.
November 7, 2018 at 1:55 pm
JustJay - Wednesday, November 7, 2018 1:20 PMOur auditors want to see a yearly list of everything that has changes about how our data warehouse works. This is NOT about data changes. This is about schema changes, job changes, cube changes, etc. For example, they want to be able to see when a change was made to a certain calculation and who made the change. I've set up Audit Logging to track changes to database objects, permissions, etc. But how do I audit changes to SSAS cubes? Can I use the same Audit Log technique and have it audit changes to all database objects on my SSAS server? Is there a better way?For reference: We are using SQL Server 2016, Enterprise Edition.
Thanks.
Some can depend on your company. I've worked at places where no changes are allowed in production without a change ticket. Change ticket went through a process with the change advisory board, had the scripts, schedule, outcomes, etc. If you have some type of ticket system like that, you can use reports from those to give to the auditors.
Some other trace options would likely require a bit of trial and error on your side but it's doable. You can run traces against SSAS as you do against SQL Server - it's xmla instead of t-sql but the same basics still apply. There is also an ASTrace tool that you can use for server side traces of SSAS as well as extended events. So there are different options.
Information and download for ASTrace:
AS Trace
Some info on using extended events for SSAS:
Audit Log for Analysis Services
Sue
November 7, 2018 at 2:47 pm
JustJay - Wednesday, November 7, 2018 1:20 PMOur auditors want to see a yearly list of everything that has changes about how our data warehouse works. This is NOT about data changes. This is about schema changes, job changes, cube changes, etc. For example, they want to be able to see when a change was made to a certain calculation and who made the change. I've set up Audit Logging to track changes to database objects, permissions, etc. But how do I audit changes to SSAS cubes? Can I use the same Audit Log technique and have it audit changes to all database objects on my SSAS server? Is there a better way?For reference: We are using SQL Server 2016, Enterprise Edition.
Thanks.
Make sure that all changes are deployed from source control. And not through any other means.
If you can prove that, your auditors should only need to see a suitable report, from your VCS of choice.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply