The relationship between the dimension tables of SSAS can only have one cannot have multiple

When we create dimensions in SSAs, it is sometimes possible that one dimension needs to use multiple table fields as dimension attributes, so there is bound to be an association between the multiple tables, but remember that the correlation between the dimension tables and only one cannot have multiple, let's look at an example.

Now we have created a dimension customer, because the attributes in that dimension use the fields of the geography table in addition to the fields of the Customer table, so customer uses two tables.

But there are now two dependencies between the Customer table and the Geography table

One of the relationships is customer.customerkey to Geography.customerkey

Another relationship is customer.geographykey to Geography.geographykey

Ideally we think that when dealing with the customer dimension, the two relationships will take effect as the condition of the Customer table and the Geography table connection, which means there will be customer.geographykey= Geography.geographykey and Customer.customerkey=geography.customerkey such a relationship exists.

But what is the reality?

We now take a look at the properties of the Customer dimension geography Info, whose keycolumns is the Geographykey field of the Customer table, Namecolumns is the City field of the geography table, So this dimension attribute actually uses both the Customer table and the Geography table

Next we work with the customer dimension to see what the SQL statement is when the Geography info property is generated

The SQL statement when generating the Geography info property is as follows

SELECT   DISTINCT [Dbo_dimcustomer].[Geographykey]  as [Dbo_dimcustomergeographykey0_0],[dbo_dimgeography].[ City]  as [Dbo_dimgeographycity1_0]   from [dbo].[DimCustomer]  as [Dbo_dimcustomer],[dbo].[DimGeography]  as [dbo_dimgeography]  WHERE   (     (   [Dbo_dimcustomer].[Geographykey]   =  [dbo_dimgeography].[Geographykey]  )   )

We were surprised to find that although we previously defined two connection relationships between the Customer table and the Geography table, only the customer.geographykey= Geography.geographykey is in effect, and customer.customerkey=geography.customerkey this relationship does not appear at all in the SQL statement above ...

This means that although we define two connection relationships between the Customer table and the Geography table, only one relationship is working when the customer dimension is processed, and the other relationship is ignored by SSAS. Therefore, the relationship between the dimension tables cannot have more than one, and there can only be a single.

Now let's merge the above Customer.geographykey=geography.geographykey and Customer.customerkey=geography.customerkey from the two relationships into a relationship as follows

Then process the customer dimension again, and view the SQL statement for the Geography Info property as follows

SELECT   DISTINCT [Dbo_dimcustomer].[Geographykey]  as [Dbo_dimcustomergeographykey0_0],[dbo_dimgeography].[Dbo_dimgeographycity1_0]  as [Dbo_dimgeographycity1_0]   from [dbo].[DimCustomer]  as [Dbo_dimcustomer],      (         SELECT [ City]  as [Dbo_dimgeographycity1_0], Geographykey as [dbo_dimgeographycustomerkey1_1],[Geographykey]  as [dbo_dimgeographygeographykey1_2]   from [dbo].[DimGeography]     )      as [dbo_dimgeography]  WHERE   (     (   [Dbo_dimcustomer].[Customerkey]   =  [dbo_dimgeography].[dbo_dimgeographycustomerkey1_1]  )      and    (   [Dbo_dimcustomer].[Geographykey]   =  [dbo_dimgeography].[dbo_dimgeographygeographykey1_2]  )   )

It's obvious this time Customer.geographykey=geography.geographykey and customer.customerkey= Geography.customerkey the two connections are in effect, and we have achieved the desired effect.

So remember that there is only one connection relationship between multiple tables in one dimension in SSAs, and only one can actually work if you define multiple connection relationships.

The relationship between the dimension tables of SSAS can only have one cannot have multiple