How to store multiple text remarks for each ticket...

I am creating a Data Warehouse for our ticketing system that our helpdesk uses because it really has no reporting capability built in at all. Modeling the Data Warehouse for everything up to this point seems pretty straight forward with the exception of how to store the multiple text remarks for each ticket. Basically every ticket in our helpdesk system can have multiple remarks each with their own timestamp and comment text. I find myself saying that this data probably doesn't belong in the Data Warehouse, but then again it's useful at times for some reporting. Since it's a one-to-many from the ticket to the remarks, how would I go about modeling this data into my Data Warehouse?

I have been scouring these forums all morning while I wait for a response and the general consensus from what I read is that you never want to store text values in a fact table which is what I was considering doing with these remarks. I understand the reasoning now that I have read through a lot of the explanations in depth. So then that leaves me the option of storing them in a dimension, but how would I do this since each row in the dimension would get it's own unique primary key to be linked to the fact table. The relationship needs to go the other way for this to work, but that sounds as though it is not properly designed.

Just thinking out loud.

This is what I am thinking, but I am not too sure about it's real world performance/implications:

Is one to many or one to one? If it's one to one and the comments are rarely required, I would put the comments in another fact table. This fact would have the PK of the first fact table and the comment(s). If you ever need them join across and pull them in. If the comments are somewhat standardized, another option is to junk dim them.

It's a one-to-many. For every ticket, it has many comments. I am not sure I understand Junk Dimensions.

If there are many remarks to an issue and the fact table needs to be one row per issue then you have a multi-valued dimension and need a bridge table between the fact and remark. The bridge would contain the issue key and remark key.

Nice! I didn't even consider a multi-value dimension with a bridge. Many thanks!!!

In this context, why the need for the bridge?

Shouldn't the fact table be at the Comment Level? Or maybe it's 2 fact tables, one at the comment level and another at the Ticket level?

Isn't the ticket the same as a "grocery basket" and the comments the same as the items in the basket? Or the ticket is a claim and comments items in the claim. Comments can be rolled up to the ticket.

Also, the ticket is submitted by a user and the comments are usually entered by the IT guys, updating the status of the ticket.

I can understand a bridge table for when it's a many to many relationship such as in medical when a claim can have multiple diagnosis and treatments. But this scenario sounds like 2 facts at 2 different grains.

How many tickets? How many comments? How many comments per ticket?

The ticket is exactly the same as a "grocery basket" and the comments are indeed the items in the basket. No doubt about that. We are talking about 200,000 tickets and each one has an average of about 5 comments per ticket. So are you saying the comments should be in the fact table or in a separate fact table? I thought text in a fact table was a huge no no?

I kind of agree with Jeff, if the comments are free entry texts that can’t be standardised by a set of predefined remarks. So the other fact would be fact_comment with Issue_key (FK) and a degenerate dimension “Comment” in it. You may think of fact_issue as an aggregate fact based on fact _comment, where for each issue, only one nominated comment (eg. First one) has significant measures while others are 0. It’s good practice to fabricate a count measure and default it to 1 so that you can use SUM across all levels fact consistently.

However the more user friendly data collecting system would be having a set of predefined remarks and let user tick their boxes. In this case the predefined remarks would be a dimension on its own and everything else would fall together in the fact naturally.

So would the fact_remark would be joined to dim_issues via the issue_key, or joined to fact_issue via the issue_key? Also is it standard practice to put audit timestamps on all of your tables (dimension and fact) like insert_date and last_update? I do that in my transactional system databases, but not too sure about the DW. I know for slowly changing dimensions you need dates, but wasn't sure about just regular dimension and fact tables.

Thanks for all of your guys' help.

The comments should be in a Dimension table.

I would have 2 facts. The Comment Fact would look exactly like the Ticket fact except it would have the Dimension Key for the Comment Dimension and would be about 5 times longer. I don't like factless fact tables, so I would put a Comment Count as the measure and all of the values would be 1. It's easier for my reporting tool to summarize than to count distinct.

The Ticket Fact could be an aggregate from the Comment Fact, where you remove the Comment Dimension Key and summarize the Comment count.

Jeff,

So two fact tables and the comments in a dimension table. Why do you say the comment fact would be 5 times longer than the ticket fact? Am I missing something here?

Because each Ticket has 5 comments. 5 rows per ticket means that the comment fact would have 5 times as many rows as the Ticket fact.

Jeff,

Gotcha, you were talking rows, not columns. My bad.

Thanks a bunch!

Jeff Smith wrote:The Comment Fact would look exactly like the Ticket fact except it would have the Dimension Key for the Comment Dimension and would be about 5 times longer.

Jeff, I assume you are referring to the free entry comments instead of predefined. I wonder if the Comment Dimension would be exactly the same size as the comment fact as well. If so, is there any need to have a separate dimension for it instead of treating it as a degenerate dimension in the comment_fact, which is better for storage, ease of query and performance.

I haven't done this before. I would talk to the DBA before I implemented. I would think that the size of the comment field would impact the design. I try to avoid this type of stuff if at all possible. I suppose comments could be used for mining for certain words or something like that, but in general I don't think a DW is the correct place for this type of info. I think it's more appropriate to pull this info directly from the source system. When you look at comments, it's going to be 1 ticket at a time and not full table scans, which is what a Star is intended to do really well and which taxes a transaction system. Looking at the comments of a ticket shouldn't tax the transaction system.

Unfortunately, many transaction systems don't have any type of reporting so they push all reporting on to the DW, not just analytical reporting.

I can see pulling certain aspects of the comment level info into a Dimension, such as the technician who entered the comment. But the comments themselves can't be used for analysis unless you search the comments for certain words.

Too bad the reporting software can't be pointed to the transaction system for such reports.

My next question is, you may need to partition the fact_comment table for performance purpose as it will become much bigger than other facts. I imagine you would also have a date key in the fact table so that you pave the way for partitioning. With comment dimension, you might need to add a date key just for partitioning, but it would not look as natural as in fact table.