Flexibly performing allocations in databases
Allocation within a database environment is performed by processing a sequence of steps, each of which is described by an allocation processing block. The allocation processing block receives three inputs (input tables or input view) and computes a result. Related apparatus, systems, techniques and articles are also described.
1. A method comprising:
generating an intermediary base table in a database, the generating of the intermediary base table comprising joining an assignment paths table to a base table, the joining comprising matching conditional values from the base table with corresponding conditional values from the assignment paths table;
identifying data from the intermediary base table requiring direct assignment and data requiring allocation;
generating a filtered reference table, the generating comprising applying rules from the assignment paths table to the reference table;
disaggregating, using the filtered reference table, the data requiring allocation identified from the intermediary base table; and
generating a results table, the generating of the results table comprising combining the disaggregated data with the data requiring direct assignment;
wherein at least one of the receiving, applying, disaggregating, or generating is implemented by at least one data processor.
2. The method as in claim 1 , further comprising:
adding, during the applying of the rules from the assignment paths table to the base table, a driver name value from the assignment paths table to the intermediary base table, wherein the driver name values identify a driver that indicates whether the data requires direct assignment or allocation.
3. The method as in claim 1 , wherein the conditional values in the base table comprise at least one of:
a base allocation object value or a base other dimension value,
wherein the conditional values in the assignment paths table comprise at least one of: an allocation paths allocation object value and an allocation paths other dimension value.
4. The method as in claim 1 , wherein applying rules from the assignment paths table to the reference table comprises filtering out data records that do not meet filtering requirements.
5. The method as in claim 1 , wherein disaggregating the data requiring allocation using the filtered reference table comprises:
disaggregating at least one allocation value from the base table into separate weighted components, and
wherein the disaggregating is based on a weighted breakdown provided by the filtered reference table, and matching values in conditional values from the base table and corresponding conditional values from the filtered reference table.
6. A method comprising:
generating an intermediary reference table in a database, the generating of the intermediary reference table comprising joining an assignment paths table to a reference table, the joining comprising matching conditional values from the assignment table with corresponding conditional values from the reference table;
identifying data from the base table requiring direct assignment and data requiring allocation;
generating a filtered reference table, the generating of the filtered reference table comprising applying rules from the assignment paths table to the intermediary reference table;
disaggregating, using the filtered reference table, the data requiring allocation identified from the base table; and
generating a results table, the generating of the results table comprising combining the disaggregated data with the data requiring direct assignment;
wherein at least one of the receiving, applying, disaggregating, or generating is implemented by at least one data processor.
7. The method as in claim 6 , wherein the joining uses a path-joining rule comprising prioritizing paths with more unique matches over paths with more wildcard matches.
8. The method as in claim 6 , wherein applying rules from the assignment paths table to the intermediary reference table comprises filtering out driver values that belong to target allocation objects that do not require disaggregation.
9. The method as in claim 6 , wherein the conditional values from the reference table comprise at least one of:
a reference allocation object value;
a reference other dimension value; or
a reference driver name value.
10. The method as in claim 6 , wherein the conditional values from the assignment paths table comprise at least one of:
an assignment paths allocation object value;
an assignment paths other dimension value; or
an assignment paths driver name value.
11. The method as in claim 9 , wherein the weighted breakdown is defined by driver values in the filtered reference table.
12. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing system, result in operations comprising:
generating an intermediary base table in a database, the generating of the intermediary base table comprising joining an assignment paths table to a base table, the joining comprising matching conditional values from the base table with corresponding conditional values from the assignment paths table;
identifying data from the intermediary base table requiring direct assignment and data requiring allocation;
generating a filtered reference table, the generating comprising applying rules from the assignment paths table to the reference table;
disaggregating, using the filtered reference table, the data requiring allocation identified from the intermediary base table; and
generating a results table, the generating of the results table comprising combining the disaggregated data with the data requiring direct assignment;
wherein at least one of the receiving, applying, disaggregating, or generating is implemented by at least one data processor.
13. The non-transitory computer program product as in claim 12 wherein driver name values in the intermediary base table determine the classification of data as data requiring direct assignment or data requiring allocation.
14. The non-transitory computer program product as in claim 12 wherein conditional values from the base table comprise at least one of:
a base allocation object value or a base other dimension value,
wherein the conditional values from the assignment paths table comprise at least one of: an allocation paths allocation object value and an allocation paths other dimension value.
15. The non-transitory computer program product as in claim 12 wherein applying rules from the assignment table to the reference table comprises:
joining the assignment paths table to the reference table based on matching values of conditional values from the reference table and corresponding conditional values from the assignment paths table.
16. The non-transitory computer program product as in claim 12 wherein the disaggregating the data requiring allocation using the filtered reference table comprises:
disaggregating at least one allocation value from the intermediary base table into separate weighted components, and
wherein the disaggregating is based on a weighted breakdown provided by the filtered reference table, and matching conditional values in the intermediary base table and corresponding conditional values in the filtered reference table.
17. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing system, result in operations comprising:
generating an intermediary reference table in a database, the generating of the intermediary reference table comprising joining an assignment paths table to a reference table, the joining comprising matching conditional values from the assignment table with corresponding conditional values from the reference table;
identifying data from the base table requiring direct assignment and data requiring allocation;
generating a filtered reference table, the generating of the filtered reference table comprising applying rules from the assignment paths table to the intermediary reference table;
disaggregating, using the filtered reference table, the data requiring allocation identified from the base table; and
generating a results table, the generating of the results table comprising combining the disaggregated data with the data requiring direct assignment;
wherein at least one of the receiving, applying, disaggregating, or generating is implemented by at least one data processor.
18. A system comprising:
at least one data processor; and
memory storing instructions which, when executed by the at least one data processor, result in operations comprising:
generating an intermediary base table in a database, the generating of the intermediary base table comprising joining an assignment paths table to a base table, the joining comprising matching conditional values from the base table with corresponding conditional values from the assignment paths table;
identifying data from the intermediary base table requiring direct assignment and data requiring allocation;
generating a filtered reference table, the generating comprising applying rules from the assignment paths table to the reference table;
disaggregating, using the filtered reference table, the data requiring allocation identified from the intermediary base table; and
generating a results table, the generating of the results table comprising combining the disaggregated data with the data requiring direct assignment;
wherein at least one of the receiving, applying, disaggregating, or generating is implemented by at least one data processor.