|
influent
Constraint Violating Yak Guru
USA
361 Posts |
 Posted - 05/18/2012 : 15:51:31
|
I'm trying to get my cube working without getting "attribute key not found" errors but the only way I've found is to set up a referenced dimension from Dim_PropertyInfo to Dim_SalesInvoiceHeader (see scripts below) and leave the Materialize checkbox unchecked, but then when I browse the cube and filter on INV001 and display the PropertyInfo attribute it still returns all rows from Dim_PropertyInfo. Can somebody tell me what I should be doing?
CREATE TABLE [dbo].[Dim_SalesInvoiceHeader](
[SalesInvHeaderID] [int] IDENTITY(1,1) NOT NULL,
[SalesInvoiceNumber] [nvarchar](50) NOT NULL,
[OtherStuff] [nvarchar](max) NULL,
CONSTRAINT [PK_Dim_SalesInvoiceDetails] PRIMARY KEY CLUSTERED
(
[SalesInvHeaderID] 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
CREATE TABLE [dbo].[Fact_SalesInvoices](
[FactID] [int] IDENTITY(1,1) NOT NULL,
[SalesInvHeaderID] [int] NOT NULL,
[LineItem] [int] NOT NULL,
CONSTRAINT [PK_Fact_SalesInvoices] PRIMARY KEY CLUSTERED
(
[FactID] 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 [dbo].[Fact_SalesInvoices] WITH CHECK ADD CONSTRAINT [FK_Fact_SalesInvoices_Dim_SalesInvoiceHeader] FOREIGN KEY([SalesInvHeaderID])
REFERENCES [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID])
GO
ALTER TABLE [dbo].[Fact_SalesInvoices] CHECK CONSTRAINT [FK_Fact_SalesInvoices_Dim_SalesInvoiceHeader]
GO
CREATE TABLE [dbo].[Dim_PropertyInfo](
[PropertyInfoID] [int] IDENTITY(1,1) NOT NULL,
[SalesInvHeaderID] [int] NOT NULL,
[SalesInvoiceNumber] [nvarchar](50) NOT NULL,
[PropertyInfo] [nvarchar](max) NULL,
CONSTRAINT [PK_Dim_PropertyInfo] PRIMARY KEY CLUSTERED
(
[PropertyInfoID] 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 [dbo].[Dim_PropertyInfo] WITH CHECK ADD CONSTRAINT [FK_Dim_PropertyInfo_Dim_SalesInvoiceHeader] FOREIGN KEY([SalesInvHeaderID])
REFERENCES [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID])
GO
ALTER TABLE [dbo].[Dim_PropertyInfo] CHECK CONSTRAINT [FK_Dim_PropertyInfo_Dim_SalesInvoiceHeader]
GO
SET IDENTITY_INSERT [dbo].[Dim_SalesInvoiceHeader] ON
INSERT [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID], [SalesInvoiceNumber], [OtherStuff]) VALUES (1, N'INV001', N'blah')
INSERT [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID], [SalesInvoiceNumber], [OtherStuff]) VALUES (2, N'INV002', N'blahblah')
INSERT [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID], [SalesInvoiceNumber], [OtherStuff]) VALUES (3, N'INV003', N'blahblahblah')
INSERT [dbo].[Dim_SalesInvoiceHeader] ([SalesInvHeaderID], [SalesInvoiceNumber], [OtherStuff]) VALUES (4, N'INV004', N'stuff')
SET IDENTITY_INSERT [dbo].[Dim_SalesInvoiceHeader] OFF
SET IDENTITY_INSERT [dbo].[Fact_SalesInvoices] ON
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (1, 1, 1)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (2, 1, 2)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (3, 1, 3)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (4, 2, 1)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (5, 2, 2)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (6, 3, 1)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (7, 3, 2)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (8, 4, 1)
INSERT [dbo].[Fact_SalesInvoices] ([FactID], [SalesInvHeaderID], [LineItem]) VALUES (9, 4, 2)
SET IDENTITY_INSERT [dbo].[Fact_SalesInvoices] OFF
SET IDENTITY_INSERT [dbo].[Dim_PropertyInfo] ON
INSERT [dbo].[Dim_PropertyInfo] ([PropertyInfoID], [SalesInvHeaderID], [SalesInvoiceNumber], [PropertyInfo]) VALUES (1, 1, N'INV001', N'stuff')
INSERT [dbo].[Dim_PropertyInfo] ([PropertyInfoID], [SalesInvHeaderID], [SalesInvoiceNumber], [PropertyInfo]) VALUES (2, 2, N'INV002', N'pool')
SET IDENTITY_INSERT [dbo].[Dim_PropertyInfo] OFF
|
|
dimension confusion
