Data load wrongly identifies foreign key relationship. Repro files available.

[Anonymous]

I have found a case where Power BI Desktop fails to correctly identify the one and only foreign key relationship in a very simple database containing only one "child" table and a related "parent" table. Instead of using the correct foreign key column in the "child" table, Power BI Desktop creates an incorrect relationship using another column.

I would like to submit a zip file contiaining the reproduction of this error. It includes a backup of the sample source database, a script for that database, and the PBIX file created by Power BI Desktop (February 2019 version), with the incorrect relationship.

Please let me know how I can submit that information to you. Thanks.

William Schmidt

wschmidt@stratag.com

[v-qiuyu-msft]

Hi @Anonymous,

You can upload all these information to your OneDrive and send the share link to me via private message. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Qiuyun,

I do not know how to share links via private message with you. Isn't there any simple way to upload files pertaining to bug reports?

Thanks,

William Schmidt

[Anonymous]

Qiuyun,

I posted a comment earlier with a database script you can use to reproduce this error. However, I realized that script was not as simple as it could be, so I deleted it and am replacing it with the one below.

If you use this script to create the database (I am using SQL 2017), and then use the February 2019 Power BI Desktop to "Get Data" from that database, you should see that Power BI Desktop gets the relationship betwen dbo.Child and dbo.Parent wrong. It picks up the "wh_ParentIdIsCurrent" boolean column as the foreign key, instead of the "wh_ParentId" column.

I can upload more files (db backup, my .pbix file) if you need them. I hope you see the problem and can do something to fix it, because it's pretty serious if Power BI cannot follow standard database foreign key relationships.

William Schmidt

-----------------------------------------------

USE [master]
GO
/****** Object: Database [PowerBI_RelationshipsWronglyIntrospected_ReproDb] Script Date: 2/25/2019 8:33:28 AM ******/
CREATE DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb]
CONTAINMENT = NONE
-- ON PRIMARY
--( NAME = N'PowerBI_RelationshipsWronglyIntrospected_ReproDb', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\PowerBI_RelationshipsWronglyIntrospected_ReproDb.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
-- LOG ON
--( NAME = N'PowerBI_RelationshipsWronglyIntrospected_ReproDb_log', FILENAME = N'G:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\PowerBI_RelationshipsWronglyIntrospected_ReproDb_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [PowerBI_RelationshipsWronglyIntrospected_ReproDb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ARITHABORT OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET DISABLE_BROKER
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET RECOVERY FULL
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET MULTI_USER
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET DB_CHAINING OFF
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'PowerBI_RelationshipsWronglyIntrospected_ReproDb', N'ON'
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET QUERY_STORE = OFF
GO
USE [PowerBI_RelationshipsWronglyIntrospected_ReproDb]
GO
/****** Object: Schema [warehouse] Script Date: 2/25/2019 8:33:28 AM ******/
CREATE SCHEMA [warehouse]
GO
/****** Object: Table [warehouse].[Child] Script Date: 2/25/2019 8:33:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [warehouse].[Child](
[wh_Id] [bigint] IDENTITY(1,1) NOT NULL,
[ShardKey] [int] NOT NULL,
[ChildBusinessKey] [int] NOT NULL,
[ParentBusinessKey] [int] NULL,
[wh_LoadDate] [datetime] NOT NULL,
[wh_IsDeleted] [bit] NOT NULL,
[wh_RowVersion] [timestamp] NOT NULL,
[wh_ParentId] [bigint] NULL,
[wh_ParentIdIsCurrent] [bit] NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[wh_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Child] UNIQUE NONCLUSTERED
(
[ShardKey] ASC,
[ChildBusinessKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [warehouse].[Parent] Script Date: 2/25/2019 8:33:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [warehouse].[Parent](
[wh_Id] [bigint] IDENTITY(1,1) NOT NULL,
[ShardKey] [int] NOT NULL,
[ParentBusinessKey] [int] NOT NULL,
[wh_LoadDate] [datetime] NOT NULL,
[wh_IsDeleted] [bit] NOT NULL,
[wh_RowVersion] [timestamp] NOT NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[wh_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Parent] UNIQUE NONCLUSTERED
(
[ShardKey] ASC,
[ParentBusinessKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [warehouse].[Child] WITH CHECK ADD CONSTRAINT [FK_Child_Parent_wh_ParentId] FOREIGN KEY([wh_ParentId])
REFERENCES [warehouse].[Parent] ([wh_Id])
GO
ALTER TABLE [warehouse].[Child] CHECK CONSTRAINT [FK_Child_Parent_wh_ParentId]
GO
USE [master]
GO
ALTER DATABASE [PowerBI_RelationshipsWronglyIntrospected_ReproDb] SET READ_WRITE
GO

[Anonymous]

Qiuyun,

I have tested locally and found that the December 2017 version of PBI Desktop CORRECTLY identifies the relationships in this database. However, the January 2018 version of PBI Desktop INCORRECTLY identifies the relationships. I have also tested a few later PBI desktop versions (after January 2018), and they have also all INCORRECTLY identified the relationships.

If what I am seeing is right, this problem has existed for a long time.

[Anonymous]

Qiuyun,

I should also note that after a colleague of mine suggested that the problem might be that our FK column is nullable, I tested with an identical version of the database in which the FK column is not nullable, and the result was the same. PBI Desktop got the relationship wrong.

[v-qiuyu-msft]

Hi @Anonymous ,

Please share your database backup files and pbix with us. You can send them to my work email, I will send the address to you via private message. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Anonymous ,

I have consulted this issue internally, will update here once I get information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Anonymous,

I have reported this issue internally: CRI 107891519. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Qiuyun,

Thanks for following up. Hope the issue is resolved soon so we can move forward again from the December 2017 version!

William Schmidt