Skip to Main Content

Just how unique the logical primary key has to be?

634853Oct 2 2009
Hi folks,

a question on the primary key of the logical dimension tables. Does it have to be unique and if so, just how much so?

Let me elaborate: In OBI Apps out-of-the-box there's a fact Sales Invoice Lines and a dimension Sales Invoice Number. The physical table w_sales_invoice_line_f is used as a source for both the fact and the dimension. The fact has the granularity of a sales invoice line. The dimension Invoice Number is of course on the invoice granularity and the logical table primary key is Invoice Number. (You can check this in OBI Apps repository if you want.) But there are often several invoice lines in an invoice, all of those lines would have the same invoice number because they belong to the same invoice, and thus invoice number is not unique identifier at all. So we know the physical table is on sales invoice line granularity, yet we use it as if it was on sales invoice granularity in the dimension.

Is this possible and a valid solution, or just another bug found in the OTB repository? ;)

Do logical table keys have to be unique? And just how unique they have to be? How much you can "push it"? Or is this a special case because the same physical table is used as a source for both fact and dimension?