Invoice fact

Hi,

I'm currently modelling a fact table (or maybe set of fact tables) for a business who carry out a number of different activities around cars. They sell cars, sell parts for cars, lease cars service cars and also hire cars.

The users would like a model that will allow invoices to analysed, down to the line level. The line level for car sales or hire will be a car. Line level for parts will be parts. Line level for service will be labour and travel time.

I'm unsure whether to model 1 fact table containing all outgoing invoices or whether to have several fact tables. The advantages to 1 fact table are that you can analyse all of the company's income in one place and get some detail around which customer/product etc was involved. But the disadvantage is that you will have to model a highly generic product dimension. It if was just parts and cars, then I'm sure it would be fine. But for the invoices on the servicing of cars, the Product dimension will need to contain items such as 'Labour' and 'Travel'. Dimension members of 'Labour' and 'Travel' don't fit well into a dimension that will contain car makes/models and parts.

If you went separate fact tables then you could have additional dimensions where required. For example on service invoices you would have a Technician dimension and a service number degenerate dimension, but these wouldn't relate to the new car sales. But then if you do that then you can't analyse everything in the same place.

I've always tried to build fact tables based on a business process. These are separate business processes (e.g. sales vs service) but from the Finance perspective the single business process is invoicing.

Has anyone faced similar challenges and what option did you go for?

Thank you

Hi,
there are normally 2 ways of approaching this:
1. If your Product Types are broadly similar, with each Type having a handful of attributes that are Type-specific, then you can implement a single Product Dimension and just populate the applicable attributes for each row - probably putting something like "Not Applicable" in the not applicable attributes.
You can hide irrelevant attributes, if you want, using Views, your BI tool, etc.

2. If your Product Types are very different then create a separate Product Dim for each Product Type, ensuring that the Product Dim key is unique across all these Dims. You would have a single Product Key on your Fact table and use this to join to the relevant Product Dim table when you create your queries.
Assuming there are some attributes that are generic across all Product Types, you can also put these in a Generic Product Dim and when you join your fact table to this it will allow you to analyse all invoices regardless of Product type. In this case the Product Dim key would duplicate a key in one of the Product-specific Product Dims.

Does this help?

Yep, search for Kimball's heterogeneous product dimension.

Thanks very much for the info. I'm familiar with the kimball heterogenous approach but I'm not sure I can see it working in this case as for one of my invoice types the products are services, I.e labour and travel.

I guess that's the key part of my question. Can I really call the dimension Product if I have services in there such as labour and travel?

In your case, Product is an abstraction of Labor and Travel. Typically, in the ER world, these "things" would be modeled as a super/sub type relationship. We use that modeling technique to identify attributes and relationship that vary by entity type (product type in your case). How you approach this in the dimensional world depends on how different attributes are from subtype to subtype. If Labor and Services have a large number of attributes that are more different than alike, I would approach the solution with two different dimensions (labor and travel). Additionally, if I truly had a super/subtype data structure in the source system I would also look at adding an overall product dimension as well.

Thank you Boxes&Lines - that gives me something to think about.

Do you have any experience of producing an invoice model for different invoice types? Each 'product' really has extra dimensions (e.g for service invoices you argue that you could include the technician who did the service, or you could include the service number (DD)). Do you think you should have a fact table per invoice type or would it be better to have them all in one fact? I'm curious what's normal here as it must be quite a common problem

In general, you divide things up between stuff that happens (sales orders, service orders, etc…) and invoicing. Invoicing is more centered around revenue and accounts and less on what took place operationally. In a mixed business environment, you would normally have operational oriented facts centered around the operation as there are often significantly different contexts (dimensions) for the action. These would be used for specific analysis of those operations.

Invoicing on the other hand would be less oriented towards those differences, but rather categorizing those actions into a common fact. You would have a reference back to the original action (in the operational facts) but this could be a single pair of columns (document ID and line #). An sales type dimension would categorize the particular row to identify where it came from.

Between the two sets of facts, you want to maintain a single conforming product dimension to include all goods and services. You may create sub-types as B&L suggested to cover the disparate attributes.

What Nick said. Keep invoice metrics in the same fact.

Thanks everyone, that helps.

I can see that working to have a single invoice fact, as this would be for finance people and that's all about the invoicing view of the world.

Then as you say ngalemmo there's nothing stopping you having seperate operational facts also.