Question Re: IDT & UDT - SQL Difference in Technology Q&A

IDT & UDT - SQL Difference

Former Member — Mon, 26 Aug 2013 17:05:02 GMT

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?

Re: IDT & UDT - SQL Difference

Former Member — Mon, 26 Aug 2013 18:09:37 GMT

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.

Re: IDT & UDT - SQL Difference

Former Member — Mon, 26 Aug 2013 18:29:32 GMT

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.

Re: IDT & UDT - SQL Difference

Former Member — Mon, 26 Aug 2013 18:31:35 GMT

Great explanation

Re: IDT & UDT - SQL Difference

Former Member — Tue, 27 Aug 2013 12:41:32 GMT

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?

Re: IDT & UDT - SQL Difference

Former Member — Tue, 27 Aug 2013 13:56:53 GMT

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

Re: IDT & UDT - SQL Difference

Former Member — Thu, 12 Dec 2013 09:12:04 GMT

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.

Re: IDT & UDT - SQL Difference

Former Member — Thu, 12 Dec 2013 20:01:34 GMT

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

Re: IDT & UDT - SQL Difference

Former Member — Thu, 23 Apr 2015 07:03:30 GMT

This message was moderated.