Oracle/SQL

[cognos4321]

Hi,

Iam new to cognos and have a very basic question.

What will be the difference faced if the database is Oracle instead of SQL??
Do we need to write queries in PL/SQL instead of SQL.

I want to know all the differences which a cognos developer, responsible to extract the data using data stage ETL tool, Model it in Framewok manager and further develop reports in report studio, will face if database is Oracle instead of SQL.

Please help

Thanks a lot in advance

[cognostechie]

I don't know how much data you have but with the new versions of SQL Server, it does not really matter if you use Oracle or SQL Server. When you use Cognos, the idea is not to write queries yourself but to use the tools and let them write the query for you. The SQL used by Oracle is PL/SQL whereas used by SQL Server is T-SQL. SQL Server is a lot easier to handle than Oracle.

It really depends on whether you company has licenses for Oracle or SQL Server.

[cognos4321]

Hi,

Thanks a lot for the reply.

Since Iam new to all this, can you please elaborate "When you use Cognos, the idea is not to write queries yourself but to use the tools and let them write the query for you." Don't we write queries ourself , how do we make the tools write query for us.

And "The SQL used by Oracle is PL/SQL whereas used by SQL Server is T-SQL. SQL Server is a lot easier to handle than Oracle." Did you mean that if its Oracle server then the queries will be written in PL/SQL language and if its SQL server then it should be written in SQL.

I know how to write SQL queries. So, do I need to learn PL/SQL if the database changes to Oracle??Is that the language in which the queries will be written if we use Oracle database.

Thanks so much for the reply.May be my questions sound weird to you but pls think of me as a fresher and elaborate your answers as much as possible.

[bdbits]

We do not use data stage, but ETL tools generally require some knowledge of the underlying database. The only other person who might need to know something about it is your Framework Manager modeler, who creates packages everyone else uses to author reports. Report authors drag-and-drop columns/fields onto the report, and use Cognos expressions for any calculations.

While it is technically possible for a report author to write an SQL query, it is bad form, a last resort, and should be avoided.

There are independent standards for SQL, and most databases adhere to the basic syntax in the standards. There are minor differences. On the other hand, stored procedures and DDL, and the ways they are managed within the database, have significant differences. PL/SQL is Oracle's implementation of SQL along with the extensions they have made. T-SQL is Microsoft's implementation of SQL with their extensions. I agree with cognostechie that Oracle is more difficult to work with, but others may disagree.

You really should look at the user guides built into the Cognos tools. In most cases you can press F1, or choose help from a menu. You will get a better feel for the product if you work through them.

[cognos4321]

Thank you bdbits for all the valuable information.

My question still remains the same.

I know how to write SQL queries. That is what we have been using since we had a SQL database.
Now if the database changes to Oracle then will all the queries be written in PL/SQL language instead of SQL??
Is that the language in which the queries will be written if we use Oracle database.

Thanks again

[bi4u2]

No the SQL does not change to PL/SQL. One thing you would need to be aware of is in your report studio reports, if you are using any vendor specific functions for SQL Server then you would get validation errors upon switching to Oracle. So the best practice is to use the Common functions rather than Vendor Specific functions.

[cognostechie]

Yes, if the DB is Oracle, then it would need PL/SQL to retrieve the data.

Since Iam new to all this, can you please elaborate "When you use Cognos, the idea is not to write queries yourself but to use the tools and let them write the query for you." Don't we write queries ourself , how do we make the tools write query for us.

The reason you use all these tools is because they let you map to the DB objects (tables/fields etc) via a graphic interface, whether it is an ETL tool or a BI modeling tool.  In the modeling tool, you have to define the relationship between tables (joins). When a report is made then Cognos looks at the joins and other relationships defined in the modeling tool and then generates the SQL which will go the database to retrieve the data. This is useful because you will not have to write different SQLs for every report because it would generate the SQL automatically depending on what you need in the report. This also ensures that the SQL generated would be proper once the testing has been done for the model whereas if you write SQL yourself for every report then every SQL has to be tested otherwise you increase the chances of making a mistake.

So you do not have to write the SQL regardless of whether your DB is Oracle or SQL Server. Cognos will automatically detect which DB you are using and write the SQL accordingly. When it detects that your DB is Oracle, it will write PL/SQL and when it detects SQL Server, it will write T-SQL. This is the reason when you change the DB from SQL Server to Oracle or visa versa, you don't have to worry about re-writing the SQLs. 

[nblank]

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural language extension for SQL and the Oracle relational database.

I don't think that Cognos is using PL/SQL for fetching data out of an Oracle database. It is just plain SQL. So if you use no special Functions (that only exist in Oracle) there is no problem.

[cognos4321]

Thank you cognostechie , you gave me such an elaborated and understandable answer.

Thanks nblank for replying but now I don't which statement to follow.

cognostechie: Yes, if the DB is Oracle, then it would need PL/SQL to retrieve the data.

nblank: I don't think that Cognos is using PL/SQL for fetching data out of an Oracle database. It is just plain SQL. So if you use no special Functions (that only exist in Oracle) there is no problem.

[nblank]

I found this link:

https://books.google.nl/books?id=XHrJAgAAQBAJ&pg=PA29&lpg=PA29&dq=what+sql+does+cognos+use+for+oracle&source=bl&ots=baiKkRUaSs&sig=vW8mlSlHUXhky0lFkOmhqZ30RkY&hl=nl&sa=X&ei=ILCIVODmL4PfOJ3FgcAD&ved=0CGYQ6AEwCA#v=onepage&q=what%20sql%20does%20cognos%20use%20for%20oracle&f=false

If you read this part, in my opinion, it explains that regular SQL is being used or Cognos SQL. Not PL/SQL.

[cognostechie]

nblank - You did not read and understand properly. It says that Cognos SQL is transformed into appropriate native SQL at query time.

cognos4321 - This is the way Cognos technology works:

Since Cognos works with every database and since the intent is that the SQL should be written by Cognos so it has to have an ability
to generate the SQL depending on what is acceptable to the Database. So it uses a generic SQL called Cognos SQL which acts as a placeholder. When the user runs the report, it detects the DB and transforms the SQL into PL/SQL if the DB is Oracle. Ofcourse. there are lot of common functions in all SQLs so if any specific PL/SQL function is not required then it would not insert that PL/SQL function into that SQL. If the query is really simple then it would even send an SQL to the DB which may not contain any PL/SQL or T-SQL function but if the query requires a function which is DB specific then it will use the function appropriate to the DB. Ex: Use the Cast function in the query and you will see that it may use 'to_char' function if the DB is Oracle and use 'convert' function if the DB is SQL Server.

What nblank is referring to is different than what we are talking here. In Framework manager which is the Cognos modeling tool, there are 3 options:

1> Use Cognos SQL - This is the default behavior and also the recommended practice which would behave the way I described above
2> Use Native SQL - This option is there in case you need it. What this means is that it would let you write your own SQL and then treat that as the SQL to be sent to the DB without transforming it. It would however do some validations and make appropriate adjustments if it has to be joined to another query before accepting that SQL.
3>  Pass thru SQL -   This means that it would not interpret it at all and just pass on that SQL to the DB so basically you would not be
using Cognos features but in extreme cases when there is no other option available then you can use this by writing your own
query. I have never needed to use this and I would suggest to stay away from this as this would just make your model very limited
and non scalable.

If you use Option 1 then you would be letting Cognos do its job properly and you wont have to worry about which DB you are using. Even if you use Oracle today and use SQL Server later it would automatically transform the SQL and you wont have to worry about changing anything.

[cognos4321]

Thanks a lot Cognostechie.
I really appreciate your time and effort for such an elaborated answer for a fresher like me.