From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
4 posters
Page 1 of 1
From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
Hi,
1. In OLTP system. I have tables ...
Number of rows in the table: 10.000
Number of rows in the table: 10.000.000
Example:
Each foreign key refers to a NOT separate view of the date dimension so that the references are not independent!
2. In OLAP system. how to Design the Dimensional Model?
2.1 create only one dimension table "dim_person" and create multiple links to fact table "fct_document"
2.2 create several dimension tables "dim_person1", "dim_person2", "dim_person3"
Thanks in advance!
1. In OLTP system. I have tables ...
- Code:
create table PERSON
(
PERSON_ID NUMBER(10) PRIMARY KEY,
PERSON_NAME VARCHAR2(1000 char) NOT NULL,
...
);
Number of rows in the table: 10.000
- Code:
create table DOCUMENT
(
DOCUMENT_ID NUMBER(10) PRIMARY KEY,
FK_PERSON_1 NUMBER(10) references PERSON(PERSON_ID),
FK_PERSON_2 NUMBER(10) references PERSON(PERSON_ID),
FK_PERSON_3 NUMBER(10) references PERSON(PERSON_ID),
...
);
Number of rows in the table: 10.000.000
Example:
DOCUMENT_ID FK_PERSON_1 FK_PERSON_2 FK_PERSON_3 10 2 3 2 20 3 4 5 30 NULL 4 NULL |
Each foreign key refers to a NOT separate view of the date dimension so that the references are not independent!
2. In OLAP system. how to Design the Dimensional Model?
2.1 create only one dimension table "dim_person" and create multiple links to fact table "fct_document"
2.2 create several dimension tables "dim_person1", "dim_person2", "dim_person3"
Thanks in advance!
mail4adam- Posts : 3
Join date : 2014-03-28
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
What you want to do is called Dimension Role-Playing (page 110 of The Data Warehouse Toolkit). Having 3 Person dimensions which all would have the exact same data would be unnecessary redundant.
If a Document will always contain up to 3 Persons, it is probably safe to make the Document fact something that looks like the original Document table
id
person_1
person_2
person_3
All persons with a FK to a Person_Dimension
If a Document will always contain up to 3 Persons, it is probably safe to make the Document fact something that looks like the original Document table
id
person_1
person_2
person_3
All persons with a FK to a Person_Dimension
Booma- Posts : 12
Join date : 2014-03-10
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
Booma wrote:What you want to do is called Dimension Role-Playing (page 110 of The Data Warehouse Toolkit). Having 3 Person dimensions which all would have the exact same data would be unnecessary redundant.
If a Document will always contain up to 3 Persons, it is probably safe to make the Document fact something that looks like the original Document table
id
person_1
person_2
person_3
All persons with a FK to a Person_Dimension
I know about -
Role-Playing Dimensions
A single physical dimension can be referenced multiple times in a fact table, with each reference
linking to a logically distinct role for the dimension. For instance, a fact table can have several dates,
each of which is represented by a foreign key to the date dimension. It is essential that each foreign
key refers to a separate view of the date dimension so that the references are independent. These
separate dimension views (with unique attribute column names) are called roles.
but what about - It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent ?
in my example:
DOCUMENT_ID FK_PERSON_1 FK_PERSON_2 FK_PERSON_3 10 2 3 2 20 3 4 5 30 NULL 4 NULL |
mail4adam- Posts : 3
Join date : 2014-03-28
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
Yes you can create seperate views for each of the persons. But as I interpreted it, it is just to avoid confusion when working with the seperate Dates (or, Person in your case).
You can create a View like this in most relational databases:
so you should that for each of the 3 persons and than when you JOIN in your SELECT query you can do:
Im not 100% sure, but as far as I know it wont give you better performance or anything.
You can create a View like this in most relational databases:
- Code:
CREATE VIEW person_1 (id, name)
AS SELECT id, name FROM dim_person
so you should that for each of the 3 persons and than when you JOIN in your SELECT query you can do:
- Code:
SELECT *
FROM fct_document
JOIN person_1 ON fct_document.person_1=person_1
Im not 100% sure, but as far as I know it wont give you better performance or anything.
Booma- Posts : 12
Join date : 2014-03-10
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
mail4adam wrote:
Each foreign key refers to a NOT separate view of the date dimension so that the references are not independent!
I'm confused. Do you believe that you need a separate view for each role playing foreign key for the columns to be independent? What dependency are you talking about?
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
I'll try and clarify...
A database View exists purely to aid usability, it has no impact on how the DB engine will execute a query. Therefore there is no requirement, from a DB engine perspective, to use Views in order for something to work properly. BTW I am deliberately ignoring Materialised Views from this discussion.
The quote from the book is probably not phrased particularly well, especially if not taken in the overall context of the point he is trying to make. Instead of
"It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent"
I would have said something like:
"It is essential, from a usability point of view, that each foreign key refers to a separate view of the date dimension so that the references are independent"
The point is not just that you create a view for each role but you also give the column names role-specific values. So instead of this:
CREATE VIEW person_1 (id, name)
AS SELECT id, name FROM dim_person
you should use something like this:
CREATE VIEW person_1 (p1_id, p1_name)
AS SELECT id, name FROM dim_person
So that when someone is building a query there can be no confusion about which is the correct column for them to select - they don't need to look at the parent table/view context of a column, they can tell from the name that it is the correct one. The classic example would be where a fact table references a Date Dimension multiple times - instead of pulling a "month" column into your query you would pull the "transaction_month" column from the transaction date view, or the "processed_month" from the processed date view: there can then never be any confusion about which month the query refers to - you have eliminated the possibility of the end-user not understanding your design and pulling the wrong column into a report/query.
If your users are accessing the data via a reporting tool (OBIEE, Business Objects, etc) rather than being exposed to the underlying tables/views then you would normally build also these names into the reporting tool's metadata layer and there is no need to build Views in the DB - though having the views helps the developer when testing and, given that they add little/no overhead, I would always build them anyway.
Hope this helps
A database View exists purely to aid usability, it has no impact on how the DB engine will execute a query. Therefore there is no requirement, from a DB engine perspective, to use Views in order for something to work properly. BTW I am deliberately ignoring Materialised Views from this discussion.
The quote from the book is probably not phrased particularly well, especially if not taken in the overall context of the point he is trying to make. Instead of
"It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent"
I would have said something like:
"It is essential, from a usability point of view, that each foreign key refers to a separate view of the date dimension so that the references are independent"
The point is not just that you create a view for each role but you also give the column names role-specific values. So instead of this:
CREATE VIEW person_1 (id, name)
AS SELECT id, name FROM dim_person
you should use something like this:
CREATE VIEW person_1 (p1_id, p1_name)
AS SELECT id, name FROM dim_person
So that when someone is building a query there can be no confusion about which is the correct column for them to select - they don't need to look at the parent table/view context of a column, they can tell from the name that it is the correct one. The classic example would be where a fact table references a Date Dimension multiple times - instead of pulling a "month" column into your query you would pull the "transaction_month" column from the transaction date view, or the "processed_month" from the processed date view: there can then never be any confusion about which month the query refers to - you have eliminated the possibility of the end-user not understanding your design and pulling the wrong column into a report/query.
If your users are accessing the data via a reporting tool (OBIEE, Business Objects, etc) rather than being exposed to the underlying tables/views then you would normally build also these names into the reporting tool's metadata layer and there is no need to build Views in the DB - though having the views helps the developer when testing and, given that they add little/no overhead, I would always build them anyway.
Hope this helps
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
nick_white wrote:I'll try and clarify...
A database View ...
Thank you for clarifying!
mail4adam- Posts : 3
Join date : 2014-03-28
Similar topics
» 1 Dimension used multiple times in 1 fact table?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Dimensional Model: Connecting dimension to fact table using two approaches
» How do I connect fact tables for drill down
» Design Fact Table in Dimensional Modeling with Multiple Grain
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Dimensional Model: Connecting dimension to fact table using two approaches
» How do I connect fact tables for drill down
» Design Fact Table in Dimensional Modeling with Multiple Grain
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum