Important.docx

1. 2. 3. 4.

5.

6.

7.

By default the PowerCenter server sets the number of partitions to Ans : 1. No. of partitions one can define in the PowerCenter Server? Ans : 64 at any partition point. Multiple fact tables share dimension tables. What schema are we talking about??.... Ans: Fact Constellation Number of log files by default in Infa? a. 1 b. 2 c. 3 (session log, workflow log, reject log) d. 4 Which transformation helps to convert the rows into columns? a. Rank Transformation b. Sorter Transformation c. Normalizer Transformation d. Update Strategy No. of components in the Workflow Manager a. 1 b. 2 c. 3 d. 4 e. 5 f. User, Groups, Privileges are created in which client tool? a. Repository manager b. Workflow Manager c. Workflow monitor d.Designer 10. Drilling from Analysis Studio report to which report is possible? a. Report Studio report b. Query Studio report c. Metric Studio report d. Not possible

11. a. b. c. d. e.

Minimum no of dispatchers required to process a request?

1 2 3 4 5

12. In the Analysis studio, a cross tab is added in which two measures are adjacent to each other, what is this set called? a. Measure Set b. Analysis Set c. None of the above 13. In Report Studio, when selecting a prompt value automatically takes u to the second prompt, what are this type of prompts called? a. Cascading Prompts b. Sequence Prompts c. None of the above 14.

Content Store stores what data?

Cognos data a. Data required for the Application b. Report Specification and something c. Cube data 15. Which service in the Dispatchers helps to render reports? a. Presentation Service b. Batch Report Service c. Report Service d. Job Service e. None

16. A Query Subject named X should be accessed only by the Management level people, What security mode will u apply a. Data Level b. Object Level c. Model Level d. None 17. What is the efficient Way of distributing the reports? a. Bursting b. Emailing c. None 18.

Pick out the reporting tools

a) Cognos b) BO c) Discoverer d) Informatica

Ans: a,b and c

19. Design tools available are: ERWIN, Power Warehouse Architect and Oracle Designer 20.

Which is not possible in the Analysis Studio??    

Standard Deviation max sum Correlation

21.

Data source for Framework Manager?     

22.

XML Excel Sheets Homogenous? Heterogenous sources Informix Impromptu All the above ( Is the answer suggested by my Cognos Trainer )

Which should be used to define a interval between two task

a) set wait b) set no wait c) wait 23.

The New Power Center Informatica Suite handles 64 bit.

24. State the Correct one a) Group by should be placed before having clause b) Group by should be placed after having clause c) oderby should be placed before select clause Ans a.) 25. It is possible to create a crosstab report in Query Studio. True or False?. Ans; False It is not possible to create a crosstab report in query studio. Only a list report can be selected and converted into a Cross Tab Report using the Pivot Option. 27. MOLAP selection depends upon: ( select choice question.. ans is both the choices )  Analysis required and queries to be posed are known at run time.  Data retrieval paths follow predefined structure of datacube.

28. ROLAP selection depends upon ( If any of the choices below comes choose ROLAP )     29.

Raw Data Ad Hoc Reporting Database greater than 10 GB in size Different views of the DWH something….

What are the disadvantages of ER Modelling??  No GUI  Cannot raise queries  None of the above  Both Ans; Both

30. Read Constraints well…there was a question like whether it was compulsory to name the constraint while defining it. 31. What does Full Outer Join do ? Ans: It includes the unmatched rows from both the tables. 32.

How many components are there in the Designer? Ans : 5

33. What is true about Cursor for Loop ? Ans: No exit condition is required i.e EXIT WHEN < condition > or like EXIT WHEN <cursorname> %NOTFOUND 34. What will be the situation when RAISE_ECXEPTION is raised and the exception handler block is not provided? a) it will cause a memory leak b) it will raise % not found error

c) the control will pass through sql block 35. Find out the error in the following query ?

select o.productname,p.odernam o.location where product.product_id=order.oder_id and oder by o.sale a) o.sale should be used in the select clause b) alias order must be changed c) table alias should be used in where clause

36. There was a question on Datamarts being a logical subset of Datawarehouse. Different choices on datamarts were givenDon’t remember the exact choices. 37. a) ODS uses operation data b) DWH uses analytic data c) ODS can be a part of a larger datawarehouse d) ODS can never be a part of the DWH Best ans choice: a,b and c

38. A simple question on SQL: What clause should you use to get employees getting salaries more than 3000 Ans : WHERE sal>3000; ( simplest of all ) 39. What transformation canyou use to select employees only from Dept No.10? a) Source Qualifier b) Filter Tranformation

c) Router Transformation d) Router and Filter Transformation ( I chose this because Router can also be used ) *****n sr sq also 40. No. of ports in Rank Transformation? Ans: 4 ( Input, Output, Variable and Rank ) 41. No. of ports in the expression transformation? Ans: 3

42. Syntax for LookuP Transformation? :LKP.LKP( )

43. Another question on Joiner Transformation..i don’t

remember..Better study MASTER and DETAIL TABLE OPTIONS in the PORTS TAB of JOINER TRANSFORMATION. 44. Read SURROGATE KEYS well and also why existing keys should not

be used. 90% chance that Q’s will come based on Surrogate Keys properties. 45. Cognos 8 BI is an

   

ROLAP MOLAP < some irrelevant > Enterprise Wide BI solution

46. Log files in Workflow Logs, Session Logs and Reject Files whose default directories are $PMWorkflowLogDir, $PMSessionLogDir and $PMBadFileDir respectively. The same for reusable workflows are $PMRootDir/WorkflwLogs, $PMRootDir/SessLogs and $PMRootDir/BadFiles respectively.

47. In pmcmd command line mode if the program is successful then 0 is returned. 48. No. of modes in pmcmd? Ans: 2 (Command Line Mode and Interactive Mode) 49.

. Which of the following is possible to a date variable a. min ( MAX AND MIN CAN BE USED WITH ANY DATATYPE

) b. none of the above c. sum d. avg 50.

Match the following a. Project – Top Level b. package – ii. Published c. Model - iii. Set of related query subject d. Namespace – iv. Uniquely identifiable

51.

Which of the following are true?

a. Single-row functions can be used only in select and where clause. ( False- Single row functions can also be used with FROM ) b. character functions works on character data type column only. ( False- CHR is a character function which operates on numbers i.e. CHR ( number ) returns the character corresponding to the ASCII number ) c. date functions return date data type only. ( False )

( MONTHS_BETWEEN ( date1, date 2 ) returns a number and not a date ) d. None of the above. ( ANS ) 52. What is a detailed filter? Before aggregation I don’t remember the choices but my friends told me that detailed filter is used before an aggregator transformation. (Not sure ) 53. Summarised filter? One used after the aggregator transformation. ( Not sure ) after aggregation 54.

Suitable Data model for OLTP? a)ER b)Dimensional Modeling c)None

55.

55. Which is not very frequently updated?  ODS  DW  DM  None 56. . To operate in which pmcmd mode, username, password and server connection details are necessary?  Command line  Interactive  Both  None

57. To determine which column caused the row to be rejected, what does the server add to give more info about columns?  Key indicators  Key breaks  Row indicator and column indicators  Variable indicator 58. To compare value with some reference values which chart is used? a. pie b. bar c. line d. None 59. Which transformation row updates distinct records into target table?  Update Strategy  Rank  Sorter Aggregation 61. 60. Calculate 12*sal*commission for every record of the emp table chaoose the appropriate query? a. Select 12*sal*nvl(commission,0) from emp ; b. Select 12*sal*decode(commission,0) from emp ; 62. What’s the memory allocated by default to the index cache? Ans: 1 MB 63. What’s the memory allocated by default to the data cache? Ans: 2 MB 64. Which transformation would you use if you wanna extract data from a COBOL database ? Ans: Normalizer Transformation ( References: Informatica Help ) 65.

Count all rows :% ROWCOUNT

66.

66. How many transformation are there ?

a)12 b)18 c)8 b)13 If 16 is given in place of 18 then go for 16 . 67. what do mean by nesting a) one dimension above to another b) one dimension below to another c) one dimension next to another ans c) one dimension next to another 68.which transformation is used to reject load files a) source qualifier transformation b) router transformation c) either source qualifier nor router b) source quailifier,router and filter transformation ans a) source qualifier transformation 69.what do u mean parent fails current task fails a) current task fail if parent fail b) parent fail if the current task fail c) current task only fail d) both current and parent fail ans:b 70. metadata means a) data about data b) contains database catalogs

ans:a 71. which statement is true 1)oDs is part of Datawarehousing 2)ODS is not a part of Data ware housing 72. a)composite primary keys that are derived various dimension table are stored in fact table b)using Secodary dimensions and then storing it in fact options a is Snowflake Schema options b is star schema options a is star schema options b is Snowflake Schema both a and b are Snowflake Schema both a and b are Star Schema ans:b 73. Datawarehouse Stores a) analytical information and Current value b) analytical information and historical value c) operational information and Current value d) operational information and historical value 74. reordering option a)reordering is used for changing the position of values in column b)reordering is used for changing the position of column c)reordering is used for changing the position of object d)none ans b 75. Which window helps to access the Repositories ? a. Navigation Window b. Property Window

c. Workflow Monitor d. Workflow Manager 76.The primary server process in the infa Server? e. Repository Manager f. Load manager g. Workflow manager h. Workflow Designe 77.Writing scripts to schedule Workflows … what mode is this i. Interactive mode j. Command line mode 78.What types of data sources are there in Infa? (all the options are correct but we had to select only one option) k. Flat files l. Homogenous and heterogeneous sources m. Flat files, Homogenous and heterogeneous sources n. XML and Cobol sources o. None of the above 79.Which schema occupies Less memory Space? a. Star Schema b. SnowFlake c. none 80.Which Schema, help improving the performance? d. Star Schema e. SnowFlake f. none 81.Defn of DataWarehouse? (STUDY THE DW BASICS MATERIAL) 82.Types of transformation? g. Simple Conversion h. Structural Conversion i. Format

j. All the above 83.Defn of Dimensional Modeling STUDY THE DW BASICS MATERIAL 84. no of designer tools in informatica designer? Ans:5 85.no of tools in w.f. manager? Ans:3 86. oracle: reg constraints. If u delete one constraint, wat will happen? It affects child also…. 87. reusable tasks were developed in : ans: task devolpor 88. oracle: round(trun(34.67,2),3)… value is … similar type. Ans:34.67 89.types of extraction in dw? 1. full 2. incremental 3. full and coincilation 4.none 90. wide range of diff view on same data is required. Which one u prefer? Molap or rolap? Ans: rolap. 91.cognos how many tier ? ans 3. 92. select emp_name,salary,desig from emp order by salary desc; select emp_name,salary,desig from emp order by 2 desc;------orders by salary(the second fieldname in the select list.) result of the query? Ans:BOTH THE QUERY SHOWS IDENTICAL RESULTS 93. Which of the these dimensions have same meaning a) Junk Dimension b) Degenerated Dimension c) conformed dimension d)none of the above 94.From analysis studio u can drill down to a)report studio b)Query Studio 95.Some questions came from REVOKE AND GRANT(STUDY THE SYNTAX FOR BOTH )

96. Dataware house is a) ODS that stores historical data b)Analytical datastore that stores historical data 97. choose correct one A) single row will fetch the rows from only one table b) subqueries cant have aggregate function c) subqueries cant have analytical function d) multiple sub query can have like function ans:a 98. . What is done in staging area? a. Cleansing b. transformation c. Temporary storage for incremental extract. d. All the above ans:d 99.Self-join syntax. 100. Using which command it is possible to append data from one table to another which has same column data type but with different data. Existing data should be updated and new data should be inserted. a. Merge b. Not possible with single command. c. None of the options d. INSERT 101.One question related to merge syntax.(STUDY MERGE SYNTAX). 102. What does the following query does? select a.mid, b.maxsal from emp a, (select mid,max(sal) maxsal from emp group by deptname) b where a.mid=b.mid ; a. maximum salaried emp within each dept.

b. maximum salaried emp within company. c. Error in statement. 103. Which is not cursor method? a. %Notfound. b. %found c. %rowcount d. % many rows 104. About factless fact table? a. Coverage table. b. Event tracking table 105. More number of data marts causes a. Performance degradation b. other options I don’t remember. 106.Use of surrogate key? Ans: Improve performance 107. What is stored in the .key file? a. repository server name b. user name c. password d. all the above 108. How to over ride the order by clause in look up query? a. Write an order by clause in look up query over ride. b. Write an order by clause in look up query over ride order by c. Write an over ride query. d. Something similar. Not sure 109. What does check Integrity do? a. Parses joins, conditions and objects. b. Validates cardinalities, loops and contexts. c. validates universe structure. d. All the above.

110. Which of the below is a DML statement. a. Create b. Update c. Drop d. None of the options. e. commit 111. Which of the below should you use to create a new repository? a. Repository manager b. Repository Administration Console. c. Workflow Manager. d. Workflow Monitor. 112. What is the output of: round(17runk(mod(1600/10),-1),2) a. 0 b. 0.00 c. error (because of 1600/10 – actual syntax is mod(1600,10) ) 113. Which one this is a valid table name? a. COLUMN b. NUMBER#1 c. 1TABLE d. TABLE_MANY_COLUMN_ (goes on like that up to 34 characters.) ( a,b,c are invalid table names. , in oracle a table name can be of only upto30 characters long , so I am not sure whether ‘d’ is the ans.) 114. What is the use of grant clause? a. Grant privileges on objects to users and groups b. Grant privileges to objects. 115. What is true about implicit cursor? a. You should be careful while using implicit cursors. b. Implicit cursor is user in for all loops. c. Implicit cursors are used for SQL statements…… 116. For which of the below can a drill mode be applied: a. Measures b. Dimensions

c. Detail d. All the above 117. To search for the employees whose last name has ‘a’ as its second letter. Four queries were given. The correct condition is: last_name like “_a%” 118. To use the derived datatype for a column : Four queries were given. The correct condition is: emp.lname%type 119. In pmcmd mode for each line you need to communicate with -----------a.informatica server b.repository manager c.workflow manager d.workflow monitor 120. What is output of full outer join? A.only matched columns in both tables b. unmatched columns in both tables(ans) 121. why ER modeling not used by the user……. a.ER model don’t have gui b.not understand by end user c.a&b d.none

122. In a query in oracle the variable PICONSTANTINTEGER got error, what could be reason for that ? a.PI is a system keyword b.constant is not there in oracle c.memory to store variable not sufficient 123. Minimum number of transformations required to validate a mapping? a.2 b.3 c.4 d.5 124. which dimension is used in fact Ans: degenerate 125. which dimension don’t effect frain of fact table Ans: casual 126. which data doesn’t change from one fact table to another Ans: conformed 127. Cognos is ……enterprise wide bi solution 128. which pmcmd mode can be used to communicate with work flow manager with the use of writing scripts…command line……. 129. the detail filter is the one ……..(a. applied to summary rows b. applied to grand total c.applied to detailed rows d.applied to the entire rows) 130. if a data get over flow in memory cache then the overflowed data got stored in…(a,cachefiles b. variables c. memory files d. none) 131. which of the following calculation cannot be done by using analysis studio?

(a. sum b. avg c. correlation d. standard deviation) 132. which of the following types of schema has high performance….(ans. Star schema) 133.the schema in which multiple fact tables shared dimensions is called……(ans. Fact constellation) 134.the dimension which is shared commonly across through diff fact tables is called….(a. junk dimension 135.one question from TRIM syntax 136. Which service in the Dispatchers helps to render reports? a. Presentation Service b. Batch Report Service c. Report Service d. Job Service e. None 137. Which is not possible in analysis studio? a. none of the following b. compare trend c. score card. 138. if leftout,which of the following will cause infinite execution of LOOP a.exit b.end c.begin 139.How to take the top three performer from the table a. rank b. aggregator c. sorter

140. what will the truncate statement do..?? a. truncate all foriegn keys b. truncate all rows c. truncate all columns d. truncate both rows and columns 141. Which is true a) DM is a logical subset of DW b) DW is logical suset of DM c) neither DM nor DW are subset d) DW is the subset of DM and DM is subset of DW 142. something about packages three options were given 1. package can have maximum 1 governor 2. package can contain more than one catlog (not sure this option) (I got this question but dunno the full question ankit) 143. Increase in number of datamarts will increase the performance tremendously—false 144. the model that contains a multiplekey table connected to smaller tables 1. dimensional model 2. logical model 3. physical model 145. if user does not give temp space when creating a table , which table space the oracle will take 1. temporary 2. system 3. database 146. which transformation is used to calculate salary using commision –Expression

147. the Source System in the DW environment is …  an operational system which processes the business transactions for the current data only  an analytical system which processes the business transactions maintaining the entire history  other similar options 148. characteristics of ods: a)detailed and lightly summarised b)current and near current c)vast supply of heterogenous data d) detailed and lightly summarized & current and near current 149. To work with a view what should you have? Ans: select privilege on the view 150. types of metadata – ETL,Database,MOLAP 151. pmcmd get connected with - power center server 152. measures that can be added across few dimension and not with others Ans-- Semi-additive 153. The records can be limited by using a)Filter b)break c)Alerter d)None 154. what is hierarchy? a related series of dimensions for analysis purpose 155. They asked which is an ETL tool? a)Informatica

b)Datastage c)ab - Initigo d) all ans: all 156. Which transformation is used to load the rejected files a) source qualifier transformation b) router transformation c) either source qualifier nor router b) source quailifier,router and filter transformation 157. where can u see the status of any workflow ans: workflow monitor 158. Which is a context in analysis studio? a) Slicer b) Dicer c) Filter d) None 159. What option should you enable to be able to create shortcut for folders? a) shortcuts b) create shortcuts c) allow shortcuts d) create and allow shotcuts e) none 160. which kind of tool is framework manager? f) windows based g) web based 161. which of the following control should the user have to be able to access all repositories, folders…….somethin h) super user i) user j) none 162. The repository manager can create a back up for all repositories available in the following format. ( I don’t remem the exact wording)

k) xml l) binary m) excel n) none 163. Which is false abt surrogate key?  It’s a key of integer type to uniquely identify a record  It can have repeated values  It can be formed into a composite key along with foreign keys  It can be used as foreign key in another table 164. When you use update trategy trasnformation which is the default mode? a)Insert 165. If you want the datatype of a column existing in some other table what will you use a)%rowtype 166. when you create a workflow which task gets created by default? a) Start b) Command c) Assignment d) Timer 167. 18)Which is not a correct representation of 1 to many relation in framework manager? a. 1:0 to 1:n b. 1:0 to 0:n c. 1:1 to 0:n d. 0:1 to 0:n 168. 75)In Report Studio, when selecting a prompt value automatically takes u to the second prompt, what are this type of prompts called? 1. Cascading Prompts 2. Sequence Prompts

3. None of the above 169. Which of the following is possible to a date variable a. min b. none of the above c. sum d. Avg 170. 90)Which of the following are true? a. Single-row functions can be used only in select and where clause. ( False- Single row functions can also be used with FROM ) b. character functions works on character data type column only. ( False- CHR is a character function which operates on numbers i.e. CHR ( number ) returns the character corresponding to the ASCII number ) c. date functions return date data type only. ( False ) ( MONTHS_BETWEEN ( date1, date 2 ) returns a number and not a date ) d. None of the above. ( ANS ) 171. 127)How many repositories can you simultaneously work in a single Designer environment? 2 repositories 172.Repository server time out ans: 60 seconds 173.