SQL Select Count statement in TM1

juese72

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

itoto

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?

wlx123

Hi Tomok,

Yes I did.

rampagews3

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.

jxs0103

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

str8899

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

hnhening

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.

kangbei

@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.