Error Archiving OLAP database...

Last Post 04 Aug 2004 06:27 AM by nutjob. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Alan_SQL
New Member
New Member

--
23 Feb 2004 04:32 PM
SQL 2000 Server\Analysis SP3
Windows 2000 SP3
OLAP databases are stored in a MOLAP structure

I have a scheduled job that backs up the OLAP database daily using the msmdarch.exe utility. Its been running successfully since our implementation of SQL Server Analysis last year. It has recently failed.

I've went directly on Analysis Services and attempted to archive the database through the UI interface and received the following error:

"one of the data files is too large to be stored inside a .cab file"

The file that the error is referencing is over 2.1 GB in size.

Has anyone ran into this issue? If so, please point me to the right direction. I need a temporary resolution until I'm able to test and apply long term solutions.

Your help is very much appreciated.
satya
New Member
New Member

--
25 Feb 2004 01:19 AM
Have you tried using using the msmdarch command from command prompt?
Alan_SQL
New Member
New Member

--
25 Feb 2004 01:02 PM
Thanks for your responses.

Satya: I am using msmdarch.

RM: thanks for pointing me out to that article. That is exactly what my problem is. Unfortunately, it does not look like its labeled as a bug with Microsoft.

Do you folks have any suggestions as to how I can go about archiving\backing up these files? and be able to restore them without having to fully processed the cube?

As it stands at the moment, it looks like the only way to recover in case of failure is to reprocess the cube from Warehouse DB which is long process.

I think my only options are to implement the long-term solutions, partitioning the data in the cube and using HOLAP rather than MOLAP.

Any additional suggestions or comments to this post is much appreciated.
bigelectricmac
New Member
New Member

--
26 Feb 2004 01:34 PM
Partitioning cubes by time might be your quickest option plus it could increase query performance. The Feb 2004 SQLMAG issue has a great article on partitioning. I would avoid HOLAP if possible unless the relational database is on the same server as Analysis Services or you have a super fast relational database.

I found the below passage at http://support.microsoft.com/defaul...-US;249698

"In situations in which a large data file prevents use of the archive utility for backing up OLAP Services databases, another backup utility, such as Windows NT Backup, should be used to make backups of all files in the OLAP Services Data folder. "

I haven't tried this but it seems logical and it wouldn't require you to reprocess the cubes.

Good luck and let us know what happens
Alan_SQL
New Member
New Member

--
27 Feb 2004 11:02 AM
Thanks Anthony. Data partitioning is definitely in our plans. As for switching from MOLAP to HOLAP, its not a definitive solution yet. One of the reasons for switching to HOLAP is due to the following:

1. Most of our queries rely on the aggregations that are set and does not require a need to go all the way to the fact line to retrieve a value.

2. Due to number 1, I found having to store the duplicate values that is in our warehouse and in the MOLAP cube seems to be a waste of space. Based on my current calculations, it'll save at least 15% in disk space if I switch to HOLAP because a fact file is no longer created for the cube.

However, switching to HOLAP is still in the testing phase and I rely on the Usage Analysis and Based Optimizations to create my aggregate values to make my queries faster and at the same time saving disk space.

I will also take a crack at the suggestions on that article. However, it points to a SQL 7 version. Hopefully, someone who has tried it in SQL 2000 would give some insight prior to me spending time on it. :-D.

jpearce
New Member
New Member

--
02 Mar 2004 05:03 AM
This is a known issue, and there are several KB bulletins related to this issue. The recommened solution is to copy the entire OLAP database structure, including the repository (generally an Access database), to the archive location.

Unfortunately, this didn't work for me when I needed to move a large database to another server, since I was only moving one of the databases and not all of them. I ended up reading through the repository, extracting the entried for the database that I wanted, and transferring the associated files and repository entries to the other server. This works, but is pretty laborious.
Alan_SQL
New Member
New Member

--
03 Mar 2004 03:14 PM
Thanks for your reply jpearce.

Could you please point me to those KB bulletins. The only one I was able to find was the one referenced by RM.

I'm interested in finding out more details in copying the entire OLAP structure as a form of a backup.
nutjob
New Member
New Member

--
04 Aug 2004 06:27 AM
I assume you have found a solution by now so for future refernence, I have been directed to this link but have not try the solution

http://support.microsoft.com/defaul...-us;295443
You are not authorized to post a reply.

Acceptable Use Policy