Comments for facts

[eshtee]

Hi,
I am designing a fact-less fact table to record complaint events by customers regarding products they have purchased. the complaints are handled by front-line staff. While front line staff insert complaints in the system they add type of complaints, products, customer info, account info and sometimes comments that customer made.

For the solution i have created customer dimension, Account dimension, product dimension, complaint type dimension and Complaint fact.

How can i save complaint comments in the data mart in the most effective fashion as report users want to view them. I can either put it in the complaint fact table or create a separate table for comments and add FK in the fact table.

Thanks,
Syed

[rob.hawken]

Hi,

1st question to ask is what is the grain of your fact table? If it is 1 row per complaint then the only option you've got is to create a complaint notes dimension. However I wouldn't go that route as the notes don't represent any business entity and aren't anything you are going to use to analyze by. So I'd suggest you'd need another fact call it "complaint activity fact" where you have a 1 row for each activity that occurs during the life of the complaint and you add complaint note as a degenerate dimension on the fact. Trust this makes sense.

Rob

[ngalemmo]

I wouldn't put the text in the fact table. It would significantly impact performace of queries that do not need comment. Having an FK to the comment gives you the opportunity to add a keyword bridge table later if desired. You can parse the comment text into a list of keywords and use the bridge (comment FK/keyword FK) to quickly locate comments containing words of interest.

[eshtee]

The grain of complaint fact table is activity so thanks rob for a more descriptive name suggestion. ill rename it to "Complaint Activity Fact". however i will add comments as FK to this fact table. i know its not a true dimension but i agree with ngalemmo. This will make the queries a bit more efficient.

Thank you for your quick response guys,
Syed

[rob.hawken]

Never had to use a keyword bridge table. Perhaps because I'm in NZ our data volumes mean I've never had any query performance issues with having the text directly on the fact. However to provide the same functionality (in SQL Server) we've used Full Text indexes. Presume there is something similar for Oracle as after all Scott McNeally used to say "At Microsoft, freedom to innovate really means freedom to acquire"
Cheers

[ngalemmo]

Never had to use a keyword bridge table. Perhaps because I'm in NZ our data volumes mean I've never had any query performance issues with having the text directly on the fact. However to provide the same functionality (in SQL Server) we've used Full Text indexes. Presume there is something similar for Oracle as after all Scott McNeally used to say "At Microsoft, freedom to innovate really means freedom to acquire"
Cheers

OK. I'm old. Full text indexing and regular expressions are fairly recent (last few years) additions to some databases.

As far as Netezza goes, performance has an inverse relationship to row width. It is more so with Netezza than any other database. Which approach works better has a lot to do with the application and your ability to leverage zone maps in queries. I would play around with different configurations (single table versus co-located split table) and queries.

[Sponsored content]