none
SSIS Historical database reports RRS feed

  • Question

  • Hi all,

    I attended a session at the Project Conference 2012 by Gabriel Guenette that showed us how to create a historical database via a SSIS package that takes a snapshot of certain data in the reporting database on a daily basis so we can compare today’s data to some point in the past.

    Gabriel’s blog on the subject is here: http://www.gabrielguenette.com/CommonSenseEPM/

    I’ve successfully created the historical database and now I’m looking for suggestions on how to pull the data into some meaningful reports via Excel, SSRS, Visio, etc. that can show the differences in the data.  Gabriel mentioned that he would be posting examples of some of the reports that he uses with the data but he hasn’t posted anything yet and attempts to contact him about it have gone unanswered.

    I was wondering if anyone has created any reports with this historical type information and if I could get any suggestions on how the data could best be shown.

    Thanks for any ideas!

    DJ


    Daren Johnson

    Tuesday, May 1, 2012 3:32 PM

All replies

  • As you have data in different databases, you have to consolidate the information so you can report on it. For large amounts of data, ask your SQL Server administrator for help creating Views to do this. For small amounts of data, read it into Excel and report from there.

    Your IS team are likely to have various other tools available for reporting, so ask them: don't try to re-invent the wheel, use what they have.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, May 1, 2012 8:56 PM
    Moderator
  • Hi Daren,

    To answer you question : "I was wondering if anyone has created any reports with this historical type information and if I could get any suggestions on how the data could best be shown."

    I have worked with customers having the same requirement, As we know that project server doesn't have the historical data logs for Audit. I actually ended up with custom solution developed which on publish of a project reads the project level data & stores in custom database. It was kind of easy if I compared to SSIS (May be I am not a SQL DBA guy :) ).

    Once we have the data in tables, It was easy to create report using Excel/Report builder/SSRS & other tools.

    There is a Audit tool on Codeplex, may be you can download & see if that is helpful & can be extended to meet your requirement.

    http://epmauditing.codeplex.com/

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82


    Wednesday, May 2, 2012 5:27 AM