Product Portfolio Dimension

I work for a large Telecom. I have a need to analyze customer behavior (billing/transactions/etc.) by products and services that a customer has assigned to their account.

We have separate dimensions for physical items (devices, accessories), service items (such as voice mail), and offers (network access plans, bundles, etc. which may have one or more service items included). There is no limit to the combination of any of these that may be assigned to a customer (a customer may have multiple devices [phone, bluetooth headset], multiple service items, and multiple offers [which are combinations of physical and service items]).

The situation is that the business wants to analyze customer charges for services by the network access plan or device or another service that they hold, that may not be directly associated with the fact table row in question (for example, equipment purchase behavior for customers with unlimited text plans or voice usage behavior for customers with camera phones).

My solution is to create a Portfolio dimension, which is an anonymous key that uniquely identifies a 'portfolio' of products and services that a customer may have assigned. This concept does not exist in the source system.

The portfolio dimension is linked to fact tables with other product information already linked, at the lowest level. A portfolio fact table (factless) links the portfolio key with the other product dimension keys. There are a limited set of flags in the portfolio dimension which show whether a each type of product is included, and some other indicators showing whether a popular class of products is included (i.e., smartphone, etc.). The fact table has one of the three product type keys populated with a link to a valid row, and the other two point to a row set to |NO ITEM|. To get a complete portfolio, you would SELECT * FROM portfolio_fact WHERE a specific portfolio key. However, you could filter the portfolios based on inclusion/exclusion of attributes in the other product dimensions, and then filter the transactions/events and customers.

Portfolio_DIM
PORTFOLIO_KEY, |audit fields|, |set of indicators|

Portfolio_FACT
PORTFOLIO_KEY, PHYSICAL_ITEM_KEY, SERVICE_ITEM_KEY, OFFER_KEY, |audit fields|

The ETL to populate this is definitely non-trivial; the customer-to-product and customer-to-equipment tables in the source system has to be polled to create a unique list for a customer, then the set gets assigned a surrogate key, then rows are added to the fact and dim. Then the next customer has to be polled, and compared to the existing lists (which is multiple fact rows in the fact table) to see if it exists, and if not, it gets added. AND SO ON. BLECK.

My question is: is there a simpler way to do this? Either a simpler design, or a simpler ETL process?

Thanks!

Susan

It would be a lot simpler if there was a single Item dimension (i.e. stuff a customer can buy) giving a single unique key to identify any one of the three subtypes.

Basically, a porfolio represents a multivalued dimension. But, since it involves three different dimensions, it is a collection of three different multivalued dimensions.

Since you have three dimensions, I would create three multivalued groups, and define a portfolio as a unique combination of the three groups.

I did this for a large telecom company as well. My approach was to flatten out all of the services and their effective and end dates into a single dimension. This allowed me to easily identify what active services a given customer had at any point in time. I also leverage CDI techniques to consolidate customer data into a person or organization (i.e. Party). You're right though, it's not trivial work.