Data transformation to maintain detailed user information in a data warehouse
Transforming data prior to loading the data into a data warehouse. Software of the invention partitions data records received from a plurality of servers and performs sequential file management operations and identifier management operations on each of the partitions prior to loading the data records into the data warehouse. Data records transformed according to the invention are easier to load into the data warehouse and easier to manipulate after loading. The invention enables analysis and data mining of tens of terabytes of user level detail data and summary data.
1. One or more computer-readable storage media having computer-executable components for transforming a plurality of individual log files received from a plurality of servers in a data collection and warehousing system, said log files each comprising a data record and at least one partition key value corresponding thereto, said components comprising:
a process management component for partitioning the received data records by assigning each of the data records to one of a plurality of partitions based on the partition key value corresponding to the data record, each of the partitions having one or more of the partition key values associated therewith; and
a data management component for sorting the data records partitioned by the process management component according to the corresponding partition key values and merging the sorted data records and corresponding partition key values with other data records and other corresponding partition key values, said other data records and other corresponding partition key values being previously received, said data management component further mapping each of the partition key values to another key value, said other key value representing a unit of information smaller than the partition key values associated with the merged data records.
2. The computer-readable storage media of claim 1 , wherein the process management component further partitions the received data records by assigning each of the data records to one of the plurality of partitions based on a different key value associated with said data record to enable access to said data record via the different key value.
3. The computer-readable storage media of claim 1 , wherein the process management component and the data management component comprise one or more non-relational database application programs.
4. The computer-readable storage media of claim 1 , wherein the process management component generates a non-relational fact table for each of the partitions, said generated fact table corresponding to a relational fact table in the data collection and warehousing system, said generated fact table containing the partitioned data records and corresponding partition key values.
5. The computer-readable storage media of claim 1 , wherein the data management component generates a non-relational dimension table corresponding to a relational dimension table in the data collection and warehousing system, said generated dimension table containing the merged data records and other key values after mapping.
6. The computer-readable storage media of claim 5 , wherein the data management component loads the data records from the log files into a relational database in the data collection and warehousing system as a function of the generated dimension table.
7. The computer-readable storage media of claim 1 , wherein the data records partitioned by the process management component and sorted by the data management component represent incremental data and wherein said other data records represent historical data.
8. The computer-readable storage media of claim 1 , wherein the data management component comprises a commercial-off-the-shelf sorting application program.
9. The computer-readable storage media of claim 1 , wherein the data management component is applicable to online analytical processing.
10. The computer-readable storage media of claim 1 , wherein the data management component loads the data records from the log files into the data warehousing and collection system.
11. A data collection and warehousing system receiving a plurality of individual log files from a plurality of servers, said log files each comprising a data record and at least one partition key value corresponding thereto, said system comprising:
means for partitioning the received data records by assigning each of the data records to one of a plurality of partitions based on the partition key value corresponding to the data record, each of the partitions having one or more of the partition key values associated therewith;
means for sorting the partitioned data records according to the corresponding partition key values and merging the sorted data records and corresponding partition key values with other data records and other corresponding partition key values, said other data records and other corresponding partition key values being previously received; and
means for mapping each of the partition key values to another key value, said other key value representing a unit of information smaller than the partition key value associated with the merged data records.
12. The data collection and warehousing system of claim 11 , wherein said means for mapping comprises:
means for aggregating the merged data records into an aggregated fact table; and
means for assigning a mapped key value to each of the partition key values in the aggregated fact table, each mapped key value representing a unit of information smaller than the partition key value associated with the merged data records in the aggregated fact table.
13. The data collection and warehousing system of claim 11 , further comprising a relational database, and further comprising means for generating a non-relational dimension table corresponding to a dimension table in the relational database, said generated dimension table containing the merged data records and mapped key values.
14. The data collection and warehousing system of claim 13 , further comprising means for loading the data records from the log files into the relational database as a function of the generated dimension table.
15. The data collection and warehousing system of claim 11 , wherein said means for partitioning, means for sorting, and means for mapping comprise one or more non-relational database application programs.