A drill process that returns data in a CSV file?

[Kaveenga]

Hi,

Is it possible to create a drill process that returns data in a CSV file?

The TM1 reference says, when you select 'Other' as the datasource this means "Drills from the origination cube to any data source TurboIntegrator supports". However, selecting other only give you the 'None' option.

I need to create a drill process that returns results from multiple SQL datasources. The approach I have taken is to export each datasource output to a text file and appened the text file to a master file, and return the data in the master file to the user. However, I am not possible to do this. The workaround is to process the data in the master file into a cube and return the cubeview.

Any thoughts very much appreciated.

Kind regards

Kavee

[tomok]

Just how exactly would TM1 know what to return from the CSV file? The entire results from the file????? When you click on a cell in a cube view and choose to drill-thru, the drill-thru process is going to run it's code and return results to the user. Since a CSV file is nothing more than just a bunch of lines in an ASCII file, with the "fields" separated by commas, there is no way to selectively pick which record(s) from the CSV file that correspond to the clicked cell. If it's a SQL source then you can use the dimension intersections in your WHERE clause to return the appropriate records. With CSV there is no "query" functionality.

[Gregor Koch]

Hi Kavee

Not sure why the sources are limited when you select 'other'. A workaround would be to choose either available source to return the number of variables you need and try to reset the source in the prolog. Haven't tested it but may just work.

The best option is obviously to bring all your required SQL sources into one Data Warehouse. But I guess you considered this and somehow this is not possible.

Another option would be to set up an ODBC connection to the CSV/TXT file that you have created. I have done something similar ages ago where Excel files were the 'ODBC' source. It wasn't very nice and I haven't tried it with CSV/TXT files personally but it should work.

Cheers

tomok's comment just came in and I am not sure what the "query" functionality is with the CSV being an ODBC source.

[tomok]

tomok's comment just came in and I am not sure what the "query" functionality is with the CSV being an ODBC source.

I forgot that there is an ODBC driver for text files, so, if you have the file structured properly it does have some limited queryability. Unfortunately, that driver only exists on 32-bit Windows boxes. If you are on 64-bit then you are out of luck.

[Gregor Koch]

Just tried this and MS Query will allow to filter the data from the CSV (through ODBC).
But it appears that this is one of the very rare occasions where MS Query fails to give me a query that works in TI (TM1 9.4 FP 3 HF4 and 9.5.2 FP1 HF1).
Somehow the query just fails in TI.

Ahh, tomok beat me to it again.

[Gregor Koch]

Thinking about it again, resetting the source type in the prolog might create a hiccup with the RETURNSQLTABLEHANDLE or RETURNVIEWHANDLE.
Back to the DW approach.

[Kaveenga]

Thanks Tomok and Gregor for your replies.

Yes, I need TM1 to return the content of the entire CSV file.
No, I can't define the CSV file as an ODBC connection since each CSV file contains the UserId in the file name. I need to do this ensure that a user's drill results are not overwritten by another user executing the same drill process. Also, I'm on 64 bit.

Putting the CSV file into a cube and returning a cube view helps, but sometimes the drill result can contain several thousand rows of data which can cause Excel to hang when TM1 returns the view.

Looks like a DW is the option.

[yyi]

[msoffice driver x64, x32]
http://www.microsoft.com/en-us/download ... n&id=13255

TI ODBC to x64 DSN for csv/txt driver will give access to designated folder for SQL

eg: select top 10 * from User01.csv where ...

Hope that helps