Storing text comments in a data warehouse

View previous topic View next topic Go down

Storing text comments in a data warehouse

Post  davVisitor on Mon Apr 01, 2013 11:24 am

I have an OLTP table which stores text comments from customers based on a recent transaction.

Where should these text based comments go in the OLAP data warehouse?

I've thought of the following ideas:

1. Create a new fact table with a reference to the existing dimensions and the text comments as the fact. However, I've read that fact tables aren't supposed to have text fields in them.
2. I could create a new dimension with each distinct customer comment and then add a reference to an existing fact table with the dim_comment_id of the user's comment.
3. I could create a standalone table (which I'm currently calling a dimension) and in-line all of the search criteria (order time, customer first name, etc...). This standalone table would contain both the text-based comments and the search criteria for the table. It would be sort of like flattening a small star schema into a single table.

Is there a better option for this scenario or suggestions about which of the above options would be best?

davVisitor

Posts : 3
Join date : 2013-03-04

View user profile

Back to top Go down

re : Reply with quote Storing text comments in a data warehouse

Post  hkandpal on Tue Apr 02, 2013 7:10 am

Hi,

are the comments fixed or the will be different for eact fact row ? How big are the comments (how many characters).
Will you be doing any search on comments ?
thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Storing text comments in a data warehouse

Post  davVisitor on Tue Apr 02, 2013 9:48 am

Thanks for the questions.

>> are the comments fixed or the will be different for eact fact row ?

No, the comments are not fixed. They will be different for each fact row. The comments are a free text box where end users can type anything they want. One row might be 'Great service' and another line might be 'Greatt service'.

>> How big are the comments (how many characters).

They are currently modeled as a MySQL text data type which has a maximum length of 64 KB. However, 99% of the comments are less than 500 characters long.

>> Will you be doing any search on comments ?

We are currently not planning to search the comment text, but we instead plan to view comments based on criteria of the order (by productType, monthOrdered, numberOfItemsInOrder, etc...).

davVisitor

Posts : 3
Join date : 2013-03-04

View user profile

Back to top Go down

Re: Storing text comments in a data warehouse

Post  ngalemmo on Tue Apr 02, 2013 1:11 pm

This question keeps coming up...

Option 2 is the normal way to deal with it. Separate dimension. It performs best for most queries and gives you flexibility to expand analytic capabilities on the text at a later date.

With that said, if you never expect users to do keyword analysis, or you really don't care how long such a query would run, you could consider adding it as a degenerate dimension on the fact. But only if the database does not actually store it in the fact table itself. Many databases, if you declare the column as CLOB, TEXT, or MEMO or whatever data type is a huge text field, will not store the value in the table. Instead it stores the text in a pool and places a pointer to it in the fact table (VARCHAR is always stored on the row, not matter how large it may be). It, in effect, gives you a separate dimension table without having a separate dimension table. The net effect is that queries that do not reference the text column will not be impeded by it. If the text is physically stored in the fact table itself, the shear size, in relation to the other members of the row, can slow any query considerably because there is much more data that needs to be read off disk. That is not the case with databases that place large objects into a separate pool. (Such as SQL Server and Oracle). Check the documentation for your database.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Storing text comments in a data warehouse

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