Matrix Data Load - Data Source Setup

[vmanojrc30]

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?

[NicolasArgente · Accepted solution]

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]

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]

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]

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]

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