Defining Excel as a Data Source?

[fleaster]

Ok, basically what I'd like to do is have an Excel template that contains a table of data in a range; then have an Action Button the user can click that will extract this Excel table and use it as the source of a TI process...

Now I know it's possible to pass individual Parameter values to TI, but is it possible to pass an entire range from Excel, and have TI trawl through each row of data...?

cheers,

Matt

[Steve Rowe]

The normal way if doing this would be to export the range as a flat file and then launch the TI. You could use the TM1Tools option for this rather than an action button.

I don't of anyway to do exactly what you want, though I think that some of the products in 10 do something like this under the hood.

[fleaster]

Thanks for the advice... actually another thought - how about sending string values to a Data Entry Cube , referenced by ClientID and LineID dimensions (eg the template could have 1-100 lines)... the TI process could then pickup the ClientID as a parameter, then load their data from this cube.

...would this work?

Matt

[Steve Rowe]

That approach would work but you would have to watch out for the data volume when DBSing over your network.

You'd need to clear your data entry cube out after you processed the data since if you get less rows next run you'll have an issue.

[fleaster]

Yes good point re: clearing out the cube... otherwise I was thinking of flagging each entry after it has been processed (so TI will skip over them next time), and incrementing the LineID in the user's template... this is in the event we need to keep an audit trail of entries or something

[Harvey]

It sounds like it might not be appropriate given your use case, but you can use Excel spreadsheets as an ODBC datasource. If it interests you, there is some mention of it in this MSDN article, or you can google to find lots of other examples and tutorials.

[fleaster]

thanks, that is an interesting idea... but like you said, may not be a robust solution in our case