ssas - How can I implement multiple nameColumns that target the same table? -

i have 2 tables. first 1 references second 1 multiple times. demo data:

id      fk1     fk2     fk3 ------------------------------- 4009    9419    2282    9005 4057    9419    2112    null 5480    null    4279    null 5989    null    1677    null 

the second table contains names foreign keys in table 1

id      name -------------------------------- 1677    bank account no1 2112    loyalty account 2282    sales account 4279    employee account 9005    warehouse no1 9419    sales 

i have create dimension table1. fields fk1, fk2 , fk3 should use namecolumn attribute, , show values table 2.

i tried creating named query in data source view, if try put cube, deployment breaks without proper error.

select [table1].[id]     ,fk1     ,fk2     ,fk3      ,t2_fk1.name fk1name     ,t2_fk2.name fk1name     ,t2_fk3.name fk1name [dbo].[table1] left join table2 t2_fk1     on table2.id = [table1].fk1 left join table2 t2_fk2     on table2.id = [table1].fk2 left join table2 t2_fk3     on table2.id = [table1].fk3 

how can implement multiple namecolumns target same table?

even if null values can technically used in dimensions in analysis services, bad idea so, analysis services , relational database have different ideas how treat them: analysis services treats them empty strings or numerical zeroes, while relational database, - exception of e. g. old versions of oracle, treat empty strings null - these different. hence when analysis services issues sql statement containing distinct or group by, can happen there more 1 row analysis services expects one, etc.

it best practice avoid nulls in attribute columns, foreign key columns in star schema. in measure columns, nulls fine. thus, should change statement to

select [table1].[id]     ,coalesce(fk1, -1) fk1     ,coalesce(fk2, -1) fk2     ,coalesce(fk3, -1) fk3     ,coalesce(t2_fk1.name, '<unknown>') fk1name     ,coalesce(t2_fk2.name, '<unknown>') fk1name     ,coalesce(t2_fk3.name, '<unknown>') fk1name [dbo].[table1] left join table2 t2_fk1     on table2.id = [table1].fk1 left join table2 t2_fk2     on table2.id = [table1].fk2 left join table2 t2_fk3     on table2.id = [table1].fk3 

or whatever chose replace nulls instead of -1 or <unknown>.