3 Replies Latest reply: Sep 5, 2014 9:43 AM by Yaniv Feldman RSS

    Inserting a date variable in PseudoMDX D - SAP OLAP Connector

      Hi

      My query looks something like this:

       

      [Query]:
      Load *;
      Select PseudoMDX D (
      Dimensions (
        [A] (),
        [B] (),
        [C] (),
        [D] (),
        [CALMONTH] ()),
      Measures (
        [1],
        [2],
        [3],
        [4],

      Variables (
        [0TYEAR] (I [CALMONTH].[201401]:[CALMONTH].[201403])),
      From (Query));

       

      Since there are limitations with the amount of data I can retrieve, I have to retrieve data every 3 months at a time.

       

      There is also no Primary Key - hence I cannot do Incremental Loads.

       

      Is there a way to loop through the query but then changing the date range (To the next three months)? - Maybe using a variable or any other way for that matter?

       

      Any help would be appreciated - Thanks!

       

      Magen

        • Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector
          Yaniv Feldman

          Hi

           

          You can do this:

           

          for vMonth=201401 to 201412

           

               DATA:

               Select PseudoMDX D (
               Dimensions (
                 [A] (),
                 [B] (),
                 [C] (),
                 [D] (),
                 [CALMONTH] ()),
               Measures (
                 [1],
                 [2],
                 [3],
                 [4],

               Variables (
                 [0TYEAR] (I [CALMONTH].[$(vMonth)]:[CALMONTH].[$(vMonth)])),
               From (Query));

           

          next vMonth

           

           

          This loop will return the query results for each calendar month. The data will be aggregated to table DATA.

            • Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector

              Hi Yaniv

               

              Thanks that works great, but, what if i want to retrieve data over 2 years.

               

              So for example 201301 to 201412.

               

              I cannot declare this in the variable as it doesn't recognize it to be a year and month.

               

              Hence if i attempt to run it, it would return 201301, 201302,...201312, 201313, 201314 and so on ...

               

              Any idea?

               

               

              Regards,

               

              Magen

                • Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector
                  Yaniv Feldman

                  Hi Magen

                   

                  Been busy during the last couple of days...

                   

                  You should use nested loop, for example:

                   

                  for year = year(today()) to year(today())+1

                   

                   

                    for period = 0 to 12

                   

                   

                    if len(period) = 1 then

                   

                   

                    let v_period = '0'.

                   

                   

                    else

                   

                   

                    let v_period = period;

                   

                   

                    endif

                   

                   

                    Select PseudoMDX D (

                        Dimensions (

                          [A] (),

                          [B] (),

                          [C] (),

                          [D] (),

                          [CALMONTH] ()),

                        Measures (

                          [1],

                          [2],

                          [3],

                          [4],

                        Variables (

                    [0VYEAR] (I = [0CALYEAR].[$(year)]),

                    [0VCALMONTH] (I = [0CALMONTH].[$(v_period)])),

                        From (Query));

                   

                   

                    next period

                  next year

                   

                  Thanks

                  Yaniv