Dimensional Modelling

View previous topic View next topic Go down

Dimensional Modelling

Post  djeevan on Thu Jul 23, 2009 6:29 pm

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.

djeevan

Posts : 3
Join date : 2009-07-23

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  BoxesAndLines on Thu Jul 23, 2009 6:51 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  djeevan on Thu Jul 23, 2009 8:39 pm

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

djeevan

Posts : 3
Join date : 2009-07-23

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  BoxesAndLines on Thu Jul 23, 2009 9:06 pm

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
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  djeevan on Thu Jul 23, 2009 9:31 pm

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

Posts : 3
Join date : 2009-07-23

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  BoxesAndLines on Thu Jul 23, 2009 10:17 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional Modelling

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum