v_DIM_MARKETINGSOURCECODE
The marketing source code dimension breaks out all the marketing source code parts for marketing source codes.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| MARKETINGSOURCECODEDIMID | int | BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID] | |
| SOURCECODELAYOUT | nvarchar(100) | BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID] | |
| SOURCECODE | nvarchar(50) | BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID] | |
| SOURCECODEISACTIVE | bit | yes | BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID] |
| SITEDIMID | int | yes | Reference key to the site dimension, derived using [dbo].[DIM_MARKETINGSOURCECODE].[SITEID] |
| Package | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Package' |
| Package Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Package' |
| Segment | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Segment' |
| Segment Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Segment' |
| Marketing Effort | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Marketing Effort' |
| Marketing Effort Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Marketing Effort' |
| Channel | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Channel' |
| Channel Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Channel' |
| Test Segment | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Test Segment' |
| Test Segment Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'Test Segment' |
| List | nvarchar(11) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'List' |
| List Description | nvarchar(255) | yes | BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = 'List' |
Definition
Copy
create view [BBDW].[v_DIM_MARKETINGSOURCECODE] as
select scm.[MARKETINGSOURCECODEDIMID], scm.[SOURCECODELAYOUT], scm.[SOURCECODE], scm.[SOURCECODEISACTIVE], scm.[SITEDIMID],
max(case when scmp.[PARTNAME] = 'Package' then scmp.[PARTCODE] else null end) as [Package],max(case when scmp.[PARTNAME] = 'Package' then scmp.[DESCRIPTION] else null end) as [Package Description],max(case when scmp.[PARTNAME] = 'Segment' then scmp.[PARTCODE] else null end) as [Segment],max(case when scmp.[PARTNAME] = 'Segment' then scmp.[DESCRIPTION] else null end) as [Segment Description],max(case when scmp.[PARTNAME] = 'Marketing Effort' then scmp.[PARTCODE] else null end) as [Marketing Effort],max(case when scmp.[PARTNAME] = 'Marketing Effort' then scmp.[DESCRIPTION] else null end) as [Marketing Effort Description],max(case when scmp.[PARTNAME] = 'Channel' then scmp.[PARTCODE] else null end) as [Channel],max(case when scmp.[PARTNAME] = 'Channel' then scmp.[DESCRIPTION] else null end) as [Channel Description],max(case when scmp.[PARTNAME] = 'Test Segment' then scmp.[PARTCODE] else null end) as [Test Segment],max(case when scmp.[PARTNAME] = 'Test Segment' then scmp.[DESCRIPTION] else null end) as [Test Segment Description],max(case when scmp.[PARTNAME] = 'List' then scmp.[PARTCODE] else null end) as [List],max(case when scmp.[PARTNAME] = 'List' then scmp.[DESCRIPTION] else null end) as [List Description]
from BBDW.DIM_MARKETINGSOURCECODE scm
inner join BBDW.DIM_MARKETINGSOURCECODEPART scmp on scm.[MARKETINGSOURCECODEDIMID] = scmp.[MARKETINGSOURCECODEDIMID]
group by scm.[MARKETINGSOURCECODEDIMID], scm.[SOURCECODELAYOUT], scm.[SOURCECODE], scm.[SOURCECODEISACTIVE], scm.[SITEDIMID]