Fact - Dimension relationship 1:1

View previous topic View next topic Go down

Fact - Dimension relationship 1:1

Post  Aleksandr on Wed Nov 11, 2015 1:46 am

Hi,

There is a scenario. We have a table containing Vacation Request with the following attributes:
RequestKey
RequestType
Employee
Approver (Employee too)
Duration (measure to analyze)
RequestNumber (it's unique integer value)
Description (free form text field. The most frequent value - NULL)
There are about 100-150 thousand new rows every year.

Business users wish to analyze duration of vacation, count of vacations and see RequestNumber and Description in their reports. Now we have to build relational data mart, but in the future we will design OLAP cube too based on VacationRequst data mart.

Is it good in this case to create both Dimension and Fact tables based on VacationRequest?

Any thoughts would be very helpful!

Aleksandr

Posts : 10
Join date : 2015-11-11

View user profile

Back to top Go down

Re: Fact - Dimension relationship 1:1

Post  ngalemmo on Wed Nov 11, 2015 2:07 pm

There are a few ways to go with large free-text description columns. If all you want to do is store the text, you can either have a separate dimension table or you can leverage your particular database system. Some DBMS's store large text columns (TEXT, MEMO, CLOB and other such data types) in a separate data structure from the primary table. This is effectively the same thing as a separate dimension table, but without the complexity. This allows you to simply define it as a column of the fact without adding baggage to queries that don't use the column.

A more complex solution if there is a need to do analysis on the actual content of the text is to break it down into a keyword/phrase structure, or to go event further, implement a natural language processing system to determine characteristics of the comment.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact - Dimension relationship 1:1

Post  Aleksandr on Wed Nov 11, 2015 3:46 pm

Thank you for reply.

If I understand you correctly, the solution below could be acceptable?

Fact table:
- RequestKey
- Employee
- Approver
- Duration

Dimension table:
- RequestKey
- RequestNumber (it's unique integer value which users want to see in reports, not pk)
- Description

Aleksandr

Posts : 10
Join date : 2015-11-11

View user profile

Back to top Go down

Re: Fact - Dimension relationship 1:1

Post  ngalemmo on Wed Nov 11, 2015 9:13 pm

The request number should be a degenerate dimension in the primary fact table.

If the description is not a VARCHAR data type (MEMO in SQLServer or CLOB in Oracle), you could store it in the primary fact as well, otherwise use the dimension as you have described.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact - Dimension relationship 1:1

Post  Aleksandr on Thu Nov 12, 2015 7:42 am

Thank you!

We are going to keep descriptions in a separate dim table and set one-to-one relationship between it and fact table.

Best regards.

Aleksandr

Posts : 10
Join date : 2015-11-11

View user profile

Back to top Go down

Re: Fact - Dimension relationship 1:1

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