Microsoft KB Archive/231382

Infranet DB Architecture Limitations - Specific to bundling of c

PSS ID Number: Q231382 Article last modified on 05-07-1999

WINDOWS:1.0,1.0a,1.1,1.5,2.0,All

WINDOWS

================================================================ ==

The information in this article applies to:

 == OnEBill, versions 1.0, 1.0a, 1.1, 1.5, 2.0, All == 

= SUMMARY =

It would seem that such a straightforward thing as determining the deal or plan name of a cycle forward or arrears event would be very easy from a database standpoint, but unfortunately it is not.

The only viable link to the deal or plan tables is via the deal_obj_id0 and plan_obj_id0 which are located in the event_billing_product_t table. While this approach works successfully for products which have been purchased or cancelled in the given month the functionality breaks when attempting to determine the plan or deal name of a cycle_forward or cycle_arrears (monthly) charge in subsequent months. This is due to the fact that only purchase and cancellation events are recorded in the event_billing_product_t table.

Infranet’s current DB structure provides no linkage between these cycle_forward or cycle_arrears events to their original purchase event making it impossible to determine what plan or deal corresponds to the event. This linkage is an important component of the Infranet hierarchy of plan-deal-product-rate. The database needs to record events in such a way as to preserve this hierarchy and allow for its reconstruction from the database side, during any phase of the products life cycle. It is important to realize that this hierarchy is not a one-to-one relationship. One product does not necessarily map to only one deal nor does one deal necessarily map to only one product. The relationship is many-to-many in nature and requires additional handling on the database side to preserve.

= MORE INFORMATION =

DETAILS I have attempted taking the back-door approach to figure out a way in which I could obtain this information consistently by mapping to other tables, however I was unable to find a consistent means of reaching this goal. The most successful approach was a six table join as shown below, however even this approach had numerous limitations as well. When changing a rate of a product the rate_t table is updated with a new row and the old one is overwritten. This leaves a rate_obj_id0 in the event_bal_impacts_t table without a matching poid_id0 in the rate_t table. This results in the loss of database integrity since there is no match for this key in the rate_t table. This finding was supported by querying the production database which found seventeen such cases in our current implementation.

SELECT DISTINCT et.account_obj_id0, et.poid_id0, et.session_obj_id0, dt.name, pt.name

FROM event_t et, rate_t rt, event_bal_impacts_t ebit, event_billing_product_t ebpt, deal_t dt, plan_t pt

WHERE et.account_obj_id0 = AcctNum AND et.poid_id0 = ebit.obj_id0 AND ebit.rate_obj_id0 = rt.poid_id0 AND rt.name IN (‘cycle_forward’,‘cycle_arrears’,‘purchase’) AND rt.product_obj_id0 = ebpt.product_obj_id0 AND ebpt.deal_obj_id0 = dt.poid_id0 AND ebpt.plan_obj_id0 = pt.poid_id0;

It has been argued that such a complex query should not be necessary to obtain this information from an event and as we go forward with version 2.0 of the OnEBill project with hopes of tackling MSN in the future numerous scenarios which will require this logic loom in our future:

Plan/Deal Presentment Pages: (Intelligent Display of Plans/Deals) Cancellation Page: (Displaying Plans/Deals) OnLine Statement: (Grouping on a Plan/Deal level) OnLine Statement: (Grouping on a Rate level)

Currently the only field in the database which attempts to handle this logic is the session_obj_id0 in the event_t table. The session_obj_id0 works successfully BEFORE running billing.

However, when running billing all active cycle_forward and/or cycle_arrears products are assigned the same session_obj_id0 thereby breaking the link back to the original purchase event and making it impossible to group rates with their corresponding products from that point forward. It appears that the session is not a viable tool to link products to their associated deal. SUGGESTIONS (In order by ease of implementation and flexibility - from our standpoint) Create a new id in the event_t table, persistent_product_id0 (or some similar name), this id would be created when purchasing a product and would be carried throughout the lifecycle of the product (from purchase through each cycle_forward or cycle_arrears charge up through cancellation).

Add the persistent_product_id0 to the event_billing_product_t table allowing for a viable link through this table (via the deal_obj_id0 and plan_obj_id0 back to the plan or deal name).

Re-work the implementation of the session_obj_id0. Make it able to differentiate between different deals/plans when running billing and keep the session_obj_id0 consistent throughout the life cycle of the product.

Record /event/billing/cycle/forward and /event/billing/cycle/arrears events in the event_billing_product_t table keeping the table structures as they are now. This would increase the size of the table greatly, but would allow for mapping back to the deal and plan information associated with that event.

Do not overwrite rate information in the rate_t table when updating/changing rates. Make sure all rated events in the event_t table are recorded in the rate_t table.

Additional query words: Session Object, Bundling, OLS,

=
========================================================= Keywords :

Version : WINDOWS:1.0,1.0a,1.1,1.5,2.0,All Platform : WINDOWS ============================================================================= Copyright Microsoft Corporation 1999.