BI Question List.doc

A. DWH Concept and methodology

1. What’s Data Warehouse feature? How to understand it? 1

2. OLTP Vs. OLAP 1

3. What’s Data Warehouse architecture? 2

4. Data Warehouse Build Lifecycle 3

5. How to do data modeling in DWH project? (How to design a DW?) 3

6. Relational Data Modeling Vs. Dimensional Data Modeling? 4

7. How to handle Late-Arriving Dimensions and Late-Arriving Facts? 4

14. List type of Fact. 7

15. List type of Fact table. 8

16. What’s Data Warehouse Schema? 8

17. What’s Surrogate key and the advantage? 8

18. What’s Grain / Granularity? 8

19. RDBMS Vs. MDDB. Benefits of MDDB over RDBMS 8

20. What is Drill down, Drill up? 9

21. What is Rotation? 9

22. What is Slicing and Dicing? 9

23. List type/model of OLAP? 9

4. Full loading Vs. Incremental loading？ 10

5. List components in Informatica Power Center client? 10

6. List Methods of creating Target Definitions 10

7. List common Informatica transformations. 11

8. Active transformation Vs. Passive transformation 11

9. Connected transformation Vs. Unconnected transform 11

10. Connected Lookup Vs. Unconnected Lookup. 11

11. When to use dynamic cache of lookup transformation? 11

12. Cached lookup and an uncached lookup? 12

13. What are the types of lookup caches? 12

20. What are the steps follow in performance tuning ? 13

C. Report (BO)

1. List the BI report tools. 14

2. Desktopi Vs. Webi 14

3. What’s universe? How to create a BO Universe？ 14

4. How to avoid Loop in BO Universe 14

5. What’s LOV? 14

6. List type of BO objects 14

7. List datatype of BO objects 15

14. How to get value from filter toolbar in WebI? 15

15. How to define drill path for WebI reports? 15

D. Report (Cognos)

1. Cognos Report Studio and Query Studio 16

2. List the report types in Report Studio 16

3. List some types of prompts 16

4. How to create drill-through? 16

5. List the three types of variables. 16

6. List formats for output of report. 16

9. List type of Report Filter 17

10. What’s a package? Steps to create package 17

14. Regular Shortcut Vs. Alias Shortcut 18

15. Namespace Vs. Folder 18

16. What type problems in general at report runnig time? 18

E. Oracle Database and PL/SQL

1. Procedure Vs. Function in Oracle 19

2. List Join Type 19

3. Sort these clauses 19

4. Sql for group by and having 19

5. How to catch exception in procedure/function 19

F. Oracle ERP

1. What are the basic modules of Oracle Apps? 21

Subject-Oriented: Data warehouse is designed to help you analyze data of some

subject matter.

Integrated: Data warehouse must put data from different source into a consistent

format.

Time-Variant: In order to discover trends in business, we need large amounts of

data, data warehouse focus on change over time.

Non-Volatile : Once entered into the data warehouse, data should not

change.

2. OLTP Vs. OLAP

OLTP stands for On-Line Transaction Processing

large volumes of simple

transaction queries

continuous data changes

low processing time

mode of processing

transaction details

data inconsistency

mostly current data

high concurrent usage

highly normalized data structure

Focus on update speed

small number of diverse queries

periodic updates only

high processing time

mode of discovery

subject oriented - summaries

data consistency

historical data is relevant

low concurrent usage

fewer tables, but more columns

per table

Dynamic (ad-hoc) applications

Star Schema/ de-normalization or

normalization is not required

3. What’s Data Warehouse architecture?

There are four components in the data warehouse environment.

warehouse maybe comes from multiple operational source systems.

• Data Staging Area: Data staging area is a storage area and set of processes

systems and the data presentation area.

• Data Presentation Area: The data presentation area is the place where

warehouse data is organized, stored, and available for direct querying by

users, data access tools, and other analytical applications.

• Data Access Tools: Data access tools are client tools that query and fetch

data stored on data presentation area.

Normally, we extract the data from Operational Source System and store the

data into Data Staging Area.

the data presentation area. Once the data is extracted to the staging area, there

are numerous potential transformations, such as cleansing the data (correcting

misspellings, resolving domain conflicts, dealing with missing elements, or parsing

into standard formats), combining data from multiple sources, removing duplicated

data, and assigning warehouse keys. These transformations are all precursors to

loading the data into the data warehouse presentation area.]