Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Limit record returned by fact table in OBIEE Analysis
Hi
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
Answers
-
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.
0 -
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')
0 -
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.
0 -
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.
0 -
Thanks Gianni, appreciate it , but this filter doesn't return any thing and i get the following
The RECORD_DATE is a date column (e.g 4/10/2018 04:00:00 PM)
0 -
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.
0 -
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.
0 -
Tgat is my point...op must be precise otherwise he goes off with an unadapted solution
0 -
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
0 -
@Gianni Ceresa @Christian Berg, Can you please provide an example on how to use LAST aggregates across the time dimension?
0
