2 Replies Latest reply on Aug 18, 2018 10:50 AM by Ken Flerlage

    SQL Views not passing through underlying table relationships?

    Robert Looney

      The problem we're experiencing is that views aren't auto-creating correct relationships in Tableau, even when the underlying tables have relationships defined.

       

      Documentation from Microsoft indicates that unless you specify "WITH VIEW_METADATA", the base table meta data is passed back by default (CREATE VIEW (Transact-SQL) | Microsoft Docs ).  So it seems like this should be working by default?

       

      Simplified example to duplicate (in an empty DB):

       

      CREATE TABLE [dbo].[Fact] (FactID int, DimensionID int, AaaID int, Amount money, CONSTRAINT PK_Fact PRIMARY KEY (FactID));

      CREATE TABLE [dbo].[Dimension] (DimensionID int,AaaID int,Name nvarchar(50),CONSTRAINT PK_Dimension PRIMARY KEY (DimensionID));

      ALTER TABLE [dbo].[Fact] ADD FOREIGN KEY (DimensionID) REFERENCES [dbo].[Dimension](DimensionID);

      -- then...

      CREATE VIEW [dbo].[FactView] AS SELECT * FROM [dbo].[Fact];

      -- then...

      CREATE VIEW [dbo].[DimView] AS SELECT * FROM [dbo].[Dimension];

       

      When you drag in the Fact and Dimension, you see the correct relationship auto generated:

       

      However, when you drag in the FactView and DimView, Tableau guesses the relationship (and wrongly picks the same named AaaID column, which isn't a FK to anything):

      Creating views "WITH SCHEMABINDING" doesn't seem to change anything here either.

       

      Has anyone else experienced this or figured out how to get views to correctly pass through underlying relationships?