企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 355|回复: 7

SQL Select Count statement in TM1

[复制链接]

73

主题

386

帖子

554

积分

高级会员

Rank: 4

积分
554
QQ
发表于 2014-3-18 16:14:30 | 显示全部楼层 |阅读模式
Hi there,

I have tiny issue that I cannot seem to get around solving it.

I am using TM1 9.5.1 to import data from an Oracle data warehouse into TM1 for reporting. As part of the validation process I need to produce a report confirming that the number of records in the data warehouse is the same number of records that where processed in TM1. I was planning to achieve this by writing Select Count statement and importing the results to the validation cube. The below query works fine in Oracle but when I write the same SQL language in the TurboIntegrator using the ODBCOutput, it gives me an output of "1" (which i'm not sure what it means).

Select count (*) from vj_abc_finance_cost;

Interestingly, the same query works fine when I execute it in the data source tab of the TurboIntegrator

The question is how do I display the results of the number of records in the Oracle data warehouse? I'd really appreciate your help on this

本帖子中包含更多资源

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

x
回复

使用道具 举报

85

主题

408

帖子

596

积分

高级会员

Rank: 4

积分
596
QQ
发表于 2014-3-18 17:57:02 | 显示全部楼层
slique-cya wrote:but when I write the same SQL language in the TurboIntegrator using the ODBCOutput, it gives me an output of "1" (which i'm not sure what it means).
Did you preface your ODBCOutput function with an ODBCOpen so that the conncection to the database is actually open before you attempt to run your query?
回复 支持 反对

使用道具 举报

67

主题

407

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-18 18:12:16 | 显示全部楼层
Hi Tomok,

Yes I did.
回复 支持 反对

使用道具 举报

73

主题

397

帖子

567

积分

高级会员

Rank: 4

积分
567
QQ
发表于 2014-3-18 18:15:22 | 显示全部楼层
I'm not sure how validating the number of records processed is very meaningful.  Seems to me that the proof is in the pudding, the only thing the business cares about is whether the totals tie up and that's what you should be testing.

I'm also not sure you are going about solving your problem in the right way.  Determining the number of records you process in a TI is pretty straightforward.

Initialize a counter on the Prolog tab:
nRecords = 0;

Then increment the counter as one of the first lines of code on the Data tab;
nRecords = nRecords + 1;

Then do something with the counter on the Epilog.  Either spit out a file or write it to a cube somewhere.
回复 支持 反对

使用道具 举报

71

主题

366

帖子

519

积分

高级会员

Rank: 4

积分
519
QQ
发表于 2014-3-18 18:57:06 | 显示全部楼层
slique-cya wrote:when I write the same SQL language in the TurboIntegrator using the ODBCOutput, it gives me an output of "1" (which i'm not sure what it means).

  Select count (*) from vj_abc_finance_cost;

First thing, I have no idea, how does it return this "1". AFAIK ODBCOutput is not a function that returns anything. It can be used to initiate Insert/Delete/Create actions in the database. Technically it can have the "select" command, but I do not see any chance to get the "select" result thanks to ODBCOutput (this result potentially can be something more than just one row/one column value).

Second, even if there is a place when you can see this "1" I would suspect it is just a flag that tells "there were no problems with executing given SQL", maybe it would return "0" when you query select on a table that does not exist... I don't know.

Third is (as lotsaram said) TM1 iterates through all the records, so yes, "it is in the pudding".

Fourth, if you still (for some reason) need to know how many rows are there (e.g. before you run the major process you need to make sure there are more than 100 records, so in this particular weird case lotsarams good tip would not be enough) you need to have another process run as a subprocess with your select command right there in the Data Source tab you mentioned. You can then process this number of rows both on Metadata and Data tabs and react accordingly.

HTH
回复 支持 反对

使用道具 举报

64

主题

354

帖子

512

积分

高级会员

Rank: 4

积分
512
QQ
发表于 2014-3-18 19:08:37 | 显示全部楼层
I understand where you're coming from lotsaram. The 'pudding' as you put it is taken care of and essentially we are looking to have some sort of audit trail in place to make the internal auditors happy. So in our checks we have qualitative as well as quantitative checks of all records extracted automatically. I already have a count in the data section which counts each record as it is processed. What i want from the SQL query is a count done in the data warehouse and populate that in my validation cube. This way, it will be easier for anyone looking for such information to just look in one place instead of having to go to the data warehouse and confirming the number of records in the table and validating that against the records processed in tm1 separately.

Anyways I have found a workaround by creating a separate process which specifically counts the number of records in the data warehouse (still need to stress test it though) but it would be interesting to interpret what that 1 means because at this stage it is senseless to me.

Thanks a million gents for your input

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

86

主题

402

帖子

589

积分

高级会员

Rank: 4

积分
589
QQ
发表于 2014-3-18 19:41:31 | 显示全部楼层
I sincerely hope that when you say:
Anyways I have found a workaround by creating a separate process which specifically counts the number of records in the data warehouse (still need to stress test it though) but it would be interesting to interpret what that 1 means because at this stage it is senseless to me.

that you are doing a COUNT(*) and are not actually counting in the metadata or data.
回复 支持 反对

使用道具 举报

85

主题

427

帖子

621

积分

高级会员

Rank: 4

积分
621
QQ
发表于 2014-3-18 20:07:56 | 显示全部楼层
@Kyro, your comment puzzles me. Why _wouldn't_ slique-cya want to count the number of records in their data source? And, in my experience (not admittedly with extreme volumes), count(*) is pretty quick since the RDBMS knows how large its tables are. Could be painful for certain views though.
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2020-9-20 06:24 , Processed in 0.193945 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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