3 Replies Latest reply on Jan 2, 2019 5:34 PM by Marc Klenotic

    Track-It 11 Executive Dashboard

    Marc Klenotic
      Share This:

      The table WorkOrderActiveDay in my Track-It database is 14.5 GB.  That's over 3/4 the size of the database.  The table only has 3 fields.


      UTCDat, MaxDbChangelogId, WOID


      But, there are 339,000,000 records going back to the original install.


      When I checked the ERD diagram, I found this table, along with two others, is part of "502 Dashboard Data Mart Incremental Load".  The other tables are DbChangeLogUTCDay and DataMartLoadHistory.  They don't have a large number of rows.


      I can't find anything in the documentation about an executive dashboard.  But, I do find mention of this in relation to the Footprints product.  Part of me wonders if this table was accidentally included due to cross development of the products.  Or perhaps it is temporary data that is not being cleaned up properly.


      Does anyone know what this table, WorkOrderActiveDay, is used for and why it gets so large...and perhaps if the rows are safe to delete?

        • 1. Re: Track-It 11 Executive Dashboard
          Marc Klenotic

          It's the 1 year anniversary of this question being posted.  I'm sad that it never received an answer. 

          • 2. Re: Track-It 11 Executive Dashboard
            David DeKeizer

            The entire '502 Dashboard DataMart' structure appears to be the underpinning for a feature that did not get released. I suspect that BMC hasn't responded to your inquiry because (a) they don't want customers hacking out that tables due to the portions of the feature are baked into the application and (b) the developer team has moved on to work on the new version.


            I also have a huge number of records, 472k of them, with ~6k related to Work Orders that no longer exist


            So.. caveat: I am not a BMC developer...


            The stored procedure 'DataMart$FullRecalc' has the following notes:

            - force a full recalc of the DataMart ... only need to be done ..if historical data was purged or altered

            It then deletes all records from DataMartLoadHistory, DbChangeLogUTCDay, and WorkOrderActiveDay

               {yes, deletes.. not truncate. This means log entries for all ~472k entries! }


            I have no idea if the SSIS weekly maintenance plan job or other modules have any hooks into these tables.



            I would like to just drop all the DataMart related garbage out of the database, something the dev team should have done prior to releasing the product.


            Cris Coffey,  is there a negative impact to truncating the DataMart tables? Is there any UI feature that uses the DataMart records?

            • 3. Re: Track-It 11 Executive Dashboard
              Marc Klenotic



              Thanks so much for digging this up.  It's quite a comforting thought knowing that if things go bad trying to resolve this, there is an easy way to recreate the records.


              I suppose the developers might have chosen DELETE vs TRUNCATE because TRUNCATE requires higher permissions.


              As an exercise, I truncated the 3 tables and then ran the DataMart$FullRecalc SP.  It ran for 2.5 hours, but when it was done the WorkOrderActiveDay table had been reduced from 17 GB to 23 MB.


              So, I suppose a work-around might be to run through this cleanup process on an annual basis.