1 2 Previous Next 18 Replies Latest reply on Nov 3, 2017 3:18 PM by Robert Angel

    Ago function

    Luigi_Gif

      Dear all,

       

      I am having a problem with Ago funtion application. I created a time dimension  and I am interested to have all value YTD of the last year. the formula I am using is:

      AGO("Area Sales"."Vendite"."Order Scenario Actual" , "Area Sales"."Schedule Ship Period"."Year" ,1)

      It works if the filter is month, while if I try with week, I have no values, or maybe....values are not correct

      Does anyone have an advice ?

       

      Thanks

      Luigi

        • 1. Re: Ago function
          Joel Acha

          Can you provide information about your OBIEE version? Also, have you checked the query log files to see what query has been generated by the BI server? Screenshots are always useful to enable people to properly visualize your issue.

          • 2. Re: Ago function
            asim cholas

            Do you have a proper key for your week in time dimension? Are you using the week column from time dimension in report?

            • 3. Re: Ago function
              Robert Angel

              Check that you have chronological keys defined at each level of the time dimension hierarchy.

               

              Check that the underlying key for each level is unique at that level

               

              e.g.

               

              Year 2015

              Quarter 201504

              Month 201510

              Week 201548

              Date 20151201

               

              Note your keys can be surrogates, the important factor is that they are each unique at that level of the hierarchy and ordered the same as the underlying data

              • 4. Re: Ago function
                Luigi_Gif

                Hi Joel,

                 

                Oracle Business Intelligence Product Version 11.1.1.9.160719

                 

                while the error is:

                 

                Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P

                 

                Odbc driver returned an error (SQLExecDirectW).

                 

                 

                State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27045] Nonexistent column: "Schedule Ship Period"."CAL_WEEK_DESC". (HY000)

                 

                 

                SQL Issued: {call NQSGetQueryColumnInfo('SET VARIABLE LOGLEVEL=7; SELECT "Schedule Ship Period"."CAL_WEEK_DESC" FROM "Area Sales"')}

                 

                 

                SQL Issued: SET VARIABLE LOGLEVEL=7; SELECT "Schedule Ship Period"."CAL_WEEK_DESC" FROM "Area Sales"

                 

                • 5. Re: Ago function
                  Luigi_Gif

                  Untitled.png

                  I set my time dimension as displeyed

                  • 6. Re: Ago function
                    Christian Berg

                    That's just a logical dimension and does not pertain to your question. You are missing all and and actual dimensional hierarchy objects?!

                     

                    Either screenshot the real time dimension object with its configuration or - if they really do not exist - create them.

                     

                    What kind of RPD is that? OBIA? Looks atrocious.

                    • 7. Re: Ago function
                      Joel Acha

                      The error is quite clear:

                       

                      Message returned from OBIS. [nQSError: 27045] Nonexistent column: "Schedule Ship Period"."CAL_WEEK_DESC".

                       

                      You are referencing a column that doesn't exist in your rpd.

                      • 8. Re: Ago function
                        Luigi_Gif

                        Dear Christian,

                         

                        I have never seen a funny RPD ahah, I am a Deloitte consulting and now I am trying to drive a costumer of ours in his BI activity. So they contact me when there is a problem, this RPD was build by them...

                        • 9. Re: Ago function
                          Christian Berg

                          Is that column blocked for access by that user/role?

                          • 10. Re: Ago function
                            Robert Angel

                            Hi,

                             

                             

                            CAL_WEEK_DESC - call me psychic but I suspect that is a descriptor field, not a key. You need to reference a key in AGO function.

                             

                            Might be worth a punt with CAL_WEEK_ID, but I would need to see what the corresponding dimension hierarchy looks like to comment more fully.

                            • 11. Re: Ago function
                              Luigi_Gif

                              Hi Robert,

                               

                              Here below I attach my time dimension hierarchy

                              Untitled.png

                              The Key in this case is DAY, like in all others time dimensions. In my report if I use AGO function and I use the month as a filter, It will work, when I use week it will not

                              • 12. Re: Ago function
                                Robert Angel

                                Hi Luigi,

                                 

                                 

                                I don't think you are understanding me.

                                 

                                For functions like AGO to work you have to have a well formed time hierarchical dimension with appropriate chronological keys at each level.

                                 

                                Appropriate = uniquely reflecting the level of granularity of the level in question.

                                 

                                You need to check the key that is defined as the unique identifier at week level in your time dimension hierarchy that must have been created to enable AGO to work for month to work.

                                 

                                Your key should be along the lines of 201749 for week 49 of the year 2017, 49 by itself is not valid. (Or a surrogate key equivalent that changes at the same rate as the underlying year / week does.)

                                 

                                What you are showing me is the dimension, you need to show me the (probably level based) hierarchy.

                                 

                                 

                                More importantly you need to check that the week key is fit for purpose, and not a weak key....

                                • 13. Re: Ago function
                                  Luigi_Gif

                                  Hi Rober,

                                   

                                  Ok now, I'm sorry but I have been enjoying BI for 7 days. Below I attached the schema requested I hope it will be ok for you to understand my problem:

                                  Untitled.png

                                  In the physical column on the right I also opened the period variable used in my report

                                   

                                  Thanks a lot

                                  Luigi

                                  • 14. Re: Ago function
                                    Robert Angel

                                    Hi Luigi,

                                     

                                     

                                    thanks for the screenshot, what I am asking you to do is to click on the WEEK key in your business model hierarchy as per your screenshot above, query the PHYSICAL column. Then when you are 'on' the physical model column use the tool to query the underlying database and ascertain if your key; -

                                     

                                    a. truly represents the granularity of your physical weeks, i.e. if you have 5 years data and 52 weeks in each year (to keep my maths simple) do you have 5 x 52 week distinct keys?

                                     

                                    b. Is the key sufficient to represent the uniqueness at that level, i.e. NOT 48,49,50, but 201748, 201749, 201750 or surrogate key equivalent.

                                     

                                     

                                    Hope that clarifies?

                                    1 2 Previous Next