Forum Discussion

vmanojrc30's avatar
vmanojrc30
Contributor
2 years ago

Matrix Data Load - Data Source Setup

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?

 

 

Workflow
  • NicolasArgente's avatar
    NicolasArgente
    2 years ago

    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's avatar
    NicolasArgente
    Valued Contributor
    2 years ago

    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

     

    • vmanojrc30's avatar
      vmanojrc30
      Contributor
      2 years ago

      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's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    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)

     



    • vmanojrc30's avatar
      vmanojrc30
      Contributor
      2 years ago

      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's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    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))}"