none
Needh Help with Dimension

    Question

  • Good Morning,

    I'm trying to create a dimension and am facing the problem. I'm getting an error. "Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_STZ_ZONE_DIM', Column: 'Zone', Value: ''. The attribute is 'Zone'.". I have a table from which i'm creating this DIMENSION.

    Zone	SpaceNumber
    1	100
    1	200
    1	300
    1	400
    2	500
    2	600
    2	700
    2	800
    2	900
    3	1000
    3	1100
    3	1200
    3	1300
    3	1400
    4	1500
    4	1600
    4	1700
    4	1800
    4	1900

    So i used the following SQL to get the table to look like one Zone as parent and multiple space numbers as child for each zone

    select CASE when l.rn=1 then l.Zone else ' ' end as Zone,l.[Space Number]
    from
    (select k.*,ROW_NUMBER() over(partition by k.zone order by k.zone) as rn
    from
    (SELECT distinct [Zone]
          ,[Space Number]
          
      FROM TABLE A  where Zone is not null)k)l

    I want my DMENSION such that when reporting if the user cghooses ZONE1 he needs to see only the SPACE NUMBERS associated with that zone.Please need help.

    Thanks

                                                                                                                                                                                            

    Friday, September 27, 2013 1:58 PM

Answers

  • You need a unique key for the Zone in order to avoid the duplicate attribute error.  I would normalize the data, and have a table in the cube data source view where Zone is the unique key:

    SELECT DISTINCT [Zone] FROM Table A where Zone is not null

    Then join that table in the data source view to your 'dbo_STZ_ZONE_DIM' joined on the Zone field.

    Use the Zone field in the new Zone table in the dimension as the Zone attribute key.  It will not find any duplicates in that table since you have selected distinct.


    Martina White

    • Marked as answer by thinkingeye Monday, September 30, 2013 12:50 PM
    Monday, September 30, 2013 1:55 AM

All replies

  • You need a unique key for the Zone in order to avoid the duplicate attribute error.  I would normalize the data, and have a table in the cube data source view where Zone is the unique key:

    SELECT DISTINCT [Zone] FROM Table A where Zone is not null

    Then join that table in the data source view to your 'dbo_STZ_ZONE_DIM' joined on the Zone field.

    Use the Zone field in the new Zone table in the dimension as the Zone attribute key.  It will not find any duplicates in that table since you have selected distinct.


    Martina White

    • Marked as answer by thinkingeye Monday, September 30, 2013 12:50 PM
    Monday, September 30, 2013 1:55 AM
  • Finally i had to go that route. Thanks Martina White
    Monday, September 30, 2013 12:50 PM