quinn.jay (10/28/2016)
PB_BI (10/28/2016)
quinn.jay (10/27/2016)
All dev, Test and Prod is all on the same one instance.You can turn on the Olap Query Log (https://technet.microsoft.com/en-us/library/cc917676.aspx) and let that run for a week or so and then query it to see what is being used.
You can use extended events to capture any queries coming in to your cubes (https://blog.crossjoin.co.uk/2016/04/18/profiler-extended-events-and-analysis-services/) and then analyse the output of that after a week or so.
Yes this helps, thanks. I went hunting, and found that the OLAP query logs show to be turned on, but can't find the table anywhere. I like this option that you mentioned. I'll get with the DBA to see whats up.
Meanwhile, how much of a performance hit is this? Can it feasibly be left on all the time and and manage history kept?
It's generally minimal, but the QueryLog Sampling number has to be set right for your environment and your needs. The lower the number the more it will log, so if you have 1000 users 24/7 and this is set to 1 then it might cause an issue. I would speak to your DBA to see what number they are comfortable with.