In OBIEE, can I turn one row of data into many?

Question

We are taking the revenue total from an Opportunity in OBIEE and trying to break that into 12 month buckets with a 12th of the total in each bucket to indicate that the sales person will not realize all of the opportunity all at once, but will trickle in over the course of a year starting in the month of the opportunity close.

So, we have data that looks like...

"Opportunity Name","Close Date","Revenue"
"Oppty 1", 5/23/2013, 1200
"Oppty 2", 5/30/2013, 1800
"Oppty 3", 6/1/2013, 600

I need this to turn into...

"Opportunity Name","Month Bucket","Revenue"
"Oppty 1", "5/2013", 100
"Oppty 1", "6/2013", 100
...
"Oppty 1", "3/2014", 100
"Oppty 1", "4/2014", 100
"Oppty 2", "5/2013", 150
"Oppty 2", "6/2013", 150
...
"Oppty 2", "3/2014", 150
"Oppty 2", "4/2014", 150
"Oppty 3", "6/2013", 50
"Oppty 3", "7/2013", 50
...
"Oppty 3", "4/2014", 50
"Oppty 3", "5/2014", 50

If I can get the data to look like this, I believe I can put together a pivot table to give me what I need.

In case it matters, we are on a cloud solution, so I don't have access to the database directly or the middleware.

Answer 1

If using Oracle Sales Cloud, you have the ability to create a Revenue schedule. You must enable first in Application Composer. By using the Revenue Schedule, it should allow you to build a report like this.

Alternatively, you could create a custom child object underneath Opportunity (in Application Composer) called "SplitRevenue" wherein you perform your divide-by-12 calculation and build a table of the individual revenue lines.

After doing this, you can add this object as a Child object when creating a Custom Subject Area.

Your child object will contain the granularity of data that you seek.

---

Also note that if using Oracle Sales Cloud, and if you have the BI Administrator role, you can do direct database queries via New > Analysis > Direct database query. You need to know the connection pool for the application schema you will query against. For example, for Oracle Sales Cloud, it's "CRM_OLTP"."Connection Pool".

You can also do joins using Logical SQL. Please read through this Oracle document for more information on how to do this.