Factless Sales table
3 posters
Page 1 of 1
Factless Sales table
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.
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
Re: Factless Sales table
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
Re: Factless Sales table
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.
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
Re: Factless Sales table
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?
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
Similar topics
» Sales Rep <--> Customer relationship with Sales Fact Table
» Fact Table or Factless Table: Please Suggest
» Advice on factless table use
» Same attribute in multiple dimensions or Create new dimension?
» Help with design of Factless Fact Table SCD
» Fact Table or Factless Table: Please Suggest
» Advice on factless table use
» Same attribute in multiple dimensions or Create new dimension?
» Help with design of Factless Fact Table SCD
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum