Post reply

Track the no. of new records loaded into Cube in SSAS

  • December 1, 2008 at 6:09 am

    #196444

    Hi Guys

    How do i track the no. of new records loaded into the cube in SSAS.

    do i need to track the count of dimension and fact table records?

    Do we need to write a MDX query for that?

    Cant we track the track the no. of new records with normal T-SQL.

    Please Help!!!

    Thanks in Advance

    Itzsam

  • December 1, 2008 at 9:22 am

    #905703

    Typically you would do this in your ETL process. Not sure how you have this setup currently (stored procedures, SSIS, etc.). Not sure what your requirements are to store this type of information or how you want to access it either for reporting purposes. Within SSIS you can use a row count transformation and with variables you could use this information to populate an audit table for inserts, updates, errors, etc.

    In Chapter 6 of the 'The Microsoft Data Warehouse Toolkit - with SQL Server 2005 and the Microsoft Business Intelligence Toolset' by the Kimball Group they talk about setting up this auditing system. You can download the example going against the Adventure Works from the sample site here - http://msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm along with other helpful utilities.

    You could create an audit dimension or use your data dimension and include additional keys in your tables to get some counts that way also based on when the data is loaded. Just not sure how you want to track this or report on it along with how you are actually loading the data.

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

  • December 2, 2008 at 3:16 am

    #906110

    I am in agreement with Dan, as part of my SSIS package for loading data I use the Row Count task in the data flow to capture the number of records, that and a select of the total number of records in the fact table are then added to the System Variable PackageName, along with the Start and End Date and Time of the package are then put all that into a table. This then allows you to see the growth of data, and any change in performance of the package.

    Hope that helps,

    Mark.

Viewing 3 posts - 1 through 2 (of 2 total)

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