November 16, 2014 at 6:13 pm
I would assume most data warehouses would be providing a fact table containing sales revenue, and a dimension indicating product, such that revenue can be broken down by product. This seems straightforward enough.
But what about revenue that is not coming from "products" as such, but rather from additional fees and charges?
Take, for example, an airline. The product that they offer to a customer is a flight from one place to another. But when a customer actually purchases the product, they might pay additional fees such as fuel levies, or excess baggage charges, etc. These fees and charges do generate extra revenue, and the business does want to be able to see the breakdown of revenue by specific fee and charges types, but these fees and charges are not really "products" in the sense that they are not things that are "offered for sale to customers", and so it doesn't seem like you'd want them to exist as leaves in a product hierarchy?
How would you model a situation like this? Do you have another dimension which is a superset of the product hierarchy which also includes these kinds of things? If so, what do you call this dimension? Or would you, perhaps, have two independent fact tables, one containing product sale revenue, another containing fee and charge revenue? If so, does this not make it difficult for the business to easily analyze total revenue?
November 16, 2014 at 9:13 pm
I'd leave them in as "products" and utilize another attribute to allow for filtering of "type". e.g. these could be considered "fees & charges" vs "seat".
A variant on this is to utilize the additional attribute and create some additional measures. Eg fees, surcharges, revenue and then something like "revenue excluding fees and surcharges" and a "total revenue".
Technically prob not hard to implement both.
Goods transportation (Eg on trucks) has a similar problem pattern with fuel surcharges, hazmat etc.
Steve.
November 16, 2014 at 10:45 pm
Yes, that's certainly the simplest solution.
I'm concerned that it might not be correct, though. The attributes of a product (defined as "something a customer might want to purchase") could well be different from the attributes of a fee or charge. For example, a product might have a brochure code, being how that product is keyed in a printed or online catalog, whereas a fee or charge might not. A product might be deliverable, whereas a fee or charge is not. A product is organized in a way that makes it easiest for customers to find, whereas a fee or charge may not be well placed in that hierarchy. And the pricing structure for products might be very different from the way in which fees and charges are calculated.
In short, it seems to me that on the OLTP side these may well be different kinds of thing kept in different tables because they may have different sets of attributes and different relationships. However, they do have some kind of common ancestor, being a type we might define as as "anything that generates revenue". Ie, "anything that generates revenue" is the parent class, product is a child class, feescharges is another child class. Creating a dimension in the DW called "product" which actually mapped to the "anything that generates revenue" concept as opposed to mapping "product" to "product" seems like a sure fire way to create terminology problems in the enterprise.
But then, what's a good name for the dimension... "anything that generates revenue" is nice and descriptive but.... "dimAnythingThatGeneratesRevenue"? <shudder>. I also don't at all like naming a schema based on a presentation layer construct... so, for example "invoice line", "order line", and so on, so that's out.
November 17, 2014 at 7:23 am
Yes, that's certainly the simplest solution.
I'm concerned that it might not be correct, though.
Any solution that works is in some sense correct. If a second opinion helps, it seems to me the answer from StevefromOz is probably the best approach. The fees are items for sales, and adding a layer on top to allow for easy filtering makes it easy to view the products only.
November 17, 2014 at 4:53 pm
Sure, a classic inheritance idea... sometimes we want to treat two things as the same, sometimes differently. So product and fee are both children of some higher level of the hierarchy. But then... what's that higher level called? Product? Then product is a child of product 😀
November 17, 2014 at 5:23 pm
You could think of each of these as a service, that service is still a product. Even if it is a unwanted good, it still generated a line item of revenue. And these line items should add up to the total in a order header table. Kimball method has a Deal dimension. But this is for instance, there could be a time when a VIP customer has extra luggage, but gets a discount so that customer only pays for the seat. A deal key shows the reason for a no zero value in a discount fact attribute. It is not the same as the services (the products in the service industry) .
FYI- (In one of his books Kimball devotes a chapter to the transportation industry and has among others a frequent flier dimension. )
So I think you are correct in your thinking focusing on the revenue generating activities.
----------------------------------------------------
November 17, 2014 at 5:46 pm
Don,
What you're running into is a classic problem in Warehousing. I've heard it phrased as "Know the question before looking for the answer."
One of the reasons Kimball vs. Inman is still a huge discussion is because of the proliferation of different ways of needing to organize facts to be able to dimension the answers properly. In your case described here, is a fee an attribute of a product, or is it a product in its own right?
How I've usually seen things like this handled depends on the scenario and the variance in the product types. Transportation usually sells in a unit. Be it skid, seat, or square foot, or cabin. These items now have x items that are attached as additional fees. Hazmat, smoking, extra legroom, seat preferences, refridgerated, whatever.
If you use these as direct attributes (columns), you end up with an issue any time a new fee type is included, you're modifying schema all over the place. So, OLTP-wise, you build these into a second related table with a (for example) Flight-Time-Seat ID, ChargeTypeID, and a Charge value.
When you bring these over to the warehouse though, you need to know what your detail level requirements for the question are. If these are simply revenue, your fact table has two columns: ProductRevenue and ProductRevenue including fees. Simply total them up. If you need a breakout, you need to adjust the data and homogenize it for the question being asked.
So, the answer to your question is "What question are we answering?". You WILL have multiple fact tables in your warehouse (or datamarts) to answer different questions. There's no avoiding that. Data will repeat, information will be flattened, and things organized in one way one place have nothing to do with how the same data is organized in another.
Don't try a one size fits all approach unless it's a pretty obvious one. It's usually 10 times the work to keep it maintained that doesn't make up for the benefit.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2014 at 7:18 pm
Evil Kraig F (11/17/2014)
Don,When you bring these over to the warehouse though, you need to know what your detail level requirements for the question are. If these are simply revenue, your fact table has two columns: ProductRevenue and ProductRevenue including fees. Simply total them up. If you need a breakout, you need to adjust the data and homogenize it for the question being asked.
So, the answer to your question is "What question are we answering?". You WILL have multiple fact tables in your warehouse (or datamarts) to answer different questions. There's no avoiding that. Data will repeat, information will be flattened, and things organized in one way one place have nothing to do with how the same data is organized in another.
Yep, I totally agree that one source of the problem that the DW team are having is knowing exactly what questions the business wants to ask (for the record I'm not in the DW team, I do more general enterprise information architecture, hence my pedantry on issues like this).
In their particular case, it's definitely not true that fees are additional columns on the sales fact table, since there are fees not associated with any given "order line", but rather the order as a whole. Well known examples of this kind of thing might be credit card surcharges or "order processing" fees. So it looks to me like fees are either a fact table in its own right, or fees are rows in the sales fact table.
But the question of how we browse those fact rows is the same no matter which of these we pick. In either case, we need some dimension through which we can understand the facts. The relevant dimension, here, is one which allows us to look at the various things that generate the revenue on the order. But the question remains: what is this dimension called?
If you call it "Products", then your "Products" dimension contains two different things: "Fees", and "Products". But that makes no sense. So what is the name of the dimension that contains both "Fees" and "Products"?
November 17, 2014 at 9:08 pm
So if you had special gift wrapping (a service) would you be as concerned that your "Product" dimension contains "Services" as well? I guess I'm not seeing the problem here. You have additional Attributes in the Dimension that your users can easily use to classify and/or filter in/out the different types of products/services. You can probably add User Hierarchies to help them in navigating this too.
Another thought that comes to mind is - ask some of your users. If they have no issue in calling it Product, then Product it is. If they have concerns, then perhaps they'll have some suggestions for alternate names for you.
Steve.
November 17, 2014 at 9:28 pm
stevefromOZ (11/17/2014)
So if you had special gift wrapping (a service) would you be as concerned that your "Product" dimension contains "Services" as well? I guess I'm not seeing the problem here. You have additional Attributes in the Dimension that your users can easily use to classify and/or filter in/out the different types of products/services. You can probably add User Hierarchies to help them in navigating this too.
The problem is a purely logical one: It doesn't make sense to say that "the set of all products" is equal to "the union of the set of all products and the set of all charges". IE, product can't be a child of product.
But more generally, yes, it absolutely depends on how you define what a product is.
It seems to me (but, see below) that a "product" is best defined as "something we offer to customers", or equivalently "Something a customer might call us up and say 'i want one of those'". Given this definition, it seems clear to me that fees and charges are not products, but services are.
So then the question might be "why define products this way in the first place?". Well, because you'd certainly want *something* which captures that idea. If you ever want to build a printed brochure, you're going to need the list of things to put in the brochure. If you ever want to offer things to customers via e-commerce, you're going to need the list of things that customers can add to their shopping cart. This definition of a product clearly defines lists like these. And this segue's nicely into your next comment:
Another thought that comes to mind is - ask some of your users. If they have no issue in calling it Product, then Product it is. If they have concerns, then perhaps they'll have some suggestions for alternate names for you.
Which users?
Sales and marketing will clearly have an idea of "product" that is extremely similar to the one I have defined above. Other parts of the business might be using the term "product" as well, but probably aren't using it appropriately. For example, the data warehouse team collaborates with the asset management team, and from asset's point of view they use "product" as, effectively, a synonym for asset. That seems obviously wrong to me, but since they have no other use of "product" it doesn't matter to them in isolation. It does, however, matter when assets talks to sales and marketing! 🙂
November 18, 2014 at 11:36 am
I'd go as generic as possible:
"Revenue Generators"
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply