Hi All,

How can i set the dimension usage between fact and dimension using multiple key?
I was able to create relationship(see the snapshot) in DSV. but dont know to do in dimension usage tab. 

Hi There,

I think this is what you are looking for

https://social.msdn.microsoft.com/Forums/en-US/cdfb8b7c-3908-4bee-824b-7b86e3705b32/dimension-usage?forum=sqlanalysisservices

HTH!

Prathy :)

Hi,

Try like below,



Set the key column property of the key attributes to be Composite Key

Hi,

Thanks for reply.

I have 2 fact table and one dimension. The first fact table has the relationship with dimension using single column.But for the second fact, i need to take another 3 column combination from the same dimension. is it possible to create composite key in this scenario?

Hi NikkRED,

According to your description, you want to create composite key with three columns. Right?

In Analysis Services, we should specify unique key as KeyColumns. Otherwise it will throw error like "A duplicate attribute key has been found when processing:.....". So in your dimension, if the combination of three columns are unique values in dim table, you have to specify all the three as KeyColumns. Please refer to an article below:

SSAS - Understanding KeyColumns Dimension Property

Regards,

Hi,

Here, one fact table(FACT_1) is joining with one column(Col_1) of Dimension Dim_A and another fact(FACT_2) is joining with Dim_A with another 3 columns (Col_2,Col_3,Col_4). Actually the unique value in that dimension is Col_1 but ,i got another requirement to add another set of data(Fact_2) which has different set of granularity(Col_2+Col_3+Col_4). 

Hi NikkRED,

For this scenario you have to create two named queries on DSV using the Same Dimension A Table. For One Named Query Create Logical Primary Key on Col_1 and Map with your Fact_1. For Second Named Query create a Logical Primary Key on Col_2+Col_3+Col_4 and map with Fact_2

Thanks, Let me try it out. But if we create new named query, we need to create one extra dimension right?

Yes . you have to create the extra Dimension.