From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

View previous topic View next topic Go down

From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  mail4adam on Fri Mar 28, 2014 4:37 am

Hi,

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

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  Booma on Fri Mar 28, 2014 5:19 am

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
avatar
Booma

Posts : 12
Join date : 2014-03-10

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  mail4adam on Fri Mar 28, 2014 5:30 am

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
Each foreign key refers to a NOT separate view of the date dimension so that the references are not independent!

mail4adam

Posts : 3
Join date : 2014-03-28

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  Booma on Fri Mar 28, 2014 5:45 am

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:
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.

avatar
Booma

Posts : 12
Join date : 2014-03-10

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  BoxesAndLines on Fri Mar 28, 2014 7:56 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  nick_white on Fri Mar 28, 2014 8:35 am

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



nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

Post  mail4adam on Mon Mar 31, 2014 1:22 am

nick_white wrote:I'll try and clarify...
A database View ...

Thank you for clarifying!

mail4adam

Posts : 3
Join date : 2014-03-28

View user profile

Back to top Go down

Re: From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?

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