SSAS Named Query Key Column

Question

I am fairly new to SSAS and I have tried to find out the answer to this question but have had no luck.

I have a data source view with multiple tables and their relationships defined. However what I want to return to my application are values from multiple tables with some processing performed on them. Such as GeneralLedger table has FacilityCodeId and the FacilityCode table has FaciltyCode and FacilityName values. I want to return the GeneralLedger amount but instead of just FacilityCode I plan to return 'FacilityCode - FacilityName'

From my understanding the best route to handle this is to create a named query. I have created the query with all the values I want, I go to create a new dimension on this named query and I am uncertain as to what I select for my key columns.

Any help or advice if I am heading in the wrong direction would be greatly appreciated.

Answer 1

Define a named query like the following;

SELECT   [col1] + [col2] AS [key]
        ,[col1]
        ,[col2]
FROM     [db].[table1]

And select [key] as business key. This assuming that the combination of col1, and col2 will be unique.