Skip to Content
author's profile photo Former Member
Former Member

How to create measure comparing 3 different dimensions

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 27, 2013 at 03:22 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 27, 2013 at 06:21 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 27, 2013 at 04:26 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.