Email Domain - EmailAddress Hierachy

[Thejuan]

Sorry if this is already covered.. search is down.

I want to create a Dimension that is Based on an emailaddress field.

But i want it to be a heirachy starting with the domain.
So all emailaddresses with the same name are grouped under their domain.

I can see any string functionality in the Calculated Memebers (like substring)

How is this done when creating a cube?

 

[MDXer]

Your est bet is to create a physical table that would be used to populate the email dimension. Build a process that would map the various portions of the email address into a heirarchy.

The approach I might take would be

email_dimension (table)

EmailHey   EmailAddress       DomainID   DomainName TopLvlDomainID TopLVLDomain
--------   ------------       --------   ----------  -------------  -----------
1          JoeBob@hotmail.com 1          HotMail     1               com
2          JoeBob@cox.net     2          Cox         2               net


EmailTopLvlDomain_LKP (table)

TopLvlDomainID TopLvlDomain
-------------- -------------
-2             Unknown
1              COM
2              NET
3              GOV
4              ORG

EmailDomain_LKP (Table)

DomainID  DomainName
--------  -----------
-2        Unknown
1         Hotmail
2         Cox
3         Yahoo

Have a process possibly usig substring and paern matching tha would compare the various portions of your email address to the corresponding tables

Something like

Select
IsNull(DomainID,-2)  ,
IsNull(DomainName,'Unknown'),
ISNUll(TopLvlDomainID,-2) ,
IsNull(TopLvlDomain,Unknown)
From MySourceTable
Left OUTER JOIN EmailTopLvlDomain_LKP td ON td.TopLvlDomain = Reverse(SubString(Reverse(EmailColumn),0,PATINDEX('%.%',Reverse(emailColumn))))
Left OUTER JOIN EmailDomain_LKP ed ON ed.DomainName = Reverse(SubString(Reverse('Nobley@hotmail.com'),PATINDEX('%.%',Reverse
('Nobley@hotmail.com'))+1,(Len('Nobley@hotmail.com')-(PATINDEX('%@%',
Reverse('Nobley@hotmail.com'))-1))))

Not Glamourous but something like this would do the trick. Then you would just build your dimension using the appropriate columns for level keys and names.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck