Creating a Dimension with no Primary Key

  • daithiboy

    Valued Member

    Points: 65

    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:

    1

    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

  • Mike01

    SSChampion

    Points: 11282

    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/

  • Phil Parkin

    SSC Guru

    Points: 244662

    Mike01 wrote:

    It's hard to see from the data, but why wouldn't 'theDate' be the PK?

    My thoughts exactly!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • daithiboy

    Valued Member

    Points: 65

    "theDate" field has duplicates in it.  The "YTD TY" measure has some of the sames dates in it as "MAT TY".

  • Phil Parkin

    SSC Guru

    Points: 244662

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • daithiboy

    Valued Member

    Points: 65

    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.

    23

  • Phil Parkin

    SSC Guru

    Points: 244662

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • daithiboy

    Valued Member

    Points: 65

    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?

  • Phil Parkin

    SSC Guru

    Points: 244662

    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.

    • This reply was modified 4 weeks ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply