After an intensive weekend of trying to get my database under sontrol, and
get millions of call records properly deleted, I think I have make great
success. I have historically had problems in the calls consolidation.
Since the process had not successfully completed in over 5 months I had
millions of call records to process, and finally after 48 grueling hours I
have processed all that Emerald will automatically process. That's the
good news.
After this process and trying again to calls consolidate each month
manually I looked at the calls table and found ~75,000 records in the calls
table with the AcctStatusType =2. To speed up processing I delete the
Start records or AcctStatusType =1 on a daily basis. When looking at the
remaining records I see that they fall under one of but a few categories.
The first batch I am not concerned with at all, they are present month,
ie.May 1998. The second batch are normal also, they are the ones in April
where the Master Account has not exceeded the calls consolidation period.
The next batch are a great mystery.
This next batch, I'll call the mystery records. They span several months
and contain Null for the UserName. I do not understand a Null accounting
packet, can someone explain this? Also since I do not care about the Nulls
(or do I?) is it safe to simply delete these call records? I assume I
could run the following script to clear them out?
Delete From Calls
Where AcctStatusType = 2 AND UserName = ""
The next batch I'll refer to as deadbeats. These are the majority of the
records remaining. They contain stop records for user accounts that are no
longer valid. For example a user that did not pay the bill or cancelled
and there is not subaccount in the database. Since I have deleted the
account and the information, I assume that Emerald has no way to process
the call table in the consolidation? Therefore I can manually delete these
call records. I can parse the invalid names and make a simple delete
statement as above that would remove them.
My question is the proper procedure to remove a Master Billing Record and
the Sub Account from Emerald, so that it will automatically delete the
associated records in the Calls table. Could you not simply make a
compiled procedure that deletes these records upon deletion of the account?
Or is it that since my database was not fine tuned that something
prevented this from happening? I am simply looking for the best method to
keep the database complete with the accurate information. I delete the
accounts pretty soon after non-payment because they count against my
allowed total. Is there a way, (maybe a feature request) where the account
can be marked as inactive, then when calls were consolidated they would
clean this up. This has many more advantages as you can easily identify
CHURN users or Retreads as I refer to them.
In addition this would preserve the past history, the disadvantage is
again that you are storing information in a database that needs to be very
efficient, then comes my next suggestion for comment. Take the inactive
users, and place them in another database Called .... inactive and store
all their information there. I don't know.... maybe too complicated.... I
just wonder am I doing the deletion correctly and if so, then do I manually
clean the database every so often?
Michael Whisenant
Vice-President, Operations
AIRnet Internet Services, Inc.