IDT & UDT - SQL Difference

[Former Member]

IN BI4.0 - When we build the universe in IDT there is different SQL option for Single source and Multi Source.

Single source - Database specific SQL Syntax

Multi Source - Standard SQL-92 and SAP Business Objects SQL functions

What is the difference between this two?

[Former Member · Accepted solution]

Hi Satheesh,

Say you have a BO Object called TODAY, you can define it anyway as below:

• Database specific SQL syntax - syntax which will work only for a specific database

           SYSDATE - will work only for oracle

           GETDATE() - will work only for mssql

• Standard SQL-92 - syntax which will work in any database

          CURRENT_TIMESTAMP will work in any database (irrespective of oracle or mssql)

• SAP BO SQL Functions - BO will generate syntax based on the database

          curDate() will work for any db

In multisource since there could be 2 different databases involved it generally use sql-92 or SAP BO functions sql, if apart from these any specific function(like lead, lag in oracle or @@servername in mssql) needs to be used, they have to be defined as database specific sql.

[Former Member · Accepted solution]

Great explanation

[Former Member · Accepted solution]

Hi Jawahar - Yes i know those details

Hi Preetha - Thanks for the details and clarification. I hope if you use multi source universe and create universe with custom SQL thare also this SQL-92 standard only will work. am i correct?

[Former Member · Accepted solution]

Hi Satheesh - If you use multisource universe it use sql-92 syntax. If you are trying to create a custom sql (derived tbl/ col) that uses any specific function which is not in SQL-92 they are defined as database specific (shown in screenshots below). 

[Former Member]

This message was moderated.

[Former Member]

IDT is the new tool introduced in BI 4. Concept is same as UDT, but presentation is different.

1. We have structure pane and class n object pane in UDT, in IDT, they have Data foundation layer (in stead of structure pane) and Business Layer (instead of Class n object pane). First of all, a project is created, then a relational or OLAP connection, then data foundation and then business layer. Then the universe is published as .unx

2. In IDT, the biggest advantage is that you have create universe based on multiple data sources. It could be different relational data sources (must be 64-bit), or different SAP BW source or them combined.

3. You can create universe based of SAP BW. The tables underneath the cube would be extracted, and would be used like any relational database.

4. They have introduced the concept of 'views' in Data Foundation Layer. If you have many tables to be used in the universe, it would be difficult to manage joins and structure. So you can create multiple windows. For ex., one view or window would be related to client and all the related tables. another window could be related to product and related tables, and another could be related to FACT and the main dimension tables. The master view would be the union of all these views.

5. In UDT, you can add a table any time and then can drag it to object pane to create objects automatically. In IDT, automatic classes and objects are created only once when you create the Business Layer for the first time. After that, if you add any table, you have to create objects based on it manually.

6. Identifying loops and creating contexts is not as clean in IDT as in UDT.

[Former Member]

Hi Sandeep - You can create classes/objects automatically in IDT at anytime. In BLX drag the table and drop it into the right location (say within a folder or below the universe name).

[Former Member]

Multi Source in IDT, it will allow to connect to 2 different DB (Oracle/SQL Server) or data sources and build the universe.

Previous versions of BI (3.1 or before)  you cannot build the universe with only one DB or data source. BI 4.0 will allow to connect to multiple Data sources.