Multiple Facts Mapped to Single Dimension Record

Fairly new to Dimensional Data Warehouses.

But I have my data in the form where it is possible to have multiple facts which map to a single dimension record.

Now within this, I require the following aggregations. Min, Max, First Non Empty and Last Non Empty.

It appears that First Non Empty and Last Non Empty do not choose the first and last values when you have multiple facts mapped to a single dimension record. Instead all facts are first aggregated. To get around this, I have aggregated my facts to a form where a single fact does map to a single dimension record (Second facts mapped to Second Dimension).

So my questions are:

1. When designing a dimensional data warehouse, is it common design to have to aggregate facts in the ETL process to the desired grain?
2. If I require multiple grains, do I simply have to have multiple fact tables of the same data aggregated to that desired grain?
3. Do any OLAP vendors who provide First Non Empty for aggregates treat the first value of a sequence of facts mapped to a single dimension record as the value which should be used for aggregation? (SSAS doesn't do this, it aggregates the facts first).

To clarify for 3. An example would be, we have 5 facts all of which happened within the same second (so multiple facts mapped to single second record). We have a fact attribute price. I am interested in the first price, not the sum of all the prices. My preference is not to have to do the aggregation to second level if possible in the ETL process.

1. No. Best practice is to capture facts at the lowest grain possible. Aggregate later if you must.

2. If you do 1, you don't need to do anything else unless you are trying to resolve a performance issue.

3. What is first and last in a relational database? 'First' and 'Last' only mean something if the data is ordered in some way.

But I have my data in the form where it is possible to have multiple facts which map to a single dimension record.

The fact to dimension relationship is many to one. This is normal. Why do you see that as an issue?

ngalemmo wrote:1. No. Best practice is to capture facts at the lowest grain possible. Aggregate later if you must.

2. If you do 1, you don't need to do anything else unless you are trying to resolve a performance issue.

3. What is first and last in a relational database? 'First' and 'Last' only mean something if the data is ordered in some way.

But I have my data in the form where it is possible to have multiple facts which map to a single dimension record.

The fact to dimension relationship is many to one. This is normal. Why do you see that as an issue?

I was under the impression that many to one fact to dimension relationship was normal. But it seems that if one cannot create a fact/dimension relationship where facts become unique then operators (in SSAS) such as "first" and "last" will aggregate facts first using sum before applying these operators at different dimensional levels.

Is this common behavior amongst OLAP vendors? Or do most treat "first non empty" as the first available fact during aggregation? In my case my attribute represent price, so taking a sum is definitely not the behavior that I require.

Uniqueness is a matter of many dimensions related to the fact, but each dimension on its own has a one to many relationship to the fact table.

Wither the collection of dimensional references (customer, date, product, etc...) for any one fact row is unique depends on the fact table. Although usually, that is the case.

OK, so if all my dimensions joined with my fact are not capable of producing a unique fact should I be attempting to move to a grain where a combination of my dimensions do produce a unique fact.

Example.

I have two dimensions, Dates and Time (Time is Hours, minutes, seconds).

If within my Facts there are multiple transactions that occur within a single second and I am interested in these values.

Do I either roll these facts into a single transaction which represents 1 second (Date and Time can produce a unique fact). Do I leave my facts alone (Date and Time cannot produce a unique Fact), do I add a dummy dimension which represents Daily Transaction # (this could produce a unique Fact when combined with Date and Time).

Thanks for your response.

I don't follow you. Why do you need uniqueness? Adding an artificial value will make it unique, but for what reason?

The notion of a unique key has to do with updating rows, not querying them.

ngalemmo wrote:I don't follow you. Why do you need uniqueness? Adding an artificial value will make it unique, but for what reason?

The notion of a unique key has to do with updating rows, not querying them.

Well that is exactly what I thought. But with SSAS this does not appear to be the case. It is not possible to obtain a first or last fact when there is no unique route to a fact. Perhaps I need to look into how other vendors treat this?

What do you mean by first? First in what?

You can't talk about first or last without putting things in context and sequencing the data. If you mean based on the chronology of the transactions, you need a timestamp on the transaction, preferably at a level of precision that is useful. If things occur at the exact same time, you need to include something else in the sort, after the timestamp, to serve as a tiebreaker so that repeating the same query does not produce different results.

The first and last transaction that occurred in a second.

I have a timestamp (second). And I have a transaction id for within the second. So the two combined are unique. So by first I mean the first fact (lowest transaction id in that second or first in natural order)

ok