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

[Former Member]

Hi Nanda,

As Rama rightly suggested, count function is to be done once. And in cross tab you can use as many dimensions as you want.

As you have done work till DB, follow given simple steps:

1. Bring the tables to universe (you can use Universe Designer or IDT)
2. Create all the objects from each table in 3 different folder (one for each table as they are not joined)
3. If you feel comfortable, create Count, Percentage and Sum variables in universe itself
4. Else you can do the same in Report level also
5. Go to Report, take cross tab table and dragndrop variables that you need

Let us know if you face any issue.

[Former Member]

Hi Nanda,

Answer to your 1st question:

The count variable you will be defining for 1 time not for 20 * 4 = 180 variables.

Then the three objects Year, Dept, Defined measure

This will count according to year and dept.

For your 2nd question:

Create a cross table with dept vertical and dept year horizontal for the two measure defined.

Note: the query you define should be of same table, since the tables are not joined to avoid data inconsistency.

Regards,

Rama