Post reply

Analysis Report

  • September 11, 2009 at 3:02 pm

    #226006

    I am trying to build some kind of analysis report for database growth based on record counts. Right now i record counts of each table every week and they grow rapidly for which i need a report which also predicts future growth based on current growth with a grahical view.

    I assume i may need to use SSAS and SSRS, could some one please point me in right direction where to start.

    thanks

  • September 15, 2009 at 1:20 pm

    #1054061

    any idea how to start with ?

  • September 15, 2009 at 3:19 pm

    #1054132

    I'm assuming you're storing these counts? Actually, I've been thinking along the same lines and creating an SSRS report, if somebody hasn't already done something. I haven't begun to store the counts yet, but here was what I was thinking:

    Create a stored procedure that will take the counts for the current date and compare them against the last count date. Have some sort of threshold comparison. You could even pass that threshold into the sproc if you wanted to keep dynamic.

    Then dump the tables and counts into a temp table for those beyond the threshold percentage. Return the results of the temp table.

  • September 16, 2009 at 11:25 am

    #1054629

    If you have the data stored in a table and want to do some predictions then the quickest and easiest way for you to do this (assuming you have an instance of SSAS setup) would be to leverage the data mining add-ins for Excel 2007 (either SQL Server 2005 or 2008 versions).

    SQL 2005

    http://www.microsoft.com/downloads/details.aspx?FamilyId=7c76e8df-8674-4c3b-a99b-55b17f3c4c51

    SQL 2008

    http://www.microsoft.com/downloads/details.aspx?familyid=AF070F2C-46F4-47B6-B7BF-48979B999AEB

    Videos on the data mining capabilites:

    Microsoft BI: What can you do today?

    http://blogs.msdn.com/bi/archive/2009/05/07/microsoft-bi-what-can-you-do-today.aspx

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

  • September 16, 2009 at 6:22 pm

    #1054772

    Here what I would do

    1. Create a fact table to store count and avg record length for each table (see BOL to get the exact formula) - with 2 dimension: date and table_name

    2. Create SSIS package to insert new table name (if exist) and insert count for each table into fact table

    3. Create a calculated measure to get increment count for each row (simply but compare today's and yesterday count)

    4. Multiple calculated measure in step 3 with avg record length. Voila .... You can get an avg growth by day, week, month, and year

    5. You can use data mining for future prediction

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

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