OBI Time Dimension Weeks In a Month – Prototype Hack
Christian Screen | | February 5, 2010
I was looking to create a formula that required averaging based on the number of weeks in the given month. As an example Monthly Sales Total / # Weeks In Month. Since this is a fiscal calendar the weeks are usually standardized and shouldn’t change that often. So, if the fiscal calendar is on a 4-4-5 or 4-5-4 the logic is rather straightforward.
I couldn’t make changes to the underlying Calendar dimension table and I couldn’t recall how to do this programmatically in OBI so I went with what I will call a quick prototyping hack. Here it is below.
CASE WHEN “Indirect Sales”.DIM_TIME.MONTH_NUMBER IN (3, 6, 9, 12) THEN 5 ELSE 4 END
This worked out perfectly as a place holder as we can see by a quick ad-hoc look at the new calculated measure.
If anyone has the right or clever way of doing this leave a comment.
Blog Author
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Popular Posts
Art of BI: BI Publisher (BIP) Quick Guide and Tips
Read our blog post on how to take over production support of BI Publisher reports.
How to Index a Fact Table – A Best Practice
At the base of any good BI project is a solid data warehouse or data mart.
Art of BI: How to Add Comments in Oracle BI (OBIEE)
Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.