ID column and Oracle BI for MS Office

ID column:

1.What is the use of ID column?

A) ID column is useful to improve the performance of report by passing description column corresponding ID to SQL query.

Oracle BI for MS Office:

1.What are the key features of  Oracle Business Intelligence for Microsoft power point and excel?

A) Key features of OBIEE for Microsoft powerpoint and excel

1.View live, refreshable data from the OBIS.

a. Insert BI tables,Pivot tables and graphs into powerpoint or excel as refreshable, editable objects.

b.Apply PowerPoint or excel formatting to BI data; formats are retained when you refresh.

2.Copy and paste BI analysis from OBI Answers or OBI interactive dashboards to powerpoint or excel. The data, metadata and view layout are copied.

3.Secure BI data from Oracle BI objects in power point or excel so that users must log in to view the data. Secured objects can be refreshed.

Master-Detail Report

Master-Detail Report:

1. What is meant by master detail report?

A) A report is usually called Master-Detail if it is used to display data from a hierarchical data source.

2. Generally how many approaches are available in developing Master Detail Report?

A) There are mainly two approaches in developing Master Detail report.

3. What is the first approach in developing the Master-Detail Report?

A) The first approach is based upon using the Detail Report band and is useful when a data source contains an ADO.NET relation between master and detail instances within it.

4. What is the second approach in developing the master-detail report?

A) The second approach is to create two different report classes, and incorporate the detail report into the master report as a sub report.

5. What can be the form of the Master-Detail report?

A) A master area can be a form, list or tree of items, and a detail area can be a form, list or tree of items typically placed either below or next to the master area.

6. What kind of relationship is Master-Detail report possessed?

A) Master–Detail relationship is a one-to-many type relationship.

Unbalanced Hierarchies(Ragged and Skipped)

Unbalanced Hierarchies(Ragged and Skipped):

1.38073 - Dimension ‘@1%ls‘ has multiple leaf levels which are not  identical?

A) This error occur when the Leaf levels in a dimension have to be identical.But also when you have metadata problem. You get them by using “Create a parent level” from a leaf level. Try to use “Create a child level” from the Grand Total level.

2) What is Ragged and Skipped Hierarchies?

A) Ragged Hierarchies:

The number of levels in all the branches of a hierarchy are not equal. All child nodes/lowest nodes are not at the same level, one or more of the nodes are in a higher level.

b. Skipped Hierarchies:

The number of levels between a parent node and a child node in a branch is more than one. In other words, a few levels are skipped between the parent node and child node.

3) What is Level based Hierarchy?

A) Level-based Hierarchy:

Consists of an ordered set of two or more levels For example, a Time hierarchy might have three levels for Year, Quarter, and Month. Level-based hierarchies can also contain parent-child relationships.

a. Level-based Dimension hierarchy levels allow :

• To perform aggregate navigation,
• To configure level-based measure calculations,
• Users from Dashboard and Answers to drill down from one parent to a child level.

Special type of level-based dimension is supported:

• Unbalanced (or ragged) and Skip-level hierarchy
• Time dimension to provides special functionality for modeling time series data.

4) How to handle the NULL Values problems?

A) In case of Ragged we cannot define fixed number of levels as for certain rows don’t have all the levels.

1. In case of skip level again we have to handle the NULL values in between levels which lead to removal of the row which is part of the skip level and won’t be displayed in the output.

OBIEE 11g has a way to handle NULL problems easily.

1. It’s just we have to mark those dimensions as Ragged or Skipped level and rest will be taken care of.

5) How to identify if parent have no child ?

A)“Whenever parent and child values are same, that means there is no child.”

Parent Child Hierarchies

Parent Child Hierarchies:

1. Parent child hierarchies are also called as?

A) Parent child hierarchies are also called as Value based hierarchies.

2. What is a parent child hierarchy?

A) One to many relationship among data is called as parent child hierarchy.

3. Who needs parent child hierarchies?

A) This kind of hierarchy is required for employees or team based hierarchies.

4. How many levels do parent child hierarchies have?

A) In parent child hierarchy we will have only two levels (One is Total and other one is Detail).

5. What needs to be done to resolve the issue of circular joins in OBIEE11G?

A) Alias must be used in order to resolve the issue of circular joins in OBIEE11G.

6. How do we need to manage the concept of parent child hierarchy in OBIEE10G?

A) We used to manage this concept in OBIEE10G by de normalizing the position table.

a. In this concept no of columns in the table is equal to number of positions in organization.

b. Hence this table metadata (Number of columns) changes from organization to organization.

c. If extra level is added then Meta data changes need to take care in DB, ETL and reporting levels.

Opaque View (select Table) And Alias

Opaque View(Select Table) And Alias:

1. What are opaque views in database? When is it advisable to create one?

1. The opaque views are tables that are created with join or other query data that contain “SELECT” query output. The opaque views make the logical understanding simple for implementation but there are heavy performance constraints. They are only used when there is no other way to get to the final solution.

2.What is the purpose of Alias Table?

A) An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a logical table source, and inherits all its column definitions and some properties from the logical table source. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these logical table source types.

a. Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table.

b.” To reuse an existing table more than once in your physical layer (without having to import it several times).

c. ” To set up multiple alias tables, each with different keys, names, or joins.

d. To help you design sophisticated star or snowflake structures in the business model layer.

e. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

2.  Advantages of Alias table?

A) Alias is useful to rename physical layer objects.

a. Creating self joins.

b. Avoiding closed paths.

MUDE (Multi User Development Environment)

MUDE (Multi User Development Environment):

1. What is MUDE/ MUD in OBIEE? On what basis would you create projects?                                 

A) By default only one user can work on RPD file in offline mode.

If multi users need to work on same RPD file in offline mode then we needs to implement MUDE.

a.  A piece of RPD is called as project. RPD will be divided into projects based on Business Model or subject area. On one project two or more members can work.

2. How do you work in a multi user environment? What are the steps?

A) Initially, the network directory contains the master repositories. The repositories in this location are shared with other developers. Later, the network directory contains additional multi-user development history files, including historical subsets and repository versions. Do not manually delete any files in the multi-user development directory; these files are important and are used by the system.

a. Create a shared directory on the network for Multi-user Development (MUD).

Open the RPD to use in MUD. From Tools then go to Options, setup the MUD directory to point to the above directory.

b. Define projects within the RPD to allow multiple users to develop within their subject area or Facts.

c. Save and move the RPD to the shared directory setup in point 1.

d. When users work in the MUD mode, they open the admin tool and start with

MUD then Checkout to check out the project they need to work on (not use the File open as you would usually do).

e. After completely the development, user checking the changes back to the network and merge the changes.

3. Two people (Developer A and Developer B) are assigned to the same project inside MUD and opened the same project simultaneously. Developer A made some changes to the project and merged his changes to the original repository. Developer B also made some changes and committed his changes to the original repository? Does the MU environment preserve both Developer A and Developer B changes?     

A) YES, but check it.

4. In MUDE, Can two resources checkout the same project simultaneously?                

A) NO.

5. In MUDE, Can two Users checkout the same project simultaneously?   

A) Technically YES.

a. When the developer selects and saves the projects to a local repository file, the Administration Tool does not place a lock on the projects in the master repository on the shared network drive. b. Therefore, nothing physically prevents others from working on the same project. To determine if a project has been checked out, you need to look in the log file in the multiuser development directory on the shared network drive.

c. But not typically. If they do, they have to wait until the first USER checks in his changes before the other person can check-in.

6.If User1 will check out the RPD (Say A1) from Master RPD in his system after 10 Mins Is it possible to check out the same RPD(Say A1) from Master RPD by User2 in the same system...?

A) Technically YES. Every time you checkout a project, it will create a subset RPD on you local, which you will merge your changes locally before publishing the RPD back.

7. Two people (User 1 and User 2) are assigned to the same project inside MUD and opened the same project simultaneously. User 1 made some changes to the project and merged his changes to the original repository. User 2 also made some changes and commited his changes to the original repository? Does the MUD environment preserve both User 1 and User 2 changes?

A) As far as I know,I think as soon as USER1 checks out a project, the objects under the selected project will be checked-out and the other user will not be able to check-in until the USER1 checks in his changes back.

8. In MUDE, what happens if the physical tables are shared between different projects?

A) Projects are created based on the Presentation Catalog/Subject Area or the subset of logical fact tables related to the selected subject area and NOT the Physical tables. So you should be fine.

Usage Tracking

Usage Tracking:

1. Why would we go for usage tracking in OBIEE11G?

A) Business wants to know the top 'N' users using application.

2. Business wants to submit application usage to federal or local govt.

3. Admin or development team want to know the top 'N' reports taking longer time

ETC

4. By default user logs will be created in NQ query log file.

5. If we want insert these logs into a DB table then we need to go for usage tracking.

6. Once data is loaded into usage tracking table (S_NQ_ACCT) then we can develop usage friendly report.

*******7.Collects usage statistics on each logical Query submitted to the server********

2. What is a usage tracking table?

A) At the time of installation usage tracking table is created in DEV_BIPLATFORM to track the usage of the users.

3. What parameters must be enabled to set usage tracking on in OBIEE11G?

A) Usage tracking enabled—True.

a. DIRECTINSERT—True.

b. Connection pool—Provide respective names and click on apply.

c. Physical Table Name—Provide the value and click on apply.

Security

Security:

1. In how many ways security in OBIEE 11G can be divided?

A) Security in OBIEE 11G can be divided into two ways:

1. Authentication.

2. Authorization.

2. What is meant by Authentication?

A) Authentication is a process by which a system verifies (with a userID and password) that a user has necessary permissions and authorization to log on and access the data.

a. In simple words it is nothing but validating username and password which is called as authentication.

E.g.: Analytics application Username and password.

3. Which server authenticates each connection request it receives?

A) Oracle BI server authenticates each connection request it receives.

4. How many types’ authentications are available?

A) Authentications are two types:

1. LDAP

2. EXTERNAL TABLE

5. What is LDAP?

A) It is called as Lightweight Directory Access Protocol.

a. It is a integrated part of OBIEE11G.

b. Can be accessed using console.

6. What is external table authentication?

A) Validating username and password against external table is called External table authentication.

a. In any project first priority goes to LDAP authentication. If customer rejects LDAP then we will go to External table.

7. Do we need a dedicated connection pool while defining session initialization block?

A)Yes, we need a dedicated connection pool while defining session initialization block as one connection pool cannot sustain more tasks.

8. What is required for authentication option usage?

A) If we need to pass external table initialization block then just enable it.By doing which LDAP users will not get authorized to log in into the analytics page or any web based pages.

9. What is Edit Execution Precedence?

A) To maintain session initialization blocks execution order we will use it.

10. How can we create user in web catalog group?

A) LDAP user will automatically appear in web catalog.

a. External Table user will be created when ever user first time login into analytics application.

11. What is the use of creating catalog groups?

A) The catalog group is useful if we want to provide permissions to ‘n’ no of users.

a. we can map ‘n’ no of users to group and we can provide the permissions instead of each individual user.

b. Catalog group name and console group name should be same(It is the best practice).

c. Catalog groups are also useful to group ‘n’ no of users.

12. What is authorization?

A) Once a user login into the application, what can he access controlled by authorization.

13. How many types of authorization is present in OBIEE11G?

A)  Mainly two types of authorization is present in OBIEE11G

They are:

1. Object level

2. Data level or row level

14. Into how many types object level security is divided?

A) Object level security is divided into two types:

1. RPD level objects

2. Presentation catalog or web catalog objects

15. What are RPD level objects?

A) We can control below objects from presentation layer of RPD

a. Subject area

b. Presentation table

c. Presentation column

d. Hierarchy object

16. What are presentation catalog or web catalog objects?

A) We can control below objects of web catalog

a. Folder

b. Dashboard

c. Dashboard page

d. Section

e. Report

f. Saved filter

g. KPI

h. KPI watchlist

i. Score card …etc

17. How many types of permissions are there in OBIEE11G?

A) In OBIEE11G we have six types of permissions.

They are:

1. No access

2. Traverse

3. Open(Read+Traverse)

4. Modify(Read+Delete+Write+Rename+Traverse)

5. Full control(Modify+Permission)

6. Custom(New in OBIEE11G)

18. How can we provide section level permissions?

A) Open any dashboard then Click on section properties then Click on permissions.

19. What is meant by traverse?

A) This option will disable the navigation which is done with the help of action links.

20. What is meant by a privilege?

A) It can control inbuilt options of OBIEE tool using privileges.

Ex:Denying analysis option to BxxxDDR user.

21. Is it possible to set query limits in OBIEE11G?

A) Yes, it is possible to set a query limit to any defined number.

It can be done by following these steps:

a.    Open RPD in online mode.

Then go to manage then Identity manager.

a.    Double click on BxxxDDR user.

b. Click the permissions tab and then click on query limits.

22. Can we restrict the time period for which users can access specified repository resources?

A) Yes, We can restrict any user from accessing the specified repository in any specified period of time.

23. What is the difference between Data Level Security and Object Level Security?

A) Data level security controls the type and amount of data that you can see in a report. Object level security provides security for objects stored in the OBIEE web catalog like dashboards, dashboards pages, folder and reports.

24. How do you implement security using External Tables and LDAP?

A) Instead of storing user IDs and passwords in a OBIEE Server repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes. The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for Siebel Analytics Web users.

      Instead of storing user IDs and passwords in a OBIEE Server repository, you can have the OBIEE Server pass the user ID and password entered by the user to an LDAP(Lightweight Directory Access Protocol ) server for authentication. The server uses clear text passwords in LDAP authentication. Make sure your LDAP servers are setup to allow this.

25. Login slowness concern. What needs to be done?

A) Do Configurational Changes (instanceconfig.xml) to solve the issue.

26. Where are passwords for userid, LDAP, external table authentication stored respectively?

A) Passwords for user id are in OBIEE server repository LDAP authentication in LDAP server external database in a table in external database.

27. Can you bypass OBIEE server security? if so how?

A) Yes you can bypass by setting authentication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the 2 places.

28. Does OBIEE has two level of security?

A) Yes, first at the RPD level and second at the presentation services level.

29. Why OBIEE security is different compared to other BI tools?

A) Because it’s very flexible and can integrate into any existing security architecture an organization has built and reduces the need for one more layer of administration.