Skip to main content
V
vmanojrc30
Contributor
June 30, 2023
Solved

Matrix Data Load - Data Source Setup

  • June 30, 2023
  • 3 replies
  • 0 views

I have a Matrix data file with 36 Months of data. The header has the periods in MM/DD/YYYY format. Is it possible to convert this Format to OS Period format like 2023M1 and return in each of the 36-time source dimension in Data Source Setup?

 

 

    Best answer by NicolasArgente

    Hi vmanojrc30 !
    Yes you can Transform that in OneStream.
    Before I go further, please have a look at HoustonBudget datasource in Golfstream! It will surely help.
    Do do that you will need 36 Time timensions in your source.
    Then you will need to define the Line of your header - the matrix hearder values line.
    After that you will need to add a Transformation for your time dimension that will transform each time member into a onestream member.
    Just for your testing purpose, i would say, keep it simple and try to do that only with 2 time columns! 
    Have a look at the printscreen below.
    Please provide a kudo if it helps!
    Nic

    NicolasArgente_0-1688109204841.png

     

    3 replies

    N
    NicolasArgenteAnswer
    Veteran
    June 30, 2023

    Hi vmanojrc30 !
    Yes you can Transform that in OneStream.
    Before I go further, please have a look at HoustonBudget datasource in Golfstream! It will surely help.
    Do do that you will need 36 Time timensions in your source.
    Then you will need to define the Line of your header - the matrix hearder values line.
    After that you will need to add a Transformation for your time dimension that will transform each time member into a onestream member.
    Just for your testing purpose, i would say, keep it simple and try to do that only with 2 time columns! 
    Have a look at the printscreen below.
    Please provide a kudo if it helps!
    Nic

    NicolasArgente_0-1688109204841.png

     

      V
      vmanojrc30Author
      Contributor
      June 30, 2023

      Thanks Nicolas!

      Is there a way to convert the date format to OS Format while Importing itself? I am looking for a way to use a Logical expression within Data Source and convert it dynamically (For example : 2/1/2023  - > 2023M2) So I can eliminate the manual transformation rule setups for each year.

      I am not able to find the api to read the calendar date format (header values) in each time dimension. Appreciate any pointers.

      RobbSalzmann
      RobbSalzmann
      Legend
      June 30, 2023

      String-mangling dates is part of the .Net Framework.  You can do it yourself like this:

      As a one-liner:
      Return $"{Convert.ToDateTime("2/1/2023").Year}M{Month(Convert.ToDateTime("2/1/2023"))}"

      Yeilds: "2023M2"


      In an XFBR:

      Dim colHeaderDate As String = "11-Feb" 'taking 11-Feb from your screenshot above
      Dim convertDate As String = $"20{colHeaderDate}-01"
      Dim dateTime As DateTime = Convert.ToDateTime(convertDate)
      Dim osDate As String = $"{dateTime.Year}M{Month(dateTime)}"
      BRApi.ErrorLog.LogMessage(si, osDate)

      RobbSalzmann_1-1688152772419.png

       



        V
        vmanojrc30Author
        Contributor
        June 30, 2023

        Thanks Rob! 

        I am looking for ways to read the calendar date ("2/1/2023") from the header and do the conversion. Because the calendar date in header is not static. It Changes every month.

          RobbSalzmann
          RobbSalzmann
          Legend
          June 30, 2023

          Sry for the confusion, my example was to show the string mangling needed to get from the date you posted to the OneStream Date format.  You of course can use your own variable in place of the hardcoded date:
          Return $"{Convert.ToDateTime(SomeStrDateVar).Year}M{Month(Convert.ToDateTime(SomeStrDateVar))}"

          Terms & ConditionsAccessibility statement