SAP IDT Interview Questions

IDT is known as Business Objects design tool that extracts the data from different data sources using an OLAP and Relational connection to create Universes. There are different Universe parameters that can be passed at time of Universe creation.

It can be used in below reporting and dashboard tools −

• SAP Business Objects Web Intelligence (WebI)
• SAP Business Objects Dashboard Designer (Earlier known as Xcelsius)
• SAP Business Objects Crystal Reports
• SAP Business Objects Explorer

In UDT, Universe are created with file extension as .unv. In IDT, Universe file extension is changed to .unx file.

To open unv file in IDT, it can’t be directly opened but you can convert unv file to unx file to open in Information Design tool.

You can’t open an IDT .unx file in Universe Design tool nor you can convert it to unv file.

Universe Design tool is single source enabled however IDT is multi source enabled means you can extract the data from different data sources while creating a Universe.

Using UMT Upgrade Management provided with BI 4 for prior releases or in IDT you can do a direct conversion.

Business Layer −

This layers contain all the classes and objects, you can check dimensions and measures that are defined in a Universe.

When you publish the business layer in repository, this shows the completion of Universe creation.

You can check the summary of Business Layer to see number of attributes, dimensions, measures, classes, LOV’s etc.

Data Foundation Layer −

This layer is used to define data foundation - includes tables from data source, joins, and keys etc.

The idea is basically if you need to access data from a table and regular RDBMS then your connection should be a relational connection but if your source is an application and data is stored in cube (multidimensional like Info cubes, Information models) then you would use an OLAP connection.

Relational connection can only be created in IDT/UDT.

OLAP can be created in both IDT and CMC.

Another thing to note is relational connection (including HANA) will always produce a SQL statement to be fired from report while OLAP connection normally create a MDX statement.

No, business layer reads the structure of the OLAP source automatically.

File extension remains same when you run over in UMT. You need to perform a conversion in IDT to change file extension from unv to unx.

In IDT, it doesn’t convert OLAP universes created with prior releases. It is recommended to make a connection to OLAP source to take dimensional modeling advantages.

You can’t convert universes based on stored procedures using IDT.

Using IDT, you can convert linked universes however they are not supported in BI 4.

They are used to perform custom drill down on objects from same or different classes in Universe. To create a Custom hierarchy, go to Tools → Hierarchies.

.cns - secured Repository connection

.cnx - local unsecured connection. If you use this connection it will not allow you to publish anything to repository.

A Personal connection is defined as created by one user and cannot be used by other users.

A shared connection can be used by other users through a shared server. You can’t publish a Universe to repository using a shared connection.

A secured connection overcomes the above limitations and you can use this to export Universe to the central repository.

When you join a dimension table with two fact tables with one to many relationship, when you drag a dimension along with measure from both the fact tables, value of measures are inflated. This is called Chasm trap.

This can be solved using Context by creating two different contexts.

Other way is to go to Universe parameters and select the check box → Multiple SQL statements for each measure

In a Universe, when you have 3 tables in structure and first table is joined with one to many relationship with second table which is connected with one to many relationship with third table and when you drag a measure from 2^nd table and dimension from 3^rd table, value of measure is inflated, this condition is called Fan trap.

You can resolve this by creating an alias of the 2^nd table and defining contexts so that normal table is joined only with the first table, while the alias is joined with both the 1^st and the 3^rd table. We would take 2^nd table’s measure only from the normal table and other dimensions of the 2^nd table from the alias table.

Using aggregate awareness, you can use pre-aggregated data in tables in the database. It is used to improve query performance by processing less number of rows.

When you add an aggregate aware object in query, query generator retrieves the data from table with highest aggregation level.

Example −

Consider a Sales Fact table where sales is aggregated by per month. If your query asks for sales per month, query generator will retrieve the data from aggregated table.

How to set up Aggregate awareness?

To use aggregate awareness, first the aggregated table has to be loaded to database and then add the table to Data Foundation.

Define aggregate aware objects. These are objects in the business layer for which you want queries to use the aggregate tables when possible instead of performing aggregation using non-aggregate tables.

In the SQL expression for the object, define the SELECT statement to use the @Aggregate_Aware function −

@Aggregate_Aware(sum(aggr_table_1), …, sum(aggr_table_n))

Index awareness in a Universe determines which values in a filter conditions of the queries built from the universe, are replaced by their corresponding indexes or surrogate keys. Values in filter comes from dimensions table and you need a join with the fact table to get this value.

You can use query panel to create or preview queries on a Business Layer or on the top of Universe published in repository.

Query panel allows you to add objects in the query and to preview the query results.

A derived table is a virtual table in the data foundation that combines other tables using calculations and functions.

You can use derived tables for below purpose −

• To create a table with columns from other tables. The column definitions can include complex calculations and functions.

• To create a single table that combines two or more tables.

• To create a table that contains a selection of columns from different tables.

• To Insert Derived table, Select table header → right click → Insert → Derived table.

By using derived table function

@derived_table(Derived Table Name)

An Alias table is known as reference to a standard table in Data Foundation. The data in Alias table is completely same as the original table.

Alias tables are used to break loops in Join path in Data Foundation layer. An Alias table can also be used to rename a table.

In a context, there are three states defined for a Join −

Included joins − In a part of the schema that is ambiguous, the context solves the loop by defining a path with the included joins.

Excluded joins − In a part of the schema that is ambiguous, the excluded joins define the path that context will never take.

Neutral joins are in a part of the schema that is not ambiguous, and are always included in the query path of the context. Any join that is not explicitly included or excluded is neutral.

A Context can be defined manually or by clicking detect Context option.

Objects in the business layer are inserted automatically based on the cube. You can add below functions to enhance features in Business Layer −

• Using analytical dimensions, hierarchies, and attributes.
• Named sets
• Calculated members
• Insert measures
• Pre-defined filters (mandatory or optional) to limit data returned in queries
• Parameters with optional prompts
• Lists of values to be associated with a prompt

LOV’s allows you to select multiple values from list of available values.

Prompt allows you to select a value at run time.

You can define various access levels of an object −

• Private
• Public
• Controlled
• Restricted
• Confidential

When you define an object as public, all users can access the object. If an object is defined as restricted, users that are granted access level of restricted or higher can access. To define access level of objects −

Select the object in Business layer for which you want to define the access level. You can use CTRL key to select multiple objects. Right click on object → Change Access Level.

In a Universe there can be many objects which are dependent on each other and moving, deleting a resource can impact other resources that depends on that resource.

To check the dependency between different resources, you can select show local dependency.

To see dependent resources in Repository for a particular resource under Local project, right click and click on show Repository Dependency.

Enter the session details where resources are published and click on log in. It will show you list of published Universes in repository that are based on selected resource under Local Project.

Dashboards designer is SAP Business Objects data visualization tool that is used to create interactive dashboards from different data sources. Dashboard designer allows BI developers to create custom dashboards and analysis that meet the business requirement in an organization.

Dashboards can include different graphs, charts and gauge that are based on the data provided by data sources. Dashboards are used by Senior Management that offers up to date information to information to company CEO’s and VP’s.

• BOXI 3.1 compatible Dashboard is called Xcelsius 2008.
• BOXI 4.0 compatible Dashboard is called Dashboard 4.0.
• BOXI 4.1 compatible Dashboard is called Dashboard 4.1.

• Category
• List
• Tree

There are different ways −

You can use SLT method for data replication to HANA and then create a Universe on the top of HANA database. Universe can be consumed in Dashboard using query browser.

You can directly create a Universe on the top of transaction system.

Using transient provider, you can connect ECC to load data to BW and query browser can be used to connect to BW.

Using query browser, you can click on Add query → Universe as data source

Select the published Universe and to add objects, you can use Query panel.

When query is added, you can do mapping of objects to spreadsheet and later to Dashboard components.

You can publish Webi report block as a BI service and can be consumed directly in Dashboard.

Spreadsheets are preferred when the number of rows are less in Dashboard. It is by default set to 512 rows but you can edit this setting by going to Preferences.

It is also possible to move object browser, components browser, Query browser and properties panel from the default locations. To dock a component, you have to click on top of panel and drag it to docking icon. To move a component, you have to first remove auto hiding.

When you add objects using a query, you have options to select −

• Refresh before components are loaded
• Refresh every time duration

Select the component → right click Properties tab

On right side, you get options to define various properties of a component in dashboard model.

Alerts are used to set notifications for data values, if values are acceptable or require attention. To enable alerts, click the check box.

You can set Alert Thresholds, enable auto color, etc.

To clear the binding from chart, from list select clear binding as below −

You can set color for each element of a component in data model. Colors can be defined in Appearance tab under Properties pane.

There are wide range of colors available and you can also create your own custom colors.

To define color for each element, select the element → Go to Color Selector for each element. You can select below sections in color dialog box −

Theme Color −

To define color of current theme.

Standard Color −

These are group of basic colors.

Recent Color −

This shows recently used colors.

• Power point
• Word
• Attach in email
• PDF

In a Data model, charts are used to perform comparisons, value analysis, and trend summary of data from data source. There are different charts options available for multiple purposes −

Container component can be nested to create multilayer models. You can use canvas container within main canvas to hold one or more components. You can add, move, delete or change components in panel container.

You can use different types of containers −

• Panel Container
• Tab Set

Selector component allows users to pick different options at run time. Selector can be used to configure item’s row, value, position and label into embedded spreadsheet.

You can add selectors from Component browser in Dashboard designer.

• Check box
• Combo box
• Filter

Single value components are used for adding interactivity to data models. You can use them to add more attention to important parameters.

Single value components can be used in different styles −

• Horizontal Progress Bar
• Vertical Progress Bar
• Dual Slider
• Dual Slider 2
• Dial

These components allow you to connect your data model to web. Apart from this, you can also use data manager to configure web connectivity.

URL button −

When user clicks at run time, a button links to a relative or absolute URL.

Reporting Service Button −

This allows you to connect to reporting services server and select a report to use in data model.

Slide Show −

This allows you to create a slide show of URL based images and SWF files.

SWL loader −

This allows you to load SWF files from a URL and you don’t have to import the files. It is similar to slide show component but it provides better memory management.

Connection Refresh Button −

This allows user to refresh the connection manually when you link to it.

These components can be used with queries on Universe.

Query Refresh Button −

This allows user to refresh the data at runtime. You can include Universe or BEX query to allow users to request a query refresh at run time.

Query Prompt Selector −

This allows user to select values from BEX or Universe query prompts at run time. As per prompt type, user can select a single value to use as filter parameter or can choose selection objects.

To create new models, you can use template as starting point. Template also gives an idea how different component works and you can add them to canvas to build a new model.

It shows you list of template under that category.

Using themes, you can apply same layout, appearance and formatting to data models when you need all models with same look. Theme provides you with a color scheme but you can customize it as per requirement.

To apply a theme, go to Format → Theme

In SAP Dashboard Designer, below data connections are supported −

• Query as a Web service (QWAAS)
• Web Service Connection
• SAP NetWeaver BW connection
• XML Data
• Crystal Report Data Consumer
• Live cycle Data Services
• External Interface Connection
• Live Office Connections
• Web Dynpro application as Flash Island

By manually entering values in cells in Spreadsheet and then binding these cells to component label and values under Properties.