DWT Frequent Flyer and Customer

[kangaroo]

I'm looking at figure 11.2 in the Data Warehouse Toolkit Second Edition. It shows a Frequent Flyer dimension and a Frequent Flyer Profile Dimension. How would these two dimensions conform to a Customer Dimension? Or should they be combined or an outrigger?

For example, let's say that the airline takes online reservations and has Customer info at that time. Some of these are Frequent Flyers too.

[ngalemmo]

I guess it depends on who you consider your customers to be from a data analysis perspective... Is it anyone who buys a ticket or is it those you know something about (i.e. those who registered as frequent flyers).

One could have a customer dimension (and add the foreign key to the fact), but I doubt that has much value and would most certainly be a problematic dimension to maintain as there would be no business key to identify them.

[kangaroo]

Let's say that at the time the customer buys a ticket you are provided with that customer's first name, last name, optional phone number, optional email address, and optional Frequent Flyer Number.

The flight fact activity table has a FK to the Frequent Flyer Dimension.

For the sake of understanding, let's say we do add a FK to a Customer Dimension. If there is not a unique customer ID used each time that customer buys a new ticket, then multiple Customer records could exist for the same customer in the Customer Dimension. And if I'm understanding you correctly, this is the problem, right? -- that the Customer Dimension table won't be able to provide you much more than demographic info? Could you work with the email address somehow to gain additional info about customer behavior?

Anything else I'm missing?

[kangaroo]

And let's say that you did have the same data available for Frequent Flyers and normal Customers. Customers can become a Frequent Flyer at any time. Frequent Flyers can also request to be removed as a Frequent Flyer. How should this be modeled? Or is this too detailed of a question for this thread.

[ngalemmo]

Wither anything is a problem is a matter of what the business need is and what the information is worth.

When a non-FF customer buys a ticket what information about the customer does the airline collect? Does that information have any useful analytic value months down the road after the ticket is used? If there is value, is there a reasonable chance the information is sufficient to re-identify that customer on subsequent trips? If there is, what can the airline do with the information?

The whole point of the frequent flyer programs was to provide a mechanism where the airline could identify and gather demographic and other information on customers who, by their own action, indicated a preference for the airline. Someone who registers as a FF has essentially told the airline that they intend to fly again and may choose their airline. Someone who does not register is indication that either they don't fly that much or they don't care about that airline's FF program. Is the latter group one that an airline may wish to spend their limited marketing budget to pursue?

If the business decides that they want to track individuals in the non-FF group, then a customer dimension and all the complexities in making any sense of it is necessary. If not, just a 'not a FF account' row in the frequent flyer dimension is all that is needed.

[Sponsored content]