Data Warehouse Design Help

[Edwin Reina]

Hello, I am taking my first shot at designing a data warehouse. I have always worked with transactional databases and am trying to think differently aka no normalization :) The initial source for this DW is a transactional database with information very similar to Netflix. The main business process I want to answer first is 'Customer Life Cycle'. The dimensions I have so far are Date, Customer, Affiliate (source that sent the customer to our site) and possibly Transaction Type (Not sure about this one). The Fact table would be each Transaction. A transaction can be a successful charge of a credit card, a decline, or a cancel of the subscription

The main questions I want to answer will be:
1. Which rebill cycle are customers in? In other words how many months have they been a member and when did they leave
2. How many declined?
3. How many cancelled?
4. How much revenue have we collected?
I want to know for a given time period how many people signed up and who sent us the leads and then how long did each stay on before they cancelled or were declined

Source Tables:
User - All user information including user_level which is defined by the last transaction. 3=active, 4=declined, -1=did not complete signup. Also has the affiliate id of who gets credit for the sale.

Subscription - foreign key is the userid. It's main field is status. 1=active user, 0=nonactive user. Also contains canceldate if someone cancelled. User_level (3) from the user table and status (1) together tell me how many active users we currently have.

Billing_History - All transaction info is stored here.

I hope someone is still reading cause here come my questions :)

1. Should I have a transaction_type dimension or should that 'flag'/field be in the transaction fact table? I have read where I can do either. Having a dimension table seems like I'm normalizing??

2. Billing_History table has a ton of information about each transaction including a field named subscription_payment_number that tells me which rebill each transaction is in. My fact table so far only has a date fk, user fk, affiliate fk, transaction_type fk and then amount ( for revenue purposes). What do I do with all the other billing_history data? Should I take the subscription_payment_number and put it in the fact table as well? It seems like dimension data but I definitely need it. Should I have a transaction dimension and add the transaction_type in there and then the fact table will have the transaction fk and amount?

Any help is appreciated. Sorry for the length but wanted to give as much detail as possible.

thank you,
Edwin