Dimensional Modelling

In OLTP I have product table -- contains product information

customerproduct table -- customer product info like when this customer bougth this product etc (custid, prodid is PK)

customerprodsubprod table -- customer product sub product information (products have sub products ) (custid, prodid , subprodid is PK)

No mesuares in any of these tables ; All are descriptive attributes.


Can you please share your ides on how to dimensional model the above tables in GENERAL.


Thanks
jeevan.

Customer dimension
Product/sub product dimension hierarchy
Fact table in the middle

I did not get it . Can you please explain it.

I assume you understand the dimensions. The fact is a factless fact table. You could be counting customer product installs or disconnects, or customer install/disconnects. More importantly, reporting requirements drive fact table design. That would be an easier way to approach this problem.

Here's a link to some of Ralph's writings on what I'm trying to get across here. Factless Fact tables

Please bear with me . Please help me understand . I do understand FactlessFact.

My question is in tblCustFileProd , I have customer specific product information . In CustFileSubProdId I have customer specific product and subprod information . How can I move these columns to product dimension as they are related to Customer and product.

I gave the column names. Can you please explain me your approach.


tblProduct

ProdCd
ProdTypeCd
ProdEffDt
ProdExpireDt
ProdStatusCd
ProdStatusDt
WorkOrderCd
SupplierProductFlg
InClientPolicyFlg
ProdLevelCd
ProdBrdcstMsgTxt
ProdCmnt


tblCustFileProd
ProdCd
CustFileProdId
CustFileProdStatusCd
CustFileProdStatusDt
CustFileProdStatusChngReasonCd
CustFileProdActivitationDt
CustFileProdAuthDt
CustFileProdSeqNo
LegacyCustFileProdExcptnFlg
LegacyCustFileProdExcptnDesc
LegacyCustFileExcptnAmt


tblCustProdSubProd

CustFileSubProdId
CustFileId
ProdCd
CustFileProdId
SubProdCd
CustFileSubProdStatusCd
CustFileSubProdStatusDt
CustFileSubProdStatusChngReasonCd
CustFileSubProdStatusChngCmnt
SubProdOnHoldFollowUpDt
CustFileSubProdId
CustFileId
ProdCd
CustFileProdId
SubProdCd
CustFileSubProdStatusCd
CustFileSubProdStatusDt
CustFileSubProdStatusChngReasonCd
CustFileSubProdStatusChngCmnt
SubProdOnHoldFollowUpDt

djeevan wrote:Please bear with me . Please help me understand . I do understand FactlessFact.

My question is in tblCustFileProd , I have customer specific product information . In CustFileSubProdId I have customer specific product and subprod information . How can I move these columns to product dimension as they are related to Customer and product.

I gave the column names. Can you please explain me your approach.

The event of a customer purchasing a product is a row in your fact table. That's where you put the data from tblCustFileProd. Now you can easily answer what products a customer has purchased over time by customer by product. Hopefully that is what your users are asking.