DW Factless Fact Table w/ Transactional Free Form Fields

Question

I am reconstructing a Factless Fact table from a transaction table. There are obvious shared dims like Org, Status, Service, ServiceAction, Send Date, etc. However, There are 2 issues I'm trying to work through:

1. On the Transaction table there are Free Form Entry fields for values like phone, Email, chkbxRequestReceipt. They are all directly related back to the TransactionKey. If I pull these fields out of the fact table into their own dim, it creates a 1-1 dim-fact relation which does not seem correct.

2. The ServiceAction dim is 1 field on the fact table but then broken out into 3 different dim tables. This was done because the Services share almost no common fields. There is 1 Transaction for every ServiceAction. So the sum of the rows in the 3 Service tables = total rows of the Transaction Table.

Could anyone offer advice on the best way to model this?

Answer 1

1) Not every data item necessarily belongs in a dimensional model. Dimensions are supposed to be the things you would analyse your measures by. Are your users going to look at any of your measures by phone number, email address, or whether a receipt was requested? If not, these things quite likely don't belong in your dimensional model. They might belong in a normalized warehouse layer or a reporting database, if you also have those.

If someone insists that these must be available within the dimensional model (and their reasoning is sound - say, they do analysis using more obvious dimensions first, and occasionally they then need to look at one of these data items to take action on something odd they've found):

• First of all check whether these should be attributes on existing dimensions. Maybe email address and phone belong to a dimension like Org, or perhaps a Customer or Person dimension you haven't mentioned. If they belong together conceptually but don't fit with an existing dimension in your model, it's worth thinking about whether there's a dimension you should be adding. You've mentioned these are free text, so you might well need to manage the data quality as part of your ETL process if you go this way.
• If they don't make sense on another dimension, or if you have any free text fields with things like comments/notes, then Ralph Kimball's method of dealing with free text fields is to shove them in a dimension so that people can add them in when they need to.

2) It's honestly difficult to judge whether this is correct from the information given. Without knowing more about what a Service is, what a ServiceAction is, what fields exist in your ServiceAction dimension, etc. I wouldn't want to guess at a suitable design for this. I'll happily revisit this if more information is added.

Answer 2

1. You can consider the Phone, Email, chkbxRequestReceipt as a Degenerate Dimension (or multiple degenerate dimensions). A Degenerate Dimensions is a dimension without a dimension table. You have degenerate dimensions when the fact table has transaction level grain.

2. About the three tables for ServiceAction. My suggestion is to put them in the same dimension table, but I understand that you will have a table with a lot of NULLs. If you just write your reports manually three tables aren't a problem, but if you use a tool which leverages a dimensional model to generate automatically the SQL code, then you probably need to have a single dimension table.

You can even think to have three different dimensions that share the same column on the fact table, this will work, but it can be a bit confusing for the business users, if they can create their own reports. Anyway in this case you will need also to create a forth dimension AllSerivceAction in case you need to create a report where you want to show all calls and also information about ServiceAction