企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 452|回复: 7

A drill process that returns data in a CSV file?

[复制链接]

83

主题

421

帖子

611

积分

中级会员

Rank: 3Rank: 3

积分
611
QQ
发表于 2014-3-19 08:20:24 | 显示全部楼层 |阅读模式
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
回复

使用道具 举报

86

主题

396

帖子

578

积分

中级会员

Rank: 3Rank: 3

积分
578
QQ
发表于 2014-3-19 09:29:35 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

82

主题

391

帖子

561

积分

中级会员

Rank: 3Rank: 3

积分
561
QQ
发表于 2014-3-19 09:32:28 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

82

主题

406

帖子

582

积分

中级会员

Rank: 3Rank: 3

积分
582
QQ
发表于 2014-3-19 10:04:06 | 显示全部楼层
Gregor Koch wrote: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.
回复 支持 反对

使用道具 举报

85

主题

414

帖子

603

积分

中级会员

Rank: 3Rank: 3

积分
603
QQ
发表于 2014-3-19 11:12:06 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

85

主题

408

帖子

581

积分

中级会员

Rank: 3Rank: 3

积分
581
QQ
发表于 2014-3-19 11:33:55 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

96

主题

400

帖子

610

积分

中级会员

Rank: 3Rank: 3

积分
610
QQ
发表于 2014-3-19 12:01:59 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

64

主题

404

帖子

540

积分

中级会员

Rank: 3Rank: 3

积分
540
QQ
发表于 2014-3-19 12:27:52 | 显示全部楼层
[msoffice driver x64, x32]


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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2018-4-27 14:51 , Processed in 0.205808 second(s), 13 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表