Data warehousing infrastructure for web based reporting tool

• US 6,714,979 B1
• Filed: 09/24/1998
• Issued: 03/30/2004
• Est. Priority Date: 09/26/1997
• Status: Expired due to Term

• Current Assignee: Verizon Patent and Licensing Incorporated (Verizon Communications Inc.)
• Original Assignee: WorldCom, Inc. (Verizon Communications Inc.)
• Inventors: Brandt, Andre R., Pillai, Sajan J., Frueh, Barbara, Rehder, Karl, Shearer, Donald J.
• Primary Examiner(s): Wiley, David
• Assistant Examiner(s): Vaughn, Jr., William C.
• Application Number:
  US09/159,402
• Time in Patent Office: 2,014 Days
• Field of Search: 709/205, 709/225, 709/227, 709/224, 709/219, 709/217, 709/228, 709/229, 709/223, 705/26, 705/18, 705/27, 705/51, 705/34, 713/155, 713/201, 713/152, 707/102, 707/523, 707/513, 707/2, 707/9, 707/3, 707/6, 707/10, 379/114.01, 379/114.02, 379/114.28, 379/114.29
• US Class Current: 709/225
• CPC Class Codes: G06F 11/0709 in a distributed system consisting of a plurality of standalone computer nodes, e.g. clusters, client-server systems
• CPC Class Codes: G06F 11/0769 Readable error formats, e.g. cross-platform generic formats, human understandable formats
• CPC Class Codes: G06F 11/0781 Error filtering or prioritizing based on a policy defined by the user or on a policy defined by a hardware/software module, e.g. according to a severity level
• CPC Class Codes: G06F 11/3495 for systems
• CPC Class Codes: G06F 16/972 Access to data in other repository systems, e.g. legacy data or dynamic Web page generation
• CPC Class Codes: G06F 2221/2149 Restricted operating environment
• CPC Class Codes: G06Q 10/10 Office automation; Time management
• CPC Class Codes: G06Q 30/02 Marketing; Price estimation or determination; Fundraising
• CPC Class Codes: G06Q 30/06 Buying, selling or leasing transactions
• CPC Class Codes: H04L 12/14 Charging , metering or billing arrangements for data wireline or wireless communications
• CPC Class Codes: H04L 12/1428 Invoice generation, e.g. customization, lay-out, database processing, algorithms for calculating the bill or formatting invoices as WWW pages invoicing in general G06Q30/04
• CPC Class Codes: H04L 41/06 Management of faults, events, alarms or notifications
• CPC Class Codes: H04L 41/18 Delegation of network management function, e.g. customer network management [CNM]
• CPC Class Codes: H04L 41/22 comprising specially adapted graphical user interfaces [GUI]
• CPC Class Codes: H04L 41/28 Restricting access to network management systems or functions, e.g. using authorisation function to access network configuration
• CPC Class Codes: H04L 63/0209 Architectural arrangements, e.g. perimeter networks or demilitarized zones
• CPC Class Codes: H04L 63/0236 Filtering by address, protocol, port number or service, e.g. IP-address or URL
• CPC Class Codes: H04L 63/0281 Proxies
• CPC Class Codes: H04L 63/0428 wherein the data content is protected, e.g. by encrypting or encapsulating the payload
• CPC Class Codes: H04L 63/0464 using hop-by-hop encryption, i.e. wherein an intermediate entity decrypts the information and re-encrypts it before forwarding it
• CPC Class Codes: H04L 63/0815 : providing single-sign-on or federations
• CPC Class Codes: H04L 63/162 : at the data link layer
• CPC Class Codes: H04L 63/166 : at the transport layer
• CPC Class Codes: H04L 63/168 : above the transport layer
• CPC Class Codes: H04L 65/1045 : Proxies, e.g. for session initiation protocol [SIP]
• CPC Class Codes: H04L 65/1063 : Application servers providing network services systems providing special services to telephonic subscribers H04M3/42
• CPC Class Codes: H04L 65/1069 : Session establishment or de-establishment
• CPC Class Codes: H04L 67/025 : for remote control or remote monitoring of applications
• CPC Class Codes: H04L 67/142 : Managing session states for stateless protocols; Signalling session states; State transitions; Keeping-state mechanisms
• CPC Class Codes: H04L 67/306 : User profiles
• CPC Class Codes: H04L 67/56 : Provisioning of proxy services store-and-forward switching systems in data switching networks H04L12/54
• CPC Class Codes: H04L 67/564 : Enhancement of application control based on intercepted application data
• CPC Class Codes: H04L 67/565 : Conversion or adaptation of application format or content adding application control or application functional data H04L67/561
• CPC Class Codes: H04L 69/08 : Protocols for interworking; Protocol conversion
• CPC Class Codes: H04L 69/329 : in the application layer [OSI layer 7]
• CPC Class Codes: H04L 9/40 : Network security protocols
• CPC Class Codes: H04M 15/00 : Arrangements for metering, time-control or time indication ; Metering, charging or billing arrangements for voice wireline or wireless communications, e.g. VoIP
• CPC Class Codes: H04M 15/41 : Billing record details, i.e. parameters, identifiers, structure of call data record [CDR]
• CPC Class Codes: H04M 15/43 : Billing software details
• CPC Class Codes: H04M 15/44 : Augmented, consolidated or itemized billing statement or bill presentation
• CPC Class Codes: H04M 15/49 : Connection to several service providers
• CPC Class Codes: H04M 15/51 : for resellers, retailers or service providers
• CPC Class Codes: H04M 15/58 : based on statistics of usage or network monitoring
• CPC Class Codes: H04M 15/745 : Customizing according to wishes of subscriber, e.g. friends or family
• CPC Class Codes: H04M 15/80 : Rating or billing plans; Tariff determination aspects
• CPC Class Codes: H04M 15/8044 : Least cost routing
• CPC Class Codes: H04M 15/83 : Notification aspects
• CPC Class Codes: H04M 15/8351 : before establishing a communication
• CPC Class Codes: H04M 15/84 : Types of notifications
• CPC Class Codes: H04M 2215/0104 : Augmented, consolidated or itemised billing statement, e.g. additional billing information, bill presentation, layout, format, e-mail, fax, printout, itemised bill per service or per account, cumulative billing, consolidated billing
• CPC Class Codes: H04M 2215/0108 : Customization according to wishes of subscriber, e.g. customer preferences, friends and family, selecting services or billing options, Personal Communication Systems [PCS]
• CPC Class Codes: H04M 2215/0152 : General billing plans, rate plans, e.g. charge rates, numbering plans, rate centers, customer accounts
• CPC Class Codes: H04M 2215/0164 : Billing record, e.g. Call Data Record [CDR], Toll Ticket[TT], Automatic Message Accounting [AMA], Call Line Identifier [CLI], details, i.e. parameters, identifiers, structure
• CPC Class Codes: H04M 2215/0168 : On line or real-time flexible customization or negotiation according to wishes of subscriber
• CPC Class Codes: H04M 2215/0176 : Billing arrangements using internet
• CPC Class Codes: H04M 2215/018 : On-line real-time billing, able to see billing information while in communication, e.g. via the internet
• CPC Class Codes: H04M 2215/0188 : Network monitoring; statistics on usage on called/calling number
• CPC Class Codes: H04M 2215/42 : Least cost routing, i.e. provision for selecting the lowest cost tariff
• CPC Class Codes: H04M 2215/46 : Connection to several service providers
• CPC Class Codes: H04M 2215/54 : Resellers-retail or service providers billing, e.g. agreements with telephone service operator, activation, charging/recharging of accounts
• CPC Class Codes: H04M 2215/745 : Least cost routing, e.g. Automatic or manual, call by call or by preselection
• CPC Class Codes: H04M 2215/81 : Notifying aspects, e.g. notifications or displays to the user
• CPC Class Codes: H04M 2215/8108 : before establishing a communication
• CPC Class Codes: H04M 2215/8129 : Type of notification
• CPC Class Codes: H04M 2215/82 : Advice-of-Charge [AOC], i.e. notify subscriber of charges/cumulative charge; meter at the substation
• CPC Class Codes: Y10S 707/99933 : Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 : Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99939 : Privileged access

Abstract

A data warehousing infrastructure for telecommunications priced call detail data is integrated with a Web/Internet based reporting system providing a common GUI enabling the requesting, customizing, scheduling and viewing of various types of priced call detail data reports. Such an infrastructure performs an extraction process to obtain only those billing detail records of entitled customers, and a harvesting process for transforming the billing records into a star schema format for storage in one or more operational data storage devices. The system is integrated with a database server supporting expedient and accurate access to the customer'"'"'s telecommunications priced call detail data for priced call detail data report generation.

Claims

1. A Web/Internet based reporting system for providing timely delivery of a customer'"'"'s priced telecommunications call detail data to a client workstation running a web browser application, said system comprising:

a data warehousing infrastructure comprising;

process for generating a current customer list on a daily basis comprising customers entitled to receive daily telecommunications call detail data;

device for receiving customer'"'"'s raw telecommunications call detail data records from one or more telecommunications network switch mechanisms, and extracting certain call detail records for predetermined customers;

harvest device for receiving said extracted call detail data records and replacing a call detail data item therein with a corresponding dimension key found in an associated dimension build table for that call detail item; and

,

device for generating an output fact table comprising customer records having unique key structures for enabling consolidated storage of specific customer call detail data;

at least one secure server for managing client sessions over the Internet, the secure server supporting secure communication of customer request messages between the browser application client and the secure server; and

,

device for receiving said customer requests from said secure server and generating corresponding database queries implementing said dimension keys for application against said output fact table to obtain a specific call customer'"'"'s call detail data, said call detail data being transmitted back to said client web browser via said secure server;

whereby expedient and updated web/Internet-based access to said customer'"'"'s daily call detail data is assured.

2. The system as claimed in claim 1, wherein said dimension build tables are continuously generated and updated with key structures for each extracted telecommunications call detail data record having a call detail item in which no associating match is found.

3. The system as claimed in claim 1, wherein said harvest device applies look-ups to each of said one or more dimension build tables having said unique dimension keys, said harvest device further replacing a call detail data item therein with a corresponding key structure when a match is found.

4. The system as claimed in claim 2, wherein a dimension table includes a dimension key associated with a particular billing/customer, said system further comprising process for updating said billing/customer dimension table prior to inputting a received modified call detail record to said harvest device.

5. The system as claimed in claim 3, wherein said updating of said billing/customer dimension includes process for comparing said current customer list with a customer list from a prior point in time, wherein new customers in said current daily list is added to said billing/customer dimension build table.

6. The system as claimed in claim 3, further including a device for organizing said fact table and dimension tables in one or more operational data storage devices, said operational data storage devices comprising a database server for facilitating expedient retrieval of customer'"'"'s daily call detail data upon received customer requests.

7. The system as claimed in claim 3, further including a tranlation process for converting call detail data in said fact table from a first character format to a second character format, and transferring said fact table data to said operational data storage devices via ftp protocol.

8. The system as claimed in claim 1, wherein said output fact table and said dimension build tables are organized according to a star schema structure.

9. The system as claimed in claim 2, wherein said dimension build tables include dimension keys associated with a particular calling area.

10. The system as claimed in claim 5, further including dispatch server for communicating with said secure server through a firewall over a second socket connection, the first and second secure sockets forming a secure communications link, said dispatch server enabling forwarding of a report request message and an associated report response message comprising requested call detail data back to the client browser over the secure communications link.

11. The system as claimed in claim 9, further including a report manager server for maintaining an inventory of priced call detail reporting items associated with a customer and managing the reporting of daily call-detail data information in accordance with a customer request message, the report manager initiating access to said customer daily call detail data from said operational data stores.

12. The system as claimed in claim 5, wherein said data warehousing infrastructure further includes process for totaling each customer'"'"'s daily call detail data for a predetermined period of time.

13. The system as claimed in claim 12, further including process for verifying data harvesting and database load totals.

14. The system as claimed in claim 12, wherein said data warehousing infrastructure is further capable of providing output fact table associated with customer'"'"'s monthly call detail data that has been repriced, said system further including process for reconciling a customer'"'"'s daily call detail data totals in said operational data storage device with that customer'"'"'s repriced monthly call detail data.

15. The system as claimed in claim 14, wherein said reconciling process includes process for fragmenting blocks of call detail data included in said operational data storage devices with repriced monthly call detail data.

16. A method for providing timely delivery of a customer'"'"'s priced telecommunications call detail data to a client workstation running a web browser application comprising the steps of:

generating a current customer list on a daily basis comprising customers entitled to receive daily telecommunications call detail data;

receiving customer'"'"'s daily raw telecommunications call detail data records from one or more telecommunications network switch mechanisms, and extracting those call detail data records for only those customers entitled to receive said call detail data;

receiving said extracted call detail data records and replacing one or more call detail data items therein with a corresponding dimension key found in a dimension build table associated with said call detail item; and

,

generating an output fact table comprising customer records having said unique key structures for enabling consolidated storage of specific customer call detail data;

implementing a secure server to manage client sessions over the Internet, the secure server supporting secure communication of customer request messages between the browser application client and the secure server; and

,

receiving said customer requests from said secure server and generating corresponding database queries implementing said dimension keys for application against said output fact table to obtain a specific customer'"'"'s call detail data, said call detail data being transmitted back to said client web browser via said secure server,

whereby expedient and updated web/Internet-based access to said customer'"'"'s daily call detail data is assured.

17. The method as claimed in claim 16, wherein said step of replacing a call detail data item therein with a corresponding dimension key includes implementing a dimension table look-up of the call detail item for a key structure corresponding to said call detail item and replacing said key in said record when a match is found.

18. The method as claimed in claim 17, wherein said step of replacing said call detail item with said corresponding dimension key further includes the step of generating a new key to replace said call detail item and providing said new key to said dimension build table when no match is found.

19. The method as claimed in claim 18, wherein a dimension table includes a dimension key associated with a particular billing/customer, said method further updating said billing/customer dimension table prior to inputting a received modified call detail record to a harvest device.

20. The method as claimed in claim 19, wherein said step of updating said billing/customer dimension includes comparing said current daily customer list with a customer list from an immediate prior day, wherein new customers in said current daily list is added to said billing/customer dimension build table.

21. The method as claimed in claim 20, wherein said output fact table is capable of being loaded into storage/retrieval devices, said method further including the step of organizing said fact table and dimension tables in one or more operational data storage devices, said operational data storage devices comprising a database server for retrieving customer'"'"'s daily call detail data upon received customer requests.

22. The method as claimed in claim 21, further including the step of converting call detail data in said fact table from a first character format to a second character format, and transferring said fact table data to said operational data storage devices via ftp protocol.

23. The method as claimed in claim 21, wherein said fact table and dimension tables are organized according to a star schema structure.

24. The method as claimed in claim 23, wherein a dimension table includes dimension keys associated with a particular calling location.

25. The method as claimed in claim 24, further including the step of forwarding a report request message and an associated report response message containing requested call detail data back to the client browser over a secure communications link.

26. The method as claimed in claim 25, further including maintaining an inventory of priced call detail reporting items associated with a customer and managing the reporting of daily call-detail data information in accordance with a customer request message, and initiating access to said customer daily call detail data stored in said operational data stores.

27. The method as claimed in claim 26, further including the step of totaling one or more call detail data item values for a predetermined period of time.

28. The method as claimed in claim 26, further providing an output fact table associated with customer'"'"'s monthly call detail data that has been repriced, said method further including reconciling a customer'"'"'s daily call detail item value totals in said operational data storage device with that customer'"'"'s repriced monthly call detail data.

29. The method as claimed in claim 28, wherein said reconciling step includes fragmenting blocks of call detail data included in said operational data storage devices with repriced monthly call detail data.

30. The method as claimed in claim 26, further including interfacing with a report manager server for retrieving the customer-specific data from the operational data stores in accordance with a customer identity and report name.

31. The method as claimed in claim 26, further including generating a report utilizing the retrieved data and the metadata description of call detail reporting items.

32. A Web/Internet based reporting system for providing timely delivery of a customer'"'"'s priced telecommunications call detail data to a client workstation running a web browser application, said system comprising:

a data warehousing infrastructure comprising;

a traffic component to receive raw telecommunications call detail records from one or more telecommunications switches and to sort the call detail records into billing detail records;

a billing system to perform pricing in connection with one or more billing detail records;

an extraction component to extract selected customer billing detail records;

a harvesting component to transform the selected billing detail records based on a set of business rules into transformed billing detail records;

a data store component to store the transformed billing detail records into one or more data marts;

at least one secure server for managing client sessions over the Internet, the secure server supporting secure communication of customer requests between the browser application client and the secure server; and

,

a device for receiving the customer requests from the secure server and generating corresponding database queries to obtain a specific customer'"'"'s call detail data, the call detail data being transmitted back to said client web browser via the secure server.

33. The system of claim 32 wherein the traffic component comprises a module to convert the raw telecommunications call detail records into a format that is readable by a mainframe.

34. The system of claim 32 wherein the traffic component comprises a module to determine if records are billable and drops unbillable records.

35. The system of claim 32 wherein the extraction component comprises a file transfer protocol pull of a list of selected customers.

36. The system of claim 32 wherein the set of business rules for transforming the selected customer billing detail records comprises a star schema data model which incorporates a central fact table and a plurality of referenced dimension tables.

37. The system of claim 32 wherein the data warehousing infrastructure comprises a component to provide incremental, daily updates to the transformed billing detail records stored in the one or more data marts.