Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
To get week ending dates from week number in obiee report
I have a report which shows calculated data in pivot tables for last four weeks i.e, 30,31, 32, 33 (week number as heading ). I just have week number column in the presentation folder .
I do not want to
* Make any changes in repository or ETL
* Also, I do not want to put CASE statements with 52 statements for 52 weeks as it will affect the performance.
Please let me know if anyone has the solution. I have a week starting from Monday to Sunday. So I would need Sunday's date as week ending dates in my OBIEE 12c report.
Thanks
Answers
-
If your data is stored per date:
TIMESTAMPADD(SQL_TSI_DAY,(DAYOFWEEK(CURRENT_DATE)*-1)+7 ,CURRENT_DATE)
CURRENT_DATE should be replaced with the date in your date/time dimension.
0 -
So you don't want to do anything which would be the right thing to do like having a proper time dimension with the various time attributes neither expose the needed column in the subject area to let people work by referencing a single column and so having all the same logic but you prefer to have a formula which will end up being different in every single report. Fine, your choice ...
The answer is simple: how did you calculated your week number in your source? There isn't a unique and universal definition of the week, so you are the only one knowing what a week is for you and how it is calculated. Take that formula and use it to go back from a week number to the day. The last day of the week is the easy part of the job as once you have a date in the week you can easily get to the Sunday.
As you see it's maybe easier to expose the right column in the subject area or add a new attribute by ETL than having to do that in a formula (and I'm not even going to talk about performance etc.).
0 -
2791486 wrote:I have a report which shows calculated data in pivot tables for last four weeks i.e, 30,31, 32, 33 (week number as heading ). I just have week number column in the presentation folder . I do not want to* Make any changes in repository or ETL* Also, I do not want to put CASE statements with 52 statements for 52 weeks as it will affect the performance.Please let me know if anyone has the solution. I have a week starting from Monday to Sunday. So I would need Sunday's date as week ending dates in my OBIEE 12c report.Thanks
"I don't want to do anything or think, but I want stuff to do...stuff" - I suggest Hogwash, the prime school for Lazy Magic!
0 -
You have major layers: Database - BI Logic - Presentation
"* Make any changes in repository or ETL"
^ so you remove the possibility of the Database & BI Logic layers leaving you with Presentation
"* Also, I do not want to put CASE statements with 52 statements for 52 weeks as it will affect the performance."
^ the last one now is out as you realize that's the worst of the options
You always trade performance for flexibility:
Database - low flexibility; high performance
BI Logic - more flexibility; less performance
Presentation - high flexibility; low performance
You've ruled out all three layers ... not much we can tell you at this point.
0 -
Dear Christian
You should know the actual situation before judging anyone. I am part of a team in which we do not have access to repository or ETL, what we can do is make changes on answers and that is the reason I have mentioned those conditions. As it's a team which follow lots of processes to get things done i.e, raising requests to DEV , ETL team,..creating documents define business rules...bla bla bla.. And in the situation when user wants something quickly at answers level... I thought to raise it here in OTN to get some help but not for useless and stupid comments.
anyways.. you keep you advice for yourself
0 -
Thank you very much Thomas.
But we can apply below formula when something is needed in urgency . I know it'll hit the performance but there are cases when you need to listen to business and get things done quickly
CONCAT(CONCAT(CONCAT('Week', CAST("TABLE
Date"."DIM Week " AS CHAR(2))), ' w/e '),
LEFT(CAST(TIMESTAMPADD(SQL_TSI_DAY, 6, EVALUATE('TRUNC(%1, %2)' AS DATE,
"DIM Date"." Col Date", 'IW')) AS CHAR(10)), 6))0 -
I agree with you completely and well aware about the right things and performance issues which we can have after applying the formula at answers level.
But there are some limitations and privilege issues which restrict me to answers level.
Anyways I found the solutions. Thanks
0 -
Thanks Martin. Below formula worked for me
CONCAT(CONCAT(CONCAT('Week', CAST("TABLE
Date"."DIM Week " AS CHAR(2))), ' w/e '),
LEFT(CAST(TIMESTAMPADD(SQL_TSI_DAY, 6, EVALUATE('TRUNC(%1, %2)' AS DATE,
"DIM Date"." Col Date", 'IW')) AS CHAR(10)), 6))0 -
@2791486 Happy to help. If you found my answer to be helpful or correct can you please mark it as such?
0 -
All that description does is showcase why tailorism and silos in analytics environments are a really bad idea. It's a traversing discipline and also kind of the reaon why waterfall doesn't work and agility is key.
"user wants something quickly at answers level" - oh sure. And who goes back and does thigns properly afterwards? Then the performance issues start. Then the maintenance horrors start because there's logic all over the place and no one understands anymore why 42 is the answer since there's 5 layers of calculation logic on top of each other - each one "because something was needed quickly". Then the next DB upgrade happens and things run into a wall because the EVALUATE utilized doesn't work anymore. Then the next platform upgrade happens and things run into a wall because half the front-end hacks encounter issues.
Rushed workarounds always come back to bite you with interest.
0
