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.

They key column should be a unique key, the business key. If you know that your FacilityCode - FacilityName will be unique, you can use it. However in the named query you would need to define FacilityCode - FacilityName as Key, FacilityCode, FacilityName separately. — Mez
How do I handle needing a composite key? Seperatly all the values might repeat, however five fields of the same should not repeat. — Idea Man

Mez Mez · Accepted Answer · 2014-07-09T14:21:58

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.

SSAS Named Query Key Column

1 Answers