I'm new to SSAS
, so please bear with me.
I have two tables: FactTest
and DimLocation
. I'm including the table schema, but in essence, and FactTest.LocationName
is foreign key to DimLocation.LocationName
and DimLocation.LocationName
is primary key
:
CREATE TABLE [dbo].[FactTest](
[test_date] [date] NULL,
[DOWNLOAD] [float] NULL,
[LocationName] [varchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactTest] WITH CHECK ADD CONSTRAINT [FK_FactTest_DimLocation] FOREIGN KEY([LocationName])
REFERENCES [dbo].[DimLocation] ([LocationName])
GO
ALTER TABLE [dbo].[FactTest] CHECK CONSTRAINT [FK_FactTest_DimLocation]
GO
CREATE TABLE [dbo].[DimLocation](
[LocationId] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [varchar](50) NOT NULL,
CONSTRAINT [PK_DimLocation] PRIMARY KEY CLUSTERED
(
[LocationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DimLocation
looks like this:
LocationId LocationName
1 Houston
2 San Antonio
3 Dallas
4 Austin
and FactTest
looks like this:
test_date Download LocationName
1/4/2020 8.56 Houston
1/4/2020 9.43 Dallas
1/5/2020 3.20 Houston
When I'm creating the cube
in VS 2013
, I notice that the column LocationName
is not included as one of the Measures
. I also have other Dimension tables, and none of the primary key columns are part of the Measures.
Aren't the primary keys supposed to be used as Measures
when creating a cube
? If not, then how am I supposed to link the fact tables to the dim tables?
It is caused by incorrect PK definition in DimLocation.
All dimensions PK should be integers, so LocationId should be your PK and you should include it in the fact table instead of LocationName. Location Name should be defined as a dimension attribute.
In your cube the only measure is "[DOWNLOAD] [float]".
Definition of a Measure: It is any numeric quantity/value that represents a metric aligned to an organization's goals. Measures are stored in the fact table.