Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Displaying data from Dyamic tables (Monthly and yearly) depending on the selected date from Filter.

Received Response
64
Views
8
Comments
Hasan Sd
Hasan Sd Rank 5 - Community Champion
in Oracle Analytics Cloud and Server

Hii everyone, In our Database , we store Current 3 Days data in the Main Table and the rest of the data in Monthly and yearly Tables. An Example is shown below:

Table Name

Data

Sales

Current 3 Days Data.

Sales _2025_Feb

February 2025 Data

Sales _2025_Jan

January 2025 Data

Sales _2024_Dec

December 2024 Data

Sales _2024_Nov

November 2024 Data

Sales _2024

All 2024 Data of all the months

Sales _2023

All 2023 Data of all months

Sales _2022

All 2022 Data of all Months

Note: All these tables have identical column names .

We need to create Canvases in which when the user selects a date range from the date Filter, then depending on the selected date, the data is fetched from the related table and displayed on the canvas. Kindly find some scenarios below:

Scenario 1: If today’s date is 13-Feb-2025, When the user selects date 12-Feb-2025 to 13-Feb-2025, then the data from table named Sales should be displayed on the canvas.

Scenario 2: When the user selects date from Nov 2024, then the data from the table named Sales_2024_Nov should be displayed.

Scenario 3 :When the user selects data range of multiple months ex: 3rd March -2024 to 25th June -2024 ,then the data should be fetched from the yearly table named Sales_2024.

Kindly suggest on how to create the canvases as per the above tables structure.

Let us know if more information is required .Thank you.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    If you use the Semantic Model to model your data, you can easily create multiple logical table sources for a unique logical table "Sales" with fragmentation logic to tell the system what tables contains what data.

    Then, when you run queries, the system will automatically query the right table(s) based on the conditions of your query.

    As you don't mention anything about what your source is, I imagine you are just using a single dataset directly. If you don't build a unified dataset with all your tables unified, then you will need to come up with weird logic to query different datasets based on a parameter.

  • Hasan Sd
    Hasan Sd Rank 5 - Community Champion

    Thanks a lot @Gianni Ceresa for the reply .Our Data source is ADW and we were using till now just single dataset by using unions/views which is very heavy on the query of canvas.

    We followed your suggested approach of fragmentation in semantic model .However we are facing below errors:Since we cannot create joins as per out table structure ,how to resolve these errors ?

    image.png

    Kindly review the steps we have taken for this :

    1)Created new semantics model and imported the tables :Sales _2025_Jan,Sales _2025_Feb and Sales _2025_Mar in the physical layer and also created physical Table alias for these 3 tables.

    2)Defined User preferences rules for Physical and Logical Layer.

    3)In Logical Layer,created Business model and imported the tables into Facts . Then created Logical table named like Saleslt as shown below:

    image.png

    4)In Saleslt Logical table → Source ,Created 3 Logical Table sources for the 3 months table and Open Detail did Table mapping ,ticked on Data is Fragmented option and wrote the f(x) as per created_on date .Screenshot for one of LTS is shown below :

    image.png

    image.png
    image.png

    5)In presentation Layer ,created the subject area and imported logical table Saleslt .

    6)After checking consistency ,we got the errors mentioned at the start .

    Kindly help us in resolving this issue asap if possible .Let us now if you require any further information.

    Thank you in advance.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Since we cannot create joins as per out table structure

    Mmmhhh, why? You maybe have no physical join, but you still can have logical joins. If you have no joins, then your measure tables contains only measures, but it would be meaningless measures if they have no attributes.

    And in your fragmentation rule you write:

    Created On >= '01-Jan-2025' and Created On < '01-Feb-2025'

    This just can't work, because you do a textual sorting on the Created Oncolumn. That column being a text is just a major design fault: if it's a date, it should be stored as a date, because that one has a meaning, while as text, it's meaningless.

    Overall, it sounds like you may want take a step back, design your data first, review your sources and data types and apply common good practices there. Then, design your analytical model and only when all the designs make sense you start building the semantic model. Otherwise you are trying to put things together that can't work in the semantic model, and you will lose lot of time trying to run after each individual bit, while the whole design is maybe flawed.

  • Hasan Sd
    Hasan Sd Rank 5 - Community Champion

    Thank you so much @Gianni Ceresa for the reply . I think there is some misunderstanding regarding the data types.

    1)Our Table Sales has columns like id(Integer),Customer_name(String),Created_on (DateTime),City(String),Amount(Float) etc . It's just that we are storing data in monthly tables(based on created_on) instead of a single table . All the monthly tables have identical columns . Please advise how can we create joins(physical or logical) in this case ?

    Thank you…..

  • Gianni Ceresa
    Gianni Ceresa Mod

    If all your tables have the same structure, then the physical joins are all the same for all the tables, but they all need to have the joins.

    And from a logical point of view you only define the joins once, because all your tables are different LTS of the same logical table.

  • Hasan Sd
    Hasan Sd Rank 5 - Community Champion

    Thank you @Gianni Ceresa for the valuable information and support. The errors are now resolved and Semantics model is deployed successfully. We made use of payment_id column in Sales and joined it with the Dimension Table- Payments .But while creating Tabular visualization from Logical Table ,it is displaying data from only one LTS table i.e.Fact1-Current-Table . Kindly advise how to resolve this :

    Additional details of the same are as follows :

    image.png
    image.png

    image.png
    image.png
    image.png
    image.png

    Thank you in advance….

  • Hasan Sd
    Hasan Sd Rank 5 - Community Champion

    Dear @Gianni Ceresa ,We changed the sequences of LTS as shown below and observed that the data from LTS :Mar-2025-LTS only is now getting displayed in Canvas and not for other LTS . So ,why is it only displaying the first LTS values ? Pls advice in resolving this issue . Let us know if you need additional information .

    image.png

    Thank you,

  • Hasan Sd
    Hasan Sd Rank 5 - Community Champion

    Hii @Gianni Ceresa….hope you're doing well ,could you please advise on how to resolve the above issue pls ?Thank you….