Long running analysis view creation

troark
troark Member Posts: 3
edited 2006-05-12 in NAV/Navision Classic Client
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

Comments

  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    RAID 5 should perform better when just reading data is it not?
    Marije Brummel | Freelance Business Central Specialist
  • ara3n
    ara3n Member Posts: 9,258
    Mark by setup he means also creating the analysis view, which involves writing.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Yep, that is true, and that means writing, so RAID 5 is wrong... :?
    Marije Brummel | Freelance Business Central Specialist
  • troark
    troark Member Posts: 3
    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
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories