Monday 28 September 2015

The BISQLGroupProvider is not available in dropdown list.

Problem
While creating a BISQLGroupProvider Authentication Provider, you might not get it in dropdown list of Type.

The BISQLGroupProvider is not available in dropdown list.

Solution:
Please check the existence of BISecurityProviders.jar in location $MW_HOME/wlserver_10.3/server/lib/mbeantypes

If not, copy the file from $MW_HOME/ORACLE_HOME/bifoundation/security/providers to $MW_HOME/wlserver_10.3/server/lib/mbeantypes

Restart all services (AdminServer + bi_server + OPMN) to reflect the changes.

You are done.!! :-)

Wednesday 9 September 2015

Start/Stop OBIEE 11g services on Unix platform

Steps to start the OBIEE services on UNIX platform:
  1. Start node manager:
    Navigate to,
    /<MW_HOME>/wlserver_10.3/server/bin

    command,
    nohup sh ./startNodeManager.sh &
    To confirm whether nodemanager is up and running, you can check the log in nohup.out file, you can find this file at above location only.

    Note: nohup will start nodemanager in backend, so you will be able to use the existing terminal for starting AdminServer or any other activitites.

  2. Start the Administration Server:
    After confirming node manager is started, we need to start the AdminServer.

    Navigate to,
    MW_HOME/user_projects/domains/bifoundation_domain/bin

    command,
    ./startWebLogic.sh
    You need to provide Administrator user name and password here.

    Note: Do not close the terminal window later on, or the server will get shutdown.

  3. Start Managed Servers:
    Navigate to,
    MW_HOME/user_projects/domains/bifoundation_domain/bin

    command,
    ./startManagedWebLogic.sh bi_server1 http://<administration_server_ip_address>:7001
    You need to provide Administrator user name and password here.

  4. In the end, you need to start OPMN process.
    Navigate to,
    /<MW_HOME>/instances/instance1/bin

    command,
    ./opmnctl startall

    To check the status of OPMN processes,
    command,
    ./opmnctl status

Steps to stop the OBIEE services:
  1. Stop OPMN processes:
    First, you need to stop OPMN process.
    Navigate to,
    /<MW_HOME>/instances/instance1/bin
    ./opmnctl stopall

  2. Stop Managed Servers:
    Navigate to,
    MW_HOME/user_projects/domains/bifoundation_domain/bin
    ./stopManagedWebLogic.sh bi_server1 http://<administration_server_ip_address>:7001

    To confirm whether Managed Server is stopped, execute the below command..
    ps -ef|grep java|grep obiee|grep bi_server

  3. Stop the Administration Server:
  4. Navigate to,
    MW_HOME/user_projects/domains/bifoundation_domain/bin
    ./stopWebLogic.sh

    To confirm whether AdminServer is stopped, execute the below command..
    ps -ef|grep java|grep obiee|grep AdminServer

  5. Stop node manager:
    Just kill the nodemanager process, use the below command
    kill -9 <nodemanager_pid>

    To confirm whether nodemanager is stopped, execute the below command..
    ps -ef|grep java|grep obiee|grep nodemanager



Saturday 1 August 2015

OBIEE 12C - Upload Repository Commands - downloadrpd & uploadrpd

As we all know there are so many changes that come with OBIEE 12C bundle package. One of them is uploading a repository to the server and making it available to the queries.

Again, Windows only utility "The Administrator tool" lets edit the data model in "on-line" or "off-line" mode.

Online mode is strongly recommended when all changes that we make, immediately get saved to the uploaded RPD file and are available to users after a metadata refresh within OBIEE.
As far as, so much so same.

Unlike, in off-line mode we can edit an RPD file downloaded from the OBIEE server and the changes are not available in OBIEE until we re-upload the RPD file. This is where OBIEE 12c is very different from 11g in downloading and uploading a repository from/to the server resp.

In 11g we can just Copy/FTP the current RPD file down from the server, make changes and then using EM we would upload the same to the server. After a while (Activate Changes and all), we need to restart few OBIEE system components which we called OPMN.

But from 12c, we simply cannot Copy/FTP the current/online RPD down from the server and use it to further modification. The major change is, the Enterprise Manager no longer contains a deployment tab to upload a repository. Here, everything is moved to one command-line utility, data-model-cmd.sh

With this, Oracle provides the downloadrpd and uploadrpd commands for offline repository diagnostic and development purposes.

uploadrpd:
We use this command to upload the Oracle BI Repository in RPD format.
Note that, we cannot use this command to upload a repository composed of MDS XML documents.

downloadrpd:
To download an uploaded RPD file for editing, we use downloadrpd command.

We execute the utilities through a launcher script, data-model-cmd.sh on UNIX and data-model-cmd.cmd on Windows. We can find the launcher script at the following location:
Oracle_Home/user_projects/domains/bi/bitools/bin

Syntax:
downloadrpd -O <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Where,
O specifies the name of the repository that we want to download.

W is the repository's password. If we do not supply the password, then we will be prompted for the password when the command is run. For security purposes, Oracle recommends that we include a password in the command only if we are using automated scripting to run the command.

SI specifies the name of the service instance.

U specifies a valid user's name to be used for Oracle BI EE authentication.

P specifies the password corresponding to the user's name that we specified for U. If we do not supply the password, then we will be prompted for the password when the command is run. For security purposes, Oracle recommends that we include a password in the command only if we are using automated scripting to run the command.

S specifies the Oracle BI EE host name. Only include this option when we are running the command from a client installation.

N specifies the Oracle BI EE port number. Only include this option when we are running the command from a client installation.

SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when we are running the command from a client installation.

H displays the usage information and exits the command.

To download a repository:
./data-model-cmd.sh downloadrpd -O /home/oracle/share/FedEx.rpd -SI ssi -U weblogic -P Welcome1

Upon running, it prompts to enter a password for this RPD file.
It doesn’t ask to confirm this, so make sure we know what we entered or else you’ll need to re- run the command to download it again!

Now we can FTP this (newly created file) to local system and open it in the Administrator tool. Once done with the changes we want, we can upload it using the below command line utility.

./data-model-cmd.sh uploadrpd -I /home/oracle/share/FedEx.rpd -SI ssi -U weblogic -P Welcome1

Where,
I - specifies the name of the repository that we want to upload.
Here, are prompted to enter the RPD password, the same one we entered when we downloaded it.

Now we get to know that we have saved our valuable (!) time in “Activating the Changes” and restarting our favorite (!!) OPMN.

Here with 12C, we can directly use the newly uploaded repository without restart of any services. Happy enough?? Wait, there's something waiting for you. Somewhat new you can say.

Before trying all this on your own, let me tell you that we can’t FTP the uploaded RPD file from server or the default repository location and open it in the Administrator tool thinking that you now know the password..
Obviously we know it, we entered it when uploading it, right? No, it's wrong; it changes the password during the upload. This is Oracle’s Intelligence. ;-)

So, it was the quick overview of how to download/upload the repository in OBIEE 12C. It might seem a little complex to start with, but once you know the data-model-cmd.sh command (and scripted it for your environment, which I will certainly do!), it’s a big improvement over the 11g method of deployment RPD files.

References:
http://www.redstk.com/
https://docs.oracle.com

Friday 31 July 2015

Rename Apply / Reset button in OBIEE prompts

If you ever wondered to change or rename Apply / Reset buttons on OBIEE dasboard prompts, here's the way.

Go to the following path on your box,

C:\Oracle\Middleware\Oracle_BI1\bifoundation\web\msgdb\l_en\messages

At this location, you will get all XML files that OBIEE uses to throw messages/errors that in human readable format. For instnace, pick the file viewmessages.xml

Here you wil get all the messages or defauls strings that we see in day-to-day things (like Apply, Reset, Reset, ViewDisplayError, NoRows, etc) in OBIEE.

Change the below tag for renaming Apply button,

Default one:
<WebMessage name="kmsgEVCPromptApply"><HTML>Apply</HTML></WebMessage>

Change it to,
<WebMessage name="kmsgEVCPromptApply"><HTML>Go</HTML></WebMessage>

That's it. Just bounce your services to load all the modified configuration files. You in with Go..!!


Happy stay.!! :-)

Hide BreadCrumbs in OBIEE 11g

There might be a requirment like, you need to hide breadcrumbs on your dashboard or reports. To achieve this, obviously you need deal with some css files. Here we go

There are files common.css you can find on below mentioned paths.
Location:
C:\Oracle\Middleware\Oracle_BI1\bifoundation\web\appv2\res\s_FusionFX\b_mozilla_4\common.css

From below path, select the skin you are using.
C:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res

My skin is, s_blafp so my path would be,
C:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp\b_mozilla_4

Find .breadcrumb tag thoughout this file and add "display:none;" (without the quotes) (You probably have total 11 breadcrumb tags in this file) :-P

For sample, one of your tag will look like this,

.BreadCrumbDiv{width:100%;border:none;background-color:#DEEAF1;margin:-7px 0 0 0;height:22px;display:none;}


PS: Changing a CSS files in the default OBIEE folder, is NOT a recommended method at all. Instead create a Custom StyleSkin from existing one and you are ready to make plenty of all possible changes throughout your CSS world. If gets failed, switch back to defaul one. ;-)

The reason this is not recommended is,

You change a CSS file in the default OBIEE folder, you are happy with your changes, that's fine.
4 months later somebody patch your OBIEE to fix some bugs or security issues and surprise: your changes disappeared, they are gone.

So you go back and change the CSS file again after having your users complaining with you first that things changed. 3 weeks later another patch is applied and guess what? Again, you lost your changed and you have to apply all these things again.

Now, if you only set the "display:none" for a single class or ID it's 1 line to change, but as you started in that way you will probably change other things, in the end you have a list of 10-20 things to change. The first time you still remember everything, the second time you forget to change 3 lines of the 20, the third time only half of the changes etc.

Happy bloggin..!!

Wednesday 17 June 2015

New features in OBIEE 11.1.1.9 (Release 1)

Oracle BI EE 11g Release 1 (11.1.1.9) is released in this May and is now available for download. Following are some key highlights of this new release. If I have missed anything, please feel free to add it through comments.
  1. Contribution wheels have been renamed to strategy contribution wheels.
  2. A new view type named Tree map. (since OBIEE 11.1.1.7.10)
  3. Initiate a search within the subject areas that are displayed in the Subject Areas pane.
  4. Save columns to the Oracle BI Presentation Catalog for reuse in other analyses.
  5. Improved Aggregate Persistence wizard to generate the aggregates with levels auto-correct, etc
  6. HTML5 output is supported and can be configured as default output format. So flash limits will not stop your analysis on any devices.
  7. NOT supported on 32-bits Operating Systems.
  8. When you export a column that includes an image to Excel, the extra blank column that previously appeared will no longer appear.
  9. Other enhancements to the Export Functionality:
    • Orientation component to specify the orientation for PDF and Powerpoint.
    • Options to specify whether to include charts, images and formatting.
    • Option to specify whether to wrap text in columns.
    • Option to limit column width.
    • The default value for the Print Rows component has changed to All.
  10. Enhancements to Dashboards:
    • Aability to create custom export layouts for exporting dashboard content to Excel.
    • A new style named Skyros, similar to Oracle BI Cloud Service (since OBIEE 11.1.1.7.10)
    • Ability to delay the execution of dashboard pages. (since OBIEE 11.1.1.7.10)
The detailed features can be find out @,
https://docs.oracle.com/middleware/11119/biee/BIEUG/whatsnew.htm#BIEUG13418

Download @:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bi-downloads-2537285.html

Documentation @:
https://docs.oracle.com/middleware/11119/bisuite/index.html

System Certification Matrix:
http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

Tuesday 24 March 2015

OBIEE 11g Interview Questions & Answers.

1. Tell me your experience in OBIEE, along with your roles & responsibility in projects?
  • I'm bla bla bla.!! I'm working for bla blah company from this much of time period as an OBIEE consultant/developer. So far, I have been involved in multiple implementations of OBIEE 11g as well as 10g also (If you so.!!). The main domains I have worked on are this & this. (Tell only the domains on which you have worked on, e.g. Health Care, Data Mining, and Retail). Apart from this, I have some exposure on OBI Apps installations also.
  • Here, even you can mention like on how many projects you have implemented from the scratch. If you have BI Publisher experience for any particular project, mention it along with the project name/domain.

2. What is Business Intelligence?
  • Business Intelligence, on the other hand, is simply the art and science of presenting historical data in a meaningful way (often by using different data visualization techniques).
  • Raw data stored in databases turns into valuable information through the implementation of Business Intelligence processes.

3. What is OBIEE?
  • OBIEE is an application that implements the concept of business intelligence by the means of slicing & dicing the huge volumes of data across numbers of data sources, and as a result, it creates a platform of business information that enables users to query valuable information as a web application which a centralized and highly interactive.
  • From  a pure technical perspective, OBIEE utilizes Kimball data modelling philosophy in its implementation that enables users to analyse and monitor their business information through the uses of reports, charts, alerts, etc.

4. What are your roles and responsibilities in your current project?
  • As we know, OBIEE is typically the 'front end' of data warehouse process since most of the data that OBIEE deals with come from analytic database, I worked on designing schema for a data warehouse.
  • As an OBIEE developer, the responsibilities like repository configuration and reports generation based on the client requirements.
  • At the end, we need to implement the security
  • Here, you can also mention out of box utilities if you had any like External Table Utilization, BI bursting, etc.

5. Can you explain the architecture of OBIEE?

You can follow the following link to answer this.
http://obieenow.blogspot.in/2013/05/obiee-architecture-and-its-components.html


6. Explain about OBIEE Repository (rpd) three layer architecture?
  • Physical Layer: The Physical layer defines the data sources to which Oracle BI Server submits queries. It also defines the relationships between physical databases and other data sources that are used to process multiple data source queries.
  • Business Model and Mapping Layer: The Business Model and Mapping (BMM) layer of the Oracle BI repository defines the business―or logical―model of the data and specifies the mapping between the business model and the Physical layer schemas. Business models are always dimensional, unlike objects in the Physical layer, which reflect the organization of the data sources. The BMM layer can contain one or more business models. Each business model contains logical tables, columns, and joins.
  • Presentation Layer: Provides a way to present a customized view of a business model to users. It exposes only the data that is meaningful to users. Here, we can organize the data in a way that aligns with the way users think about the data.

7. What is connection Pool in OBIEE?
The connection pool is an object in the Physical layer that describes access to the data source. It contains information about the connection between the Oracle BI Server and that data source.
The Physical layer in the Administration Tool contains at least one connection pool for each database. When you create the Physical layer by importing a schema for a data source, the connection pool is created automatically. You can configure multiple connection pools for a database. Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database.

8. What is the difference between OBIEE 10g & 11g?
  • OBIEE 11g uses weblogic server as an application server where as OBIEE 10g used OC4J.
  • A new column called the hierarchical column is introduced.
  • We have only one join. i.e. New Join
  • Session variables initialize when they are actually used in OBIEE 11g unlike OBIEE 10g where they were initialized as soon as a user logs in.
  • OBIEE 11g now supports Ragged and Skipped hierarchy.
  • Introduced Parent-Child hierarchy as well.
  • In OBIEE 11g, there are some changes in terminology as well.
    • iBots becomes Agents.
    • Answers renamed as Analysis.
    • Charts renamed as Graphs.
  • As per the Security point of view, there are many changes in OBIEE 11g implementation. Some of like,
Security Task/Object
OBIEE 10g
OBIEE 11g
Define Users and Groups in RPD file using OBIEE Admin Tool
Default
By default, users are defined in embedded WLS LDAP via FMW EM Console, or alternatively, in external LDAP.
Defining security policies
Policies in the catalog and repository can be defined to reference groups within a directory.
Policies are defined in terms of application roles, which map to users and groups in a directory.
"Administrator" user
Unique user with full administrative privileges.
No single user named nor full administrative privileges. Administration can be performed by any user who is member of BIAdministrators group.
Repository Encryption
Available on sensitive elements only - i.e. user passwords, connection pool passwords, etc.
Entire RPD encrypted via a password.
“Everyone” Presentation Server Group
Default
Replaced with AuthenticatedUser role.


9. Have you ever worked with ETL-tools?
As an OBIEE developer, I know the flow of ETL process. I have been involved in designing a data model, but I never worked on that. The ETL people take care of everything.

10. Explain OBIEE Application Development Life-Cycle.

There are 4 phases in the life-cycle
1. Requirement gathering.
Business analyst (BA) who is the mediator between the client and the organization. 
  • 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.
  • GAP Analysis: This is the comparison 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 work and prepares a sample model which is going to send for the client’s approval. 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 data warehouse from the heterogeneous data sources.
  • Reporting development: Once the DWH is built, the reporters will configure the repository and generate the reports as per the client’s requirement.
3. Testing: 
Testing 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 organization 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 needs to give the application support for one month at least. After that based on the client’s decision, the development company or some other company will give the production support.

11. What is star and snow-flake schema?
Star schema is the simplest kind of schema where one fact table is present in the centre of the schema surrounded by multiple dimension tables.
In a Star schema design, any information can be obtained just by traversing a single join, which means this type of schema will be ideal for information retrieval.
Snow flake schema is just like star schema but the difference is, here one or more dimension tables are connected with central fact table as well as with the other dimension table. Snowflake schema requires one more join (to connect one more table) to retrieve the same information. This is why snowflake schema is not good performance wise.
12. Why do we use snowflake schema then?  
The reason we do it is, suppose we have another fact table with granularity store, food type and day. This fact will use the key of "type" dimension table instead of "food" dimension table. Unless you have this dimension table in your schema, you won't get the "type" key. This is the reason we need to snowflake the "food" dimension to "type" dimension.

13. What are OLTP and OLAP? Difference?
  • On-Line Transaction Processing: It describes processing of short and simple transaction data at operational sites i.e. day to day operations in the Source systems. The Database is designed as Application-oriented (E-R based) i.e. Highly Normalized so as to efficiently support INSERT and UPDATE operations. Data stored in these systems are raw Current (Up-to-date) and Isolated Data, in a much detailed level in flat relational tables.
  • On-Line Analytical Processing: It describes processing at the Centralized, Integrated and Consistent Data Warehouse. It acts as the Decision Support System for the Business End Users. The Database is designed as Star/Snowflake Schema i.e. highly de-normalized to support the SELECT operations. Data in these systems are generally Consolidated, Summarized and Historical Data in nature.


14. What is fact and dimension table in data warehousing?
A fact table holds the data to be analysed, and a dimension table stores data about the ways in which the data in the fact table can be analysed. Thus, the fact table consists of two types of columns. The foreign keys column allows joins with dimension tables, and the measures columns contain the data that is being analysed.

15. If you forget administrator password to access your repository, what corrective actions you would be taking? 
  • Start your weblogic server.
  • Go to C:\Middleware\oracle_common\common\bin
  • Run wlst.cmd command
  • wls:/offline> connect("weblogic","password","your_server_name:7001")
  • listCred("oracle.bi.enterprise","repository.SampleAppLite_BI0001")

16. Have you used Time Series functions in OBIEE?
Yes. There are three time series function in OBIEE. These are AGO, TODATE, PERIODROLLING. Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods.
  • AGO - Calculates aggregated value as of some time period shifted from the current time
  • TODATE - Aggregates a measure attribute from the beginning of a specified time period to the currently displayed time
  • PERIODROLLING - Performs an aggregation across a specified set of query grain periods, rather than within a fixed time series grain.

17. What are aggregation tables in OBIEE?
As we know, data in fact and dimension sources is stored at the lowest level of detail and that data often needs to be rolled up or summarized during analysis. This can lead to the performance issues if there's a huge amount of data or any run time calculations. So, this aggregate table is a popular technique for speeding up response time. Aggregate tables store pre-computed measures that have been aggregated over a set of dimensional attributes.

18. What is multiple LTS?
Logical tables have a Sources folder that contains one or more logical table sources. These logical table sources define the mappings between the logical tables in the business model and the physical tables in the Physical layer. 

19. What is query repository tool?
  • It is utility of Seibel/OBIEE Admin tool
  • Allows you to examine the repository metadata tool
  • For example: search for objects based on name, type.
  • Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer

20. Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?
An opaque view is a physical layer table that consists of select statement. An opaque view should be used only if there is no other solution.
21. Why do we have multiple LTS in BMM layer? What is the purpose?
To improve performance and query response time.
22. How do I disable cache for only a particular table?
In the physical layer, select that particular table. Open Properties of that table. Un-check the cacheble option.
23. What is Authentication and Authorization?
Authentication is process of confirming whether the user is a valid user or not. Is he part of this company? Is he an employee of our external suppliers?
Authorization is process of giving access to different pieces of the OBIEE. One user “User A” can access dashboards, can create iBots, can schedule reports and can do administrative tasks. Another user “User B” can see only part of what “User A” has access to and “User B” has access to some other tabs of the dashboard that “User A” does not. 
Types of Authentication in OBIEE:
  • LDAP authentication
  • Database authentication
  • External table authentication
  • Operating system authentication 

24. External Table Authentication? Where does this come into play? 
You can choose to maintain lists of users and their passwords in an external database rather than in the repository. An external database table consisting of user login information has been provided so that you can import this information into the repository and use it to authenticate users during login. The table contains a list of users, their logins and passwords, and the group they belong to. Optionally, the table can also contain the logging level for each user, Web interface information, and the names of specific database catalogs or schema to use for each user when querying data. After this information has successfully been imported, you need to create an initialization block that retrieves this data. This also helps in rpd migration and deployment across multiple environments. 

 25. What is object level security?
There are two types of object level security: Repository level and Web level.
  • Repository level: In presentation layer, we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column. 
  • Web level: this provides security for objects stored in the Siebel analytics web catalog, such as dashboards, dashboards pages, folder, and reports you can only view the objects for which you are authorized. For example, a mid level manager may not be granted access to a dashboard containing summary information for an entire department.

 26. What is data level security?
This controls the type and amount of data that you can see in a report. When multiple users run the same report the results that are returned to each depend on their access rights and roles in the organization. For example a sales vice president sees results for all regions, while a sales representative for a particular region sees only data for that region.

27. What is the difference between Data Level Security and Object Level Security?
  • 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 Siebel analytics web catalog, like dashboards, dashboards   pages, folder, and reports.

28. How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?
Dimension tables contain descriptions that data analysts use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and year values. Every table contains a primary key that consists of one or more columns; each row in a table is uniquely identified by its primary-key value or values.

29. What is variable in OBIEE?
You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment.
There are two classes of variables:
  • A repository variable has a single value at any point in time. There are two types of repository variables: static and dynamic. Repository variables are represented by a question-mark icon (?).
  • Session variables are created and assigned a value when each user logs on. There are two types of session variables: system and non-system. System and non-system variables are represented by a question-mark icon (?).
Initialization blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables. The icon for an initialization block is a cube.
30. Explain default Security Settings for Oracle BI 11g?
There are changes to how security is managed in Oracle BI 11g. Security is now managed by using WebLogic Administration Console and Fusion Middleware Control.
During installation, three Oracle Business Intelligence security controls are preconfigured with initial values to form the default security model. The security controls include:
  • An embedded directory server functioning as an identity store designed to hold all user and group definitions, required to control authentication
  • A file-based policy store designed to hold the application role and permission grant mappings to users and groups, required to control authorization
  • A file-based credential store designed to hold all user and system credentials, required to control authentication or authorization.

31. What are the new features in OBIEE 11.1.1.7.0?
  • Enhancement to Dashboards
    • The new default style is FusionFX. 
    • The ability to export an entire dashboard or a single dashboard page to Microsoft Excel 2007+.
  • Enhancements to Graphs
    • Waterfall graph. A waterfall graph lets you visualize how a value increases or decreases sequentially and cumulatively. 
    • Stacked graph as a new subtype to the area graph.
    • 100% stacked graph as subtypes to the bar graph and the area graph. A 100% stacked graph, like the stacked graph subtype, lets you compare the parts to the whole. But while the stacked graph shows cumulative total in the units of the measure, the 100% stacked graph always shows units as percentages of the total, and the axis scale is always zero to 100 percent.
    • Zoom to data range
    • Hide sliders in graph views that listen to master-detail events.
  • Enhancements to Views
    • A new view type named performance tile. This view type displays a single aggregate measure value in a manner that is both visually simple and prominent.
  • Enhancements to Prompts
    • Limit values by

We are done with your interview, our HR will get back to you. Thanks..!! ;-)


Monday 23 March 2015

Purge All Cache OBIEE 11g using Analysis Issue SQL

1. Open Analysis page.

2. Go to Administration tab. (You should logged in with Administrator privileges)

3. Click Issue SQL.

4. Call the below command & hit Issue SQL.

    Call SAPurgeAllCache();

Use following screenshot to see output of issued SQL.


Happy blogging.!! :-)

Sunday 15 March 2015

OBIEE 11g – Lookup Tables – Sparse and Dense Lookups

Lookup table are mainly used for,

1. Resolve code columns and get description/name values from a lookup table.
2. Currency conversion.

There are 2 types of lookup tables.

1. Sparse Lookups – A sparse lookup basically means that the main driving table does not necessarily have corresponding lookup values in the lookup table for all the id values. This can be considered to be an equivalent of a Left Outer Join.

2. Dense Lookups – A dense lookup basically means that the main driving table will have matching lookup values in the lookup table for each of its unique id value. This can be considered to be an equivalent of an inner join.

In short, we can summarise all this in one equation,

Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup = Outer join (with Nulls having a custom name) Dimension and lookup table

To illustrate this in more detail,

Consider the following schema, PLAYERS & TEAM_LKP. You can download this schema from this link.
  • PLAYERS table contains all the information of players with TEAM_ID being the unique primary key.
  • TEAM_LKP has Team information with TEAM_ID being the primary key. 

The main difference between these 2 tables is, not all players in the main PLAYERS table have a corresponding team assigned in TEAM_LKP table.

1. Import those two corresponding table in Physical layer of your repository.

2. It is mandatory to define a table as a Lookup table, you must define a primary key for that particular table.

3. Take the lookup table to the BMM layer. Here, you will notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.

4. Double click on TEAM_LKP (Here in ref, Lookup table) logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

5. Now, in the Business Model and Mapping layer, lets create a two columns called TEAM_DENSE & TEAM_SPARSE. After that one, lets go to the LTS mapping and apply the following the function for each of them resp. Both column should be derived from, "Derived from existing columns using an expression".

Note: If you have more than one column as a primary key, the order of columns used in the key should match with the column order in the Lookup function.

1. TEAM_DENSE:

Formula:  
LOOKUP( DENSE  "Loopkup Example"."TEAM_LKP"."TEAM" , "Loopkup Example"."PLAYERS"."TEAM_ID")


2. TEAM_SPARSE:
Formula:
 LOOKUP( SPARSE  "Loopkup Example"."TEAM_LKP"."TEAM" , 'Team Not Assigned', "Loopkup Example"."PLAYERS"."TEAM_ID")

6. We now have the 2 lookup columns in the PLAYERS dimension as follows:

7. Take the newly created columns to the presentation layer into the customer dimension display folder and save your work. Check consistency of your repository.

8. Create a sample analysis as follows and verify the result.

9. Notice that, you are getting only those players information whose correspondence value are present in Lookup table i.e TEAM_LKP.

By seeing query log, you can get to know that BI server is applying inner join between TEAM_LKP & PLAYERS table. So, we are not getting any records for Mario & Naymar.

10. Next we create a report with the “SPARSE TEAM”.

11. Notice that, you are getting all players information. The result will be as follows. Notice that, 'TEAM_ID' doesn’t have a corresponding lookup value in TEAM_LKP lookup table and we see the string “Team not assigned”.

Happy blogging. :-)

Friday 27 February 2015

Importing data from Essbase cubes.

While using Oracle Essbase as your data source in OBIEE, there are some additional things that you need to configure before proceeding.

My previous article about "Setting Up Essbase Data Sources" will help to do this.

When you are done with this configuration steps, you are good to go with handling Essbase data source with OBIEE. But there's a problem if you don't know what are default port on which your Essbase data source is installed. If everything is installed on the default ports by your Admin, you are lucky enough; but unfortunately that's not the truth always. If it is not, you might get the following error message,

Essbase Error: 1030818: Login failed. Please check if server and port are correct. If you received timeout or handshake failure, please check if you tried to connect to secure port without secure keyword or clear port with secure keyword.


To get on which port your essbase can be accessible, you can follow the below steps.
  • Open you easconsole
  • http://Ess_Server:9704/easconsole
  • Launch application.
  • Put the credentials to connect your Essbase server.
  • Expand Essbase servers.
  • Select your particular Essbase server where your cubes are residing. (In my case, EssbaseCluster-1)

  • Right click and go to Edit > Properties.
  • Move to Environment tab.

You can use this AGENT PORT at the time of importing your cubes in OBIEE. Refer the following screenshot,

Happy stay.. :-)

Tuesday 17 February 2015

Admintool could not load Essbase driver. Please check the configuration.

Problem:

On Windows systems, when we install Administration Tool from "client-only" installation, it can throw an error while import Essbase data source.

"Admintool could not load Essbase driver. Please check the configuration"


Cause:

The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. You must ensure that the Essbase client libraries are installed on the computer running the Oracle BI Server before you can set up a connection to or import metadata from Essbase data sources. You also need to ensure that the Essbase client libraries are installed on any computer where you want to run the Administration Tool.

After you verify that the Essbase client libraries are installed on the appropriate computers, you must ensure that the PATH environment variable on each computer includes the location of the Essbase client driver (for example, EPM_ORACLE_HOME/products/Essbase/EssbaseClient)

You also need to ensure that an additional environment variable is set appropriately where Admin tool is installed.

Because, other system processes need the information contained in the environment variables set for the Essbase client libraries. The following sections provide additional information:

Solution:

On Windows systems, to enable connectivity, you need to update bi-init.cmd on each computer running the Administration Tool.

To add Essbase environment variables to bi-init.cmd:

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin

Add a line setting the ESSBASEPATH or ARBORPATH environment variable, as appropriate for your version and installed location of the Essbase client libraries. 
For example:

set ORACLE_HOME=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome
set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst
set ORACLE_BI_APPLICATION=coreapplication
set ESSBASEPATH=C:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0
set ARBORPATH=C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

Locate the line that sets the PATH environment variable, and add the location of the Essbase client binaries.
For example:

set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;%ESSBASEPATH%\bin;%ARBORPATH%\bin;%PATH%

Save and close the file.

Now you can open your Admin Tool & can try to import Essbase cubes,


Credits:
Tarun Kumar Swami. :-)