SQL Select Count statement in TM1

[slique-cya]

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

[tomok]

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?

[slique-cya]

Hi Tomok,

Yes I did.

[lotsaram]

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.

[jstrygner]

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

[slique-cya]

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

[Kyro]

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.

[David Usherwood]

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

[rmackenzie]

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

Interestingly, a lot of TurboIntegrator methods have a return value, e.g.:

Code: Select all

nReturnValue=DimensionCreate('temp');
AsciiOutput('temp.txt',NumberToString(nReturnValue));
nReturnValue=DimensionDestroy('temp');
AsciiOutput('temp.txt',NumberToString(nReturnValue));

I don't believe that it is documented or explained why this is, but Iagree with jstrygner's comment that it is likely that it means 'successful outcome'. E.g. this will produce a minor error and you will see a 0 in the output.

Code: Select all

nReturnValue=DimensionElementDelete('}Clients','DEFINITELY_NOT_A_CLIENT_NAME');
AsciiOutput('temp.txt',NumberToString(nReturnValue));

I guess you can use this to do some clever error-trapping in your code.

I think that slique-cya's original idea, although flawed, is a good one though. I would like to be able to do this and get a value back:

Code: Select all

sReturnValue=ODBCOutput('SELECT value FROM status_table');

and have sReturnValue contain something like 'LOADED' or 'BATCH_FAILED'.

The work-around for this absent, yet useful,functionality is to have a process that you can call with a parameter for the query and then dynamically set the DataSourceQuery property of the process. If you know these queries will only ever return 1 row and 1 column then V1 will contain the result. In the Data tab you can write this to a cube/ attribute, or perhaps use a session variable. I think that is what the OP was looking for originally.

HTH

[Kyro]

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

Think you may have misunderstood me, I'm firmly recommending that slique-cya uses COUNT(*) as opposed to using a counter variable in the data/metadata. This is because of the huge inefficiency of doing a manual count.

[KWS]

Resurrecting an old thread.

I need to get a count out of a database and put that result into a TI variable.

I've got a monthly process that will zero out some intersections, pull new data from a database, and put the new data back into the zero'd out intersections.

All that works fine. I've got a data source query that populates variables and cycles through the returned rows on the data tab.

But before I do any of that, I want to make sure the new data has arrived in the database. Don't want to zero out my cubes until I know I have data to replace it with.

So, I want to run a one-time query on the prolog something like*:
nCount = ODBCOpen(DNS, 'select count(*) from SomeTable where Date >= THISMONTH');

* Let's not get hung up on the syntax and quoting and all that. All I'm looking for is to put the results of a query into the a variable.

And then I would do a test
IF(nCount = 0);
ProcessQuit;
ENDIF;

Is there a way to put the results if a query into a variable on the prolog?

As the OP mentioned, doing it this way just makes nCount = 1, which is the exit status of the ODBCOpen function. It has nothing to do with the return dataset. All it means is 'your query was run successfully. You're welcome'.

[tomok]

I need to get a count out of a database and put that result into a TI variable.

The only way to get data from SQL is to have it is a data source in a TI process, which means getting it in the Data tab. So, split your one process into two processes. The first process only runs the COUNT query and places it in a variable. Then in the Epilog you put wrap an ExecuteProceess statement inside an IF that only calls the second process when result of your Count is > 0. The second process does all the work with the data in the DB.

[KWS]

Yeah, kinda figured that. I built a check process that gets the count, and if it returns a zero count, it throws an error. I put a call to that process at the top of the main process. If the check process fails, the main process fails too, before we get into the nuts and bolts.

Thanks for the reply.

--ken