How to create measure comparing 3 different dimensions

[Former Member]

Experts,

      I am asked to create a web Intelligence report based on Excel in BO 4, so no dimensional modelling here.

      I dumped data to Oracle and created table for each sheet and have 3 tables with no joins in IDT.This is not the problem.

      The following is my requirement:

      1. I need to create two main objects, which are of the following description. Total Courses :   Count([Course ID]) where ([Main Sponsor]="Marketing" OR [Co-sponsor]="Marketing" or [Co-sponsor2]="Marketing")

        I am now creating at present for each department and for each year. I need to show data for 20 departments and for 4 years so now I am creating for each cell. so 180 variables for each measure.

     2. Second one is:% Courses as co-sponsor:  % of couses as co sponsor: Count([Course ID]) where ([Co-sponsor]="Marketing" or Co-sponsor2="Marketing")/Count(Course name) where (Main Sponsor="Marketing" OR "Co-sponsor="Marketing" or Co-sponsor2="Marketing")

My report should look like:

Dept               - Total Courses in 2013    - % Courses as co-sponsor 2013   - Total Courses in 2012  - % Courses as co-sponsor in 2012

Marketing       -  20                   --   30%                                                    -  38                            -42%                  

.

.

.for 20 depts

I am not sure which one to use as dept and is there a way to avoid cell wise formulae. All these columns are in single sheet so single table.

Is there a way to create objects in Universe or creating a custom SQL.

Please suggest.

Regards

Nanda Kishore

[Former Member · Accepted solution]

Hi Kishore,

It will impact the report performance if you create variables on the report.

Consider that you have 1000 records in you database and if you create 180 variables on the report level. Then the calculation performed is 1000*180 times which may lead to poor performance.

Better way is to create a variables at universe level and use them directly in the report.

My suggestion is:

1. create a count variable irrespective of the course.

2. create a flag which sets a value 'Y' where course name matches with all three dimension values Main course, Co-sponsor & Co sponsor2

3. use the flag to filter the records in the report.

4. similarly create variable for % courses.

Hope, this will help you solving your problem.

Regards

Sunil Koneru