OBIEE - Oracle Business Intelligence Enterprise Edition

OBIEE(Oracle Business Intelligence Enterprise Edition)

OBIEE is ‘Oracle Business Intelligence Enterprise Edition’ and it is a tool which is used to develop enterprise wide dashboards/reports. So, in simple words it is a report generation tool. OBIEE is previous called as Seibel Analytic tool before it is acquired by Oracle that now it is called OBIEE tool.

Some of the main OBIEE tools are 
Oracle BI Server, 
Answers, 
BI Publisher, 
Interactive Dashboards, 
Oracle BI Administration, 
Delivers (for scheduling), etc.

Meta data repository i.e. RPD (Rapid File database, .rpd) file is the main one on OBIEE; it can be developed by using 3 layers (Physical/Business/Presentation layers) in ADMIN tool.

Oracle BI Administrator tool has 3 layers;

1      1. Physical Layer

2     2. Business Model and Mapping

3     3. Presentation Layer.

Physical Layer: We can import the Facts (_F) (T) and Dimensions (_D) (M) with constraints into Physical Layer by using Connection Pool.

Business Layer: We can develop multiple dimensions (Drill-down hierarchies) & Logical & Facts measures objects into it.

Presentation Layer: The Presentation layer provides a way to present customized, secure, role-based views of a business model to users. Presentation layer views are called subject areas (formerly called presentation catalogs). Example is Product/Market catalog.

Oracle BI Server: is a stand-alone process that maintains the logical data model which it provides to BI Presentation Services through ODBC. high-impact query, reporting and analysis server that provides its services to the other components of OBIEE such as Answers, Dashboards etc. an analysis server providing a calculation and aggregation engine that integrates data from multiple relational, unstructured, OLAP, and other sources.

Oracle BI Answers (BI Webpage):  Provides answers to business questions, Allows us to explore and interact with information. An ad hoc query and analysis tool that processes the data from multiple data sources in a pure web environment. Present and visualize information using charts, pivot tables, and reports.

Answers will have Catalogs (My folders/Shared Folders/My filters/Shared Filters), Dashboards & Subject Areas. In my folder, we can create a new request by selecting the Subject area (tied with Presentation layer in OBIEE Admin tool).

We can create Adhoc reports with just SQL Query in Answers by selecting ‘Direct Database Request’ by passing the connection pool/SQL statement.

Oracle BI Interactive Dashboards (BI Webpage): Provide personalized views of corporate and external information. Display required information to users for their decision making. Can aggregate content from other sources like Internet, shared file servers, and document repositories? Access to the information is interactive, based on the individual's role and identity. The end user works with live reports, charts, tables, prompts, pivot tables, and graphics and has full ability to modify and interact with results.

BI Publisher originated as a Java application called "XML Publisher" xmlp) embedded within Oracle's E-Business Suite. XML Publisher has been an integral component within E-Business Suite to deliver highly refined PDF-formatted output of raw transactional data, primarily for hard-copy printing.

In its incarnation as Business Intelligence Publisher (BIP), it is essentially the same application with several powerful new features. Like XML Publisher, BIP is a XML transformation engine that applies XSLT templates (created from user-generated templates in RTF format) against raw data to produce reports with highly customized visual layouts in a variety of output formats.

XMLP differs from BIP in two significant ways:

First, BIP has much greater flexibility using a variety of template sources, output formats, delivery methods and scheduling options.

Second, and more importantly, BIP is not restricted to E-Business Suite as a data source - in fact it has been expanded to interface with a wide variety of applications, including JDBC databases, Web services, Hyperion and OBIEE.

High-level perspective, BI Publisher...

• Transforms raw data from various sources (JDBC, Webservices, OBIEE, etc) from word/excel/acrobat layout design into HTML, PDF, XML, CSV file formats and delivers via Email/FTP/FAX/HTTP.

Assumptions for this discussion:

• BIP was correctly installed and configured onto a demo system during a full OBIEE installation process (full installation & configuration of BIP can be tricky, but is out of scope for this discussion - see Oracle's documentation for more info)

• OBIEE on this demo system is serving the demo "Sample Sales" repository

For Creating New Reports:

1.Got to -> New ->Analysis you will have your Presentation Tables under Select Subject Area
Click on your Subject Area -> you'll get list of tables under the Subject Area
2. Expand the Folders and click the required column.
3. Click on Result tab - > you'll get Title and Table View
4. Format your Table view and Title View
5. Save your New Report in Shared Folders (You may create your own folder under Shared folder)

View the Reports on Dashboard.

1. Goto -> New ->Dashboard -> you will have New Dashboard Window
Name - Give the name of your Dashboard
Select Location - /Shared Folders/Standard Dashboard/
Click Ok -> you'll get Empty Dashboard window.
2. Edit the Dashboard -> Drag and Drop your Saved reports from Shard Folder under Catalog
3. Click on save button (Top Right) and save your dashboard -> click run [Green right Arrow (Top Right)].
4. Now you'll have your report in your dashboard page.
Rename Dashboard Page Edit the Dashboard - > Dashboard Properties
-> You'll have the Dashboard Properties window -> Select the Dashboard Page -> Rename

Prompts (Parameters):

1.Got to -> New ->Dashboard Prompt-> you will have your Presentation Tables under Select Subject Area Click on your Subject Area -> you'll get the Untitled Prompt Window
-> Click + symbol -> Column Prompts ->you'll get list of tables
2. Expand the Folders and click the required columns from the list (the Columns should be your Report's table).
3. Save your New Prompts in Shared Folders.

Note: The Prompt columns should be Is Prompted in your Reports Filters.

-------------------------------------------------------------------------------------------------------------------------------

1. Architecture of OBIEE contains Presentation Service (Web) and 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. To get the SQL from OBIEE Reports à a. Modify the request and click advanced in that you get xml code and also the actual SQL. Or .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. Or by clicking Administration->Manage sessions-> view SQL.

3. How will you do sort in Reports in OBIEE Answers: 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? By clicking modify request and Narrative View and by giving @1 for the first column result and @2 for the 2nd column and so on and we can also give a heading for No Results by clicking the Narrative view.

5. To create Interactive Dashboards: 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:-In Answer Reports, you can give a column as updateable and then view the reports, this option is called write back.

7. How will you execute Direct SQL in OBIEE:-By clicking Direct Database Request below the subject area in Answers.

8 .To create a report from two subject areas:- 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:- for the RPD use the Merge option in Admin Tool and for dashboards and reports use Content Accelerator Framework.

10. There are two types of variables in OBIEE: 1. Repository variable (for the whole repository) 2.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.To enable or disable caching in the system level and table level:-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 and then merge the repository with the current working one.

12. How will you go about adding additional column to the repository in the presentation layer :- 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 affect the changes for a report, if for certain users only the column heading in the report should be changed:-using session variables for that user.

14. What is a table alias:- 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. We can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. It 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. Level-base metrics 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. Different layers of OBIEE Repository: - 1.Physical Layer 2.Business Model 3. Presentation Layer.

18. Authentication is the process by which a system verifies, through the use of a user a. Operating system authentication b. External table authentication c. Database authentication d. LDAP authentication

19. What are the different types of security you have worked in obiee: - Object Level and data level

20. Bridge table:- To connect two tables where there is no relationship you can use a third bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.

21. If OBIEE reporting is slow then:- 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:-NQServer.log, NQQuery.log, and NQSAdminTool.log— mainly.25.Name few Configuration files in OBIEE.

23. Key Configuration Files in OBIEE: - NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

24. To change port of OBIEE answers: - by changing its port in instanceconfig.xml

25. To Scale up the performance by clustering and distributing the services across the multiple servers, we can use ClusterServices.

26. Can we Change the location of the OBIEE Repository: - 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:- 16

28. Can you run multiple rpd’s in a Single OBIEE Instance: - No . Create another instance in the same server.

29. To implement the security in OBIEE, use security Manager in OBIEE Admin Tool, by creating user groups.

30. 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 dimension.

31. Star Schema: One fact and many dimensions. Snowflake is one fact and many dimensions with relations.

32. Start/Stop OBIEE server on Linux:

./run-sa.sh start #starts obi server

./run-saw.sh start #start saw server

./run-sch.sh start #start scheduler

-------------------------------------------------------------------------------------------------------------------------------