October 7, 2010 at 6:00 am
Here's a sample path for where your SSAS database could be located. Check this and see if you can find the .cub and .dim files associated with the cube. Is it there?
D:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db
I still think your best bet is to try to get DDL triggers to log detaches and deletes to a table. It certainly beats a 24x7 trace that will slow down your production server. But I'm about to the point where I can get back to playing with SSAS, so I'll experiment and see what I can find.
October 7, 2010 at 6:53 am
I still would check this out.
Because there are other ways to delete the files / structure than being an Admin for SSAS, this gets you to the file system where the structure is located.
6 cooks in the kitchen - do they each have their own project?
And do they each have their own test / dev cubes, or are they sharing?
There are only 2 of us here, and we keep these activities separate.
Both of us in the production project at the same time can cause problems.
So some of this could be just the number of Admins and how they manage it.
It sounds like that will be handled much differently in production.
Greg E
October 7, 2010 at 7:04 am
Brandie Tarvin (10/7/2010)
Here's a sample path for where your SSAS database could be located. Check this and see if you can find the .cub and .dim files associated with the cube. Is it there?D:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db
I still think your best bet is to try to get DDL triggers to log detaches and deletes to a table. It certainly beats a 24x7 trace that will slow down your production server. But I'm about to the point where I can get back to playing with SSAS, so I'll experiment and see what I can find.
Is there such a thing as DDL triggers for a Analysis Services?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 7, 2010 at 7:39 am
Brandie Tarvin (10/7/2010)
Here's a sample path for where your SSAS database could be located. Check this and see if you can find the .cub and .dim files associated with the cube. Is it there?D:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db
I still think your best bet is to try to get DDL triggers to log detaches and deletes to a table. It certainly beats a 24x7 trace that will slow down your production server. But I'm about to the point where I can get back to playing with SSAS, so I'll experiment and see what I can find.
Note that the path can be influenced by version of SSAS, instance, 32 or x64 bit, etc.
My path on my laptop is this.
C:\Program Files\Microsoft SQL Server\MSAS10.GTEDEV\OLAP
I agree with Alvin's question - DDL triggers in SSAS? There are DMVs for SSAS.
There are more DMV's associated with 2008 and greater versions.
What I don't know is if you have only SSAS installed and not SQL.
So if you can influence the version, besides performance gains, there are management enhancements.
You have much greater insight into performance.
We just moved up to SQL 2008, running on W2008 R2, and are looking at moving up to SQL 2008 R2.
Between the OS and SSAS 2008, we are much happier.
Memory management is much better.
What version are you running?
But my suggestion will not care about DDL triggers, and would cover any deletion.
One thing you would want to do though is test using all the different methods that could delete the structure.
That would also be the case with DDL triggers - you would have to cover all the methods one could use to delete structure.
Some could occur under the user, while some could be under the ID SSAS runs as.
Greg E
October 7, 2010 at 11:01 am
mnDBA (10/5/2010)
From time to time, one of our SSAS databases disappears. I am trying to track down the who/when/how with regard to this happening.I could trace all activity against the given DB, but that is going to be a huge trace.
1: When you say disappears, what exactly do you mean? Is the db no longer visible to users, or in management studio?
If its not visible to users there is an easy resolution to that (well not easy when it happens to a prod server the first time)
Using SQL Server Management studio, connect to {server name} server Analysis Engine.
Expand Databases -> Cubes -> Roles
Right click on "User Group" select Properties
Verify that the user group has the following check boxes set under "Set the Database permissions for this role:"
Full Control
Process Database
Read Definition
Click OK
If you updated the roles, the users will now be able to view the Cubes.
If the SSAS database is no longer visible in management studio.
Check the file system for the db files, see if somehow they got deleted.
if the files are in tact Restart Analysis Services
Tracing SSAS is a different ball of wax....
Hope this helps
October 7, 2010 at 11:54 am
Stephan Douglas (10/7/2010)
If the SSAS database is no longer visible in management studio.
Check the file system for the db files, see if somehow they got deleted.
Stephan, I found out this morning that looking for a db file doesn't work. You have to look for .cub and .dim files under the OLAP folder. There is no standard .mdf or .ldf (at least not on my box) for an SSAS database.
mnDBA, Hence my last post with a file path to check. You really need to check there to see if those files exist or not. The folder would be named something along the lines of MyDB.0.db
October 7, 2010 at 12:04 pm
Yea, sorry about not being more specific.
You can find / configure the location of the files through Management studio.
Connected to the SSAS server
Right click, scroll down to properties
Show Advanced properties, check box
AllowedBrowsingFolders=semi colon delimited path to files (cube files)
DataDir=Path Dimension and security files
It is advisable to move the cubes and dimensions to drives where they will not max out the drive space, if that happens then the cubes will go offline and need to be rebuilt.
October 7, 2010 at 12:29 pm
Stephan Douglas (10/7/2010)
Yea, sorry about not being more specific.You can find / configure the location of the files through Management studio.
Connected to the SSAS server
Right click, scroll down to properties
Show Advanced properties, check box
AllowedBrowsingFolders=semi colon delimited path to files (cube files)
DataDir=Path Dimension and security files
It is advisable to move the cubes and dimensions to drives where they will not max out the drive space, if that happens then the cubes will go offline and need to be rebuilt.
If you run out of disk space, windows will start complaining. Since one of the things mentioned was if SSAS was cycled, an email or alert would be sent, I would sure hope it wouldn't be that simple. Especially since a redeploy and process seems to be the restore method.
But you never know.
That does remind me - if you install to C: drive, over time, windows updates tend to take up quite a bit of space.
And depending on the server OS, this can be ugly to fix.
Greg E
October 7, 2010 at 12:34 pm
That's good information, Stephan. I didn't even see that there. Thanks.
October 8, 2010 at 6:52 am
Okay. I've tested the DDL trigger and it does not work against SSAS databases. Unfortunately.
I have it straight from the horse's mouth that when reprocessing occurs, there is data file transfer. The new file(s) slide into place, replacing the old file(s). But that's not an actual delete. Of course, in this case there might be something not allowing the new file to come up?
October 8, 2010 at 9:23 am
Brandie Tarvin (10/8/2010)
Okay. I've tested the DDL trigger and it does not work against SSAS databases. Unfortunately.I have it straight from the horse's mouth that when reprocessing occurs, there is data file transfer. The new file(s) slide into place, replacing the old file(s). But that's not an actual delete. Of course, in this case there might be something not allowing the new file to come up?
What you are describing by things "sliding into place" is why we sometimes find a need to physically delete the folder structure, deploy, then process the cube. We know for sure that we will not have any issues.
Did you look at file watcher? Would you consdier this a valid option?
Greg E
October 8, 2010 at 9:31 am
Greg Edwards-268690 (10/8/2010)
Did you look at file watcher? Would you consdier this a valid option?
Greg, I'm not the OP. I was just testing a suggestion I made earlier in the thread.
October 8, 2010 at 11:20 am
Brandie Tarvin (10/8/2010)
Greg Edwards-268690 (10/8/2010)
Did you look at file watcher? Would you consdier this a valid option?Greg, I'm not the OP. I was just testing a suggestion I made earlier in the thread.
I understand that.
I'm just seeing if after your investigation, would you consider this a viable option.
Or would you have a better suggestion?
Maybe there is a SQL solution, but I tend to sometimes look outside the box.
This would seem to me to solve the problem unless there is a compelling business reason it couldn't be used.
If nothing else, could be a temporary solution to at least find out what is causing the problem.
Greg E
October 8, 2010 at 11:39 am
Oh. Sorry. I'm having a weird Friday.
I have not yet tested File Watcher. I'll check into that as soon as I get a chance. Have you tested it yet?
October 8, 2010 at 1:44 pm
Brandie Tarvin (10/8/2010)
Oh. Sorry. I'm having a weird Friday.I have not yet tested File Watcher. I'll check into that as soon as I get a chance. Have you tested it yet?
It was one of the first .Net samples I played with.
So yes - used it before, and No, not specifically for SSAS.
You can monitor any number of events - from access to change and delete.
And as I see it through my rose colored glasses, wtih the multiple ways and people that could cause this problem, a low level file tool is a viable solution. Maybe there is a reason it couldn't be used, but I would hope SQL and a DDL trigger because I am a DBA is it.
The end result may be more important than the method.
But it is Friday, and it is spectacular outside. So I am losing my mind. 😛
Have a great weekend.
Greg E
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply