Go to Chapter 3          Contents

Chapter 4. BigBroBill

CDR's are necessary for billing, but not sufficient. You may not invoice a client in terms of Dial Peer ID's because this is an private company definition that your client don't know about and, very likely, doesn't care to know either. You may not invoice minutes, you invoice in currency terms, this meaning that you apply a a rate policy that may involve, not just a rate per minute, but also minimum call length, time unit or peak hours. If you already have a billing system, you may transform the Big Bro CDR's to match the format required by this Billing system you are using but if you don't have one, then BigBroBill will allow you to use the CDR's to render invoices, pay provider and dispute reports, it also provides forms for easy data entry. BigBroBill is provided as a Microsoft SQL Server Database with MS Access User Interface.

4.1 The Datadase

The name of the Database is GW_CDR, it provides three types of objects: Tables, Views and Stored procedures. The code will be open for you to see and modify.

4.1.1 Table Structure

Besides the CDR_Table, billing requires another 7 tables:

See structure below:

4.1.1.1 The CDR_Temp and the QueryData Tables

The CDR_table has been thoroughly discussed in Section 3.1, This table can get huge, millions of records, having indexes for every possible filtering need may render indexes bigger that the table itself, this way defeating the purpose of an index. The operating procedure for reporting is to "Pull" the records between two dates and other filter specifications into a temporary table with stored procedure Rep_PullRecsBill and the do the reports from that one. This table is the  CDR_Temp, it has all of the CDR_Table fields plus other six fields to allow for efficient reporting. All filtering specs are stored in a single row table called QueryData, in other words, which records are to be appended to CDR_Temp will depend on the contents of QueryData. NULL fields in table QueryData will mean, no filter. The [From] and [To] datetime fields though, are not nullable.

The [Truncate] field also can't be NULL, it specifies if the is the CDR_Temp table should be truncated or not before appending new records, so it must be a yes or a no. It may happen that the set of record to report can't be specified in a single action, for instance, is you need CDR's from non contiguous days or simply that pulling the interval you need takes too long and, to avoid a timeout, you pull it in several partial intervals.. 

The six extra fields will be filled by the Rep_PullRecsBill procedure. The [Charge] field means: what you are going to charge your client for this call. It will be calculated using the information in that client's set of records in the RatePolicy table. The [Cost], or what you are going to be charged for this call by your provider, is calculated using the same RatePolicy table but with the provider's set of records. The ID's specific rate records used for the calculations will be stored in the [RateID] and [CostID] fields of type float (8bytes).

For a calls coming from the IP side to the PSTN the clients can be identified by the IP while the provider by the span and DS0 group. For calls in the opposite direction, then the IP defines the provider while the span and DS0 group does it for the client.  The pulling procedure will store the right string into each of these field. The strings for each side are built in the same manner as the field [IP] in table IP_Reference.

4.1.1.2 The Q931CauseDescription Table

Cause codes are good for machines or maybe for geeks (in case you don't know what a Geek is, for a good definition, click here and then go to the bottom of the page), but for the rest of the human race a text description becomes necessary and that is exactly what this table provides. Unfortunately, it is not a simple translation from a number to a text, the [Cause] field packs three pieces of information (See Section 3.1.8), Some manipulation is necessary before the cause info can go into a report.

4.1.1.3 The DestinationNames Table

Dial peer ID could make sense to the CISCO programmer and even maybe to you, but certainly it could have no meaning to a client or provider. In an invoice you must refer to the actual destination name, Delhi Cellular, Ivory Coast proper, Buenos Aires, etc. This table connects Dial Peer ID numbers [DialPeerID] to destination names [Destination]. But even though Peer ID's are unique within a gateway, it is not necessarily true when multiple gateways contribute to the traffic, that's why the [GW_Name] field becomes necessary. This is the reason behind having the primary key to this table as one with two fields: the [DialPeerID] and the [GW_Name].

4.1.1.4 The IP_Referernces Table

This table links the client or provider company and its rate information to the IP's or DS0 groups in the CDR's. The IP will be stored in the [IP] field as such, since it is always unique to a client or provider, but the PSTN side string has to be built with information from three CDR fields: [DS0_Group]; the span, that is calculated by dividing [ChNum] into 100  and [GW_Name] because there could be more than one gateway, right?. The DS0 string is built as follows:

<Span>:<DS0 Group>:GW_Name

The [Company_ID] field connects IP (or DS0 string) with company info in the Companies Table. The [RateSetID] field connects the call with its corresponding rate set.

4.1.1.5 The Companies Table

This is a free table, besides the [Company Name], BigBroBill only uses the [Contact]  and address fields for the Invoice report. You may add or remove fields as needed for your operation.

4.1.1.6 The RatePolicy Table

This table specifies, not just a minute rate, but a policy on how to charge. Another important aspect of BigBroBill, is that charges are not stored into the CDR's, but calculated when required, this means that rates must be kept as references, so that any report involving money can be made for any call in the past. When a rate has to change, you don't want to edit the record, but add a new one with a new activation date, the BigBroBill procedures will take care of choosing the right rate record for the date-time of a call Description of the field bellow: 

LegBRateLookup

RateSetID

PeerID

LegBRate

MinCallSecs

IncrementSecs

ActivationDate

RateID

1

1151

0.15

30

6

11/1/03

8

1

5360

0.25

30

6

11/1/03

9

1

1721

0.05

30

6

11/10/03

10

100

1151

0.10

60

60

10/1/03

11

100

5360

0.20

60

60

10/1/03

12

RateID   

Is an identity field, a primary key that identifies the record.

RateSetID   

Not all clients are charged the same and you may have several providers for the same destination that don't charge the same or, maybe, not even the same way. Each can have its own rate set. This field matched same name field in IP_References.

PeerID  

Is the ID of the originating Dial Peer ID, matches the same name field in the CDR_Table.

LegBRate   

Is a floating point number specifying a rate per minute

MinCallSec   

If a call lasts less than this value in seconds, it will be charged for this minimum time.

IncrementSecs   

Call length will be rounded to the nearest multiple of these increments, i.e. A 34 second call is charged for .6 minutes while a 32 seconds one only for .5 minute.

ActivationDate   

These records are no to be edited or deleted, to change a rate you must append a new record. This field specifies when it goes in effect

 

There are three additional fields to support peak time

rates.

 

PeakFactor LoHour HiHour
1 0 12
1 0 12
1 0 12
1.1 7 19
1.1 7 19

   

PeakFactor  

Is the factor that multiplies the rate during the period specified by the fields below. The number can be greater than one or less than one.

LoHour   

Specifies the hour (0-23) at which the period starts

ActivationDate   

Specifies the hour (0-23) at which the period ends

 

4.1.2 Views

Views are precompiled queries that will deliver records more efficiently than one requested through ODBC. These views will provide the records for the most common reports in the billing system.

4.1.2.1 ReadableCDR

It is basically the same CDR_Temp table with some additional fields that translate the numeric reference fields into readable text. The [Cause] field has been processed to show only the dropping side cause code and the sign to indicate which this is dropping side. 

CallID Setup Delay DeciMinutes ChNum Codec PSTN_2_IP PeerID Destination Gap_2_pl rtt IsFax PacketsIn BytesIn PacketsOut BytesOut ...
135931654 10/31/03 5:00:52 PM 12 588 215 g729r8 1 3018 Dest1 0.03 253 0 51461 2009245 3539150 182037  
135931625 10/31/03 5:01:54 PM 15 106 230 g729r8 1 3018 Dest1 0.03 244 0 1678 32800 648660 33831  
135931623 10/31/03 5:05:43 PM 9 110 226 g729r8 1 3018 Dest1 0.03 278 0 8826 342324 667010 36864  
 
CallID ... GW_Name Cause DropSideCause Charge Cost IP_Client Client_ID Client IP_Provider Provider_ID Provider
135931654   NTK_LMA_2 16 NORMAL_CLEARING $5.880 $5.880 2:0:NTK_LMA_2 100 Client1 67.28.19.61 1001 Provider2
135931625   NTK_LMA_2 -16 NORMAL_CLEARING $1.060 $1.060 2:0:NTK_LMA_2 100 Client1 67.28.19.61 1001 Provider2
135931623   NTK_LMA_2 16 NORMAL_CLEARING $1.100 $1.100 2:0:NTK_LMA_2 100 Client1 67.28.19.61 1001 Provider2

4.1.2.2 InvoiceByDestination

 
Company_ID Company Name From To Destination Charge
100 Client1 8/31/03 4:31:00 PM 9/1/03 5:51:07 PM Dest1 $127.74
101 Client2 9/1/03 7:30:27 AM 9/1/03 5:40:23 PM Dest1 $49.25

This View queries the CDR_Temp table and renders subtotals by client and destination. [From] and [To] field refer to the first and last call registered for that destination and client.

4.1.2.3 InvoiceTotals

 
Company_ID Company Name Date1 Date2 GrandTotal
100 Client1 8/31/03 4:31:00 PM 9/1/03 5:51:07 PM $896.74
101 Client2 9/1/03 7:30:27 AM 9/1/03 5:40:23 PM $497.92

This View renders total charges per client. [Date1] and [Date2] refer to first and last calls from each client in the CDR_Temp table.

4.1.2.4 PayProviderByDestination

 
Company ID Company Name From To Destination Charge
1003 Provider1 9/1/03 7:30:27 AM 9/1/03 5:40:23 PM Dest1 $147.92
1005 Provider2 8/31/03 4:31:00 PM 9/1/03 5:51:07 PM Dest2 $457.74

This View queries the CDR_Temp table and renders subtotals by Provider and destination. [From] and [To] field refer to the first and last call registered for that destination and provider.

4.1.2.5 PayProviderTotals

 
Company ID Company Name Date1 Date2 GrandTotal
1003 Provider1 9/1/03 7:30:27 AM 9/1/03 5:40:23 PM $147.92
1005 Provider2 8/31/03 4:31:00 PM 9/1/03 5:51:07 PM $457.74

This View renders total cost per Provider. [Date1] and [Date2] refer to first and last calls to each Provider in the CDR_Temp table.

4.1.2.6 ClientDispute

 
Company Name Setup ChargeT
(s)
LegBRate MinCall Secs Increment Secs Peak Factor LoHour HiHour Charge dnis Destination IsFax ani
Client1 8/31/03 4:31:00 PM 1800 $0.10 30 6 1 0 12 $3.00 5112353519 Dest1 0 3055523634
Client1 8/31/03 4:52:49 PM 43 $0.10 30 6 1 0 12 $0.07 5113387656 Dest1 0 7184569873
Client1 8/31/03 5:00:52 PM 3530 $0.10 30 6 1 0 12 $5.88 5112921582 Dest1 0 7033694544

When a client requests CDR's after receiving an invoice you should provide the information it needs, no more and no less. Plain CDR's won't make reference to your rate policy and on the other hand, it includes a lot of information not relevant to a dispute and even information about providers that may be confidential. This view renders the CDR's specialized for a client dispute.

 4.1.2.7 ProviderDispute

 
Company Name Setup ChargeT LegBRate MinCall
Secs
Increment
Secs
Peak
Factor
LoHour HiHour Charge dnis Destination IsFax ani
Provider2 8/31/03 5:01:54 PM 635 $0.10 30 6 1 0 12 $1.06 5115671310 Dest1 0 5038791562
Provider2 8/31/03 5:05:43 PM 658 $0.10 30 6 1 0 12 $1.10 5115844143 Dest1 0 8144961365
Provider2 8/31/03 5:34:26 PM 1547 $0.10 30 6 1 0 12 $2.58 5115784687 Dest1 0 5619634562

When a there is a dispute with a provider, CDR's are usually the conciliation document. This document should contain the just pertinent information. Plain CDR's won't make reference to the agreed rate policy and on the other hand, it includes a lot of information not relevant to the dispute and even information about clients that may be confidential. This view renders the CDR's specialized for a provider dispute.

 

4.1.3 Stored Procedures

4.1.3.1 proc_GW_CDR

This procedures is called from the GW_Relay upon every call that finishes. Before inserting it verifies that the record is not already in the CDR_Table, if it finds it, then it skips the insertion and returns without error, if it doesn't, then it inserts a new record with the information it receives in a 25 parameter list. 

4.1.3.2 Rep_PullRecBill

This procedures populates the CDR_Temp table applying the filters in QueryData. It also finds the right rate for each call and calculates the client charges as well as the cost for the call. For this last task it uses an auxiliary procedure: Rep_CallMoney, it takes RateSetOD, PeerID, SetupTime, CallDuration in seconds as input parameters and delivers the cost and RateID as output parameters.

 

 

4.2 MS Access Interface

A user interface under MS Access is provided that provides the basic tasks of a Billing System: Invoices, Providers reports, disputes and forms for data entry. The main window is almost self-explaining..

4.2.1 Filters

The General strategy of the BigBroBill is not to report directly from the big CDR_Table but from the temporary CDR_Temp table. The Filter section helps specify which records need to be pulled from the big table to the temporary one. To facilitate the entry, there are combo boxes so select only valid values, this avoids mistyping and save you the time of having to look them up. An empty box means no filter. If you don't specify a value for the GW_Name_Filter, then the combo boxes can show no lists for the IP_Filter or the DS0_Group_filter,

4.2.2 Pulling Records

Once the filters has been set you are ready to pull the records, but you still must make a couple of choices, Clean the temporary table or keep the records, the most common choice is to clean or truncate the table, you only need to keep record when the set of records that you need can not be pulled in with a single query. This can happen if you need CDR's from non contiguous days or from several IP's, Peer ID's, etc. but not all of them. First pull you do it with Truncate checked and then for the rest, you uncheck it.

Also you may do partial pulling when you fear that the record set might be too big and that the query may time out. This brings us to the other choice, the Query Timeout, you may set the timeout for your query as big as you want, but you may not always want to go that route, for example, assume that you are pulling all the records for a whole month of heavy traffic, say, about a million records. You know that it takes around 300 seconds for pulling a week, so you figure...OK I can set the timeout to 1500 seconds and hit it! But what if it takes 1530 seconds? It will timeout and you would have lost 25 valuable minutes of your time. Piece of advice: If you are in a hurry, go partial, make 4 or 5 pulls, one for each week.

The button on the right will show a datasheet view of the CDR_Temp table.

4.2.3 Reports

The Section to the right of the Main_frm has for button for opening the available reports. The dispute report simple show the Client and Provider dispute views are show in datasheet view just as in sections 4.1.2.6 and 4.1.2.7 respectively. Invoice reports, by default looks as shown below, one client per page.

The PayProvider report is more compact and this is what it looks like:

4.2.4 Entry Forms

Data entry can be made directly to the tables in datasheet view, but forms with combo boxes may save some lookup and mistyping time. The Section in the bottom of Main_frm has buttons control for all the entry forms. The Companies button, will open the companies form ready to append a new record, it even show up with a recommended ID for the company:

The big text-box at the bottom left, will show full address once you have entered all the address elements (no entry on that one).

The DestinationNames and IP_References have the two forms below:

When new PeerID's or IP's are defined, you may type them in or, if you are lazy enough, you may wait for them to show up in the CDR's and use the button in the above form that will automatically append any unregistered IP or PeerID, of course, it doesn't go as far as guessing the destination name or the company ID it belongs to, but it still helps.

The Rates Entry Form help looking at the whole picture, on the same window it shows all the companies that charge or are charged with the same rate set and, in the bottom subform, the rate records for each dial peer.