title: Multiple referenced dimensions using same intermediate dimension
body: <p>SQL Server 2008 R2</p>
  
  <p>I've got a fact table that links to a dimension.  That dimension has multiple date_ids for different dates, start, end etc.  So in the dimension usage screen there are various incarnations of the date dimension linked to the measure group as referenced dimensions.  </p>
  
  <p>It seems that the SQL that is generated to populate the measure group links to the dimension table multiple times and this is slowing things down enormously.  There are actually 6 joins and according to the execution plan each one is 14% of overall cost, so if I can stop it doing this, I figure the thing will execute at about 30% of the current cost. </p>
  
  <p>The fact table is actually a view, so I could add the date_id's to the view and set the dimension up as a regular relationship.  </p>
  
  <p>Is there a cleaner way of forcing more efficient SQL?</p>
  
  <p>The following three tables can be used to illustrate the problem:</p>
  
  <pre><code>CREATE TABLE dbo.FactTable
  (
  Fact1 numeric(16, 2) NOT NULL,
  ForeignKey1 int NOT NULL
  )  ON [PRIMARY]
  GO
  
  CREATE TABLE dbo.IntermediateDimension
  (
  [Key]    int NOT NULL,
  DateId1 int not null,
  DateId2 int not null,
  DateId3 int not null,
  DateId4 int not null,
  DateId5 int not null,
  DateId6 int not null,
  Attr1 nvarchar(30),
  Attr2 nvarchar(30)
  ) on [PRIMARY]  
  GO
  
  CREATE TABLE dbo.DateDimension
  (
  [Key]    int NOT NULL,
  [Date] datetime not null
  ) on [PRIMARY]   
  GO
  </code></pre>
  
  <p>Fact Table links to the intermediate dimension to retrieve the date id's.<br>
  6 dimensions are created in the cube from the table DateDimension.  These are linked as referenced dimensions via the intermediate dimension.</p>
  
  <p>When I process the cube the following SQL runs:-</p>
  
  <pre><code>SELECT 
  [dbo_FactTable].[Fact1] AS [dbo_FactTableFact10_0],
  [dbo_FactTable].[ForeignKey1] AS [dbo_FactTableForeignKey10_1],
  [dbo_IntermediateDimension_2].[DateId1] AS [dbo_IntermediateDimensionDateId17_0],
  [dbo_IntermediateDimension_3].[DateId2] AS [dbo_IntermediateDimensionDateId29_0],
  [dbo_IntermediateDimension_4].[DateId3] AS [dbo_IntermediateDimensionDateId311_0],
  [dbo_IntermediateDimension_5].[DateId4] AS [dbo_IntermediateDimensionDateId413_0],
  [dbo_IntermediateDimension_6].[DateId5] AS [dbo_IntermediateDimensionDateId515_0],
  [dbo_IntermediateDimension_7].[DateId6] AS [dbo_IntermediateDimensionDateId617_0]
  FROM 
  [dbo].[FactTable] AS [dbo_FactTable],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_2],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_3],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_4],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_5],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_6],
  [dbo].[IntermediateDimension] AS [dbo_IntermediateDimension_7]
  WHERE    (       (    
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_2].[Key]   )  AND     (    
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_3].[Key]   )      AND     (   
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_4].[Key]   )      AND     (    
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_5].[Key]   )      AND     (    
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_6].[Key]   )      AND     (    
  [dbo_FactTable].[ForeignKey1]   =  [dbo_IntermediateDimension_7].[Key]   )     )
  </code></pre>
  
  <p>You can see that it's linking to the same row 6 times to get each of the date id's.  It doesn't need to do this.</p>