Modeling multiple multivalued dimensions and other design questions

Hello, all. This is my first time working on a dimensional model and I have some questions regarding multivalue dimensions.

I am modeling our Remedy trouble ticket process and was settling on the grain being each individual ticket. The problems come in when I started looking at the technicians, work log, and audit log. The work log and audit log are very similar in design. For each ticket there is a CLOB field that holds all the work log entries for the steps the technician took to resolve the problem. There is a timestamp, technician that made the entry, and the actual entry. The audit log is similar but tracks changes that happen to the ticket, timestamp, technician that made the change, type of change (i.e. assigned to new group, status changed from pending to work in progress, etc.) I need to break these CLOBS apart into individual rows for each entry to allow the business users to search for tickets that had an entry made during a time period, or work log entries made my certain individuals, etc. This puts this at a much higher grain than the fact table and I was looking at information for multivalue dimensions and thought I had it resolved with the weighting, etc. But then it occurred to me, how do you handle weighting when you have multiple mulitvalued dimension? We are looking at getting Business Objects edge and they built a demo universe for a single multivalue dimension, but it didnt occur till today about multiple mutlivalue dimensions.

The second issue is there is a technician table with a column that lists all the groups "group1; group2; group3" that technician belongs to and a group table that has the individual groups. I broke the column out into a bridge table between the two. Now, the ticket can only be assigned to one group at a time and the purpose of the bridge table is just to model the tech/group relationship and track changes. If I include the resolving user in the report there is a cartesian product that duplicates the fact row for each group the tech belongs to, which gets fixed if I join the group directly to the fact table. This shows up as a loop however and am worried about how Business Objects will handle that. Should I be modeling this differently?

And two final questions, the facts are essentially all derived and are 1 or 0 and used for sums and the sums will be used to calculate percentages, however if the user drills down, 1's and 0's are not exactly informational when looking at an individual tickets. Yes/No would be better, I could probably figure something out in the BI tools, but curious how others have handled this. And the final question. There are several derived timespans such as total ticket resolution hours, total business hours, total time in pending, etc. I thought of storing these as the number of seconds, but is it reasonable to force the business users to apply math, or functions against this to get a meaningful days hh:mm:ss time format? In the end, how much work should I expect them to do to get information?

I am attaching a limited model of the questions I have. Any help will be greatly appreciated.

Nobody has an idea?

It sounds to me like you need to revist your basic assumption that the core fact table grain is a customer ticket. From your description, it sounds like your Work Log is an underlying (transactional, perhaps factless) fact table.

In addition, I'd think about developing an accumulating snapshot fact table for Customer Ticket might be useful.

These concepts -- transactional, factless, and accumulating snapshot fact tables -- are all described in The Data Warehouse Toolkit (2nd Edition).

Joy, thanks for replying. After reading your post, I've done a lot of looking and thinking. My current plan now is to combine the Audit and Work log into one table with another column to filter between the two. This will provide a much more useful view of what happened during the life of the ticket as well as cutting down complexity. I don't really have any issues with making this a separate fact table, however the only tie in to the ticket fact table would be the ticket number degenerate dimension. It doesn't make sense to try and tie the two together using any of the dimensions that are in both as there is no correlation. This really makes it sound like the header/line item issue discussed in the book and articles. But if I go that approach, for every new log entry made, I would need to update the fact measurements for all log entries of that ticket to apply a weighting so the facts aggregate properly. An alternative to recalculating the facts is to only have the facts on one of the line items with the rest of the ticket log entries containing 0's for their facts. I don't know if that is good design practice however. The only other idea I have is to keep it two separate fact tables and use the BI tool to sub query the log table when they want to drill down into the log.

My only other issue is the Technician dimension. Something I didn't explain fully is there are two locations that list technician. One is the ticket submitter and only lists their name. The other is the ticket resolver and lists their name as well as the group the ticket was assigned to. Having a collapsed user/group table would work fine for the ticket resolver and eliminate the multi-value bridge and provide a surrogate key for each technician/group name pair. But the problem then lies in which tech/group name pair surrogate key to associate with the ticket creator. For one derived fact we need to determine if the creator was in a specific group hierarchy, as well as aggregating upon the creators main group hierarchy.