Multi Source UNX - date definitions

[system]

Hi guys… any thoughts on how to do the following using the IDT database functions/operators WITHOUT having to add extra columns/create a view in the underlying Oracle RDBMS?

to_char(DW_OWNER.DW_PERIOD.PER_DATE,'Mon')

i.e. show the Month Name of the date instead of number, e.g. Apr instead of 04

or

to_char(trunc(DW_OWNER.DW_PERIOD.PER_DATE,'IW'),'DD/MM/RR')

i.e. display the day of the start of the week this date falls in

I suspect it’s not possible… but then I can’t seem to find any documentation onwhat is possible
[Moderator Edit: Added code formatting - Andreas]

---

spoons (BOB member since 2012-06-26)

[system]

Just create a Dimension object in IDT using the very same Oracle SQL code you already posted.
But be aware that any db indices from those base columns will not be used (because you wrapped another SQL function around it); look at function based indices in Oracle to support better performance for your queries.

---

Andreas (BOB member since 2002-06-20)

[system]

There a link in this post, that may help you with the Multi-source syntax. Also Debbie who posted on there has done quite a bit of work on it:-

https://bobj-board.org/t/227720

---

Mak 1 (BOB member since 2005-01-06)

[system]

Thanks for the quick response guys, much appreciated

EDIT

as suspected, i’m fairly sure that i will be probably much better off getting the data into the Data Warehouse and properly dimensionally modelled

---

spoons (BOB member since 2012-06-26)