nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Limit record returned by fact table in OBIEE Analysis

Aiman.Al-Khammash-Oracle

I have a fact table in OBIEE that include records with time stamps. for example every hour we capture records , and in a day we have 24 record per customer . i want to do aggregates on the latest record (from last hour) , how i can do this? how i can limit the record only to the last hour record

Thanks

Aiman

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

Add a WHERE condition on the logical table source of your logical fact table.

It will limit your fact table by the condition you write all the time.

Aiman.Al-Khammash-Oracle

Sorry i'm new to OBIEE and may not know the exact syntax. i tried to use this, but i can't build my where clause using the function provided

For example i want to run this where

RECORD_DATE = TRUNC(SYSDATE - 2 / 24, 'hh')

Gianni Ceresa

Forget SQL, you are in the logical layer of OBIEE, the logical layer doesn't speak SQL like a database because it could be pointing to an XML file, a SQL Server etc.

So click the icon on the right of the textarea, the one with the "fx".

There select "Category" = Functions, "Functions" = Calendar Date/Time Functions and build your conditions with that.

You click on "Category" = Physical Tables to select the column you want to use in the filter, then you write you = and then with these functions you build your condition.

You have the Current_* functions replacing SYSDATE, you have TimeStampAdd for calculations (add / subtract time/days) etc.

The easiest way is probably to go in the front-end, build an analysis and write the formula there matching your "TRUNC(SYSDATE - 2 / 24, 'hh')" as you can see "live" the result of your formula and adapt it super quickly till you get the required result. It's the same functions you fine in the Expression Builder.

Gianni Ceresa

As you are new I will put you on the right direction

So you want to get the current date/time, subtract 2 hours and keep only the hour (so 00:00 for minutes and seconds).

In OBIEE that's equivalent to this: TIMESTAMPADD(SQL_TSI_HOUR, HOUR(CURRENT_TIME(0)) - 2, CAST(CURRENT_DATE as TIMESTAMP))

CURRENT_DATE = give you the date without any time component, it's casted as timestamp as we are going to add some hours to it.

CURRENT_TIME(0) = give you the current time without any date component, calling HOUR on that return the current number of hours (so if it's 9pm HOUR return 21).

Substracting 2 to the result of HOUR give you the "2 hours ago" piece.

Last step is putting together the date and the calculated time, so on the current date we add the calculated number of hours (so no need to care about minutes and seconds and they don't exists).

TIMESTAMPADD can add or subtract, so when you are 1am it will actually subtract 1h from the current date and give you yesterday at 11pm.

Aiman.Al-Khammash-Oracle

Thanks Gianni, appreciate it , but this filter doesn't return any thing and i get the following

Screen Shot 2018-04-10 at 4.21.18 PM.png

The RECORD_DATE is a date column (e.g 4/10/2018 04:00:00 PM)

[Deleted User]

Sorry to contradict @Gianni Ceresa here but are both of you sure that lts filtering is what is needed here?

@Aiman.Al-Khammash-Oracle you provided an extremely narrow question withoutnany context which makes me wonder whether the solution is adapted to what your real requirement is.

Do you really want to filter that data? Or - since you mentioned aggregates - is what you're looking for a solution with LAST aggregates across the time dimension?

Edit: Also - when you get no results or errors - please look at the quey log and look and the effevtive statement sent to the source to see which.filter gets applied how. We can't know what's happening there from here and each bit of information you don't look at and share in the thread makes the issue that much longer and harder to solve.

Gianni Ceresa

Well ... I took what I got as input

It sounded like a snapshot table where the OP wanted to work only on the last snapshot, and because LAST is already an aggregation when the OP asked for aggregates on last snapshot it sounded as a WHERE ...

But of course everything is possible, depends on the exact need of the OP.

@Aiman.Al-Khammash-Oracle : what Christian said, if you have no result have a look at the query generated and "debug" it as a query to find out what you have to adapt on the OBIEE side. From a theoretical point of view your TRUNC... is equivalent to the above formula, in my Oracle DB they also give the same result, so if you get no data there is still something somewhere else ... But you are the only one able to find it as we don't have your OBIEE server and database in front of us.

[Deleted User]

Tgat is my point...op must be precise otherwise he goes off with an unadapted solution

Aiman.Al-Khammash-Oracle

Thanks Gianni and Christian for the suggestion. I'm looking into the SQL logs like you suggested and may update you later. but some thing i tried is to use session variable and use it from analysis filter and that worked .

i will look into LAST aggr also , but a challenge i have in this DW is that i don't have a time dimension, just have dates for each record.

So another question if i may (any by now you know i'm really new to all this, so feel free to educate me) what is the best way to create a Time DIM and use dates columns to map to the dimension ? i have some ideas but i would like to hear from you guys about best way

Aiman.Al-Khammash-Oracle

@Gianni Ceresa @Christian Berg, Can you please provide an example on how to use LAST aggregates across the time dimension?