Hierarchy for a fact table with a composite primary key

Question

I have a fact table with a primary key composed of the invoice number, invoice date and line number.
I have defined the related dimension to aggregate in respect of the invoice number + the invoice date.
Now I want to define a hierarchy with the invoice number and the invoice date as the first level and
the line number as the second level.
How can I solve this issue? Many thanks

Answer 1

".. I have defined the related dimension to aggregate in respect of the invoice number + the invoice date. .." - not sure what that means? Is there a separate attribute whose composite key is (invoice number + invoice date) - and is the key attribute of the dimension (invoice number + invoice date + line number)?

---

- Deepak

Answer 2

The primary key of my fact table has invoice number + invoice date + line number.
So when I created the related dimension I have selected these three columns as dimension key.
But now I want to can aggregate by invoice number + invoice date that represent the identifier for an entire invoice.
How can I do it?
Thanks

Answer 3

".. But now I want to can aggregate by invoice number + invoice date that represent the identifier for an entire invoice .." - why would you wish to build aggregations on the fact dimension? What's the number of invoices - is it much lower than the fact table count? You could create an "Invoice" attribute with a composite key of (invoice number + invoice date), which could potentially permit aggregation. But what issue are you trying to solve - if it's performance, could you describe your scenario in more detail?

---

- Deepak

Answer 4

But now I want to can aggregate by invoice number + invoice date that represent the identifier for an entire invoice.
How can I do it?
Thanks

If the invoice number + invoice date identifies a dimension record, generate a primary key in dimension table with only these two columns being a part of the dimension, You don't need to keep the Line Number as a part of dimension composite key. Line Number should be maintained in the fact table itself and would be a part of Fact table's composite key ( Line number + Primary key of Dimension). 

Cheers!!

M.