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]

Ok, then you could try concatenating the three tables and use a mapping table and the applymap function to add the opportunity id's to the invoice records.

[QlikNoviceNo1]

In the link table option that you suggested, along with the keys suggested (ie  %KeyOppId,%KeyOrderId and %KeyInvoiceId), can we not also add the common dimension too?

And then drop all the common dimensions from the individual fact tables?