When we work with RDBMS tables as data source for TM1, we use Sql query to retrieve the data. What data should we get? It depends on the requirement. But the RDBMS table views should define as “clean” data same as text file or other forms of data sources. To avoid data checking one by one during data reading in TM1, we can filter it through Sql statement.
Instead of using “select * from [table name]” we can add some conditions, to filter data and reduce data checking. Here are two basic conditions that we can use:
- To get non zero data
Of course we load only “non zero” or not blank data. So do not forget to filter data to have non zero data. Here is the condition to filter non zero data
a. For numeric data type:
…where [field name] <> 0”
Why we need to use “<>” or not equal sign?
Because for numeric data type there is number above zero and below zero, unless there is requirement to have data greater than 0 or below zero only, just change the sign into “>” (greater than) or “<” (less than)
b. For string data type
…where [field name] <> “”
…where length([field name])>0
We can combine it with function “trim” as space in RDBMS (maybe not all RDBMS) is not equal to blank, it will be counted as one character.
…where trim([fieldname]) <> “” - To get data from certain period
The data inside RDBMS table is getting bigger as data added. It is not like flat file that can be replace with the same name. The data inside the file is renew even though the file name still same. To avoid reading the whole data that will increase time processing, read data based on period you need. For example we need data from year 2014 for the whole year. We can retrieve it with condition:
…where [year field name] = [year period]
Let say the year field name is “year” and year period is “2015”
So the condition will be
…where year = “2015”
Still we can add some other filter conditions and combine all become one query to have “clean” data rather than putting “if condition” in data tab to check whether data meet the requirement or not.
You can use logical operator “OR” and “AND” to combine more than one conditions in one sql query.
The logic for “OR” and “AND” is same with “%” and “&” in TM1.
Here is the basic form of Sql query with condition filtering:
Select * from [table name] where [condition1] and [condition2]
Another tip, you can change the * by mentioning field name one by one. This tip is useful when not all field names will be loaded but we need to use certain field name in the condition to filter data base on that field name.
The sql query become like this:
Select [field1],[field2], [field3],… from [table name] where [condition1] [AND/OR] condition2,…
Still there are many SQL functions we can use in sql query to retrieve data from RDBMS tables. (Veronika Rotua Gultom)
Veronika Rotua Gultom 