0

Can someone tell me why we need to use T-SQL over SQL when creating reports from Data Warehouse?

SQL also has functions and Joins but I see all of the online tutorials use T-SQL when creating reports from DW.

Can it be done with SQL? If T-SQL is must, could you please explain why? In terms of what can T-SQL do that SQL cannot.

Some useful tutorial links for T-SQL and creating reports would be great too!

Thanks in advance~

user3601310
  • 795
  • 2
  • 11
  • 18
  • 1
    http://stackoverflow.com/questions/1043265/what-is-the-difference-between-sql-pl-sql-and-t-sql, http://stackoverflow.com/questions/1301038/what-are-the-differences-between-t-sql-sql-server-and-sql – Jithin Shaji Oct 31 '14 at 10:40
  • 3
    T-SQL is Microsoft's proprietary extension of the SQL language (Sybase, SQL Server). If you are using SQL Server or Sybase then you are using T-SQL regardless of whether your environment is OLAP or OLTP. The only thing you can control is if you strictly adhere to the SQL Standard, and do not use any of the extensions that are specific to T-SQL. I however, have never been a fan of not doing something the most efficient way for portability reasons. – GarethD Oct 31 '14 at 10:45
  • Thanks guys, I use SQL server 2012 and I'm learning DW. The above links have answered to what T-SQL can do that SQL cannot. Long story short, T-SQL has more functions than SQL like cast, convert, date(), looping, if/then, case etc. I'll just assume that creating reports need these functions that's why all the tutorials use T-SQL rather than SQL. Thanks for the answers! However, I don't understand why my question was down voted when other similar questions were considered as useful... – user3601310 Oct 31 '14 at 11:21

1 Answers1

1

Regardless the fact T-SQL has more functionality than plain SQL, in general data warehousing you have two main approaches:

  1. Put business logic closer to the data. This way you develop lots of T-SQL functions and apply many optimizations available there to improve performance of your ETL. Pros is greater performance of your ETL and reports. But cons are the cost of making changes to the code and migration cost. The usual case for growing DWH is migration to some of the MPP platforms. If you have lots of T-SQL code in MSSQL, you'll have to completely rewrite it, which will cost you pretty much money (sometimes even more than the cost of MPP solution + hardware for it)
  2. Put business logic to the external ETL solution like Informatica, DataStage, Pentaho, etc. This way in database you operate with pure SQL and all the complex logic (if needed) is the responsibility of your ETL solution. Pros are simplicity of making changes (just move the transformation boxes and change their properties using GUI) and simplicity of changing the platform. The only con is the performance, which is usually up to 2-3x slower than in case of in-database implementation.

This is why you can either find a tutorial on T-SQL, or tutorial on ETL/BI solution. SQL is very general tool (many ANSI standards for it) and it is the basic skill for any DWH specialist, also ANSI SQL is much simpler as it does not have any database-specific stuff

0x0FFF
  • 4,948
  • 3
  • 20
  • 26
  • Thanks OxOFFF, I'm an IT graduate and had an interview for an entry BI position but I didn't learn DW or T-SQL in my Database Engineering class so I've been doing self-study since then coz I really wanna be BI in the future. Your answer helped me to understand why T-SQL is used. Thanks again for spending time to write an answer! Have a great day~ – user3601310 Nov 05 '14 at 01:21