September 27, 2016 at 8:28 am
Order to our website can come via links on our partner sites, retail shops email blasts, direct google searches etc. This is really an attribute of the customer i.e. this customer was referred from http://www.asite.com. Many customers can be placed on one order (a wedding party) therefore we could have many referral sources for one order. The business wants all customers on an orders to inherit the party leader of the order's referral info as this will be how commission is paid out. As you cant have a party leaders-party member relationship without an order, in this instance this make the referral source also an attribute of the order.
I can easily pull in the referral source of a customer when building the DimCustomer. I can also pull in the referral source of the party leader of an order when building the FactOrder. MY question is should the referral source be a dim on its own? (this would have to be built every day as referrals can come from any www address) if so wouldn't this mean the lookup on the DimReferral could only be done using the address string? (how would this DimReferral tie to DimCustomer referral info?) OR should i just put the web address in the fact order table as a referral source string as it is in DimCustomer?
Any other ideas?
September 28, 2016 at 7:57 pm
General rule of thumb that I've always followed and read from references such as experts like Ralph Kimball is that if you can find a clear correlation between referral source and the customer, then it belongs in the DimCustomer dimension. Exceptions to that rule depend on how large DimCustomer grows in records with each attribute you add.
As each customer will only likely have one referral source, it should be safe as long as there is correlation. In this case, I would be willing to bet there is a very strong relation to referral source and customers where someone will want to know how many customers came from what referral sources.
In instances where you have to pull multiple customers and their correlated attributes like referral source, then how is that any different from any other attribute to the customer like name, account date and so forth? If you don't see a reason to split out those other attributes, then you can assume referral source is also safe to leave in DimCustomer.
October 3, 2016 at 5:24 am
"As each customer will only likely have one referral source, it should be safe as long as there is correlation. In this case, I would be willing to bet there is a very strong relation to referral source and customers where someone will want to know how many customers came from what referral sources."
yes this will be required, but they will also want to know how many orders came from a referral source which is when the party leaders referral source would be inherited by the party members. Im doing a transform on the fact order using the dimcustomer for this just now but i think i may be able to build this as a measure into the cube?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply