Creating a Dimension with no Primary Key
-
Hi all,
I'm rebuilding some cubes in SQL Server 2017 using Visual Studio and SSAS. The cubes were originally constructed in SQL 2000. It's been going well so far until I try to recreate a "Calculated Time" dimension that worked so well in the old build. The dimension table was linked to the fact table on three fields - Year, Month and Day. Within the table there were multiple measures, such as YTD last year, YTD this year, etc. Snapshot view below:
As you can see there's no unique key in the table, which was never a problem when building the cube in Analysis Services 2000. When I attempt to create the same dimension in SQL 2017 it insists that I need a unique key in the relationship. I'm sure I'm missing something obvious.
I would appreciate it if someone can point me in the right direction and show me how I can build the dimension successfully.
Many thanks,
Dave
-
It's hard to see from the data, but why wouldn't 'theDate' be the PK?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ - Mike01 wrote:
It's hard to see from the data, but why wouldn't 'theDate' be the PK?
My thoughts exactly!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
"theDate" field has duplicates in it. The "YTD TY" measure has some of the sames dates in it as "MAT TY".
- daithiboy wrote:
"theDate" field has duplicates in it. The "YTD TY" measure has some of the sames dates in it as "MAT TY".
I don't see 'YTD TY' or 'MAT TY' anywhere in your post, making a useful response very difficult.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
My apologies, I was abbreviating out of habit and got a few steps ahead of myself.
The table snapshot below shows dates for MAT Jun-20 and YTD Jun-20. The "MAT Jun-20" measure will incorporate all dates from the 1st of July 2019 to the 30th of June 2020 while the YTD Jun-20 will incorporate all dates from the 1st of January to the 30th of June 2020.
I hope this is more use.
-
OK, understood (though I would probably keep the date dimension table clean and have another table containing measure and measureno).
It therefore appears that your PK should be composite, comprising (theDate, measure).
The order in which you define the PK columns is important and should depend on the nature of the queries running against the table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks for the advice Phil, it's greatly appreciated.
Sorry I'm being such a newbie but this is my first foray into the world of SQL 2017 since SQL 2000 so it's a bit of a sea-change.
Would you be able to walk me through how you would do it?
-
Here's an example of how you might structure the underlying tables and then SELECT from them to return your dimension:
DROP TABLE IF EXISTS dbo.Date;
CREATE TABLE dbo.Date
(
theDate DATE NOT NULL PRIMARY KEY CLUSTERED
,Day TINYINT NOT NULL
,Month TINYINT NOT NULL
,YEAR SMALLINT NOT NULL
);
DROP TABLE IF EXISTS dbo.Measure;
CREATE TABLE dbo.Measure
(
MeasureId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
,MeasureStartDate DATE NOT NULL
,MeasureEndDate DATE NOT NULL
,MeasureNo INT NOT NULL
,Measure CHAR(10) NOT NULL
);
CREATE UNIQUE NONCLUSTERED INDEX UX_MeasureNo_StartDate
ON dbo.Measure (
MeasureNo
,MeasureStartDate
);
--Populate the dates (thanks to https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/)
DECLARE @StartDate DATE = '20180101';
DECLARE @CutoffDate DATE = DATEADD(DAY, -1, DATEADD(YEAR, 5, @StartDate));
WITH seq (n)
AS (SELECT 0
UNION ALL
SELECT seq.n + 1
FROM seq
WHERE seq.n < DATEDIFF(DAY, @StartDate, @CutoffDate))
,d (d)
AS (SELECT DATEADD(DAY, seq.n, @StartDate)
FROM seq)
,src
AS (SELECT TheDate = CONVERT(DATE, d.d)
,TheDay = DATEPART(DAY, d.d)
,TheMonth = DATEPART(MONTH, d.d)
,TheYear = DATEPART(YEAR, d.d)
FROM d)
INSERT dbo.Date
(
theDate
,Day
,Month
,YEAR
)
SELECT src.TheDate
,src.TheDay
,src.TheMonth
,src.TheYear
FROM src
OPTION (MAXRECURSION 0);
--Create a single measure row
INSERT dbo.Measure
(
MeasureStartDate
,MeasureEndDate
,MeasureNo
,Measure
)
VALUES
('20200101', '20200201', 12, 'YTD Jun-20');
--Return the results
SELECT m.MeasureNo
,m.Measure
,d.theDate
,d.Day
,d.Month
,d.YEAR
FROM dbo.Measure m
JOIN dbo.Date d
ON d.theDate >= m.MeasureStartDate
AND d.theDate <= m.MeasureEndDate;It's been a while since I built a cube from scratch, but I believe you can 'tell' SSAS what the PK of a table (or dataset) is. In this case, (MeasureNo, theDate) should work fine.
Whether this approach will work well in your environment depends on the structure of your fact table. If your fact table is keyed on (theDate, MeasureNo), for example, the MeasureId column is pointless.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply