Create dimension using arithmetics operations

[Former Member]

Hi Experts

Im using BO Information design tool, then I need to create a dimension of age based on the employee's date of birth. Any idea how?

thanks

[Former Member]

For create universe somebody knows if is it  importan type database?, conection is ECC, its not other source like mysql, access, etc.

[michael_melters]

Yes, it depends on the type of database you use. Normally if you design against a relational database like oracle or MSSQL server you can do what Amit already suggested. I think there is a limitation if you work with SAP ECC, even if its underlying database is for example Oracle you might not have all the SQL functions which are provided by Oracle. If your Data Foundation is multisource enabled you should be able to use much more functions in order to create the objects in your business layer. Because then the functions are provided by the data federation layer. In this case you would use timestampDiff function. The first parameter is 4, which means that the difference is given in days and that is divided by 365. I don't have a list for all the units but of course there is one for years, which would be more precise because of leap years.

[Former Member]

I'm beginning to understand that the problem is the type of database that I select. The reason is that when SAP R / 3 realease 4 The SQL editor displays only me I have that database. It is correct?

[amitrathi239]

Hi,

You can do in multiple ways.

subtract the date of birth from current date based on the years.You will get the age in years.

year(curDate())-year(@catalog('Dummy')."PUBLIC"."Table"."DOB")

or find  out the number of days difference between two dates and divide by 365.

Amit

[Former Member]

Hi, thanks

   the problem it's when im using some commands as "year" I  recive this mensaje "Error on query validation : Invalid function: year".

[amitrathi239]

might be your date datatype is not date.Year function will work with date data type.

[Former Member]

Thanks  !

At some point I thought the same thing, that was different, however, I validate it in the business layer but no, add an image data.

[amitrathi239]

What is the datatype in the business layer.i can see something starting with Fech.what is that?

[Former Member]

Thank

"Fecha" is the translation of date in spanish.

[amitrathi239]

check if Year function is available or not in the functions section.

[Former Member]

It is not there,. What can I do ?

[amitrathi239]

Check if some other functions are available like daysdiff (days difference function)

[Former Member]

Something its wrong, but I don know what.

I dont have function for date. Just I have this functions

[amitrathi239]

Check in the All.

[Former Member]

Not.

[amitrathi239]

other option is calculate at webi report level.