dimension confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

dimension confusion

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-18 : 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

igorkruk.pl
Starting Member

4 Posts

Posted - 2012-05-22 : 07:01:35

so you are sure your data is correct and still getting "attribute key not found" error.

Maybe processing one dimension at a time before processing the cube will work it out?

---------------------
my website: www.igorkruk.pl

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-22 : 13:34:07

Yes, you see the data, it's in the scripts I posted. I always try one dimension at a time first, that's not helping in this situation. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources