I am quite new to SQL Server Analysis Services and now try to build my first cube (the first except for Adventure Works tutorial)
The problem is how to specify a particular field in a table to serve as a link to another table, when several options (several keys pointing to the same second table) are present.
In my data source I have a Department table: (irrelevant fields omitted)
| department_key (PK) | ...
and a Discipline table like this:
| discipline_key (PK) | title | department_for_key (FK) | department_of_key (FK) | ...
Both foreign keys in Discipline table point to the Department table and for both the constraints are specified in the DB. Still their meaning is quite different.
When constructing a Discipline dimension in SQL Server Data Tools I would like to specify which of the foreign keys should be used as a link between the levels of a particular hierarchy in the dimension (or in the whole dimension), but I haven't found a way to do this explicitly.
Interestingly, it so happens that SSDT (or SSAS) uses the column that I want now ( department_of_key ) to relate the levels. Nevertheless, it is likely that I would like to use the second key in a separate dimension (or in a new hierarchy in the same dimension) in the future, so it'll be better to know how to control the process.
Although, I suppose this question is a simple one, I have failed to find any solution so far. I also have a feeling that there was something relevant in the tutorial devoted to SSAS, but I couldn't find any help there.
Thank you for any response!
(I will also appreciate any recommendations regarding a better title for the question =) )
I would avoid relying on relationship definitions in SSAS for this - the results are too obscure and difficult to debug ref your post.
I would build a SQL view (ideally specifically for SSAS use only) which covers the entire scope of each dimension. That can be tested independantly and fed into SSAS with predictable results.
Using SQL views also has the advantage of insulating your cube from schema changes and data issues (e.g. Nulls).