6 Replies Latest reply on Feb 23, 2017 11:31 AM by Francisco Amores

    FDMEE PSU210: MultiPeriod load from Excel issue

    finskiy
    finskiy Feb 14, 2017 11:57 PM

      Hello all!

       

      How to load Multi Period data from Excel(.xlsx) into Planning using 'one-column' format for periods at Excel template ?

       

      for example:

      AccountEntityPeriodAmount
      acc1ent1Jan100
      acc1ent1Feb200
      acc1ent1Mar300

       

      I've already tested loading multi periods data from .txt using file type 'Delimited - Numeric Data' - it working fine! I also tested different source mappings - Explicit and Adjustment (for custom period names) - it's also working fine!

      I'm also tested loading multi periods data from Excel using differents columns for periods (Jan,Feb..Dec), file type 'Multi column - Numeric Data' and Expression in Import Format period mapping - it's also working fine!

       

      But when i'm trying to use 'one column' format at Excel template without any Expression in Import Format mapping - all rows are loading into first period - Jan!

      i've been tested different ways - file type 'Delimitied..' or 'Multi column..' and Explicit/Adjustment source period mappings...still same problem, all rows are loading into one period..

       

      Any ideas how to solve this ?

      Thanks!

      Best Regards,

      Artem.

        • 1. Re: FDMEE PSU210: MultiPeriod load from Excel issue
          SH_INT
          SH_INT Feb 15, 2017 8:39 AM (in response to finskiy)

          I don't think you are going to be able to do this using Excel. Excel based loads don't really make use of an import format as all of the metadata that defines the dimension associations is held in the Excel sheet itself in header lines. This includes the period information for multi-period files when you have multiple amounts. In an Excel that only has one value it will defult to using the Period defined in the POV (which in your case is probably January, hence why all your data is being loaded there).

          • 2. Re: FDMEE PSU210: MultiPeriod load from Excel issue
            3388391
            3388391 Feb 16, 2017 1:29 AM (in response to finskiy)

            Hi Artem

             

            What worked for me was adding a Period column to the Import Format (tried adding a Year column too but did seem to work) and mapping it to the Period column in the source file. However, the entries in the source file had to match the Periods in the Period Mapping e.g. Period Name = Jan-16 (mapped to Target Period Month = Jan and Year Target = FY16) rather than just using Jan. When you load the data you can specify to and from Periods and the relevant data will be loaded.

             

            Cheers

            Grant

            • 3. Re: FDMEE PSU210: MultiPeriod load from Excel issue
              finskiy
              finskiy Feb 16, 2017 12:43 PM (in response to SH_INT)

              Hello SH_INT,

              no, loaded period is taken from the data-load-rule start-period.

              I just wanna know, how to FileData.py working, when i start import data from Excel into FDMEE, but there is no 'FileData.py' script in the FDMEE folder.. it's seems it taken from ODI xml or something.. mb some later i will check this.

              Thanks

              • 4. Re: FDMEE PSU210: MultiPeriod load from Excel issue
                finskiy
                finskiy Feb 16, 2017 12:54 PM (in response to 3388391)

                Hello Grant.

                 

                When i tested this method, i used all possible spellings Periods in file. For e.g. period key(1/31/2017), period name(2017.01), from Global Period Mapping.. GL Period(Jan), GL Period Number(201701) from Source File Period Mapping - still have no sense.. but thanks, i will test it again today.. mb i did something wrong..

                 

                If this work for U and U loading different Periods from Excel(.xlsx ?) single-column source file.. it's a good news!

                Cheers,

                Artem

                • 5. Re: FDMEE PSU210: MultiPeriod load from Excel issue
                  3355552
                  3355552 Feb 21, 2017 4:18 AM (in response to finskiy)

                  Hi Artem,

                   

                   

                  For multi period load, I normally used this format instead of one column period:(either in patch 210 or patch 200), but even this need a mappings script to differentiate the amount for each different month in a column :

                   

                  maybe you need to create mapping script(expression) in the import format to tell the system that you have different month in one column???

                   

                  Otherwise how does FDMEE knows that you have different month, because the target is only one month in Import Format.

                  • 6. Re: FDMEE PSU210: MultiPeriod load from Excel issue
                    Francisco Amores
                    Francisco Amores Feb 23, 2017 11:31 AM (in response to finskiy)

                    You may need to add 2 lookup dimensions, one for year and one for period.

                     

                    Then add them to your import format and configure your Excel accordingly.

                     

                    You will need to adjust the SQL view used by FDMEE to export data so period/year are taken from columns assigned to lookups rather than period mapping tables.