Solved

Dynamic/Calculated Dimension attribute

Posted on 2014-07-11
Medium Priority
275 Views
Last Modified: 2016-02-18
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
0
Question by:Alpesh Patel
• 5
• 4
9 Comments

LVL 49

Expert Comment

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

& what version of sql server please
0

LVL 21

Author Comment

ID: 40191857
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

LVL 49

Expert Comment

ID: 40191901
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

LVL 21

Author Comment

ID: 40193791
``````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]
``````

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

LVL 49

Expert Comment

ID: 40193815
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
``````
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
``````
0

LVL 21

Author Comment

ID: 40193823
We need it in Cube as a calculated dimension attribute.
0

LVL 49

Expert Comment

ID: 40193845
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

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 40193863
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
)
``````
0

LVL 21

Author Closing Comment

ID: 40194392
Thanks a lot
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error āConnecting to the Integration Services service on the computer failed with the following error: 'The specified service ā¦
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.