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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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