Post reply

Reporting on a busy database with lots of records

  • August 31, 2009 at 3:38 pm

    #214062

    Hi all,

    I have a database that I need to create reports on (using SSRS) that is very busy, so I don't want to impact the live data at all. What I'd like to do is copy the data off to another server periodically (probably every 5 to 10 minutes), massage it somewhat, and report on it from there.

    Can anyone suggest a good book that could point me down this road? I don't have much training in SQL... I've mostly picked it up as I've gone along. Our main SQL admin is a contractor and we've had to cut his hours down to nearly nothing so it's all on me right now, eek!

    Thanks!

    Beverley

  • August 31, 2009 at 3:53 pm

    #1047198

    Would SSIS / ETL be a good solution, do you think?

    http://www.developer.com/db/article.php/3497511

  • August 31, 2009 at 4:29 pm

    #1047215

    Yes. If you want to copy data to another DB and transform it in the process, SSIS is the tool of choice.

    However, if won't be easily picked up if you are not fairly proficient in working with SQL Server. Has your reporting database already been designed, or do you have to start from scratch and develop the reporting solution from the ground up?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • August 31, 2009 at 11:02 pm

    #1047307

    I'm great at writing queries 🙂 I started out as a programmer, and I've learned SQL as I went along, with a couple of courses here & there. I don't have much structure to worry about in my reporting database -- a couple of schemas to hold different types of data, and then a bunch of stored procedures & views (also using schemas to separate them) for the SSRS reports I've already written.

    So I'm thinking I'll use this process to bring the data (mostly intact, but with some pre-processing / denormalizing / etc) over, drop it in yet another schema named after the application whose data this is, and then use my normal method of views (if any are necessary at that point) and stored procedures to report against it.

    I've requested a couple of likely-sounding books from the library, but if anyone has any great books to recommend (or websites) I'd appreciate it!

  • September 1, 2009 at 5:01 am

    #1047399

    I'd look for "A Dimensional modelling Manifesto" by Ralph Kimball, a great start to designing reporting systems using dimensional modelling. In fact anything by Ralph Kimball on the subject

    http://www.ralphkimball.com/ contains links to books and a ton of great articles

    also

    http://www.IntelligentEnterprise.com

  • September 1, 2009 at 9:58 am

    #1047637

    Thanks, Andrew. My library doesn't have the title you suggested but they do have "Data warehouse toolkit : the complete guide to dimensional modeling" by Ralph Kimball. I'll check it out and see what it's all about and see if I can request they add the other title.

    Edit: I see now that the title you mentioned is not a book but an article: http://www.ralphkimball.com/html/articles_search/articles1997/9708d15.html

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply