Import TextValue from Matrix-Excel

Tobias
New Contributor III

Hi,

I have an Excel template file to import via the stage. This Excel is using a matrix-style for the values (time in columns) and also one column with a text commentary.

As I have to change the view to “Annotation” in order to import a text value, I was thinking about duplicating the rows by a derivative rule and change the view in that rule. The outcome shows the duplicated rows, but these are missing the text value. So this approch is not working.

I also tried to duplicate one time column and use a parser rule to change the view. Doing so, I would need to identify, which time period I am in during pasing, as otherwise all rows are changed.

Any ideas about that?

Regards,
Tobias

16 REPLIES 16

NicolasArgente
Valued Contributor

Please use the TV#:[Annotation] in the Excel and leave the View as YTD.
Remember that Annotation is not loaded in the cube.
Look at the help below

image

 

image
Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

Thanks for your reply. I was reading the guide multiple times. It seems that I was always skipping that sentence 

Thank you Nicolas, this response greatly helped me figure out a solution to my issue. I want to include two additional steps / reminders that I had to take in order for my imported Annotation data to show up in a cube view.

1. Make sure the Text Value field is enabled in your cube's integration settings so the data will be imported to the stage (as Nicolas is showing in his screenshot above) - kudos to this post for that tip

2. Make sure your cube view Origin member for the annotation data is O#Import - I had to change mine from what I usually use, O#BeforeAdj, because text data does not roll up from O#Import or O#Forms into O#BeforeAdj.

TomABC
Contributor

And for the thread note there are sample templates in Marketplace for download as well so you can examine sample token formats for cell text, etc.

SxD
New Contributor III

Sorry to reopen this thread, but I have a similar question on a Matrix Excel Template load. Can you load an Excel Template file to the Import staging table with multiple columns as the time period with the amounts in each of them? I'm running into an error where it's looking for an Amount and Time column separately. Perhaps I don't have my Data Source setup properly? This is what my template looks like for the amounts:

SxD_0-1640901059510.png

If I need to start a new thread, please let me know.

Thanks,

Steven

Tobias
New Contributor III

Hi Steven,
Please check your datasource settings. The "data structure type" needs to be "Matrix data".

Regards,
Tobias

SxD
New Contributor III

Hi Tobias - I already have that set to "Matrix data", but still no luck. 

NicolasArgente
Valued Contributor

Hi Steven, 
Please can you check the title of the columns. 
I would say, for testing purposes, please try to simplify this naming. (ie. Try only with this format for now : T#[2021M1])  Then do not forget that you need to map all the members in Transformation too. (ie 2021M1 to 2021M1). That should work... and only then you compexify.

NicolasArgente_0-1641371492838.png

PS: Thanks with a kudos point 🙂

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

SxD
New Contributor III

Hi Nicolas,

If I make the value just T#[2021M1] then I get an error saying it's missing an amount column. 

Steven

NicolasArgente
Valued Contributor

Hi Steve,

Here is a sample picture. Double check that time dim is in your transformation.

If you still get the same error, please try with only one column : January. Change your datasource accordingly and try to see if you can load. Error could be in the amount column and that would be a way to double check.

NicolasArgente_0-1641887866265.png

 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

SxD
New Contributor III

The part that I'm hung up on is all these examples are using a flat file (.txt, .csv, etc.) where you can either specify which column or start/end position. Thus, there's no way for me to tell OS that column x is my Time and Amount dim. This is my current setup and the way I know it works to load an XFD template, unsure if I can set it up as a Delimited File vs. Fixed File though, so I can try that. I tried with just one month on my last test and got the same error. 

data source.pngtime.png

NicolasArgente
Valued Contributor

Hi Steve,
I think the file loaded to OneStream might be the issue. It is not readable as it might be an XML file.
Can you send me the file you try to load into OS? Please change the metadata/data you do not want me to see, but do not change the structure of the file itself. nicolas.argente AT sonum-int D0t Com 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

NicolasArgente
Valued Contributor
  1. You need an amount column in excel even empty
  2. The time dim should follow this format in the excel T#:[]:[]:[2020M1]
Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

SxD
New Contributor III

Thanks Nicolas! Your suggestions were spot on and I was able to load data from the XFD Excel template. Hope this helps others in the future too. 

Sergey
Contributor III

Hi Steven !

In the standard demo, you can find such configuration with the "HoustonBudget" source file, I hope it will help you:

Sergey_0-1640941031102.png

 

Regards,

SxD
New Contributor III

Hi Sergey - I did check the Golfstream app for ideas first and did use as a baseline. This example uses a flat file vs. an Excel (xls/xlsx) file correct? So for the Time periods, I can see they're setting it to a certain length based on the month. However, in the Excel template I'm trying to load, I can't do that because when you upload the Excel file to preview, it's all encoded. 

Happy to send an email to Partner Enablement if it's easier to have someone jump on a quick call. 

Thanks,

Steven