Forum Discussion
How do I combine items with different names?
If you don't care about the name at time-of-sale, but only want to plot current name, then there's no need to deal with a type 2 SCD. Type 1 would be sufficient (how you track in the source is irrelevant to how you handle in the analysis and presentation layers).
If you can utilize [CustomerID] for all of the relationships in your model that have to do with customers, you can just store a single value for [CustomerName] in your customer dimension.
You can utilize SQL or Power Query to find the most recent name for a given [CustomerID]. If it's not important to preserve a specific, most recent name, you could even take a shortcut by using the 'Remove Duplicates' transformation in Power Query - this would non-deterministically remove all but one [CustomerName] associated with each [CustomerID]. It may even be deterministic with an appropriate sort order (unsure how the transformation is implemented in PQ).
CraigBlackman Since you have the account_id, I'd just create a mapping table which will be your dimension. Each account_id and the company name like greggyb suggests.
You could easily create a process after the initial table creation to check / add new companies going forward. (based on the assumption that the initial company name would stay going forward.)
CraigBlackman You could create a seperate Dimension table that rolls the names up to the parent company, in that respect it might be easier to determine missing companies that may be causing your blanks as well. So every company would be in your dimension table, and some would roll up to a parent and others would roll up to themselves...
Something like:
company Parent
A 1
B 1
C 2
D 3
And how would I do that exactly?
Thanks for you help.
What is your backend, SQL?
Can you show a sample of what your data looks like now?
Ideally, I would try to do this on the database side, is that possible in this case?
Anonymous,
It is indeed a SQL backend
What would you like to know in terms of how it looks?
