Factless Sales table

View previous topic View next topic Go down

Factless Sales table

Post  memphis on Thu Nov 04, 2010 7:03 am

Hi,

I was wondering if there is anything wrong with having a factless sales table?
Basically all I am required to do is to count up the total number of sales that occurred each month grouped by product type. So basically all I have in my fact table is:
Account Number
Policy Number
Previous Policy Number
Date Sold
ProductID
PositionID (which will have employer/seller info + branch info).

I ask this because in most examples, the sales table contains quite a bit more facts, so just wondering if a factless sales table is ok.

Thanks.

memphis

Posts : 19
Join date : 2010-10-21

View user profile

Back to top Go down

Re: Factless Sales table

Post  Jeff Smith on Thu Nov 04, 2010 4:18 pm

I think a factless fact table is kind of a waste of space. Besides, if you don't include quanity and amount you'll soon need to do a redesign because as soon as you provide them counts of sales, they will ask for quanity and amount.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Factless Sales table

Post  ngalemmo on Thu Nov 04, 2010 5:00 pm

Unless you are giving this stuff away for free, Jeff is right.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Factless Sales table

Post  memphis on Thu Nov 04, 2010 6:36 pm

Thanks for the replies,

My requirement in this project is to measure 3 things.
1. Number of cancellations of insurance policies.
2. Number of complaints of insurance polices.
3. Number of sales of insurance policies.

I've already worked out my fact tables and dimensions for 1 and 2 (Here is the design for complaints: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/modelling-free-text-comments-t784.htm
(The design for cancellations is very similar to the complaints.)

I also do agree that the factless table seems like a waste of space, since my source data already provides daily sales information which I can query on fairly easily (it's just not in a fact/dimension relationship).

So how should I go about implementing the 3rd requirement?
Option: A
Would I need to somehow combine 1, 2 and 3 into a single fact table so that eg: have a FactSales table that would contain information about complaints and cancellations?

Option: B
Leave the source data alone and just simply query the source data when I need to know the count of how many insurance policies were sold?

Option: C
Create a factless factsales table as mentioned previously.

Appreciate any help on this.

Cheers.


memphis

Posts : 19
Join date : 2010-10-21

View user profile

Back to top Go down

Re: Factless Sales table

Post  memphis on Thu Nov 04, 2010 10:46 pm

Actually, I had another think, would be advisable to have my FactSales and FactCancellations in one fact table so it becomes sort of like a transactional fact table? In this way, each row would either be a sale record, or a cancellation record.

I'm thinking of having a dimension: TransactionType which would indicate whether the transaction is a 'Sale' or if its a 'Cancellation'.
If I design it this way, the only thing I'm unsure about is to how to treat the 'Cancellation Reason'? I'm thinking of having a dimension CancellationReason to achieve this. However, it would mean that each row that corresponds to a 'Sale' would have a Cancellation Reason of 'N/A'.

Finally, for my fact table, I might need to have 2 Degenerate Dimensions, one for PolicyNumber and one for OldPolicyNumber. Is it advisable to instead only have a single 'Policy Number' column assuming old policy numbers are unique from current policy numbers, so merge the old and new policy numbers into the one column? I think this is doable because old and new only differ in the prefixes.

I've decided to leave the complaints Fact table as it is because it doesn't tie in as well with sales and cancellations. Does anyone agree?

memphis

Posts : 19
Join date : 2010-10-21

View user profile

Back to top Go down

Re: Factless Sales table

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