Confusion - star, snowflake, bridge table

Appreciate your expert comment on following:

Entities: Quote, Trade, DIRequest, Order User, Customer, Trader, Sales or Marketer and Product
- User is a user who is using the system or interfacing with the system. i.e. requesting quote or placing a trade
- Sales and Trader are both of type employee
- Customer can have one or more users
- Quote can be requested by User (Customer) or User (Sales on behalf of customer), but in this case customer details are not captured in OLTP system
- Trade has Customer and Trade and Sales
- Order and DIRequest can have one or more user association. One have created the request, other one provide response (reject, amend, cancel etc)

Key Relationship
- User n Customer (M to 1)
- User n Sales (1 to 1)
- User n Trader (1 to 1)

My Dimension model looks like:
Fact Tables: fQuote, fTrade, fDIRequest, fOrder
Dimensions: dUser, dEmployee, dCustomer, dProduct, dTime

dUser {SKUser, UserID, FName, LName, ...., SKEmployee (FK), SKCustomer (FK)} - one of the FK would be null
dEmployee is meant to store Trader or Sales guy's details

Star-A: fQuote, dUser, dProduct, dTime and dCustomer (indirect relationship via dUser)
Star-B: fTrade, dEmployee, dCustomer, dProduct, dTime
Star-C: fDIRequest, dUser, dProduct, dCustomer (vis User), dTime
- In above star fDIRequest to dUser has 1 to M relationship

Questions Are
a) Is my list of Fact and Dimension correct
b) Is it possible to have 1 to M relationship between Fact and Dimension table
c) Is it ok to connect two dimension using indirect relationship, see dUser scenario above

I am not convinced that this is the right design, how can I make it better?

I also have scenario that is discussed in one of the earlier thread. Quote can have one or more error, I have denormalize my fQuote table to capture errors code, provision for max 5 error code. Is it better to have separate mapping table for fQuote and dError? Is this what is called a Bridge table?

Thanks
-nilesh

nilesh,

You're throwing a lot of information out there in a single post. Most of it very high level. Your questions seem a little to generalized for answers on the forum. You'll get more answers in general if you break things down to specific design questions. However, I'll see if I can't answer some of your questions.

a) No one can really know this without seeing the source data and really understanding the target BI audience and business. This sounds more like a question for a paid consultant who has the time to review your source data and user requirements, instead of a question that could be reliably answered on a forum like this.

b) I assume that you are discussing the issue with Error codes here. I recently encountered almost the exact same issue. I haven't seen this scenario addressed directly in any of the Kimball Group's literature. That doesn't mean that it is not there, just that I haven't found it: but they've written a lot of material, so it might be out there!
If there are only a handful of possible codes with few likely to be added, then you just make each code it's own dimension with a "Yes/No" value. However, since you have a possibility of up to five codes per row, this may not be the case.
There are several ways to model the error codes, but I don't believe that is the key issue. The real problem is that adding multiple error codes to a single row (many-many) through a bridge table can explode a view of your data incorporating error codes to make facts non-additive. That means the sum of the parts appears to be more than the total, because of overlapping codes. The strategy here is to provide a separate data package view, or set of reports that is exclusively for analyzing errors. Then train users to understand the purpose and caveats of working with this particular analytic application.
One more hint on modeling this type of data - if you are working with a relatively small set of codes, say less than 16,32, or 64 - you might check if your database platform efficiently supports bit mask fields and bitwise operators for joins along with bitmap indexing - this could allow you to skip the "bridge table". I know MS SQL Server in particular has some pretty strong feature support for this.

c) Regarding User-Trader-Customer-Sales. You should usually resist the compulsion to normalize the design. If Customer, Trader, and Salesperson are separate entities or roles in the system, you should probably keep them as completely separate dimensions. The User-Customer relationship sounds like two separate levels in a customer hierarchal dimension.

Once again, if you want to get more replies to your inquiries, try to break your questions down to one smaller question per post. That also makes things more helpful for other users who are searching the forums for answers.

Thanks for your advice - bigjonroberts, appreciate it.