7 Replies Latest reply: Oct 24, 2013 2:23 PM by user10615659 RSS

    How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps

    BrianCR


      In our canned BI Apps implementation for Projects, the Date - Fiscal Calendar Time Hierarchy is not defined properly.  Quarterly values are only getting the value of the first month of the quarter.  So Q1 = Jan only, Q2= Apr only etc.  This issue is contained in the out of the box dashboards for the Project module.  I have created some simple ad hoc analysis from the same subject area to validate that it is not a dashboard problem, but an underlying problem of the hierarchy.

       

      I'm assuming others have found this issue, but I have not been able to find any discussion.

       

      Where would I need to look to fix this issue?  RPD?  We are version 11.1.1.6.4.

       

      Thanks.

      Brian

        • 1. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
          SaichandVaranasi

          Brian,

           

          Could you give more details on Dashbaord/Page/Report Name i will take a look at rpd and get back to you .

           

          btw,which version of biapps?

           

          Thanks,

          Saichand

          • 2. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
            user10615659

            Check the following

            1. Does the physical table for this date is haveing Q1,Q2,Q3,Q4 values properly populated. if the data is wrong then the issue is with ETL.

            2. If you have correct data in physical layer, Check the back end SQL generated while you run the report or dashboard or even the report from  Subject Area. Check whether any additional calc is done on Quarter etc. Run them manually on the database and check whether the result is replicate-able.

            3.If replicate-able then the issue is in RPD.

            4. Since you said you made a new report and it also resulted in same behavior, I'm sure the issue is with RPD. Find the logical table and column and check is there any expression in place, join conditions, content filters that filter the other months.

             

            My most likely guess is the issues will be with RPD and there must be an expression which does this.

             

            mark if this helps

            • 3. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
              BrianCR

              It is under Project Subject Area -> Project Cost -> Actual Cost by Project, Financial Resource.  On this analysis, the Fiscal Calendar drill down valus are not calculating properly at the quarter level.

               

              I'm not sure what version of BI Apps.  Where do I find this?

              • 5. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
                user10615659

                ok do this, if you think hierarchy is the problem, make a new report

                 

                drag drop Fiscal month, Fiscal quarter and fiscal year from Fiscal Calendar, with current year as filter.

                Check whether you are able to see all months of this year.  if you see all months then your hierarchy will not be a problem.

                 

                if you don't see all months, I think its a data issue. As you can see Jan and Apr right? the Fiscal Calendar may not have all Fiscal months associated to quarter.

                 

                If you get all months It might be more related to your facts. its also possible your fact might not have measures for all the months.

                 

                one thing is sure, you need to try it out to solve this. What we can do is give possible ways to solve this.

                The version is of no use if we cannot replicate your issue.

                • 6. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
                  BrianCR

                  I have created several reports using the specific time hierarchy I noted above.  When I drill into 2013, I get all 4 quarters.   When I drill into Q1 2013, I get Jan, Feb and Mar, all of which have data.  However, the total of Q1 only contains the value of Jan.

                   

                  So I guess I don't see how this could be data problem.

                   

                  I'm a beginner with OBIEE, but seems like aggregation logic and I don't know where to look for this.

                  • 7. Re: How to fix Time Hierarchy Qtr rollup in Projects Subject Area of BI Apps
                    user10615659

                    Brian,

                     

                    make this report,

                    go to criteria , select the fact measure only and run the report. note down the total you get from the measure.

                    then add the fiscal year/quarter/month  to the report and and re-run it without filter.

                    does the grand total of the measure match the previous total what we noted.

                    if it matches, then fact has 3 months measure value(jan,feb,mar) aggregated and place for one month(jan) or first month of the quarter.

                    if your values don't match, its absolutely a fact table issue.

                    If you want to know why below are the steps for finding/proving the issue

                     

                    Possible issues:

                    1. Your fact is not having data beyond Jan for that quarter. (Check and confirm this by issuing a sql on fact table on month of Feb or March for 2013.)

                    2. If your fact is having the right data, some thing else is filtering the data for other months.

                    Filters can either in the form of join, content filter or even expression in the measure.

                     

                    in order to go about this,Run the dashboard /report ( or just make a simple report with fiscal calendar and fact measure) and then login as Administrator and go to

                    Administration>Manage session in Analysis

                    find the report you just ran for this issue and check the log for SQL. ( other way of finding the SQL will be to check NQquery.log present in  obieehome>instance>instance1>diagnostics>OracleBIServer>)

                     

                    Copy this SQL and run it in your SQL developer. The result should be same.

                    now find out what are the joins and filter added to the SQL ( any thing after the "Where" clause)

                     

                    eliminate one of them and run the SQL again and see whether you get all months. if not try eliminating other filters one at a time and run the SQL.

                    until you get it for all months. If this is not happening.

                     

                    my guess is you fact table is not having data for all months (assuming the join is correct as it is OOB).

                     

                    for which you can directly write a SQL joining fact and fiscal calendar with 2013 as filter for year.( find the join condition from rpd) and check you get values for feb and march. if  feb/march data is there in you calendar table but if you don't get values for that then, you fact is not having the foreign keys for feb and march.( depending on granularity being month or date)

                     

                    mark if this helps