Help! Submitting manual relational data

[Misty]

Hi there,

My users want to use TM1 to upload rows of data - not multi-dimensional, but just rows of relational data, essentially with the purpose of 'flagging' certain rows 'Y/N' so that a different data warehouse can read these rows and then calculate differently for the rows with 'Y'.

Essentially this is just a workaround for submitting CSV files. Does anyone have any ideas on how to create a cube that could handle this? I tried, but when I tried to create a view that the users could see in Excel (with all the 'dimensions' across the top - no cross-tab), Architect didn't have enough memory to save the view, even when I created a dummy dimension to put down the side.

Also, the data in some of the columns is not restricted by only a certain few values - it could be anything!

Help please!

Thanks

[Martin Ryan]

Hi Misty,

Where do the users want to upload rows of relational data to? What you're outlining is a very unorthodox use of TM1. For one it's strength is not in holding strings like 'Y' or 'N', but rather in numbers. Also, another TM1 strength is that it hides empty cells where there is no data, it sounds to me like you're trying to have a view that shows all possible combinations. TM1 is reaaaaaally not the best tool for this.

What are your users actually trying to achieve? Sometimes users need to be told what tool they should be using, rather than you just doing what they ask. The user often knows what they want, but are usually not as clever as they think they are when it comes to how.

Martin

[Alan Kirk]

My users want to use TM1 to upload rows of data - not multi-dimensional, but just rows of relational data, essentially with the purpose of 'flagging' certain rows 'Y/N' so that a different data warehouse can read these rows and then calculate differently for the rows with 'Y'.

Essentially this is just a workaround for submitting CSV files. Does anyone have any ideas on how to create a cube that could handle this? I tried, but when I tried to create a view that the users could see in Excel (with all the 'dimensions' across the top - no cross-tab), Architect didn't have enough memory to save the view, even when I created a dummy dimension to put down the side.

Also, the data in some of the columns is not restricted by only a certain few values - it could be anything!

I'm not 100% sure about what you're trying to do here (some example data would help) but I think that you may have things bass ackwards. It looks like you're trying to store all of the data from your relational system in TM1's cells.

From your description, it seems like the only data that you really need to store is Y or nothing, depending on the calculation methodology that you want to use in the other system. The cube structure would therefore be:
- One dimension for each of the fields that you're importing. Each element would be one of the values received from the relational system; and
- A "Values" dimension, which would have a single string element. The name of this element (and the dimension itself) doesn't matter, but lets say that the element name is "Flag".

When you read the data file into TM1 you would use the Metadata tab to add each field's value as an element of the corresponding dimension.

Your users could then have a view that looks something like:

Code: Select all

                                                            Flag
Field1Value1    Field2Value1     Field3Value1
Field1Value2    Field2Value2     Field3Value2       Y

which they could then use to punch Y into for whichever rows they want to.

When you export data from a cube the values of each dimension element will be included in the export file, so essentially you end up with the original csv, plus a flag column.

While this would work, I do wonder if it's the simplest approach to what you need to do.

[Misty]

Thanks Martin and Alan!

I couldn't agree more about TM1 not being suited to this kind of thing. It's actually very silly that they want to use this tool, but I am a consultant and I can't talk them out of it (politics, hey?). The even sillier thing is, no one in this organisation has ever used TM1! And to top it off, what would you say if I told you that all we're going to do with the data is export it out of TM1 and import it into an Oracle warehouse with a Cognos BI front-end. Go figure!

Alan, I think I'll give your approach a try, thanks.

Tara