February 3, 2022 at 1:18 pm
I would like to link a relationship with my Fact table:
USE [PPECB]
GO
/****** Object: Table [dbo].[PI_ServiceRequest] Script Date: 2022/02/03 15:15:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PI_ServiceRequest](
[id] [int] NOT NULL,
[activitypoint] [int] NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[fbocode] [nvarchar](255) NOT NULL,
[ordertype] [int] NULL,
[ordercategory] [int] NULL,
[requestorid] [nvarchar](255) NULL,
[quotation] [int] NULL,
[address] [nvarchar](255) NULL,
[inspectiontype] [int] NULL,
[analysistype] [int] NULL,
[certificationtype] [int] NULL,
[volume] [int] NULL,
[targetregion] [int] NULL,
[targetcountry] [int] NULL,
[servicerequeststate_id] [int] NULL,
[submittedforapproval] [int] NULL,
[IsApproved] [int] NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[dateapproved] [datetime] NULL,
[modifiedby] [int] NULL,
[approvedby] [int] NULL,
[acceptedterms] [int] NULL,
[structurecode] [int] NULL,
[rejectionreason] [nvarchar](255) NULL,
[iscancelled] [int] NULL,
CONSTRAINT [PK_PI_ServiceRequest] PRIMARY KEY CLUSTERED
(
[id] 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
And Dimension Table:
USE [PPECB]
GO
/****** Object: Table [dbo].[PI_ServiceRequestAssociatedFBO] Script Date: 2022/02/03 15:15:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PI_ServiceRequestAssociatedFBO](
[id] [int] NOT NULL,
[fbocode] [nvarchar](255) NOT NULL,
[servicerequestid] [float] NULL,
[status] [int] NULL,
[nvarchar](255) NULL,
[DocumentId] [int] NULL,
[IsApproved] [int] NULL,
CONSTRAINT [PK_PI_ServiceRequestAssociatedFBO] PRIMARY KEY CLUSTERED
(
[id] ASC,
[fbocode] 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
Please assist. I linked these two Dimension Tables with my Fact table, it is just the above Dimension table that seems not to fit in anywhere?
USE [PPECB]
GO
/****** Object: Table [dbo].[PI_ServiceRequestState] Script Date: 2022/02/03 15:17:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PI_ServiceRequestState](
[servicerequeststate_id] [int] NOT NULL,
[State] [nvarchar](255) NULL,
CONSTRAINT [PK_PI_ServiceRequestState] PRIMARY KEY CLUSTERED
(
[servicerequeststate_id] 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
AND
USE [PPECB]
GO
/****** Object: Table [dbo].[Structure] Script Date: 2022/02/03 15:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Structure](
[ID] [int] NOT NULL,
[structurecode] [int] NOT NULL,
[Name] [nvarchar](255) NULL,
[Create_Date] [datetime] NULL,
[IsActive] [float] NULL,
[VersionStamp] [nvarchar](255) NULL,
CONSTRAINT [PK_Structure] PRIMARY KEY CLUSTERED
(
[structurecode] 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
February 3, 2022 at 2:00 pm
I see Id & fbocode, the PK from the table, in the other table. Aren't those the correct columns?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2022 at 2:03 pm
fbocode contains duplicates and the id column doesn't match to build a relationship. Im starting to think that this particular table is not suppose to be in the model. It does not contain useful data anyway.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply