Warm tip: This article is reproduced from stackoverflow.com, please click
sql-server ssas visual-studio-2013 cube bids

Primary key not a Measure in cube?

发布于 2020-05-03 08:38:12

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. enter image description here

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?

Questioner
fdkgfosfskjdlsjdlkfsf
Viewed
21
Piotr 2020-02-14 23:52

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.