[TYPE THE COMPANY NAME]

INTERVIEW Q&A
[Type the document subtitle]
[Type the author name]

[Year]

[TYPE

THE COMPANY ADDRESS]

Application Life-cycle: SDLC(SOFTWARE DEVELOPMENT LIFE CYCLE) This is the End-to End process of an application in the organisation (From requirements gathering to production support). There are 4 phases in the life-cycle 1. Requirements Gathering 2. Development 3. Testing 4. Production or Support 1. Requirements Gathering Business analyst(BA): He is the person or a mediator between the client and the organisation.

First Business Analyst is going to interact with the client to know about the client business and pain-areas, from that business analysis BA will prepare the document called as Business requirement document or BRD or FSD(Functional Specification document) or SPEC.

From the BRD,the development team will prepare the HLD and LLD.

HLD(High-level-document): Divide the BRD into different modules for requirement

analysis, prepared by developers.

LLD(Low-level-document): Gives the detailed information about each entity in the

BRD or HLD, prepared by developers.

GAP Analysis:This is the comparision between client requirement and

reporting tool features like is the tool fulfill all the requirements of the client or not.

POC(Proof of Concept):This is the dummy or a duplicate model where the

development team will cook the data manually and prepares a sample model which is going to send for the clients approval.

Note:The end product should be same with the POC. 2. Development There are 2 parts in development

ETL development: ETL developers will prepare a data-model with all

dimensions and facts.Also build an integrated dataware-house from the heterogeneous data sources.

Reporting development:Once the DWH is built,the reporters will configure

the repository and generate the reports asa per the clients requirement.

3. Testing

Tesing is the major part in any application or product development.There are 3 types of testing in the OBIEE application development.

Unit Testing:The developer will test the own stuff. Peer-Reviews:Test the application within the organisation as the
application developed.

UAT(User Acceptance Test):This is a client test testing,the application

will be tested by some other company.

4. Production or Support The company need to give the application support for one month by default.After that based on the clients decision, the development company or some other company will give the production support.

Obiee interview questions and answers

1.Date format issue while export to excel format A)Answers>column propeties>custom CSS style options(bottom of the window)>check the custom css style >Give the following date format -mso-number-format:mm\/dd\/yyyy\ hh\:mm\:ss AM\/PM or mso-numberformat:\@ Change the format based on you requirement. 2. When i logged into the analytics not able to see all the options available for administrator? A)First logged in as a administrator into Em, manage the privileges which is required for the user 3.I want to display the 4 narrative views in a single compound layout,but first narrative view width is 1000 and the other 3 views width should be 500.Is it possible? A)No,With a single compound view we cant achieve. 4.How can i sort values of column in subject area not in report ?

A)We can sort the column in 3 ways 1.Ascending order 2.Descending order 3.Based on another column In Rpd we can sort the column based on the another column BMM layer>properties of the column_sort>General>Sort order column >select the column_base(column_sort will be sorted based on the column_base). 4.Can we call multiple action from a single actionlink? A)Yes,We can call the multiple action links from a single action link based on the conditions. 5.What is the difference between joins in physical layer and BMM layer? A)Both are same,but can give the complex conditions in BMM layer.The Query always fire from physical layer only. 6.I have 2 tables in DataBase table1 and table2 a)Imported together with keys and foreign keys? bJoins will be automatically established in the physical layer. b)Imported together only with keys? Ans:-Keys will remain but joins not c)Dragged the 2 tables from physical layer to BMM layer(along with Joins),which act like fact table and which act like dimension table?

Ans:-Primary keyDimension Foreign key-Fact d)Same as (c) but without Joins? Ans:-Both act like fact tables in BMM layer 7.You recently changed the columns of your presentation catalog as your manager wants to enforce naming standards for all customer facing applications. What happens to all the dashboard requests written prior to this change?Do they function properly or do they appear broken?If yes, they will function, How does they work?If not,reports appear broken, what can you do to fix this?Give examples? Ans:-If alias table is available for presentation table then all the reports work fine. 8. I have configured the repository(3 layers) and saved.I have generated some reports and saved.If I change the presentation catalog name then it is showing that you dont have permissions to access this subject area in answers.How can you fix this? Ans:-Goto Advanced tab(In answers) Change the subject area name and set XML it will automatically display the subject area. PERFORMANCE TUNNING FOR REPORTS AND DASHBOARDS 9.You come Morning to the office And all the users are complaining about the OBIEE reporting system is very slow (dashboards). What is your approach to resolve the issue? Ans: 1. First check NQServer.log

2. Admin Tool -> click Session Manager. Check if there is any bottleneck and accordingly resolve. Refresh the sessions 3. Analytics or Answers -> Administration-> Manage Sessions Check the physical query and execute from the back-end(BE). 10.Performance Tuning at real time A)Performance Tuning is the huge topic to answer; lets take step by step approach as follows Check whether the same query is running or anyone update the query.If the same query is running then follow the steps given below, When you open the dashboard page, first figure out whether Prompt or Report is taking more time. If it is prompt, check for any multi select prompts which are taking time to load all values.. (best practice is set default values). If Report is taking longer time, set some default filters and query for just couple of records and check the report performance. Take physical SQL from the session log (if SQL is not generated check for log level whether it is less than 2). Run this Physical SQL in the TOAD or any SQL Editor. Check Explain plan for the cost of the query. Hash Joins/Cartesian Joins are always kills performance. Some-times, Force Inner-join also helps to force the tables to have inner-join between two tables. Check are there any Full Table Scans happening instead of Index Scan. Consult your DBA for the Stats.

OBIEE PRESENTATION LAYER BEST PRACTISES Presentation catalog should map to one Business model & mapping layer(BMM) layer objects only. Use parent folders and sub-folders to group facts and similar dimensions together. Avoid the use of aliases when a new presentation column is created. The presentation columns in a table should be sorted alphabetically if no specific order is asked by the customer. Make proper use of the permissions in this layer like authentication and authorization. Dont use special symbols () in column name, though its permitted. Presentation columns should not have the same name as presentation table. Eliminate unneeded objects to reduce user confusion. Limit number of objects in folder to 7-12 (for performance tuning only). Use object description field to convey information to users when they hover the mouse in answers on a presentation column. Keep names short to have space on reports and give the meaningful table names and columns names to identify easily on subject areas. Remove primary key columns and other unnecessary columns that does not going to use in the creation of the reports. Try to create separate folder for each data mart (HR, operation, SCM, sales) if it is coming from same logical layer. Get costumer sign off of the presentation layer structure before building reports. This will avoid later replacements of columns which affects the report. BMM LAYER BEST PRACTISES 1. Always run Global Consistency Check before releasing a repository: Whenever we make changes to a repository, always be sure to run Global Consistency Check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI server from loading the repository. Use the Consistency chec k manager to identify and debug check messages.

Note: Whenever you do Consistency check , Right Click the Changed Business Model Object and go for Check consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check. 2. Minimize the use of Snow-flakes. Always go for Star Schemas: Every logical fact table must join to atleast one logical dimension table. Note that when the source is a fully de-normalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables. 3. Create dimension hierarchies for every Dimension in the Business Model: Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. For Dimension Hierarchies the Number of Elements at this level should increase from 1 at grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones. Define keys at each level of the Hierarchy. 4. Dont keep unwanted physical columns in the Logical Layer: 5. Give Meaning ful Names to the Logical columns. Avoid assigning a logical column the same name as a logical table or Business Model Object: 6. Logical Fact & Dimension table columns: Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users. Bring only required columns into BMM layer for reporting. Do not assign logical primary key for logical fact columns. Create dummy measures to group facts. 7. Use Multi User Development Environment: Use the Multi-User Development facility if there are multiple developers. Multiple developers to connect online to the same repository file and Making changes is not recommended. Multi User Development allows user to define a series of projects with in the repository file, where each project is a subset of the entire repository. If developers want to make changes, they can check out a project to a local machine make and test the changes, and then check the modifications back into the master repository file. 8. Level-based Metrics: When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set

up aggregation content in the levels tab of the Logical Column dialog for the measure. Note that this is different from the content tab of the Logical dialog for the measure. Note that this is different from the content tab of the logical table source dialog, which is used to specify the grain of the source tables to which it maps. You only need to set up aggregation content in the levels tab of the logical column dialog for level-based measures. For measures that are not level based, leave the logical level field bank. 9. Separate business model: Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality. 10. Arrange the logical columns alphabetically if customer does not require any. This will save time when you revisit: 11. Fix the warnings if any, dont ignore it: 12. Performance Tuning: Minimize the use of conditional checks and CASE WHEN usage in the formula of Logical columns. This will affect performance. Instead make proper use of the where clause content filter of the LTS if the condition applies to all the columns/ measures in the logical table. Make proper distinction between count and count distinct. If you are counting on a unique value column dont use count distinct. This will affect performance. Avoid dimensions in Fact tables and avoid measures in dimension tables.

OBIEE INTERVIEW QUESTIONS

1.What you did in physical layer,BMM layer and Presentation layer? Ans: Physical layer:Physical layer is a connection between database and
repository by using connection pool.We can import the data from the different data sources, no.of connection pools, aliases like tables and columns, duplicate tables, opaque views and physical joins etc.

BMM layer:Also called as Logical layer, Actually we implemented the logic

here.We can do the calculations, create the logical tables and columns,

establish the joins like physical and logical or complex joins, dimensions, levelbased-metrics etc.

Presentation layer:We can show only the content which we want to show the
end-user, We can implement authorization and authentication, Implicit fact column and sort order column etc.

2.What are Level-Based-Metrics(LBM)?How you use in your project? Ans: Level-based-metrics show the measures at different levels based on the
dimension hierarchy. Generally LBMs will use to display the data at month, week and day level. To show this we will create the alias measure columns. Explain more details from your project.

3.What is Dimension Hierarchies?Explain the procedure to create the Hierarchy? Ans: Below link give the complete procedure for creating the dimension
hierarchy..

http://oracletechnotalk.blogspot.in/2012/04/creation-of-hierarchy-inobiee.html 4.What is Sort order columns? Ans: We can sort the columns at 2 levels
1. Repository:At the presentation layer,sort the one column values based on another column 2. Answers:At the criteria tab->columns->sort the column values either ascending or descending order based on the requirement.

5.Rate yourself in SQL? Ans: Here you need answer very carefully because based on your rating they are
ready to fire the questions on SQL. You have the minimum knowledge in SQL like select statement, joins, performance concepts etc.

6.What is Implicit fact table and Implicit fact column? Ans: A common fact table for dimension tables is called as Implicit fact table. An
Implicit fact column is a column in a Implicit fact table, we can select the best path between the dimensions. Implicit fact column is decided in presentation layer.

7.Can we handle the nulls? Ans: Yes,we can handle either in repository and answers.
ifnull(column_name,0)->at expression of the measure column

8.What is the use of connection pool?

10

Ans: We can create different no.of connection pools in the project. It has the
database connection details, no.of minimum users can access the application.

9.What is security in OBIEE? Ans: Security in OBIEE can be implemented in 2 ways.

1. Authentication:Verify and validate the users against the system. 2. Authorization:Validate the users against the Data.

10.What is Authentication?How can we implement? Ans: Verify and validate the users against the system.We can implement the
Authentication in 4 ways 1. External table 2. Operating system 3. Data base 4. LDAP

11.What is Authorization?How can we implement? Ans: Validate the users against the Data. Data can be limited the users by
1. Object level 2. Data level 3. Reports/Dashboard level

Did u get the data from any other data sources? If yes how?
Yes,ofcourse client will provide the data in different formats like excel sheets, files, cubes etc. We can import the data by creating the drivers for data sources.

Can i import the data directly from other sources to repository?

yes, we can directly import the data to repository

How to set the logging level in repository?

manage->identity->goto the user->set the logging level The logging level for developer:min 2 The logging level for Administrator:upto 7 In production phase the logging level should be disabled. We can set the logging level for a specific request at answers

Have you ever worked with ETL-tools?

As a reporting developer, i know the flow of ETL process. I never worked on that.The ETL people takecare of everything.

Which type of schema you are used in your project?

Actually most of the projects are designed like a star-schema.My project is also used star-schema as a data model.

What is star and snow-flake schema? 11

For complete details about star and snow-flake schema

http://oracletechnotalk.wordpress.com/2012/04/10/basic-dataware-housinginterview-questions/ What is fact and dimension table?

The detailed description of fact and dimension tables

http://oracletechnotalk.wordpress.com/2012/04/10/basic-dataware-housinginterview-questions/ What is the flow of your porject?

Every project must follow the software development life cycle. Generally,the porject flow may like requirements gathering, Development(ETL and Report), testing and Production.The flow of project may differ from project to project based on domain like retail, telecom, healthcare, construction etc. The complete knowledge on SDLC

http://oracletechnotalk.wordpress.com/2012/04/22/obiee-applicationdevelopment-life-cycle/ What is Dataware housing? http://oracletechnotalk.wordpress.com/2012/04/10/basic-dataware-housinginterview-questions/ Rate yourself in SQL?

OBIEE developer must have the knowledge on sql because he need to test his own stuff.Minimum knowledge of SQL is required for dataware housing developers.The rating should be dependent on the person knowledge. Tell me About yourself? This is the major interview question for freshers and experienced. Everyone should prepare before going to the interview. Experienced:Tell ur name with experience what you have with current and previous organization and then explain the porject what you done. If they expecting more then give your educational details but no need to mention the family back ground. What are your roles and responsibilities in your current project? As a OBIEE developer,the reponsibilities like repository configuration and reports generation based on the client requirements. At the end we need to implement the security. From where you start your work in daily routine?

12

Daily i got a mail from my reporting manager with a attached document. It includes all the requirements and details of the work. What is the architecture of OBIEE? http://oracletechnotalk.wordpress.com/2012/04/15/obiee-architecture/ Explain the 3 layers of the repository? http://oracletechnotalk.wordpress.com/2012/04/15/obiee-architecture/ What is connection pool and how many connection pools you have in your project?What is the purpose? Actually connection pool is a link between repository and database. For more details use the link http://oracletechnotalk.blogspot.in/search?q=connection+pool We can use multiple connection pools for to increase the performance. The no.of connection pools may dependent on the project. We created different connection pools for security implementation,users and variables creation etc. What is the difference between Obiee 10g & 11g? OBIEE 10g: We have 2 joins, foreign key join and complex join. We have only level-based hierarchy in the logical layer. All the configuration settings will be done at NQSconfig file. OBIEE 11g: we have only 1 join, new join. We have different hierarchies; level-based hierarchies,parent-child hierarchies,ragged hierarchies,skipped hierarchies. All the configuration settings will be done at the enterprise manager. we have option like look-up table. In answers,the chart view renamed as graph. which version of OBIEE you are using? Go to help in the repository of your project. There we can get the exact version of OBIEE. What is the difference between OCI and ODBC? OCI will be specific to the oracle database but the ODBC connection, we can use with any data source. Have you ever worked on Joins?If yes how? Yes, In physical layer i worked on joins based on the data-modelling document.

13

DATAWAREHOUSE FUNDAMENTALS

Dataware house is a
Non-volatile: Permanent data(stores historical data or past data) Subject-oriented: Data will be stored according to the subjects(like data-marts) Integrated:Make a single source from heterogeneous sources Time-variant: Data changed over a period of time

Dataware Housing: It is process of storing and retrieving data from warehouse. Steps Involved from database to datawarehouse:
1. Extract the data from the heterogeneous sources and load into staging area(Source Dependent Loading) 2. Apply transformations on staging area and load into datawarehouse (Source Independent Loading) 3. Generate the reports from the datawarehouse by using a reporting tools like OBIEE, Cognos etc. We can generate the reports from the OLTP or databases but the performance may decrease because the no.of joins are more.

why we do reporting?
Reporting is an essential event for analysis of the data of the specific organisation. Anyone can analyse like management, users, partners etc.We can forecast the values by using the reports or reporting tool. Reporting will be exist in all domains like Retail, Tele-communication, Insurance, Banking, Education, Public sector industries or factories and private sector industries.Analysis of data is everywhere like small-scale, medium-scale and large-scale industries. PHYSICAL LAYER IN OBIEE

Physical Layer:The name itself is saying,it is having the direct relation between
the repository and database.This is the one o f the most important layer in the repository.The query will be executed from the physical layer only.Joins in the other layers should be same with the physical layer.We cant rename the object names in the physical layer.In this layer we can import the data,creating connection pools,alias tables,duplicate tables,opaque views, keys and joins.

14

Data sources:We can Import the data from different Data sources like oracle,
my-sql, excel sheets, XML data and cubes(OLAP and ESS base) by using different drivers. Data will be imported with key or without keys.

Connection pool:We can create a multiple connection pools for single repository
to increase the performance.These are used for accessing users,variables and implementing the security etc.

Tables:Alias and duplicate tables will be created in the physical layer.

Alias table:It is copy of parent table and always functionally depending on the
parent table.Alias table will be created for re-use the same table for different purpose.

Duplicate table:It is a copy of parent table but dont have the relation,once
created.

Keys:If u import the tables without keys,here we can create the keys based on
the data-modelling.The joins will be established in the physical layer only based on the primary and foreign key relation.

Opaque views:Opaque view is physical select statement,which does not occupy

any space in the repository.We can deploy or un-deploy from repository to database. PHYSICAL LAYER BEST PRACTISES PHYSICAL LAYER Dont import foreign keys from the database. Avoid circular joins by using alias tables. Use a different connection pool for different data sources. Use a different connection pool for variables,data sources and security configuration. Establish the joins in the physical layer based on the data-modelling or client requirement. Use the max no.of users for each connection pool. Delete the unused physical objects from the layer by using Remove unused physical object utility. Update the row-count by using Update physical layer to check the database connection. Avoid hints in the physical table level Dont establish join between fact and fact table

15

Avoid to create a view or physical select statement in the physical layer Remove the unused connection pools,it might lead to BI server crash as it continuously ping to the connection. Better to disable the database features in the DB-features configuration file. Always use a data-ware house or OLAP(online analytical processing) system as a database for geenrating reports. Use proper naming convention for physical table and alias table also. Always define your catalog under projects. Ensure to check execute queries asynchronously option in the connection pool details. CONFIGARATION AND LOG FILES IN OBIEE Configuration files:These are the default files for every tool, it will store the default settings of the tool. We can change the settings based on the requirement. Configuration files of OBIEE are

Configuration files of RPD:

NQSconfig.ini: We can configure the RPD into BI server, setting-up the cache parameters, security by-passing, usage-tracking, setting up the time-zone, date, time-format,language and buffer stack size properties. DBfeatures.ini: We can enable/disable the database features. NQSCluster.ini: Maintains the servers in the machine(When multiple servers existed on the same machine).By default in disable mode.

Log files of RPD: Log files are Informative files of anything.

NQServer.log: It will give the information about the BI server like when server starts and stops and why server is not started.

NQSQuery.log:Information about the query manger like how much time it will
take to execute the query and performance of the query.

NQSAdministration.log:Information about the administration tool like problems

with administration tool and all.

NQS Scheduler.log:Information about the schedulers like administration tool

and analytics.

Configuation files of Analytics : Instanceconfig.xml: It will store all the settings of presentation service or
Analytics like reports, dashboards and all the elements .

16

Web-catalog: Web catalog is a folder,it will store the all the settings of a
application.Maintains an individual catalog for application is the best-practice for deploying the application. In OBIEE 11g,all the configuration settings will be done at enterprise manager. Enterprise manager is a third-party server, here all the settings are manages like configuration of RPD, cache management, users and groups, web catalog settings. OBIEE ARCHITECTURE (Oracle Business Intelligence Enterprise Edition) latest version is 11.1.1.5 or 6.It is a reporting tool for generate the reports to analyse the data.OBIEE has 2 default servers BI server and Presentation server.It has 6 components in the architecture 1.Presentation services(Answers or Analysis) 2.Presentation server 3. Repository 4. BI server 5. Database connection(ODBC (Open database connectivity) or OCI (Oracle

call Interface))
6. Enterprise Data ware House

Presentation Services:This is a front-end page for users or clients.User can see

the reports from the Answers or Analysis page.It contains answers, dashboards, account details privileges of the specific users.We can the reports and dashboards based on the client requirement.By starting the presentation server,we can operate the presentation services.

17

Repository:It is also called as RPD or RCU.Metadata settings are configured in

the rpd level.It has 3 layers

Physical layer:It has the direct connection with the database.We can apply the
joins between fact tables and dimension tables based on the data-modelling.

Business model and mapping layer:The logical changes will be done here like
creating logical joins(complex joins),new columns or tables,hierarchies etc.

Presentation layer:The objects will be visible to the users what placed in

the presentation layer.We can apply the security setting in the presentation layer.

We can operate the Repository settings by starting the BI server.we can open the RPD in either online or offline mode

Database connection:This is the database connection between database and

OBIEE.The database connection is either ODBC or OCI.

Data-ware House: Data-ware House is a huge data storage space for retrieving
data.

OBIEE architecture interview questions and answers What are the major components in the OBIEE architecture? a. Client b. Presentation services(along with cache) c. Repository d. Database connection e. Enterprise datawarehouse f. Scheduler(along with cache)

18

What are the different layers of OBIEE Repository? a. Physical Layer b. Business Model and Mapping Layer c. Presentation Layer What are the folders available in OBIEE?

Oracle BI(Administration) Oracle BIdata(analytics) What are the Key Configuration Files in OBIEE NQSConfig.ini, DBfeatures.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml What are the different Log files in OBIEE? NQServer.log NQSQuery.log NQSAdministration.log NQS Scheduler.log What are the minimum services needed to load a repository file onto memory and view a dashboard which has reports that have been refreshed on a scheduled basis? BI server (Administration Tool) Presentation Server (Analytics or Answers) Scheduler server (Scheduling the jobs) What is the location of the administration configuration files? drive:\OBIEE 11g\instances\instance1\config\oracleBIservercomponent\ coreapplication_obis1\file name

19

What is the location of the repository? drive:\OBIEE 11g\instances\instance1\bifoundation\oracleBIservercomponent\ coreapplication_obis1\repository What is the location of the presentation services configuration files? drive:\OBIEE 11g\instances\instance1\config\oracleBIpresentationservices component\coreapplication_obis1\instanceconfig.xml What is the location of the administration log files? C:\OBIEE11g\instances\instance1\diagnostics\logs

OBIEE Interview Questions & Answers 1. Explain the Architecture of OBIEE and what each components do? Answer :- OBIEE Consists of Presentation Services(Web),Oracle BI (Analytics Engine) Server The client (Answers) constructs sql and passes it to the Analytic Engine and then the Oracle BI (Analytic Engine) parses the physical sql to the Datasources and retrieve the data back to the Engine and presents to the presentation Services . 2. How to get sql from obiee for reports? Answer :- There are many ways to get the sql a. Modify the request and click Advanced in that you get xml code and also the actual sql. b. In the catalog Manager click Tools >Create Report .In the Create Report Window > Click Request SQL and save the sql to the physical path in your PC. c. Enable Loglevel to 2 in the OBIEE Admin Tool from Mange-> Security and enable the log level to 2 by clicking properties for the user, then go to the NQQuery.log in BI_HOME/OracleBI/Server/Logs.You will find the SQL for that User. d. By clcking Administration->Manage sessions-> view sql. 3. How will you do sort in Reports in OBIEE Answers. Answer :- Click modify and then click sort (order by icon) on the relevant column in the criteria pane. 4. How will you do different types of narrative Reports in OBIEE?

20

Answer:- By clicking modify request and Narrative View and by giving @1 for the first column result and @2 for the 2nd column and son on and we can also give a heading for No Results by clicking the Narrative view. 5. How will you create Interactive Dashboards ? Answer:- By clicking Administration and Manage dashboards and by adding column selector also by using view selector etc.and also by using prompts. 6. What is write-back in obiee ? Answer:- In Reports of Answer you can give a column as updatebale and then view the reports,this option is called write back. 7. How will you execute Direct SQL in OBIEE? Answer:- By clicking Direct Database Request below the subject area in Answers. 8. How will you create report from two subject areas Answer:- From the Criteria Pane of the Report Created from First Subject Area come to the bottom of the page and click combine request. but the options are limited for combining like union etc.. 9. How will you Port changes for dashboards,reports,rpd from development to production? Answer:- For the RPD use the Merge option in Admin Tool and for dashboards and reports use Content Accelerator Framework. 10. What are the different types of variables in OBIEE? explain Answer:- There are two types of variables in obiee. a.Repository variable. This variable is for the whole repository. b.session variable . session variable can be system variable and non system variable. system variable uses NQ_SESSION. - (system reserved variable). examples of non system variables are user defined filters etc. 11. How will you enable or disable caching in the system level and table level? Answer:- In the NQSConfig.ini file use ENABLE under CACHE Section for System Level for tables .If you want to enable the cache at table level , open the repository in offline mode(not the current working repository). this should be different from the current repository and click enable or disable the cache . right click the table and properties and click the cache or deselect the cache. then merge the repository with the current working one.

21

12. How will you go about adding additional column to the repository in the presentation layer? Answer :- Check whether the table is already existing if so add in physical layer ,then BMM and then Presentation layer, then reload server metadata ,then it will be visible to all users. 13. How will effect the changes for a report , if for certain users only the column heading in the report should be changed? Answer:- Using session variables for that user. 14. What is a table alias in obiee? and where and how will you create it. Answer:- Table alias can be created by right clicking the table in the physical layer then click alias, Table alias is mostly used for creating self joins. 15. Have you created Hierarchy in OBIEE. if so where and how? Answer:- Yes we can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. this can be done by right clicking the dimension table and click create dimension and then we should manually define the hierarchy and its levels. 16. What is Level Based Metrics.How will you create it? Answer:- Leval-base matrics means, having a measure pinned at a certain level of the dimension. Monthly Total Sales or Quarterly Sales are the examples. To create a level based measure, create a new logical column based on the original measure (like Sales in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Month in Time Dimension. 17. What are the different layers of OBIEE Repository? Answer:- Physical Layer, Business Model and Mapping Layer, and Presentation Layer. 18. What is Authentication? How many types of authentication. Answer:- Authentication is the process by which a system verifies, through the use of a user a. Operaing system autentication b. External table authentication c. Database authentication d. LDAP authentication 19. What are the different types of security you have worked in OBIEE? Answer :- Object Level and data level.

22

20. What is a bridge table ? Answer:- If you want to connect two tables where there is no relation ship you can use a thrid bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer. 21. You come Morning to the office. and all the users are complaining about the obiee reporting system is very slow(dasboards). what is your approach to resolve the issue? Answer:- Mainly check NQServer.log and in Admin Tool -> click Session Manager. check if there is any bottleneck and accordingly resolve. 22. What are the different log files in OBIEE? Answer:- Following are the different log files inOBIEE NQServer.log , NQQuery.log, NQSAdminTool.log etc. There are mainly 25 Configuration files available in obiee. 23. What are the Key Configuration Files in OBIEE? Answer:- NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml 24. How will change port of obiee answers? Answer:- By changing its port in instanceconfig.xml 25. What is ClusterServices in OBIEE ? Why its used? Answer:- To Scale up the Performance by clustering and distributing the services across multiple Servers ClusterServices are used. 26. Can you change the location of the OBIEE Repository? Answer:- No, Not in Standalone OBIEE Install. you can give shared location in NQSCluster.ini if the OBIEE is clustered. 27. How many BI Services Node can be clustered together? Answer :- 16 28. Can you run multiple rpds in a Single OBIEE Instance? Answer :- No . you should create another instance in the same server , then its possible. 29. How will you implement security in obiee,so that subjects areas accessed by one group are not accessed by another group. Answer:- Using Security Manger in OBIEE Admin Tool, by creating two user groups and allowing only part of the subject area for one group and other part to the other user group. 30. What is SCD type 1 and 2.

23

Answer:- SCD is slow changing dimensions. in type 1 we replace the changed dimension with old dimension. in type 2 we use surrogate keys and keep both the records(rows). Advantage of SCD2 is we keep history of the old dimensiont. 31. What is Star Schema and SnowFlake Schema? Answer:- Star is one fact and many dimensions where as Snowflake design is one fact and many dims and dims can have additonal dim relations. 32. How to startup OBIEE in Linux? Answer:./run-sa.sh start #starts obi server ./run-saw.sh start #start saw server ./run-sch.sh start #start scheduler 33. How you generally Approach to ur Analytics Project? Answer:- Any project should start from defining the scope of the project and the approach should be not to deviate from the scope. Then the project should be functionally divided into smaller modules generally done by project managers along with technical and functional leads. The functional leads then decide on majorly three things: According to the defined scope of the project they start gathering requirements while interacting with the clients. They had a discussion with the technical leads and try to reach a solution. Technical leads decides what schemas to create and what requirements are going to fulfill by that schema. Technical leads discuss all this with the developers and try to close requirements. Simultaneously testing and deployment is planned in a phased manner. 34. How we are going to decide which schema we are going to implement in the data warehouse? Answer:- One way is what is mentioned in Question above. If one ask you to blindly create schemas for the warehouse without knowing any requirements, You can simply first divide the schemas on the basis of functional areas of an Organization which are similar to the modules in an ERP like sales, finance, purchase, inventory, production, HR etc.

24

You have to broadly describe the expected analysis an organization would like to do in every module. This way you would be able to complete at least 40-50 % of the requirements. To move ahead, study the data and business and you can create few more schemas. 35. What are the Challenges You Faced while making of Reports? Answer:- Making of an report has never been a difficult task. But problem comes when users are reluctant to adopt a new system. Many developers have experienced that if they are not able to create the report in exactly the way they used to see, they will keep asking for the changes. Developers approach should be to first show them what they want to see and then add more information in the report. 36. What you will do when your Report is not Fetching Right Data? Answer:- This is the biggest problem in report creation and verification. There could be two reasons for report not fetching the right data. 1. Mostly clients do not have correct data in their database and on top of that to correct the results they make some changes at the report level to bring the desired result which you may not e aware of while creating the reports. Clients try to match the data with their existing reports and you never get the correct results. you try to discover the things and at later stage come to know of all these problems and you are held responsible for this delay. Hence always consult the SPOC(Single Point of Contact) and try to understand the logic they have used to generate their reports. 2. If the database values are correct, there there could be a problem with the joins and relations in the schema. You need to discover that analyzing and digging deep into the matter. 37. How analytics Process Your Request When you Create your Requests? Answer:- Oracle BI server converts the logical SQL submitted by the client into optimised physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc. 38. From where u Get the Logical Query of your Request? Answer:- The logical SQL generated by the server can be viewed in BI Answers.

25

39. What are the Major Challenges You Faced While Creating the RPD?????? Answer:- Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. Th type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lot of problems with the date formats also. 40. What are Global Filter and how thery differ From Column Filter? Answer:Column filter- Simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content. Global filter- This filter will have impact on across the application. 41. How to hide Certain Columns From a User? Answer:- We can hide a column in Application access level security Do not add the column in the report Do not add the column in the presentation layer. 42. How to Enable Drills in a Given Column Data? Answer:- To enable Drill down for a column, it should be included in the hierarchy in OBIEE. Hyperion IR has a drill anywhere feature where dont have to define and can drill to any available column. 43. How the users Created Differs From RPD/Answers/Dashboards Level? Answer:- RPD users can do administrator tasks like adding new data source, create hirarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyse the dashboard or can edit dashboard by adding/removing charts objects. 44. How Online/Offline mode impact Development and Delpoyment???? Answer:Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment. Offline mode- can be useful in test or development environment. 45. What is Guided Navigation?

26

Answer:- Guided navigation is a feature of Oracle BI Interactive Dashboards that enables the content and layout of Oracle BI Interactive Dashboard to change dynamically based on changes in the information being analyzed. Specifically, sections in a dashboard page can be set up and only appear when there is interesting information in the data. SCENARIOS How change the column heading dynamically We can change the column heading dynamically by passing the presentation variable in column heading, to achieve this follows following steps 1) Log into the Business Intelligence analytics with the link http://Machine_name:port/analytics The port will be 7001 if OBIEE install with simple install option with default port configuration or it will 9704 if OBIEE is install with enterprise option default port configuration 2) Click on New-> Dashboard Prompt

27

28

3) Select the column prompt

29

4) Select the Column Calendar Date from Sample Sale Lite subject area

30

5) Set the presentation variable Date and save it

31

6) Then go for report creation click New-> Analysis

32

7) Pulled the column field to display on the reports

8) Click on right most button of field and select column property

33

9) On column properties dialog box navigate over column format and select check box Customize heading. column heading Set the presentation variable @{Date} in

34

10) Save the report and pulled it on Dashboard page and select the date from prompt

35

11) The column heading will change according to value select in the prompt

Posted by yogi at 19:56 No comments: Email ThisBlogThis!Share to TwitterShare to Facebook OBIEE Security Enforcement OBIEE SECURITY ENFORCEMENT LDAP AUTHENTICATION AUTHENTICATION IN OBIEE Some authentication methods used by Oracle BI server are Database LDAP Oracle BI server (repository users) I do not recommend this method for medium to large implementations. It will be difficult to manage. I will discuss on setting up LDAP in this article. SETTING UP LDAP OR WINDOWS ADSI IN OBIEE Microsoft ADSI (Active Directory Service Interface) is Microsoft version of LDAP server. Most of the steps to setup of either Microsoft ADSI or LDAP server are similar. In either case, you would need help from your network security group/admin to configure LDAP. They should provide you with the following information regarding the LDAP server LDAP server host name LDAP Server port number Base DN Bind DN

36

Bind Password LDAP version Domain identifier, if any User name attribute type (in most cases this is default) REGISTERING AN LDAP SERVER IN OBIEE In Oracle BI repository, go to manage security.

Create a new LDAP server in OBIEE Security Manager

With the help from your network security group/administration, fill out the following information

37

Next in the Advanced tab, based on the kind of LDAP server you have and its configuration, make the necessary changes. For Microsoft ADSI (Active Directory Service Interface), choose ADSI and for all others leave it unchecked. Most of the times, Username attribute would be automatically generated. For Microsoft ADSI It is sAMAccountName; for most of the LDAP servers it is uid or cn. Check with your network security group/administrator on what is the username attribute for your LDAP server. Make a note of the user name attribute you will need it later.

38

Now we need to create an Authentication initialization block. In administration tool, under Manage go to Variables.

Under Action, go to New -> Session -> Initialization Block

39

Configure the session initialization block. Give it a name and click on Edit Data Source. In the pop up window, choose LDAP from the drop down box and then click on Browse. You can also configure a LDAP server here by clicking on New. In the browse pop up window choose the LDAP server you would like to use.

40

Next we need to create variables. User and Email are the common variables normally in play.

41

42

Upon clicking on OK, a warning pops up on the usage of User session variable (User session variable has a special purpose. Are you sure you want to use this name). Click yes.

Next enter the LDAP variable for username. sAMAccountName in the case of ADSI as configured in the LDAP.

Next following similar steps create a variable for Email. In addition, depending on you need, you can bring additional variables from the LDAP server.

43

Posted by yogi at 19:53 No comments: Email ThisBlogThis!Share to TwitterShare to Facebook Connection Pools Best Practices CONNECTION POOLS BEST PRACTICES Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool. Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage Create a separate connection pool for execution of session variables Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions. Posted by yogi at 19:51 No comments: Email ThisBlogThis!Share to TwitterShare to Facebook FRIDAY, 28 DECEMBER 2012 Mapping Analyst for Excel in Informatica 8.6 OBIA Mapping Analyst for Excel in Informatica 8.6 OBIA

44

Hi friends , hope you all have come across the Mapping Analyst for Excel in Informatica Power Centre 8.6 version.I would like to just give a brief introduction about the same.In the coming days we will discuss more about the same. As you all know before when we start a DWH project we need to gather business requirements from the client.It takes some time for us to analyze the functionality of the project and then make that as a document in technical perspective for ETL. This process is too tedious as a lot of valuable development time is spent gathering business requirements and then translating them into technical specifications, and then converting these specifications to PowerCenter data integration mappings.So in order to avoid this Informatica has come with a new feature called Mapping Analyst for Excel.

Mapping Analyst for Excel simplifies the complexity in mapping by directly converting the data in the Excel into Informatica Mappings regardless of the format.This will mainly help the Analyst.They can follow a specific template for

45

defining the source and target in the Excel and then send the same to the Developers who can directly convert this template into Informatica Mappings and execute the workflows. Posted by yogi at 08:29 No comments: Email ThisBlogThis!Share to TwitterShare to Facebook How to convert the date field in OBIEE report to a desired date format How to convert the date field in OBIEE report to a desired date format? CAST (expr AS type) is a conversion function in OBIEE that changes the data type of a value or a null value to another data type.This is one solution to change the datatype,but when I tried with this the column heading in report changed to different colour and my client didn't liked that solution.So I tried another solution.Follow the below steps and screenshots. 1.Go to Column Properties tab

Select column properties tab 2.Select Data Format tab 3.Check Override Default Data Format box 4.Select Date Format field dropdown as custom/as required in your report. If you select Date Format field dropdown as custom then specify Custom Date Format field as required in report.

46

Posted by yogi at 08:28 No comments: Email ThisBlogThis!Share to TwitterShare to Facebook How to convert the date field in OBIEE report to a desired date format How to convert the date field in OBIEE report to a desired date format? CAST (expr AS type) is a conversion function in OBIEE that changes the data type of a value or a null value to another data type.This is one solution to change the datatype,but when I tried with this the column heading in report changed to different colour and my client didn't liked that solution.So I tried another solution.Follow the below steps and screenshots. 1.Go to Column Properties tab

Select column properties tab 2.Select Data Format tab 3.Check Override Default Data Format box 4.Select Date Format field dropdown as custom/as required in your report. If you select Date Format field dropdown as custom then specify Custom Date Format field as required in report.

47

OBIA Architecture and Functionality Overview OBIA Architecture and Functionality Overview The architecture for Oracle Business Intelligence Applications (OBIA) includes several components. Understanding these components is prerequisite to a successful OBIA implementation. A brief description of each component is included below the diagram. OBIEE: OBIA is a set of prebuilt reports for every business module like Financial, HR, Sales, Order Management, etc. In order to have pre-built reports we need to have prebuilt rpd (generic business logic). The rpd is built on top of pre-defined tables (dimensions and facts). The tables reside in a BI database(OLAP). Informatica: Prebuilt OBIA Informatica mappings are created to extract data from several data sources (OLTP) and load to BI database (OLAP) tables. These data sources include Oracle EBS, JD Edwards, Peoplesoft, and other ERP systems. A set of SDE, SIL and PLP together loads data into one BI fact or dimension table SDE Mapping or Source Dependant Loads: Are used to extract data from and Data Source to staging SIL Mapping or Source Independant Loads: Are used to load data from staging to the target OBI tables (dimensions and facts)

48

PLP or Post Load Processes: Are sometimes used to transform data after it is in a fact table, or add to data after it is in a fact table. Informatica structure: An informatica workflow includes one or more sessions sessions. Each session includes one mapping. A workflow is started that runs one or more sessions. The logic of each session is defined in a mapping. DAC: DAC acts like a trigger to run the workflows in Informatica. DAC contains Execution Plans which can be scheduled or run any time. These Execution Plans are designed to do an incremental or a full load. Within each Execution Plan there are several subject areas, each subject area contains all the tasks which are associated to workflows in Informatica that loads data to the dimensions and facts to build the corresponding subject area.

OBIA Architecture and Functionality Overview

OBIA - Naming Convention Table Table Types Used by the Oracle Business Analytics Warehouse

49

Table Suffix Aggregate tables (_A) Dimension tables (_D) Staging tables for Dimension (_DS) Staging tables for Usage Accelerator (WS_) Dimension Hierarchy tables (_DH) Dimension Helper tables (_DHL) Staging tables for Dimension Helper (_DHLS) Fact tables (_F) Fact Staging tables (_FS) Column Suffix _CD Description Code field

Description Contain summed (aggregated) data. Star analysis dimensions. Tables used to hold dimension information that have not been through the final ETL transformations. Tables containing the necessary columns for the ETL transformations.

Tables that store the dimension's hierarchical structure Tables that store M:M relationships between two joining dimension tables. Staging tables for storing M:M relationships between two joining dimension tables. Contain the metrics being analyzed by dimensions. Staging tables used to hold the metrics being analyzed by dimensions that have not been through the nal ETL transformations.

In Table Types _D, _DS, _FS, _G, _GS _D, _DS, _FS, _G, _DHL, _DHLS _D, _MD _FS _D, _DHL, _DS, _FS, _F, _G, _DHLS

_DT _I _ID

Date field Language Independent Code. 1) _ID columns are used in _FS tables. They correspond to the _WID columns of the corresponding _F table. Indicator or Flag.

_FLG

50

_WID

Identifier generated by Oracle Bl linking dimension and fact tables, except for ROW_WlD. Name corresponding to the code column (columns ending with _CODE) Long Description corresponding to the code column (columns ending with _CODE)

_F, _A, _DHL

_NAME

_D, _F, _A

_DESC

_D, _F, _A

Posted by yogi at 23:14 No comments: MERGE Two Repository in OBIEE 11G Hi All, Here are the simple steps to merge two repository in obiee 11g You should have three rpd's for merging 1) Your Current RPD. (BISAMPLE.rpd) >>Current RPD<<

2) The RPD to be merged (the one you newly worked on) - sandeep.rpd >>Modified RPD<<

3) Dummy RPD. In My case dymmy.rpd

51

If you don't have a dummy. rpd , create a new rpd from the admintools (do not import metadata). ok now we are all set to start the merging. Step 1: Open your Current RPD, ie BISAMPLE.rpd in my case and select the merge option.

52

Step 2: Provide the details of RPD as given below.

53

Select dummy.rpd as your original rpd and Sandeep.rpd as the modified rpd and click next >> Step 3: Define Merge Strategy

54

In this case select Decision = Current

Click next finish >>>

55

The repository merge is now complete. variables in OBIEE 11g. There are basically 4 different types of variables in OBIEE 11g. Session Variables Repository Variables Presentation Variables Request Variables. Session Variables: As the name suggests, session variables are created during the creation of session i.e., as soon as a user logs into the BI server. So, Every login has its own session variable. There are two types of session variables System (which are defined by OBIEE and are reserved)

56

Non-System which are defined by developers. Session Variables can be created only through Oracle BI Administration Tool. Referencing session variable: For displaying session variables, we should use @{biServer.variables['NQ_SESSION.VariableName']} . For using session variables in expression, we should use VALUEOF(NQ_SESSION.VariableName). Repository Variables: A repository variable is a variable that has a single value at any point in time. There are two types of repository variables Static (which changes only if admin or developer changes it value) Dynamic ( value is refreshed using a query) Repository variables can be created only through Oracle BI Administration Tool. Referencing repository variable: For displaying repository variables, we should use @{biServer.variables.VariableName} or @{biServer.variables['VariableName']}. For using repository variables in expression, we should use VALUEOF(VariableName) for static variable and for dynamic variable VALUEOF(Dynamic Initialization Block Name.VariableName). Presentation Variable: A presentation variable is a variable which can be created as a part of creation of dashboard prompts. Dashboard prompts must be either Column Prompt or Variable Prompt. The value of presentation variable is set by the prompt for which it is created (upon user selection). Referencing presentation variable: For displaying presentation variables, we should use either @{variables.VariableName}[Format]{DefaultValue} or Format and DefaultValue are optional Format is useful to format the data for e.g., for Date, format can be MM/DD/YYYY. Note: Default Value is not formatted. @{scope.variables['VariableName']}. Scope should be used if you create variables with same name. Scope can be analyses, dashboard etc. Order of precedence is analyses, dashboard pages, dashboards.

57

For using presentation variables in expression, we should use @{VariableName}{DefaultValue}. Default value is optional. Request Variable: Request Variable is used to overwrite the value of session variable and it happens only during request initiation to the database from column prompt. Can be created only during the creation of column prompt. 1.What is the difference between OBIEE 10g and OBIEE 11g? A Database Repository must be created before installing OBIEE by using Repository Creation Utility(RCU) tool OBIEE 11g uses a WEBLOGIC server as Applictaion server whereas OBIEE 10g uses OC4J Many configuration settings (such as uploading a repository into BI server) can be done using EM OBIEE 11g displays table names and COLUMN NAMES while mapping whereas OBIEE 10g displays only table names In 10g Users and Groups(or Roles) are created in repository whereas in 11g Users and Groups are created in EM Groups no longer exist and are replaced by Application roles. Data level security is implemented by using Application roles to which users belong to. In Presentation Catalog, AuthenticatedUser role is used instead of Everyone group We can model LOOK UP TABLE is repository Creating hierarchy in Presentation layer New time series functions PERIOD ROLLING and AGGREGATE AT is introduced TIME SERIES functions can be created in the front end also AGGREGATE PERSISTENCE WIZARD creates indexes automatically SESSION VARIABLES are intialised only when they are used In addition to the existing views, Map View is introduced in 11g RAGGED and SKIPPED HIERARCHY are supported in 11g KPI's and SCORECARD's are introduced in 11g

58

ACTION LINKS, MASTER-DETAIL reports, SELECTION STEPS are introduced SELECT_PHY SICAL is supported REQUESTS are renamed as ANLAY SIS IBOTS are renamed as AGENTS CHARTS are renamed as GRAPHS 2. What is an Alias table and why is it used? An alias table (Alias) is a physical table which references a different physical table as its source Advantages of using alias table: It allows you to reuse any existing table more than once, without having to import it several times It can be used to avoid circular joins by setting multiple tables each with different keys, names or joins For example: Order date and Shipping date may reference to same column in the time dimension table. By using alias you can create two different tables OrderDate and ShippingDate It can be used for best practice naming conventions as you can rename the table and leaving the original physical table as it is

59