≡ Menu

Data Warehousing Interview Questions

Following are the list of data warehousing question which are asked in most of the interview. Do wait for more Data warehouse interview questions and answers.

1 WHAT DO YOU MEAN BY DATA WAREHOUSE OR DATA WAREHOUSING?
2 WHAT IS THE DIFFERENCE BETWEEN DATA WAREHOUSE AND TRANSACTIONAL SYSTEM?
3 WHAT IS DIMENSIONAL MODEL HOW IT IS DIFFERENT THAN ER MODEL?
4 WHAT IS THE DIFFERENCE BETWEEN OLAP AND OLTP?
5 WHAT IS ODS?
6 WHAT IS THE DIFFERENCE BETWEEN ODS AND DATA WAREHOUSE?
7 WHAT IS DATA MART?
8 WHAT IS THE DIFFERENCE BETWEEN DATA MART AND DATA WAREHOUSE?
9 WHAT ARE THE GOALS OF DATA WAREHOUSE?
10 WHY DO WE NEED DATA WAREHOUSE OR WHAT’S THE PURPOSE OF DATA WAREHOUSE?
11 WHAT ARE THE CHALLENGES AND ISSUES OF DATA WAREHOUSE?
12 WHAT ARE THE DIFFERENT ARCHITECTURAL COMPONENTS OF DATA WAREHOUSE?
13 WHAT IS DIFFERENCE BETWEEN BUSINESS INTELLIGENCE AND DATA WAREHOUSING?
14 WHAT IS METADATA?
15 WHAT IS FACT TABLE?
16 WHAT IS DIMENSION TABLE?
17 WHAT DO YOU MEAN BY GRANULARITY OF FACT OR GRAIN?
18 WHAT IS AGGREGATE TABLE?
19 EXPLAIN DIFFERENT TYPES OF DIMENSIONAL MODEL?
20 WHAT ARE THE DIFFERENT ARCHITECTURAL CHOICES AVAILABLE IN DIMENSIONAL MODELING?
21 CAN YOU EXPLAIN THE GENERAL DATA MODELING LIFECYCLE?
22 CAN YOU EXPLAIN THE DIMENSIONAL DATA MODELING LIFECYCLE?
23 WHEN SHOULD WE CREATE SEPARATE FACT TABLES?
24 WHAT IS DEGENERATE DIMENSION?
25 CAN’T WE STORE DEGENERATE DIMENSION IN DIMENSIONS TABLE INSTEAD OF FACT TABLE?
26 WHAT IS HIERARCHY AND HOW IT IS HANDLED IN DIMENSIONAL MODEL?
27 WHAT ARE SLOWLY CHANGING DIMENSIONS?
28 WHAT ARE FAST CHANGING DIMENSIONS?
29 WHAT IS SNOWFLAKING AND WHEN DO YOU SNOWFLAKE AND WHEN NOT?
30 WHAT ARE GARBAGE DIMENSIONS?
31 WHAT ARE ROLE PLAYING DIMENSIONS?
32 WHAT ARE MULTI-VALUED DIMENSIONS?
33 WHAT ARE HOT SWAPPABLE DIMENSIONS?
34 WHAT IS LOOKUP TABLE IN DIMENSION MODEL?
35 WHAT IS SURROGATE KEY AND WHAT IS ITS SIGNIFICANCE IN DATA WAREHOUSE?
36 WHAT DO YOU MEAN BY GRANULARITY OF FACT TABLE?
37 WHAT IS NON-ADDITIVE FACT?
38 WHAT IS FACT-LESS-FACT TABLE AND WHICH SCENARIO YOU USE IT?

I hope you find these data warehousing interview questions useful.

Business Objects Universe Designer Interview Questions

What is loop in Universe? Explain its problem and different methodologies to resolve it.

Loops occur when there are two different paths to accomplish one join. The following structure now includes PRODUCT_PROMOTION_FACTS in the Test Fashion universe. If users want to analyze articles versus time, there are now two join paths. Business Objects does not know which path to take, the one via SHOP_FACTS or the one via PRODUCT_PROMOTION_FACTS.

The circular appearance of these four joins is a loop, which can give undesired SQL results.

One way of spotting the problem table in the loop is the table which has at one end of the one-to-many relationship.

If loops are not resolved and report is run following error might come.

Error: Incompatible combination of objects

There are different methodologies to resolve loops depending on the type of loop.

Contexts

Context is simply a list of joins that defines a specific path for query.

It breaks loop by defining set of joins that define path through table in a loop.

It ensures that joins are not included from different path within the same sql.

If object from two different contexts are used in report. BO generates two different SQL.

Dis-advantage:
When you use a context, you expose the BO end user to the database structure. They are forced to decide which context they want to use to run their query. The role of the universe is to shield end users from the database structure, so they do not have to make such decisions.

To test the contexts:

Create a query which includes objects which are only in one context : BO should be able to get the correct result by determining the context.

Create a query which includes objects from both the contexts. BO should generate two queries and then unions it.

Create a query which includes object which are common two both the context. BO should prompt for contexts to be used.

Designer detects context by identifying table which has only many ends of joins attached. No joins following back from one to many are included.

Every Join except shortcut join must exist in at least one context.

Alias

Alias breaks the loop by using same table with different name in the query.

In above example we can create the alias for calendar_year_lookup table.

Original table would join to shop facts and alias will join to promotion fact which would break the loop.

However you would need to define the object based on there meaning.

e.g. to get the shopping month you can use original table whereas to get the promotion month you need to use alias table.

What is CHASM trap and how to resolve it

Fan traps and Chasm traps are problems in which are inherent in SQL that are caused by the order in which the elements of SELECT are processed.

In SQL a select statement processes SELECT, FROM and WHERE cause first and forms a result table in memory based on the tables specified in where clause and restrictions specified. However this does not cause a problem, but if aggregates are applied then it may cause a problem in particular circumstances.

These traps are difficult to identify unless you take a deeper look at the detailed data.

These traps return many row than expected.

Chasm trap is a common problem in relational database in which a join path returns more data than expected.

A chasm trap is a join path between three tables when two many-to-one join path converge on a single table and there is no context in place that separates the converging path.

You only get incorrect result in following situations.

There is many-to-one-to-many relationship between three table in universe.

The query includes objects based on the two “many” tables

There are multiple row returned for single dimension.

For example in above diagram there is no loop, but the flow around three table is many-to-one-many.

When a query that uses objects Y and Z is run, the inferred SQL includes tables B,C,A that have a many-to-one-many relationship. The chasm traps causes a query to return every possible combinations of one measure with every possible combination of another measure. The results in the values for each objects being multiplied by other. The effect is similar to a Cartesian product but known as CHASM trap.

CHASM trap is resolved by executing separate statement for Y and Z and combining the result.

Note: the chasm trap is not dependent on object type. Y and Z can be dimension

Detecting CHASM trap.

Unlike loops chasm traps are not detected automatically by designer. Need to detect manually.

Analyze the one to many relationship join paths in schema to detect CHASM trap graphically.

Analyze the proposed detected to separate the queries for such join paths.

Add additional dimension or detail objects to display more information in the report . If there is chasm trap aggregated value wil be doubled alerting you a chasm trap.

Now if you run query with client name and sale revenue or rental revenue. You would see correct result. However you want to see client name with sale and client revenue you would end up getting wrong result.

Suppose Sale revenue is 2000 and rental revenue is 4000 for client Kumar. You would see result as

Client Name    Sale revenue  Rental Revenue

Kumar              4000                 8000

This is because of chasm trap.

Internally it is bringing result as micrcocube

Client Name    Sale revenue  Rental Revenue

Kumar              2000                 4000

Kumar              2000                 4000

And while projecting this data in report BO aggregates it to client name and shows wrong result.

Resolving CHASM trap.

Modify SQL parameters of the universe and click generate separate queries for measures. However this works with measures and might result in inefficient queries and does not works with dimensions.

Create a contexts for each fact. This solution works well and recommended.

When you create context and two entities from separte context are used in SQL. BO generates separate queries regardless of measure/dimension. Which solves this trap problem

When any new universe changes are deployed how does the end user get the view of the new classes/objects added(apart from specs doc)?

when universe get changed, we do export the changed universe to enterprise server. user gets the view of changed classes objects what ever we done at universe and already existed objects which are not changed are viewed as same like before.

I have 2 universes. that is u1 and u2. From u1,i created one report that is r1. Now i want to give the connection r1 to u2 and at the same time delete the connection from u1 to r1 ? How is it possible explain?

can change the connection for the report r1.For webI reports in query panel on left side we have query properties there we can change the connection of the universe to u2.then it will map to the u2.

What is meant by ZABO and FC(full-client)?

This module of the application is hybrid of the thin client and the Full Client Modules of Business Objects Reporting Module. ZABO stands for Zero Administration BusinessObjects. This application uses the hardware resources of the client machine for application level processing and communicates through the protocol defined for the browser to use the server resources for processing External requests (like running a database Query, creating a data provider to access data.)

ZABO means Zero Administration Business Objects and this is 3-tier level architecture.

No need to install any BO software.

Full Client means 2 -tier level architecture we can directly connect to repository only we defines key file and this Fullclient we need BO tools our local system also.

what happens if cardinalities are not resolved?

If Cardinalities are not specified between the tables i.e the relationship between the tables ( 1-1,1-MANY,MANY-MANY)

We get more results at report level more than the actual result,like a Cartesian product.

We can’t detect the Loops & Traps (Chasam & Fan) Which are mainly detected by the

CARDINALITIES option

What is aggregate navigation.

Aggregate Navigation is used to specify the incompatible  objects.

Whenever we use Aggregate Aware we need to set up  incompatilibities to work it properly (so BusinessObjects  knows what SQL to generate)

For example, There are 2 aggregate tables fact table by

week and by day.If you set up a measure as aggregate aware  from both tables – total quantity for example, the select  might be something like: –

@Aggregate_Aware(sum(week_agg.qty),sum(day_agg.qty))

The week table listed first as this would be the  preference. But as BusinessObjects needs a reason to not go  to the week_agg table, this is done using  incompatibilities. For example the Week Number dimension  object would be compatible with the week_agg table as it  will link to it and the data is held at an appropriate  level to report against it. However the Date dimension  object would be set as incompatible with the week_agg  table, such that when it is selected alongside the Quantity  object, BusinessObjects knows to jump to the next option in  its select definition.

What is Index Awareness in Universe.

Index awareness is the ability to take advantage of the indexes on key column to speed data retrieval.

The objects that you create in the universe are based on database columns that are meaningful to an end user. For example, a customer objects retrives the fields that contains name. In this situation the customer table typically has primary key (e.g. in integer) that is not meaningful to the end user, but which is very useful for DB performance. When you set up index awareness in designer, you tell designer which database columns are primary and foreign keys. This can have dramatic effect on query performance in the following ways.

Designer Can take advantage of the indexes on key columns to speed up data retrieval

Designer can generate SQL that filters in the most efficient way. This is particularly more important in a start schema . If you build a query that involves filtering on a value from dimension table. Designer can apply the filter directly on the fact table using table foreign  key. This eliminates unnecessary joins to dimensions tables.

Designer does not ignore duplicates with index awareness. If two customers have same name. Designer retrieves only one unless it is aware that each customer has a separate primary key.

To setup index awareness open the properties of objects, open “Keys” tab. Click insert and add respective PK and FK.

What are @functions

@Aggregate_Aware (2) @Prompt (3) @Script (4) @Select (5) @Variable (6) @Where

@Prompt: message prompts the end user to enter a specific value.

Syntax: @Prompt (‘message’, [‘type’], [lov], [MONO|MULTI], [FREE|CONSTRAINED])

@Script: This function recovers the results of Visual Basic for Applications macro (VBA macro).
Syntax: @Script (‘var_name’, ‘vartype’, ‘script_name’)

@Select: This function enables you to re-use the Select statement of an existing object.
Syntax: @Select (Classname\Objectname)

@Variable: The @Variable is used to reference the value assigned to a name or variable.
Syntax: @Variable(‘myname’)

@Where: This function lets you re-use the Where clause of an existing object.
Syntax: @Where (Classname\Objectname)

What is core Universe

The core universe is a universe to which other universes are linked. It contains

components that are common to the other universes linking to it. These

universes are called derived universes.The core universe represents a

re-usable library of components.

A core universe can be a kernel or master universe depending on the way the core universe components are used in the derived universes.

What is derived universe.

A derived universe is a universe that contains a link to a core universe. The

link allows the derived universe to share common components of the core

universe:

• If the linked core universe is a kernal universe, then components can be

added to the derived universe.

• If the linked core universe is a master universe, then the derived universe

contains all the core universe components. Classes and objects are not added to the derived universe. They can be hidden in the derived universe depending on the user needs of the target audience.

What are linked universes? Explain with advantages and disadvantages.

Linked universes are universes that share common components such as parameters, classes, objects, or joins. Among linked universes, one universe is said to be the kernel or master while the others are the derived universes.A kernel or master universe represents a re-useable library of components.

Derived universes may contain some or all of the components of the kernel or master universe, in addition to any components that have been added to it.

You have the following advantages when linking universes:

• Reduce development and maintenance time. When you modify a component in the core universe, Designer propagates the change to the same component in all the derived universes.

• You can centralize often used components in a core universe, and then include them in all new universes. You do not have to re-create common components each time you create a new universe.

• Facilitate specialization. Development can be split between database administrators who set up a basic core universe, and the more specialized designers who create more functional universes based on their specific field.

Requirements for linking universes

You can link the active universe to a core universe, only if the following requirements are met:

The core universe and derived universe use the same data account, or database, and the same RDBMS. Using the same connection for both the core and the derived universe makes managing the universes easier, but this can be changed at any time.

• The core and derived universes must be in the same repository.

• The core universe was exported and re-imported at least once. The derived

universe does not need to have been exported before creating a link.

• Exported derived universes are located in the same universe domain as

the core universe.

• You are authorized to link the given universe.

Restrictions when linking universes

You need to be aware of the following restrictions when linking universes:

• You cannot link to a universe that uses stored procedures.

• You can use only one level of linking. You cannot create derived universes from a universe which is itself derived.

• All classes and objects are unique in both the core universe and the derived universes. If not conflicts will occur.

• The two universe structures must allow joins to be created between a table in one universe to a table in the other universe. If not, then Cartesian products can result when a query is run with objects from both structures.

• Only the table schema, classes and objects of the core universe are available in the derived universe. Contexts must be re-detected in the derived universe.

• Lists of values associated with a core universe are not saved when you export a derived universe with the core universe structures.

Creating a link between two universes

You can link an active universe to another universe. When you do so, the active universe becomes the derived universe, and the linked universe becomes the core universe. Components from the core universe are inherited by the derived universe.

To link a universe to a core universe, the core universe must have been

exported to the repository.

Note: When you link universes, you can relocate the core universe within the same repository with out breaking the link. This allows you to export the core universe to a different repository folder, while keeping the links with derived universes valid.

What is Object Qualification

For the purposes of multidimensional analysis, objects are qualified as one of three types: dimension, detail, or measure.

How to create filter in Universe and what is advantages and disadvantages.

A condition object is a predefined Where clause that can be inserted into the Select statement inferred by objects in the Query pane.

Condition objects are stored in the Conditions view of the Universe pane. You access the conditions view by clicking the Conditions radio button at the right bottom of the universe pane

Using condition objects has the following advantages:

• Useful for complex or frequently used conditions.

• Gives users the choice of applying the condition.

• No need for multiple objects.  Building universes Defining objects

• Condition objects do not change the view of the classes and objects in the Universe pane.

Note: You may need to direct users to use the condition objects view of the Universe pane. The only disadvantages for using condition objects is that you may want to force a condition on users to restrict their access to part of the data set. In this case you need to define a Where clause in the object definition.

Why do we need to create derived table in Universe.

Sometimes it not possible to create dimension/measure directly in universe in that case we use derived tables. E.g. First time users, union queries etc.

Explain security level in BO Universe

Defines the security access level of the object.You can select a security level which restricts use of the object to users with the appropriate security level. You can assign the following security access levels:

• Public

• Controlled

• Restricted

• Confidential

• Private

If you assign Public then all users can see and use the object. If you assign Restricted, hen only users with the user profile of Restricted or higher can see and use the object.

How to implement row level security in Universe.

You can define a WHERE clause that restricts access to row and limits the result set returned by a query.

To create restrictions Select Tools > Manage Security > Manage Access Restrictions and click rows

How do you determine when to use alias and when to use context.

There is no strict rule to follow for resolving loops. However, whenever possible you should use an alias instead of a context. When you use a context, you expose the BUSINESSOBJECTS end user to the database structure. They are forced to decide which context they want to use to run their query. The role of the universe is to shield end users from the database structure, so they do not have to make such decisions.

Contexts can be confusing for end users

A context can be confusing for end users when they are forced to make a decision about the meaning of an object. For example an object called Country can have several meanings; the customer’s country of residence, the shipment destination, or the product’s country of manufacture. When you run a query in BUSINESSOBJECTS or WEBINTELLIGENCE, you are also asked to indicate the correct database path to follow.

Deciding to use an alias or context

You can use the following rules to help you decide whether an alias or context is appropriate for resolving loops: When you create aliases and you end up with object names that sound very different (Customer’s Country of Residence, Shipment Destination, and Products Country of Manufacture), aliases are probably the right solution.

If you end up with object names that sound very similar (such as Ordered Products and Loaned Products as well as Ordered Products’ Country of Manufacture and Loaned Products’ Country of Manufacture), you should consider using contexts.

What are different ways to link universes.

You can use any the following approaches when linking universes:

• Kernel approach

• Master approach

• Component approach

You can use any of the three approaches individually, or, combine one

or more together.

Kernel approach

With the kernel approach, one universe contains the core components. These are the components common in all universes. The derived universes that you create from this kernel universe contain these core components as well as their own specific components.

In the example below, the universes Human Resources and Sales are derived from a kernel universe. They contain core components of the kernel universe as well as their own specific components.

Any changes you make to the kernel universe are automatically reflected in the core components of all the derived universes.

Master approach

The master approach is another way of organizing the common components of linked universes.

The master universe holds all possible components. In the universes derived from the master, certain components are hidden depending on their relevance to the target users of the derived universe.

The components visible in the derived universes are always a subset of the master universe. There are no new components added specific to the derived universe. The example below shows the universes Human Resources and Sales are derived from a master universe. They contain components from the master universe, some of which may be hidden. Any changes you make to the master universe are automatically reflected in the core components of all the derived universes.

Component approach

The component approach involves merging two or more universes into one universe. The Sales universe below was created by merging two universes: Part 1 and Part 2.

How do you distribute Universe?

Designer allows you to distribute universes by importing and exporting universes to the Central Management System (CMS) repository.

What is the list mode?

List Mode provides list of all the tables, joins, and contexts.àList mode command View

What is Parse checking?

Parse checking means how DESIGNER is to determine the validity of an object, join, or condition.
• Quick parsing checks only the syntax of components.
• Thorough parsing checks both the syntax and semantics of components.

If there are changes in the database on which you have already created a universe, how do you include those additional changes into your universe?

By refreshing the structure I get the updated database structure.

What is the difference between linking and including the universes?

• Linking a universe is a process, which includes the required objects/classes of the master on to the derived universe. But any change made to the master universe is reflected onto the derived universe.
• Including a universe creates all the required objects from the master universe on to the derived universe; any change made to the master universe does not impact the derived universe.

What is a list of values?

A list of values contains the data values associated with an object. These data values can originate from a corporate database, or a flat file such as a text file or Excel file. In Designer you create a list of values by running a query from the Query Panel. You can then view, edit, purge, refresh, and even export this file. A list of values is stored as an .lov file in a subfolder of the UserDocs folder.

What are the requirements for linking universes

You can link the active universe to a kernel universe, only if the following requirements are met:

The kernel universe and active universe were created from the same data account and the same RDBMS.

The kernel universe was exported and re-imported at least once.

Exported derived universes are located in the same universe domain as the kernel universe.

All classes and objects are unique in both the kernel universe and the derived universes. (Otherwise, collisions may occur.)You are authorized to link the given universe.

Note:

A derived universe does not recover the lists of values containing personal data from the kernel universe. However, if you need certain lists of values, you can use the following workaround: in the derived universe, create new objects defined in the same way as those in the kernel, then hide them. You can then assign them to these objects lists of values, which you can then modify and export.

How to create hierarchies in BO?

A hierarchy, which the designer sets up when creating the universe, consists of dimension objects ranked from “less detailed” to “more detailed”. The objects that belong to hierarchies are the ones you can use to define scope of analysis.

Creating hierarchies

You can create your own custom hierarchies from any dimensions available in

the report. The dimensions you include in a hierarchy can be local variables,

derived variables, or dimensions returned by data providers.

Note: You can also use a date-type user object as the basis for a time hierarchy.

For information on user objects see “Creating User Objects” on page 78.

To create a custom hierarchy

1. Click the Hierarchies command on the Analysis menu.

The Hierarchy Editor opens.

2. In the Hierarchy Editor, click New.

3. Type the name of the new hierarchy, then click outside the name box.

4. In the Available Dimensions box, click the first dimension for the new

hierarchy, then click Add.

The dimension you clicked appears in the new hierarchy’s folder in the

Available Hierarchies box.

Add the other dimensions you want to include and then click OK.

What are the disadvantages of Alias?

Aliases will create complex in structure of the Universe

What are linked Universes

Linked universes are universes that share common components such as parameters, classes, objects, or joins. Among linked universes, one universe is said to be the kernel or master universe while the others are the derived universes.

A kernel or master universe represents a re-usable library of components. Derived universes may contain some or all the components of the kernel or master universe, in addition to any components that have been added to it.

Approaches to linking universes

You can use one of three approaches when linking universes:

• The kernel approach

• The master approach

• The component approach

The Benefits of Linked Universes

You have the following advantages when linking universes:

• Reduce development and maintenance time. When you modify a component in the core universe, Designer propagates the change to the same component in all the derived universes.

• You can centralize often used components in a core universe, and then include them in all new universes. You do not have to re-create common components each time you create a new universe.

• Facilitate specialization. Development can be split between database administrators who set up a basic core universe, and the more specialized designers who create more functional universes based on their specific field.

Requirements for linking universes

You can link the active universe to a kernel universe, only if the following requirements are met:

The kernel universe and active universe were created from the same data account and the same RDBMS.

The kernel universe was exported and re-imported at least once.

Exported derived universes are located in the same universe domain as the kernel universe.

All classes and objects are unique in both the kernel universe and the derived universes. (Otherwise, collisions may occur.)

You are authorized to link the given universe.

Note: A derived universe does not recover the lists of values containing personal data from the kernel universe. However, if you need certain lists of values, you can use the following workaround: in the derived universe, create new objects defined in the same way as those in the kernel, then hide them. You can then assign them to these objects lists of values, which you can then modify and export.

Explain Universe Design Methodology.

The universe design methodology described in this manual consists of one planning stage, and three implementation phases:

Analysis of business problem and planning the universe solution

Designing a schema

Building the universe

Distributing the universe to users

Each implementation phase is based on an assumption that you have completed an initial planning phase. The planning phase can be done without using Designer, and is the decisive phase for the success or failure of your universe. A poorly planned universe that is not based on a study of user reporting needs will be difficult to design, implement, maintain, and will not be useful to your target users. Each of these phases is described as follows:

Plan the universe before you start using Designer

Before starting the first phase, you should spend up to eighty percent of the time allotted for the universe creation project, planning the universe. You should note the following points:

• You must analyze the data analysis and reporting needs of the target audience for the universe. The structures that you use to create the schema should be based on a clearly defined user need to access the data contained in those tables and columns.

• You should have a clear idea of the objects that you need to create before you start using Designer. Do not create objects by looking at the columns available in the database, but identify columns that match an object that you have already identified from your user needs analysis.

Designing a schema

You create a schema for the underlying database structure of your universe. This schema includes the tables and columns of the target database and the joins by which they are linked. You may need to resolve join problems such as loops, chasm traps, and fan traps, which may occur in the structure by using aliases or contexts. You test the integrity of the overall structure.

Building the universe

You create the objects that infer Select statements based on the components of your schema. You organize these objects into classes. These are objects that you have identified from an analysis of user reporting needs. You can create many types of objects to enhance user reporting capabilities, multidimensional analysis, and optimize query performance. You test the integrity of your universe structure. You should also perform tests by running reports in Web Intelligence.

Distributing the universe

You can distribute your universes to users for testing, and eventually for production, by exporting them to the Crystal Management System (CMS) repository.

Explain Universe Development Lifecycle.

Universe development is a cyclic process which includes planning, designing, building, distribution, and maintenance phases. You use Designer to design and build a universe, however, the usability of any universe is directly related to how successfully the other phases in the development cycle interact with

each other.

This section presents an overview of a universe design methodology that you can use to plan and implement a universe development project. The table below outlines the major phases in a typical universe development cycle:

Sub scribe to blog updates by Email and receive Business Objects Interview Questions with Detailed Answers FREE!!

Prepare %3

One can always prepare best by reading books here are few on BO

 

Sub scribe to blog updates by Email and receive Business Objects Interview Questions with Detailed Answers FREE!!

Business Objects Interview Questions – Universe Designer

1.What is ‘business objects’?

BUSINESSOBJECTS is an integrated query, reporting and analysis solution for business professionals that allow them to access the data in their corporate databases directly from their desktop and present and analyze this information in a BUSINESSOBJECTS document.
It is an OLAP tool that high-level management can use as a part of a Decision Support Systems (DSS).

BUSINESSOBJECTS makes it easy to access the data, because you work with it in business terms that are familiar to you, not technical database terms like SQL.

2.What are the various Business Objects products?

User Module, Designer, Supervisor, Auditor, Set Analyzer, Info View (Web Intelligence), Business Objects Software Development Kit (SDK), Broadcast Agent etc.

3.What are the advantages of Business Objects over other DSS?

User Friendly.
Familiar Business Terms.
Graphical Interface
Drag and Drop.
Powerful reports in less time.
Enterprise wide Deployment of documents using WebI
Customized dashboards using Application foundation and Business Objects SDK.

4.How many modes are there in BO & Designer

There are 2 types: Enterprise Mode, Workgroup

5.What are Enterprise and Workgroup modes?

Designer lets you save universes in either enterprise or workgroup mode. Enterprise mode means that you are working in an environment with a repository. Workgroup mode means that you are working without a repository. The mode in which you save your universe determines whether other designers are able to access them. By default, a universe is saved in the mode in which you are already working. For example, if you launched a session in enterprise mode, any universe you save is automatically in that mode.

6.How do you save a Business Objects document which can be accessed by all users in workgroup mode?

If we want to make a universe accessible to another designer working without a repository, then click the “Save For All Users” check box in the Save as universe dialog box.

7.What is online and offline mode?

If you want a universe to be accessible in offline mode, you must firstly ensure that the
universe has been opened at least once in online mode, and that it has been saved with the Save for All Users check box selected in the Save Universe As box. To make Offline mode available:

Mode Description
Online Default mode of operation for Designer when you are working in an
environment with a repository.

Offline Mode of operation for Designer when you are not connected to a repository.

• Only available if you have previously connected in online mode.
• In offline mode you can open universes that are stored on your local computer only if those universes have been opened previously in online mode.
• You can access databases where the connection and security information are stored on your local machine (personnel and shared connections.)
• You can use offline mode when you do not have access to the repository, for example when working with a laptop off site, or when the network is not available.

8.What is a universe?

Universe provides a semantic layer between you and the database. It consists of classes and objects named in business terms. It is basically a mapping of table and the columns in the database to classes and objects respectively in the query panel.
Alternatively, It is logical mapping of data in business terms.

In the BusinessObjects User module, universes enable end users to build queries from which they can generate and perform analysis. Universes isolate end users from the complexities of the database structure as well as the intricacies of SQL syntax.

9.Can a Universe connect to multiple databases?

NO

10.How do you define universe parameters?

The first step in creating a universe is to specify its parameters. These parameters include the definition of a universe, which is comprised of: the universe name, a description of the universe, a connection to an RDBMS. You enter universe parameters from the Universe Parameters dialog box. This dialog box also lets you set up database options, external strategies, graphic options, and print settings.

11.What is a database connection?

A connection is a set of parameters that provides access to an RDBMS. These parameters include system information such as the data account, user identification, and the path to the database. Designer provides three types of connections: secured, shared, and personal.

12.What are the types of connections we use when connecting to the database?

There are three types of connections namely: – Secured, Shared and Personal.

A secured connection is used to centralize and control access to sensitive or critical data. It is the safest type of connection for protecting access to data.

A shared connection is used to access common resources such as universes or documents. Several users can thus use it.

A personal connection is specific to one user and can be used only from the computer on which it was created.

13.What are different types of joins available in Universe design? Explain each

Equi or Inner or Natural or Simple join: is based on the equality between the values in the column of one table and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two tables.

Self-Join: join a table to itself i.e create a self-join to find rows  in a table that have values in common with other rows in the same table.

Theta or Non-Equi join: links tables based on a relationship other than equality between two columns. A join is a relational operation that causes two or more tables with a common domain to be combined into a single table. The purpose of joins is to restrict the result set of a query run against multiple tables.

Outer join: links two tables, one of which has rows that do not match those in the common column of the other table.

Left Outer Join: All records from first table with matching rows from second.

Right Outer Join: All records from second-named table with matching rows from left.

Full outer join: All rows in all joined tables are included, whether they are matched or not.

Shortcut join: can be used in schemas containing redundant join paths leading to the same result, regardless of direction. Improves SQL performance.

14.How do you design a universe?

The design method consists of two major phases.
During the first phase, you create the underlying database structure of your universe. This structure includes the tables and columns of a database and the joins by which they are linked. You may need to resolve loops which occur in the joins using aliases or contexts. You can conclude this phase by testing the integrity of the overall structure.
During the second phase, you can proceed to enhance the components of your universe. You can also prepare certain objects for multidimensional analysis. As with the first phase, you should test the integrity of your universe structure. Finally, you can distribute your universes to users by exporting them to the repository or via your file system.

15.What are the components of the Designer interface?

In Designer, you create a universe using three areas: the Universe pane, the Structure pane, the Table Browser.  The Universe pane displays the components of the universe from the point of view of BusinessObjects; that is the classes, objects, and conditions. The Structure pane reflects the underlying database structure of the universe including the tables, columns, and joins. The Table Browser is the component that lets you create the classes and objects of the universe from the tables and columns of a database.

16.What are classes/objects?

An object maps to data or a derivation of data in the database. For the purposes of multidimensional analysis, an object can be qualified as one of three types: a dimension, detail, or measure.

A class is a collection of objects based on business categories.

A universe is a set of classes and objects intended for a specific application or group of users.

17.What are classes?

A class is a logical grouping of objects within a universe. In general, the name of a class reflects a business concept that conveys the category or type of objects. For example, in a universe pertaining to human resources, one class might be Employees. A class can be further divided into subclasses. In the human resources universe, a subclass of the Employees class could be Personal Information. As designer, you are free to define hierarchies of classes and subclasses in a model that best reflects the business concepts of your organization.

18.What are objects?

An object is the most refined component in a universe. It maps to data or a derivation of data in the database. Using objects, end users can build queries to generate reports. The name of an object suggests a concept drawn from the terminology of a business or discipline. For a human resources manager, objects might be Employee Name, Address, Salary, or Bonus, while for a financial analyst, objects might be Profit Margin, Return on Investment, etc. For the purposes of multidimensional analysis, objects are qualified as one of three types: dimension, detail, or measure.

19.What are Dimension-Measure-Detail Objects?

When creating universes, universe designers define and qualify objects. The qualification of an object reveals how it can be used in analysis in reports. An object can be qualified as a dimension, a detail, or a measure.

A dimension object is the object being tracked; in other words, it can be considered the focus of the analysis. A dimension can be an object such as Service, Price, or Customer.
Dimension objects retrieve the data that will provide the basis for analysis in a report. Dimension objects typically retrieve character-type data (customer names, resort names, etc.), or dates (years, quarters, reservation dates, etc.)

A detail object provides descriptive data about a dimension object (or attribute of a dimension). It is always associated with a specific dimension object. However, a detail object cannot be used in drill down analysis. E.g. Address & phone number can be attributes about the customer dimension.

A measure object is derived from one of the following aggregate functions:
Count, Sum, Minimum, Maximum or average or is a numeric data item on which you can apply, at least locally, one of those functions. This type of object provides statistical information. Examples of measure objects include the following:
Revenue, unit price etc

20.What us hierarchy

Groups of related dimension objects are referred to as dimension hierarchies. An example of a dimension hierarchy is Geography, which can consist of City, Region, and Country.(Hierarchy is an ordered series of related dimensions, which can be used, in multidimensional analysis) Good examples of hierarchy are geography and time

21.How to create hierarchies in BO?

A hierarchy, which the designer sets up when creating the universe, consists of dimension objects ranked from “less detailed” to “more detailed”. The objects that belong to hierarchies are the ones you can use to define scope of analysis.

22.Can a Universe have more than 1 fact Table?

Yes. Typically a universe can have more than 1 fact table and numerous aggregated tables.

23.What are Contexts?

A context is a rule that determines which of two paths can be chosen when more than one path is possible in the database from one table to another. It helps in resolving the loops created by various joins in the universe tables.

With certain database structures, you may need to use contexts rather than aliases to resolve loops. A situation where this commonly occurs is a transactional database with multiple fact tables (“multiple stars”) that share lookup tables.

24.What are aggregated tables and how would you use it in BO Universe

Aggregate table
Aggregate tables are table which contains summarized data at different level depending on the need of reports. Imagine a fact table which contains granular data up to minute’s transaction. Now if you are developing a reports which has hour, day, week, month, quarter, year level summaries. Queries to get these summary values will scan millions of records which would in turn result in poor performance of reports. One can address this issue by creating aggregate summary table.
Possible problems of using Aggregate table
Aggregate table are good to achieve performance for high level summery queries. However if there are multiple aggregate tables which contains summary values then using aggregate table might be an issue.

Consider following example

e.g. you have two aggregate table

Table 1
AggregateID    Year    Quarter     Month    Sales Revenue

Table 2
AggregateID    Year    Quarter    Month    ServiceType    Sales Revenue

Now if you have a requirement wherein one of your report is displays year wise sales revenue whereas other report is displays Year wise ServiceType “sales revenue”. How would you tell report to use table1 for “year wise” “sales revenue” and use table 2 for another report.
Using Aggregate Table in Business Objects
Business Objects provides a wonderful function to use aggregated tables.
Aggregate_aware(). This function determines which aggregate table to use based on the attributes used in the query.

Syntax:

Aggregate_aware(<expression1>,
<expression2>,
—-
—-)

Expression = field or valid SQL expression or calculation

e.g. so formula for sales revenue might be.

Aggregate_aware (table1.salesrevenue,
table2.salesrevenue,
sum(sometable.column)
)

Notice the arrangement of column used in function. It is highest summarized to least summarized.

Now if while creating report if you use only year attribute and sales revenue. Aggregate_aware will use table1 to get the sales revenue, if used service type in report It will use table2, in all other cases it will use sum(sometable.column) which could be a fact table.

Rules to use aggregate_aware

1.If the object is at the same level of aggregation or higher, it is OMPATIBLE with the summary table.

e.g. In above example none of the table contains aggregated value for “Article” so article object will be incompatible.
You can not use aggregate_aware with “Article object”

2.If the object is at a lower level of aggregation, it is INCOMPATIBLE.

3.If the object has nothing to do with the summary table, it is INCOMPATIBLE.

25.What is Incompatibility?

The set of incompatible objects you specify determines which aggregate tables are disregarded during the generation of SQL. With respect to an aggregate table, an object is either compatible or incompatible. The rules for compatibility are as follows:

When an object is at the same or higher level of aggregation as the table, it is compatible with the table.

When an object is at a lower level of aggregation than the table (or if it is not at all related to the table), it is incompatible with the table.

26.What is the typical strategy employed in developing/maintaining/distributing Universes?

Phase 1: Break down the informational system into functional areas.
Phase 2: Analyze the information needs of users.
Phase 3: Design a conceptual schema
Design the specification of the user.
Phase 4: Create a Universe with designer.
Test the Universe with the Business Objects module.
Distribute the Universe.
Repeat the other steps for other Universes.
Phase 5:  Update and maintain the Universe.
Notify end users of changes.

27.I have Customer dimension table and a fact table with cust_to_ship_key and cust_to_bill_key. How do I get the corresponding customer names?

Create an Alias table for Customer dimension table. Join the cust_to_ship_key with a customer key of Actual customer table and join the cust_to_bill_key with customer key of the Alias.

28.What are strategies?
A strategy is a script that automatically extracts structural information from a database or flat file.

29.What are the different types of Strategies?

In Designer we can specify two types of strategies:
1)    Built-in strategies and
2)    External strategies

Built-in Strategies: Designer provides a number of default strategies which we can use. These are strategies for extracting joins, detecting cardinalities, and creating default classes and objects. Options for indicating default strategies are located in the Database tab of the Options dialog box.

External Strategies: We can also create our own strategies. Such strategies are referred to as external strategies. With an external strategy, we can specify the exact way that objects and joins are to be extracted from the database structure. The strategy we use, for example, can be a script generated from a CASE Access tool. An external strategy is specific to one RDBMS.

30.How do you specify external strategies?

With an external strategy, you can specify the exact way that objects and joins are to be extracted from the database structure. All external strategies are contained within the same text file. The name of this text file is indicated in the .prm file specific to your RDBMS. In the .prm file, the strategy file is declared as follows:  STG=[StrategyFileName] where StrategyFileName is the name of the strategy file.
An external strategy, whether for objects or for joins, is made up of the following sections:
a name and description (These are visible in the Strategies tab of the Universe Parameters dialog box.)
a type parameter: object or join
an SQL parameter or file parameter
an optional parameter that points to a connection other than the universe connection.
An external strategy can be based on SQL or a file.

31.What are the visualization options available?

Designer contains a variety of features for organizing and viewing the tables and columns in the Structure pane. Among these features are: List Mode, which adds three panes to the Structure pane. These panes are for viewing the names of tables, joins, and contexts. When you click a component in a pane, its corresponding graphical representation in the schema is highlighted. Graphic options, which let you customize the shape or appearance of the tables, columns, joins, and cardinalities in the Structure pane.
Arrange tables, a feature that reorganizes the tables in the Structure pane so as to produce an orderly display. Gridlines, a command that displays a grid, which you can use to align tables in the Structure pane.
Table (Column) Values, commands that display the data values associated with a particular table or column.

32.What is Join Path Problem?

A one to many join links a table, which is in turn linked by a one to many join. This type of fanning out of one to many joins can lead to a join path problem called a fan trap.
The fanning out effect of “one to many” joins can cause incorrect results to be returned when a query includes objects based on both tables.

33.How to add aggregate table in universe in real life scenario?

Decide the reports which are using high aggregates
Create Aggregate table in DB
Insert them in Universe
Join them with dimension table

34.If we have a user group and we want to give the access to the report from 1990 to 2000. and from that group we want one user to restrict to see the report from 1990 to 1995. Then what to do in BO designer, so that this can be possible( Not in report level).

This can be done using row level security in universe parameter.

35.What is shortcut join. What is its use explain with an example

A shortcut join is a join which links two table by bypassing intervening tables that exists in the universe. This is used when it is possible in certain circumstances to make SQL more efficient.

e.g. in Above example if you want get the client list and there countries you can simple join country ID from client table to country id from country table. However this would introduce a loop. So instead of using normal join you make it as shortcut join. So if query contains object from client table and country table it would you shortcut join. This would result in efficient query avoiding extra join of region.

36.What is isolated join, explain with an example.

Isolated joins means the joins which are not included in
any context. suppose you have 15 joins in ur universe. in
context A you included 7 joins and in context B 7 joins.
The rest means one join is ur isolated join.

37.What is cardinality and its significance in BO universe

Cardinality expresses the minimum and maximum number of instances of an entity B that can be associated with an instance of an entity A. The minimum and the maximum number of instances can be equal to 0, 1, or N.

Because a join represents a bi-directional relationship, it must always have two cardinalities.

There are two main methods for detecting or editing cardinalities:
• Detect Cardinalities command
• Edit Join dialog box

If you selected the Detect cardinalities in joins options in the Database tab of the Options dialog box, Designer detects and retrieves the cardinalities of the joins. If you do not use this option, you can still retrieve the cardinalities for one or all joins in the universe.

38.How will you know the version of BO designer you are using

Go to Help-> About

 

Sub scribe to blog updates by Email and receive Business Objects Interview Questions with Detailed Answers FREE!!

What is Business Intelligence?

SAP Business Objects Interview Questions : Designer

Here are some of the interview questions which might help you prepare for a BO Universe Designer Interview Questions

  1. What is ‘business objects’?
  2. What are the various Business Objects products?
  3. What are the advantages of Business Objects over other DSS?
  4. How many modes are there in BO & Designer
  5. What are Enterprise and Workgroup modes?
  6. How do you save a Business Objects document which can be accessed by all users in workgroup mode?
  7. What is online and offline mode?
  8. What is a universe?
  9. Can a Universe connect to multiple databases?
  10. How do you define universe parameters?
  11. What is a database connection?
  12. What are the types of connections we use when connecting to the database?
  13. What are different types of joins available in Universe design? Explain each
  14. How do you design a universe?
  15. What are the components of the Designer interface?
  16. What are classes/objects?
  17. What are classes?
  18. What are objects?
  19. What are Dimension-Measure-Detail Objects?
  20. What us hierarchy
  21. How to create hierarchies in BO?
  22. What are Contexts?
  23. What are aggregated tables and how would you use it in BO Universe
  24. What is Incompatibility?
  25. What is the typical strategy employed in developing/maintaining/distributing Universes?
  26. I have Customer dimension table and a fact table with cust_to_ship_key and cust_to_bill_key. How do I get the corresponding customer names?
  27. What are strategies?
  28. What are the different types of Strategies?
  29. How do you specify external strategies?
  30. What are the visualization options available?
  31. What is Join Path Problem?
  32. How to add aggregate table in universe in real life scenario?
  33. If we have a user group and we want to give the access to the report from 1990 to 2000. and from that group we want one user to restrict to see the report from 1990 to 1995. Then what to do in BO designer, so that this can be possible( Not in report level).
  34. What is shortcut join. What is its use explain with an example
  35. What is isolated join, explain with an example
  36. What is cardinality and its significance in BO universe
  37. How will you know the version of BO designer you are using
  38. What is loop in Universe? Explain its problem and different methodologies to resolve it.
  39. What is CHASM trap and how to resolve it
  40. When any new universe changes are deployed how does the end user get the view of the new classes/objects added(apart from specs doc)?
  41. I have 2 universes. that is u1 and u2. From u1,i created one report that is r1. Now i want to give the connection r1 to u2 and at the same time delete the connection from u1 to r1 ? How is it possible explain?
  42. What is meant by ZABO and FC(full-client)?
  43. what happens if cardinalities are not resolved?
  44. What is aggregate navigation.
  45. What is Index Awareness in Universe.
  46. What are @functions
  47. What is core Universe
  48. What is derived universe.
  49. What are linked universes? Explain with advantages and disadvantages.
  50. What is Object Qualification
  51. How to create filter in Universe and what is advantages and disadvantages.
  52. Why do we need to create derived table in Universe.
  53. Explain security level in BO Universe
  54. How to implement row level security in Universe.
  55. How do you determine when to use alias and when to use context.
  56. What are different ways to link universes.
  57. How do you distribute Universe?
  58. What is the list mode?
  59. What is Parse checking? .
  60. What are the disadvantages of Alias?
  61. What are linked Universes
  62. Explain Universe Design Methodology.
  63. Explain Universe Development Lifecycle.

 

http://www.bidwbooks.com/category/data-warehousing-books/Good Books on Data Warehouse

Register to post by email and receive Universe Designer Interview Questions and Answers FREE in the confirmation email!!!