?
Solved

Dynamic/Calculated Dimension attribute

Posted on 2014-07-11
9
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
Comment
Question by:Alpesh Patel
  • 5
  • 4
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
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

by:Alpesh Patel
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

by:PortletPaul
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 21

Author Comment

by:Alpesh Patel
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]

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
 
LVL 49

Expert Comment

by:PortletPaul
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

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
 
LVL 21

Author Comment

by:Alpesh Patel
ID: 40193823
We need it in Cube as a calculated dimension attribute.
0
 
LVL 49

Expert Comment

by:PortletPaul
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

by:
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
)

Open in new window

0
 
LVL 21

Author Closing Comment

by:Alpesh Patel
ID: 40194392
Thanks a lot
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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.

Join & Ask a Question