OAC - Datetime attribute in workbook (Dataset vs Semantic Model behavior)

Daniel Vornicu

Hello,

When I working with Datetime field in a workbook (source Dataset) OAC also show me the derived subfields (Year, Quarter, Month, Day..) and I have the possibility to make a bar graph like bellow very easily (in this case ordered by Year Month or 'Mois de l'année' in french) .

If a want to display the quarters I can choose 'Show by' select the appropiate value.

When I have a Semantic Model behind (and we work mainly with semantic models) I don't have this possibility to drill and chose the Year Month. I had to add 2 calculated fields to extract the desired fields but with a little drawback:

The calculated fields are:

1. The Year Month ( 'Mois (libelle) creation document' in french) = MONTHNAME("Date creation document")
2. The Month Index ('Mois creation document' in french ) = MONTH("Date creation document")

Then I added the both columns in Category (X axis) and I filtered on Month Index accending to obtain this graphic below.

The drawback is: I want to display only 'Janv', 'Fevr'.. on X axis (without month index) add sort by month index without displaying '1, Janv', '2, Fevr' etc .

If I don't add the second column (Month Index) I obtain a bar chart like this (order alphabetically by month). I think it's not very funny.

Sometimes we need to to sort by a column but not displaing it (not possible for moment or I don't know how )

Did you intent to add the same behavior for the workbooks builded on semantic models (like the Dataset version) ?

Thanks,

Daniel

Gianni Ceresa

Hi,

The hierarchy and the keys aren't involved in what you wanted (sorting month by number and not by name A-Z). Those were things I listed to justify why you didn't see all the "shortcuts" on your date field when it comes from a semantic model vs a dataset.

To do what you look for, you should just set a "sort by" column on your "Mois année (libelle) creation document".

Open the column's details in the logical layer and set the sort column to use for it (one with just 1-12 as number, or 01-12 as text or anything else that can be sorted correctly).

Gianni Ceresa

Hi,

I did hear a PM (or a dev, can't remember) say that "hidden" columns will come to DV at some point. No date was provided. There is maybe an idea to upvote about it, didn't search.

My guess on why the 2 behave differently is because of all the extra metadata coming with the semantic model vs just a flat dataset.

In your semantic model you generally have proper time dimensions already having all the required levels, you handle the sorting of months names by assigning a sorting column directly in the semantic model. You model hierarchies to handle to drill down and chronological keys etc. You can also have based level measure, or multiple fact table at different aggregation levels, and therefore the system can't just let you "generate" a quarter on the fly from a date. If that was your requirement, then you should have a time dimension and a proper model in the semantic model for it.

Michal Zima

@Daniel Vornicu Look at my Idea, I have recently created:

https://community.oracle.com/products/oracleanalytics/discussion/20687/ability-to-set-sorting-on-alternative-column-in-dv-data-sets-based-on-attribute-column

I think, this could be solution for you as well. So pls, vote for this idea - thanks.

Michal

Daniel Vornicu

Thanks @Gianni Ceresa for the explanation.

Effectivelly I will try in this case to join that flat Datetime column (DOCUMENT_DATE_CREATION) to a proper Time dimension (and create a hierarchy like Year, Quarter, Month, Day etc on that dimension)

Daniel

Daniel Vornicu

Hello,

So I made a Time dimension with a Time hierarchy (not a Level-based hierarchy) only for having 'Cronological Key' field there and try to make a default filter on month index (1-12) and not to the month name. I don't use time series on things like that, so for me a level-based hierarchy would have been more then enough.

Nevermind, even with the time hierarchy added like below doesn't seems to work. The cronological key is set to month name like I saw in this documentation:

https://docs.oracle.com/en/cloud/paas/analytics-cloud/tutorial-create-dims-sm/index.html#step_three

Despite my expectations when I pick the atribute for the month name and the mesure I have the not sorted version (I meen sorted in alphabetical order not by month index). So the same result like without time dimension at all.

What mistake I made ?

Thanks for help,

Daniel

Daniel Vornicu

Hi,

Thanks @Gianni Ceresa for you help. I didn't saw that 'Sort by' field in columns details :)

It's working now, so I used this sorting principle for 'month name' and 'day of the week name'.

I also switch back the time hierarchy type (Time ⇒ Level based) to keep it simple (cause no time series)

Gianni Ceresa

Glad it is working.

The sorting column is a very useful thing, mostly for date columns as you just did.

It is also has been requested as a feature in DV datasets, hopefully it will come at some point…