Long running analysis view creation

troark

I'm new to Navision so bear with me if I don't make alot of sense.

Environment: I am running ver 4.01 on SQL Server 2000 sp4. The server is running Windows Server 2003 Standard Edition SP 1 and has 4 2.8ghz cpus with 2 gig of ram. The database is 30 gig with data and log files stored on separate RAID 5 drive arrays. The database has 10 datafiles (I didn't set it up, so I'm not sure why) all stored on the same RAID 5 drive.

Issue: When the user sets up an Analysis View it takes over 24 hours to complete. The requirements for the view are that there be no date compression and the timeframe is life to date (9 years). There are 8 demensions defined, if that makes a difference. The question is, does that run time sound reasonable?

We are considering warehousing the data, but for now the reports are run against these analysis views and this generation time is unacceptable. We have already gone to restoring the database on a second server in order to not interrupt the general user community.

Thanks for any insights.
T

DenSter

One of the issues is your RAID 5 setup. You should have better performance on 1+0. Another issue is the high number of dimensions.

Marije_Brummel

RAID 5 should perform better when just reading data is it not?

ara3n

Mark by setup he means also creating the analysis view, which involves writing.

Marije_Brummel

Yep, that is true, and that means writing, so RAID 5 is wrong... :?

troark

Well, I agree that RAID 10 would be better, and that is a possibility. However, would it make a huge impact on the 24 hour generation time? Ideally we would like to run these overnight, which would be roughly 12 hours.

ara3n

Standard Navision limits dimension for analysis view to 4. The reason was the performance. it looks like your customization is taking longer ofcourse.

Try to run the routine on the server. Run the client on the server to update the analysis view.

kriki

And another issue is that in the primary key of tables 365:"Analysis View Entry", 366."Analysis View Budget Entry" (if you have budgets) there are a lot of fields AND there are SIFT-fields on it that are maintained on EACH level.
Try to NOT maintain the SIFTIndex on SQL. It will go a lot faster.
In case after the calculation, checking the data is slow, enable the SIFTIndex again. Calculating all in 1 go will be a lot faster than 1 by 1.