Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Calculating days since the first record in fact table

Received Response
31
Views
2
Comments
Jean-Pierre Hoedenaeken
Jean-Pierre Hoedenaeken Rank 6 - Analytics & AI Lead
in Oracle Analytics Forum

Hello,

We have a fact table with the following records :

image.png

We need to calculate the number of days since the first views (# Vues) of an content (Id Contenu) for each record.

So the result will be :

image.png

And finally, we can create the following dataviz that shows the consumption evolution :

image.png

Is there some functions to achieve that through a "Calculation" ?

Or, due to the large volume of the fact table, is it better to create a materialized view in our datawarehouse ?

Best regards.

Jean-Pierre

Tagged:

Answers

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod

    Hi @Jean-Pierre Hoedenaeken ,

    I would create a materialized view in the datawarehouse.

    You could calculate it with something like TIMESTAMPDIFF(SQL_TSI_DAY, MIN(Date by Id Contenu), Date), but it requires to have both Date and Id Contenu columns in your analysis/visualization/query to preserve the correct granularity. In Analytics Classic you should be able to display the chart properly (by excluding not required columns), but in DV I believe it won't work..

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi Jean-Pierre,

    Is that simple datasets in DV or you are doing the work via the RPD?

    As you talk about "fact table", I assume there is the RPD (Semantic Model) somewhere. If you add a measure being a MIN() and another being a MAX() on your date, you can then have a derived column being the difference between the 2.

    If you will need to create a materialized view depends purely on the performance you get for your analysis and if that's enough for you (consider how often you open that visualization, if caching can be used and how that compare to having a MV that you have to maintain and refresh as part of your data loading process etc.).