Dynamic/Calculated Dimension attribute

Hi,

We have requirement from client to calculate age group on fly.  Is not like calculated member from Data source view.

i.e. User drag measures and dimension agegroup (in this case calculate agegroup based on current date).
if he/she is viewing data for year 2003 then age should be based on year 2003 not she/he has today.

Early to see more solutions.

Thanks
LVL 21
Alpesh PatelAssistant ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Eager to see more information!
How about some sample data and some expected results?

& what version of sql server please
0
Alpesh PatelAssistant ConsultantAuthor Commented:
Hi Paul,

We have SQL Server 2012, SSAS 2012.

We have below Dimensions

Calendar (DateKey, Year, Month ....)
Person (DOB, .....)
Some measures

Result Expected.

Age should be based on the year for data is viewing. If shows data for 2003 then Age of person in year 2003.
i.e DOB of XX person is 1-1-1990. and user see data for person XX on Date 2003 then Age should be 13 year.
0
PortletPaulfreelancerCommented:
table:
Person
Name DOB
Fred 1970-01-01
Barney 1970-07-01
Wilma 1970-12-31

With these people what are you expecting?
>>"Age should be based on the year for data is viewing. "
How do I know when something was viewed? (where is that in the data?)
and, please consider those 3 people, if we only know the year, how do we deal with mid-year birthdays?


Some data (not words) for the tables involved is needed
(Real table names & real fields names also helps)


Short, Self Contained, Correct (Compilable), Example
... not suggesting an example is, or should be, compulsory.

It will, however, make people much more likely to help, and will therefore increase the chance of finding a solution.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Alpesh PatelAssistant ConsultantAuthor Commented:
CREATE TABLE [dbo].[DimPerson](
	[PersonKey] [int] IDENTITY(1,1) NOT NULL,
	[PERSON_ID] [varchar](12) NOT NULL,
	[FirstName] [varchar](20) NOT NULL,
	[LastName] [varchar](40) NOT NULL,
	[Country] [varchar](25) NOT NULL,
	[Gender] [char](1) NULL,
	[DOB] [date] NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DimEmployment](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[PERSON_ID] [varchar](10) NOT NULL,
	[External_Employment_Number] [varchar](20) NOT NULL,
	[Valid_From] [date] NOT NULL,
	[Valid_Through] [date] NOT NULL,
	[Departure_Date] [date] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[DimCalendar](
	[DateKey] [varchar](10) NOT NULL,
	[Date] [date] NULL,
	[Year] [smallint] NULL,
	[Month] [smallint] NULL,
	[Day] [tinyint] NULL,
	[Quarter] [tinyint] NULL,
	[Week] [tinyint] NULL,
	[DayOfYear] [smallint] NULL,
	[DayOfWeek] [tinyint] NULL,
	[DayofWeekName] [varchar](20) NULL,
	[MonthName] [varchar](20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FactTurnOverRatio](
	[PERSON_ID] [varchar](10) NULL,
	[External_Employment_Number] [varchar](20) NULL,
	[Tran_Date] [date] NULL,
	[DateKey] [varchar](10) NULL
) ON [PRIMARY]

Open in new window



Above is my table structure.

When person view the data with Reference of TOR for month of May 2013 for Person_ID XXXXX and Date of Birth for person is 01-01-1990 then Age should be calculated and it should display 23 years. If user check data for year 2010 then Age for person should be calculated as 20 years.

Hope you understand.
0
PortletPaulfreelancerCommented:
do you want the age relative to [Tran_Date] ? either of these datediff() calculations should suit for that
select
        p.FirstName
      , p.dob
      , f.tran_date
      , datediff(month,p.dob,f.tran_date) / 12
      , datediff(day,p.dob,f.tran_date) / 365.25
from dimPerson as p
inner join FactTurnOverRatio as f on p.person_id = f.person_id

Open in new window

Not sure why I have to generate test data, it's better if you do that so we don't have to guess.
    	[PersonKey] [int] IDENTITY(1,1) NOT NULL,
    	[PERSON_ID] [varchar](12) NOT NULL,
    	[FirstName] [varchar](20) NOT NULL,
    	[LastName] [varchar](40) NOT NULL,
    	[Country] [varchar](25) NOT NULL,
    	[Gender] [char](1) NULL,
    	[DOB] [date] NOT NULL,
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[DimEmployment](
    	[ID] [bigint] IDENTITY(1,1) NOT NULL,
    	[PERSON_ID] [varchar](10) NOT NULL,
    	[External_Employment_Number] [varchar](20) NOT NULL,
    	[Valid_From] [date] NOT NULL,
    	[Valid_Through] [date] NOT NULL,
    	[Departure_Date] [date] NULL
    ) ON [PRIMARY]
    
    GO
    CREATE TABLE [dbo].[DimCalendar](
    	[DateKey] [varchar](10) NOT NULL,
    	[Date] [date] NULL,
    	[Year] [smallint] NULL,
    	[Month] [smallint] NULL,
    	[Day] [tinyint] NULL,
    	[Quarter] [tinyint] NULL,
    	[Week] [tinyint] NULL,
    	[DayOfYear] [smallint] NULL,
    	[DayOfWeek] [tinyint] NULL,
    	[DayofWeekName] [varchar](20) NULL,
    	[MonthName] [varchar](20) NULL
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[FactTurnOverRatio](
    	[PERSON_ID] [varchar](10) NULL,
    	[External_Employment_Number] [varchar](20) NULL,
    	[Tran_Date] [date] NULL,
    	[DateKey] [varchar](10) NULL
    ) ON [PRIMARY]
    
    
    INSERT INTO DimPerson
    	([PERSON_ID], [FirstName], [LastName], [Country], [Gender], [DOB])
    VALUES
    	(1, 'fred', 'flintstone', 'bedrock', 'M', '1970-01-01 00:00:00'),
    	(2, 'barney', 'rubble', 'bedrock', 'M', '1970-07-01 00:00:00'),
    	(3, 'wilma', 'flintstone', 'bedrock', 'F', '1970-12-31 00:00:00')
    ;
    
    	
    INSERT INTO FactTurnOverRatio	
    	([PERSON_ID], [Tran_Date])
    VALUES
    	(1, '2009-01-01 00:00:00'),
    	(1, '2009-07-01 00:00:00'),
    	(1, '2010-01-01 00:00:00'),
    	(1, '2010-07-01 00:00:00'),
    	(1, '2011-01-01 00:00:00'),
    	(1, '2011-07-01 00:00:00'),
    	(1, '2012-01-01 00:00:00'),
    	(1, '2012-07-01 00:00:00'),
    	(1, '2013-01-01 00:00:00'),
    	(1, '2013-07-01 00:00:00'),
    	(1, '2014-01-01 00:00:00'),
    	(1, '2014-07-01 00:00:00'),
    	(2, '2009-01-01 00:00:00'),
    	(2, '2009-07-01 00:00:00'),
    	(2, '2010-01-01 00:00:00'),
    	(2, '2010-07-01 00:00:00'),
    	(2, '2011-01-01 00:00:00'),
    	(2, '2011-07-01 00:00:00'),
    	(2, '2012-01-01 00:00:00'),
    	(2, '2012-07-01 00:00:00'),
    	(2, '2013-01-01 00:00:00'),
    	(2, '2013-07-01 00:00:00'),
    	(2, '2014-01-01 00:00:00'),
    	(2, '2014-07-01 00:00:00'),
    	(3, '2009-01-01 00:00:00'),
    	(3, '2009-07-01 00:00:00'),
    	(3, '2010-01-01 00:00:00'),
    	(3, '2010-07-01 00:00:00'),
    	(3, '2011-01-01 00:00:00'),
    	(3, '2011-07-01 00:00:00'),
    	(3, '2012-01-01 00:00:00'),
    	(3, '2012-07-01 00:00:00'),
    	(3, '2013-01-01 00:00:00'),
    	(3, '2013-07-01 00:00:00'),
    	(3, '2014-01-01 00:00:00'),
    	(3, '2014-07-01 00:00:00')
    ;

**Query 1**:

    select
            p.FirstName
          , p.dob
          , f.tran_date
          , datediff(month,p.dob,f.tran_date) 
          , datediff(month,p.dob,f.tran_date) / 12
          , datediff(day,p.dob,f.tran_date) / 365.25
    from dimPerson as p
    inner join FactTurnOverRatio as f on p.person_id = f.person_id
    
    

**[Results][2]**:
    
    | FIRSTNAME |        DOB |  TRAN_DATE | COLUMN_3 | COLUMN_4 |  COLUMN_5 |
    |-----------|------------|------------|----------|----------|-----------|
    |      fred | 1970-01-01 | 2009-01-01 |      468 |       39 | 39.000684 |
    |      fred | 1970-01-01 | 2009-07-01 |      474 |       39 | 39.496235 |
    |      fred | 1970-01-01 | 2010-01-01 |      480 |       40 |        40 |
    |      fred | 1970-01-01 | 2010-07-01 |      486 |       40 |  40.49555 |
    |      fred | 1970-01-01 | 2011-01-01 |      492 |       41 | 40.999315 |
    |      fred | 1970-01-01 | 2011-07-01 |      498 |       41 | 41.494866 |
    |      fred | 1970-01-01 | 2012-01-01 |      504 |       42 | 41.998631 |
    |      fred | 1970-01-01 | 2012-07-01 |      510 |       42 | 42.496919 |
    |      fred | 1970-01-01 | 2013-01-01 |      516 |       43 | 43.000684 |
    |      fred | 1970-01-01 | 2013-07-01 |      522 |       43 | 43.496235 |
    |      fred | 1970-01-01 | 2014-01-01 |      528 |       44 |        44 |
    |      fred | 1970-01-01 | 2014-07-01 |      534 |       44 |  44.49555 |
    |    barney | 1970-07-01 | 2009-01-01 |      462 |       38 | 38.505133 |
    |    barney | 1970-07-01 | 2009-07-01 |      468 |       39 | 39.000684 |
    |    barney | 1970-07-01 | 2010-01-01 |      474 |       39 | 39.504449 |
    |    barney | 1970-07-01 | 2010-07-01 |      480 |       40 |        40 |
    |    barney | 1970-07-01 | 2011-01-01 |      486 |       40 | 40.503764 |
    |    barney | 1970-07-01 | 2011-07-01 |      492 |       41 | 40.999315 |
    |    barney | 1970-07-01 | 2012-01-01 |      498 |       41 |  41.50308 |
    |    barney | 1970-07-01 | 2012-07-01 |      504 |       42 | 42.001368 |
    |    barney | 1970-07-01 | 2013-01-01 |      510 |       42 | 42.505133 |
    |    barney | 1970-07-01 | 2013-07-01 |      516 |       43 | 43.000684 |
    |    barney | 1970-07-01 | 2014-01-01 |      522 |       43 | 43.504449 |
    |    barney | 1970-07-01 | 2014-07-01 |      528 |       44 |        44 |
    |     wilma | 1970-12-31 | 2009-01-01 |      457 |       38 | 38.004106 |
    |     wilma | 1970-12-31 | 2009-07-01 |      463 |       38 | 38.499657 |
    |     wilma | 1970-12-31 | 2010-01-01 |      469 |       39 | 39.003422 |
    |     wilma | 1970-12-31 | 2010-07-01 |      475 |       39 | 39.498973 |
    |     wilma | 1970-12-31 | 2011-01-01 |      481 |       40 | 40.002737 |
    |     wilma | 1970-12-31 | 2011-07-01 |      487 |       40 | 40.498288 |
    |     wilma | 1970-12-31 | 2012-01-01 |      493 |       41 | 41.002053 |
    |     wilma | 1970-12-31 | 2012-07-01 |      499 |       41 | 41.500342 |
    |     wilma | 1970-12-31 | 2013-01-01 |      505 |       42 | 42.004106 |
    |     wilma | 1970-12-31 | 2013-07-01 |      511 |       42 | 42.499657 |
    |     wilma | 1970-12-31 | 2014-01-01 |      517 |       43 | 43.003422 |
    |     wilma | 1970-12-31 | 2014-07-01 |      523 |       43 | 43.498973 |



  [1]: http://sqlfiddle.com/#!3/0a3bf/3

Open in new window

0
Alpesh PatelAssistant ConsultantAuthor Commented:
We need it in Cube as a calculated dimension attribute.
0
PortletPaulfreelancerCommented:
datediff(month, [dob] , [some-other-date-here] ) / 12

should be sufficiently accurate if you want an integer result
or you can use

datediff(day, [dob] , [some-other-date-here] ) / 365.25

as I'm not writing your cube for you, I will presume you would place the wanted calculation into the wanted code and the appropriate place.

not sure what else you are expecting

+ edit
btw: if I was writing the cube for you I would not use a varchar as the date_key
0
PortletPaulfreelancerCommented:
OK, looking back over the question, it seems you may not want SQL, If that's correct then perhaps this article will assist.

which provides this method that can be translated to you table structure:
CustomerAge =
YEAR ( Sales[OrderDate] )
- YEAR ( RELATED ( Customers[Birthdate] ) )
+ IF (
    DATE (
        1900,
        MONTH ( Sales[OrderDate] ),
        DAY ( Sales[OrderDate] )
    ) < DATE (
        1900,
        MONTH ( RELATED ( Customers[Birthdate] ) ),
        DAY ( RELATED ( Customers[Birthdate] ) )
    ),
    - 1,
    0
)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alpesh PatelAssistant ConsultantAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.