Fact - Dimension relationship 1:1

[Aleksandr]

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!

[ngalemmo]

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.

[Aleksandr]

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

[ngalemmo]

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.

[Aleksandr]

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.

[Sponsored content]