Modeling a bidding process

[#1]

Hello,

First of all apologies for the long post...

I have been struggling with the optimal way to model my current qlikview application. I am implementing an application that will allow users to analyse the bidding process of their company. To simplify, lets say that the bidding process is comprised of three entities: Opportunities, Sales Orders and Invoices. All three have an header and one or several line items.

The most important data are at a line item level/granularity (e.g. product name, sales quantity, unit price, line item total amount, etc.).

On the other hand, the association between the three entities is at a header level. By that, I mean that an Opportunity originates a Sales Order, which in turn originates an Invoice (so there is no direct association between opportunity line itens, sales orders line itens and invoices line itens, other than through their corresponding headers).

Furthermore, there are no constraints between opportunity line items, sales orders line itens an invoice line itens, meaning that it is perfectly possible, on the source system, that a user creates an opportunity with two line itens (line item #1 for product A and line item #2 for product B) and that the resulting sales order also contains, e.g., two line itens, but for distinct products C and D (although possible it is not common as it doesn't make much sense).

Also, the users want to capture the flow nature of the process, i.e., the users want to, obviously, be able to identify, starting with a given invoice, which sales orders originated that invoice and, in turn, which opportunities originated those sales orders.

So what would be the best model in qlikview to represent this process?

I have tried two approaches:

i) a link table connecting opportunities, sales orders and invoices (with a %KeyOppId,%KeyOrderId and %KeyInvoiceId) , which would then link to other individual tables (one for each entity, opportunity, sales order and invoice). The downside of this approach is that I ended up having three different fields for each dimension (eg Opportunity Product, Sales Order Product and Invoice Product, instead of a single Product Dimension), and this is a huge disadvantage because I loose the associative modeling features of qlikview;

ii) the second approach I have tried is to have a single fact table (concatenating the three individual fact tables: opps, orders and invoices), the downside here being that I loose the connections between the three, meaning I loose the "flow of the process", that is, I loose the information telling me which opportunities generated which sales orders which in turn generated which invoices...

Any comments/suggestions would be lovely

Thanks

[Gysbert_Wassenaar]

Can you give us some example data so we can understand how the relations between opportunity, sales order and invoice order are defined?

[#3]

Hi,

Thanks for your reply. Yes, of course. So, for example, we could have:

Opportunity #1 with the following two line itens:

Line Item #1 with 100kg of product A with unit price of 25$/kg

Line Item #2 with 25kg of product B with unit price of 5$/kg

Line Item #3 with 35kg of product C with a unit price of 10$/kg

Opportunity #1 could have generated, for instance, three Sales Orders: Sales Order #1, Sales Order #2 and Sales Order #3, with the following data:

Sales Order #1 with half the quantity of Product A

Line Item #1 with 50kg of Product A with unit price of 25$/kg (to be delivered next September)

Sales Order #2 with the other half of Product A

Line Item #1 with 50kg of Product A with unit price of 25$/kg (to be delivered next October)

Sales Order #3 with the Product B and Product C

Line Item #1 with 25kg of product B with unit price of 5$/kg (to be delivered next September)

Line Item #2 with 35kg of product C with a unit price of 10$/kg (to be delivered next September)

In turn, these three Sales Orders could each have generated three invoices:

Sales Order #1 generated Invoice #1

Sales Order #2 generated Invoice #2

Sales Order #3 generated Invoice #3

Invoice #1 would have one line item:

Line Item #1 with 50kg of Product A with unit price of 25$/kg (to be payed next November)

Invoice #2 would have one line item also:

Line Item #1 with the remaining 50kg of Product A with unit price of 25$/kg (to be payed next December)

Invoice #3 would have two line itens:

Line Item #1 with 25kg of product B with unit price of 5$/kg (to be payed next November)

Line Item #2 with 35kg of product C with a unit price of 10$/kg (to be payed next November)

So, in summary we would have:

Opp #1 -> Sales Order #1 -> Invoice #1

Opp #1 -> Sales Order #2 -> Invoice #2

Opp #1 -> Sales Order #3 -> Invoice #3

Hope this helps clarifying the relationships.

Please note that the links between opp, sales order and invoice are at a header level, while most of the important fields are at a line item level (product, unit price, quantity, develiry date, due date, etc). Nevertheless, in the original source system there is not direct relantionship between line itens.

Thanks

Vasco

[#4]

correction:

in the second line I meant three, not two:

Opportunity #1 with the following three line itens:

[Gysbert_Wassenaar]

sigh... Ok, show me an ERD diagram of the data model.

[#6]

Here it goes...

Thanks

[Gysbert_Wassenaar]

Also, the users want to capture the flow nature of the process, i.e., the users want to, obviously, be able to identify, starting with a given invoice, which sales orders originated that invoice and, in turn, which opportunities originated those sales orders.

I don't understand that part in bold. According to your data model an invoice can originate from only 1 sales order, never more than one and never less than one. Can you clarify this?

[#8]

Yes you are right. My mistake in the quote.

So, one invoice originates from one sales order which, in turn, originates from one opportunity.

On the other hand, one opportunity can generate zero, one or several sales orders, and each of these can, in turn, generate zero, one or several invoices.

So, the goal is to also capture this relantionship...

[Gysbert_Wassenaar]

I'd just merge the header and line-item tables with each other. That should give you three tables Opportunities, SalesOrders and Invoices. These tables will be linked by the key fields OpportunityID and SalesOrderId. That's all you need afaict. With this model you can select an invoice and see which sales order and opportunity are related to it. Or the other way around, select an opportunity and see which sales orders and invoices are related to it.

[#10]

Hi Gysbert,

Thanks for you reply.

Yes, that's one approach. The only downside with that is that I would end up with different fields for each entity, e.g., ProductNameOpportunity, ProductNameOrder, ProductNameInvoice.

I was trying to find a model that would allow me to keep the associations between the three entities and, at the same time, that would also allow me to have a single Product dimension (and the same goes for other dimensions that are present in all the three entities at an line item level).

Regards,

Vasco