Multiple Facts Mapped to Single Dimension Record

View previous topic View next topic Go down

Multiple Facts Mapped to Single Dimension Record

Post  davewolfs on Sun Jun 03, 2012 4:54 pm

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.

davewolfs

Posts : 5
Join date : 2012-06-03

View user profile

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  ngalemmo on Mon Jun 04, 2012 2:42 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  davewolfs on Mon Jun 04, 2012 10:41 am

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.

davewolfs

Posts : 5
Join date : 2012-06-03

View user profile

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  ngalemmo on Mon Jun 04, 2012 6:25 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  davewolfs on Mon Jun 04, 2012 10:01 pm

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.

davewolfs

Posts : 5
Join date : 2012-06-03

View user profile

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  ngalemmo on Tue Jun 05, 2012 8:17 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  davewolfs on Tue Jun 05, 2012 9:40 am

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?

davewolfs

Posts : 5
Join date : 2012-06-03

View user profile

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  ngalemmo on Tue Jun 05, 2012 10:28 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  davewolfs on Tue Jun 05, 2012 10:30 am

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)

davewolfs

Posts : 5
Join date : 2012-06-03

View user profile

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  ngalemmo on Tue Jun 05, 2012 10:47 am

ok
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts Mapped to Single Dimension Record

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum