Comments for facts

View previous topic View next topic Go down

Comments for facts

Post  eshtee on Tue Jul 19, 2011 11:13 pm

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

eshtee

Posts : 6
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Comments for facts

Post  rob.hawken on Tue Jul 19, 2011 11:43 pm

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

rob.hawken

Posts : 13
Join date : 2010-09-19

View user profile

Back to top Go down

Re: Comments for facts

Post  ngalemmo on Wed Jul 20, 2011 12:32 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Comments for facts

Post  eshtee on Wed Jul 20, 2011 12:49 am

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

eshtee

Posts : 6
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Comments for facts

Post  rob.hawken on Sun Jul 31, 2011 10:27 pm

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

rob.hawken

Posts : 13
Join date : 2010-09-19

View user profile

Back to top Go down

Re: Comments for facts

Post  ngalemmo on Mon Aug 01, 2011 9:55 am

rob.hawken wrote: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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Comments for facts

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