Back after a fairly long hiatus… I was engaged in two very interesting and very different projects and I will write about them in the coming months. Here is the case for one of them – a rather interesting conundrum – I have designed it, but if you are a data architect, I would love to hear your design ideas for a data mart…

Case:

ABC Corporation is a manufacturer of small-medium sized office printers. Every printer sold is covered by a 90-day replacement warranty including parts and labor. Beyond that, ABC sells support or maintenance services.

Examples are:

• Standard support service entitles a customer to 12 hrs every week day of phone support and all replacement of defective parts for 1 year. Labor is charged extra. Cost: 5% of equipment value.
• Gold Support service entitles a customer to 24×7 phone support and all replacement of defective parts for 1 year. Labor is included. Cost: 10% of equipment value.

For each order placed by a customer, they record the bill to, ship to, and sold to sites for every order line. These sites could be different. For example, the customer Coca Cola only places orders from its corporate office in Atlanta (SOLD TO), but wants the bills paid by their central finance division in Raleigh (BILL TO), while the items are shipped to their offices in Boston (SHIP TO). ABC attempts to up-sell its “Support service” for every order and, is successful in doing so most of the time. Printer orders usually have a line for support service for each product line. A typical order for 3 printers would look like this:

ABC also installs the equipment after they are delivered. At the time of installation, the exact site of installation (e.g. Boston office, Bldg 1, 3rd floor Loc 1A32) (INSTALLED AT) is recorded. This record also has the serial number of the actual printer. This is now part of the “installed base”.

Installed Base of ABC is a collection of the record of every individual product serial number for each customer’s installed at site. The installed base records for these printers would look like this:

Every installed base record is serviced by one active maintenance contract line at any given point of time. If customers choose to continue the support, these contracts are renewed every year after the warranty period till the life of the product. In the new/renewal contract, the contract number remains the same, but the suffix (_1 in the example below) is incremented. The support prices are incremented by 4% upon each renewal. The service contract related to this order looks like this:

What do we want to analyze? Here is a sample of analytics needed:

• What is the total value of ABC’s installed base? – by customer, by product (not serial #)
• What are the installed base counts (average/min/max/actual printers per customer, etc)
• What is the annuity (annual maintenance contract value)? by customer, by product (not serial #)
• How many customers renew every year (retention rate)? By customers region / D&B attributes, product
• Dollars renewed every year vs. dollars available to renew? By customers region / D&B attributes, product
• Which products tend to stay longer on maintenance? (contracts get renewed)

Each of these analyses is supported by these contracts and there is a requirement to be able to drill down to the contract sub-line level.

If you have worked with the Service Contracts module of Oracle eBusiness suite, all of these will sound very familiar to you. There are some obvious questions that should come up, especially about the grain of the fact tables and the detail of the dimensions – both for products and customers and complexities of the installed base. So, if you are engaged in a similar projects, just trying out this challenge, or if you have questions, do drop me a line or put in a comment.