Factless Fact table or 1:M Dimension Relation

[shinji23]

Hi, I am struggling with building first Sale DW for our company, and I would like to get some advice on DW design. Our company has 3 lines of product (Reproductive tissue, Reproductive tissue donor information product, Cell Storage). For our company, donor is almost like a product manufacturer. Donor provides reproductive tissues and their information. One of the requirement I have is 'find out the relationship between number of donor information and reproductive tissue sales'.

Here is Dimension table for product/donor/Donor information

Dim Product
- Product Key
- Product Category
- Product Name

DimDonor
- Donor Key
- Donor Number
- Active Date
- Retired Date
- Active

DimInfoProduct
- Info Product Key
- Info Product Name


Here is my sales fact table

FactSales
- Customer Key
- Product Key
- Donor Key
- Order Date Key (Role Playing Dim Time)
- Invoice Date Key (Role Playing Dim Time)
- Sales Person Key
- Ship Type Key
- Order Number
- Invoice Number
- Qty
- Amount

I do not intent to create individual sku line for each product because donor number is basically individual sku for our company. I am putting Donor Key in sale fact table. I can combine donor key and product key for individual sales item info.

Now for each donor, donors could have multiple information product, and I need to add information product activation date because information product could activated in different time frame.

In this situation, do I create factless fact table or build relationship? Since I need to put info product activation date, I assumed I need to creat factless fact table. Here is my factless fact table:

Fact Donor Info Product (Factless Fact table)
- Donor Key
- Info Productkey
- Active
- Activation Date Key

am I on right track???

[ngalemmo]

Don't see why you need the faceless fact. What would it be used for that could not be addressed in the original fact table? Why wouldn't activation date be an attribute on the info product dimension?

[shinji23]

Thank you for your reply ngalemmo! If I add activate date as part of product dimension, don't I have to create relationship (1-to M on Donor and Product) between Donor dimension and product dimension and remove donor key form fact table?

So updated design would look like

Dim Product
- Product Key
- Donor Key
- Product Category
- Product Name
- Activation Date
- Active


DimDonor
- Donor Key
- Donor Number
- Donor Educatoin
- Donor Height
- Donor Weight
- Active Date
- Retired Date
- Active

FactSales
- Customer Key
- Product Key
- Order Date Key (Role Playing Dim Time)
- Invoice Date Key (Role Playing Dim Time)
- Sales Person Key
- Ship Type Key
- Order Number
- Invoice Number
- Qty
- Amount

One of the analysis we do at our company is the Donor Tissues sales vs Donor Information products and its sales (i.e. does donor information product has any direct impact on tissue sales? number donor information has any impact on tissue sales etc.) If donor is missing one information product, do I still create product entry in product dimension with blank activation date or just don't create any entry in product dimension?

Again, thank you very much for your time!

[ngalemmo]

You tell me. If a product is unique to a donor why do you have a product dimension at all?

The thing is, if you look at product as independent of the donor, and use it to contain general desriptive information about the product, you still have a donor context through the existing fact table. I do not understand why the product dimension needs to have any information at all about individual donors. You are not trying to keep inventory or operate the business with this model, correct?

[shinji23]

Once again, thank you very much for your reply!

Correct sir. I am just trying to build DW for sales analysis. I added donor key, because one of our analysis requires us to analyze the number of tissues sold based on number donor information products, broken down by individual donor. As I mentioned on earlier post, each donor is required to provide us with 6 types of information regarding themselves. Once we collect this information, then we turn collected donor's information into product and sell it as donor information to our customers. This is reason why I added donor number on product dimension. In addition, typically donor information products get released on different day, because donors don't usually provide us with their information on time. Hence, I added release date on product dimension. Donor is in a way, manufacturer, but at the same time their information product is similar to sales promotion.

[shinji23]

I think I finally figured it. Since donor information product can be treated as attribute for each donor, I can simply put each information product's activate date in donor dimension and I think this will resolve my problem!

[Sponsored content]