October 27, 2016 at 12:45 pm
Hello,
I'm still somewhat new'ish to SQL Server and the SSIS and SSAS stacks. I taking over an recently built SQL Server 2012, VS 2013. All dev, Test and Prod is all on the same one instance.
When I logon to the Server, and then logon to the SSAS service, expand Databases, I see all the active cubes which the user community connects to from Excel and PowerBI. I also see every test and practice cube ever made, and it's really becoming a mess, and eating up space.
I can tell which are active or not, or are truly a production cube. What is the best method and practice to delete these and recover space, and not foul up the works?
Thanks,
JPQ
October 28, 2016 at 3:11 am
quinn.jay (10/27/2016)
All dev, Test and Prod is all on the same one instance.
There's your problem. Remedy this and you won't have an issue. 😉
There are a few ways in which you can see the usage of each cube and from that determine which cubes you can safely archive/migrate away from the instance.
The first is a simple profiler trace. You'd have to leave it on for a few days probably and then output it to a table and analyse it. Not pretty but it'll get you there.
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.
These methods would involve communication to the users giving them a moratorium of the aforementioned time frames to use the cubes that they need or else they will be archived.
Finally, if you have a list of SSAS users (through a security setting, for example) then you could simply ask them.
Hope this helps 🙂
October 28, 2016 at 8:29 am
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?
October 28, 2016 at 10:40 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply