OBIEE10G & 11G Knowledge Sharing

Single Prompt Switching Multiple Reports in OBIEE 11G using Presentation Variables

Single Prompt Switching Multiple Reports in OBIEE 11G using Presentation Variables:

What is Dashboard Prompt?

A prompt that is created at the dashboard level is called a dashboard prompt because the prompt is created outside of a specific dashboard and is stored in the catalog as an object, which can then be added to any dashboard or dashboard page that contains the columns that are specified in the prompt.
A dashboard prompt can be a column prompt, variable prompt, image prompt, or currency prompt.
Dashboard prompts are reusable, because you can create one prompt and use it many times.
When the prompt object is updated and saved, those updates are immediately displayed in all dashboards where the prompt is used.
A dashboard prompt is a specific kind of filter that, when created, saved, and applied to a dashboard or dashboard pages, can filter all or some of the analyses that are embedded in a dashboard or analyses that are embedded on the same dashboard page.
A dashboard prompt is interactive and will always be displayed on the dashboard page so that the user can prompt for different values without having to re-run the dashboard.
Users can create and save dashboard prompts to either a private folder or to a shared folder.

In this blog i am going to share how a single prompt functions with Multiple reports using Variables.

I have used 4 reports which I want to switch using dashboard prompt which are MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS . Here is an example along with the steps:

1. Create a Dashboard Prompt with the intended reports as a data value (MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS) to be selected.

2. Click on New->Dashboard Prompt->select any subject area. Note: It will not affect on our dashboard prompt as we have to create presentation variable of our choice.

3. Definition Pane for dashboard prompt will open. Now select "Variable Prompt" as below.

4. Now "New Prompt" dialogue will open. Create presentation variable as VARIABLE 1,label it as SELECT REPORT and select User Input as Choice List.

5. In Choice list Values click on plus (Green Icon) sign; it will display "Enter New Value" dialogue. Now manually enter the names of the reports which you want to display in prompt. Here I will put my four report names which I want to display as a list in dashboard prompt which are MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS as below :

6. Now expand the Options. In "Default Selection" you can give any report name which you want to display by default on dashboard by selecting that report name as "Specific Custom Value". Here I have given "MASSACHUSETTS" as my default selection.

7. Now save your dashboard prompt. In this way our dashboard prompt is ready from selecting reports which will look like :

8. Create reports which you want to display according to selected report name in dashboard prompt.

9. Now I will create four reports named MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS and will apply some filters in theses individual reports which will allow me to show the intended report as per selection made in dashboard prompt. So let's create "MASSACHUSETTS"

10. I am taking some columns in reports plus one dummy column. I have change the column formula of Dummy column as:

CASE '@{VARIABLE 1}' WHEN 'MASSACHUSETTS' THEN '1' ELSE '0' END

11. Now I have applied filter on Dummy Column as "is not equal to /is not in" '0'.

12. Repeat the same process for rest of the three reports New York, California and Texas

CASE '@{VARIABLE 1}' WHEN 'NEW YORK' THEN '1' ELSE '0' END

CASE '@{VARIABLE 1}' WHEN 'CALIFORNIA' THEN '1' ELSE '0' END

CASE '@{VARIABLE 1}' WHEN 'TEXAS' THEN '1' ELSE '0' END

and

apply filter on Dummy Column as "is not equal to /is not in" '0'.

13.Putting dashboard prompt and three reports in Dashboard by applying certain conditions on each sections in "Edit Dashboard".

Note: Put all the reports and dashboard prompt in separate sections as we have to apply the condition on section.
14. Now I am applying condition in section of "MASSACHUSETTS" by clicking on Properties > Condition > Section Condition as below :

15. Similarly follow the same for rest of the three reports.

16. Now save your dashboard page and run it.

17. Here "MASSACHUSETTS"is my default report so it is visible on my dashboard by default. Now whatever options (report name) you will select from prompt those report will be shown. It is not necessary to show one default report; you can remove default selection from dashboard prompt.

Output:

18. If you select the prompt value MASSACHUSETTS corresponding report will be viewed and the same applicable for NEW YORK, CALIFORNIA and TEXAS.

Hope It Helps!!

Thanks,

Shanmugha Bharathy G B

OBIEE 11G Browser Title Name To Your Custom Text

OBIEE 11G Browser Title Name To Your Custom Text :

Our client had a requirement to change the Browser title to their Business Name. The procedure which i followed is explained below for your reference.

Go to <Middleware_Home>\Oracle_BI1\bifoundation\web\msgdb\l_en\messages and Open productmessages.xml file.
Search for kmsgProductPortal attribute and change its value with your custom text.
Example:

<WebMessage name=”kmsgProductPortal”>

<TEXT>"YOUR CUSTOM TEXT"</TEXT>

</WebMessage>

save it and stop and start the Opmn to restart all OBIEE 11G services. Once the services are up, you will see the changed browser title as shown in below picture. In this example i have used SBL INTERACTIVE BI DASHBOARD as Custom title text.

Hope It Helps!!

Thanks,

Shanmugha Bharathy G B

How to Change/Reset forgotten Password in OBIEE 10G and 11G

Reset forgotten Password in OBIEE 10G and 11G:

OBIEE 10G:

In OBIEE 10g, If you forgot the repository (RPD) password, we can easily rest with simple straight configuration change (AUTHENTICATION_TYPE = BYPASS_NQS) in NQSConfig.ini file.

Open NQSConfig.INI file
Go to Security part and find AUTHENTICATION_TYPE = BYPASS_NQS;
Uncomment (remove #) this part.
Stop the BI Server service
Close Administration Tool, if opened. (Then only the changes are affecting to the Administration Tool)
Open Administration Tool, then rpd which needs password reset, in offline mode
Give any password, it accepts.
Now go to Manage > Security.
From here it’s the normal process how you change password.
Save the changes to rpd
Revert back the changes in NQSConfig.INI
Start BI Server and open rpd with new password.

OBIEE 11G:

In OBIEE 11g, you can get a password for an already deployed RPD. With the help of wlst Script, you can get the forgotten OBI Repository password that is deployed earlier.

Expand the key map: oracle.bi.enterprise. This is the key map which stores all the repository passwords that are deployed.

I want to reset/change repository SampleAppLite, to get the password of “SampleAppLite_BI0003″ Repoitory,
Open Command Prompt
C:\Windows\system32>cd D:\OBIEE11g\oracle_common\common\bin
Run wlst Script: D:\OBIEE11g\oracle_common\common\bin>wlst.cmd
Connect to weblogic Domain with the command:
connect(“<weblogic_AdminUser>”,”<weblogic_AdminUser_Password?”,”<AdminServer_HostName>:<AdminServer_Portno>”)
E.g: wls:/offline> connect(“Administrator”,”welcome123″,”localhost:9704″)
Connecting to t3://localhost:9704 with userid Administrator …Successfully connected to managed Server ‘bi_server1′ that belongs to domain ‘bifoundation_domain’.
Run listCred() command to get the password of SampleAppLite_BI0003 repository wls:/bifoundation_domain/serverConfig>listCred(“oracle.bi.enterprise”,”repository.SampleAppLite_BI0003″)
Location Changed to domainRuntime tree.
[Name : tempvalue, Description : null, expiry Date : null]
PASSWORD:Admin123

Hope it Helps!!

Thanks,

Shanmugha Bharathy G B

OBIEE 10g to OBIEE 11g Upgradation

OBIEE 10g to OBIEE 11g Upgrade

Hi,

Our client wanted their OBIEE application to be upgraded from OBIEE 10g to OBIEE 11g. For this up-gradation the steps which i have followed has been shared below.

Upgrade Assistant

Upgrade Assistant is the utility to upgrade OBIEE from 10g to 11g which has been installed by default and is located under Oracle fusion middleware home $ORACLE_FMW/bin.

Pre-Requisites:

Start up all the OBIEE 11g related services (BI Services and Web logic Server) and check that you can log in to Enterprise Manager,WLS Admin Console and OBIEE Answers.
Enterprise Manager : http://localhost:7001/em
Web Logic Server Admin Console: http://localhost:7001/em console
OBIEE Answers/Dashboard : http://localhost:9704/analytics
All the relevant TNS names used in the 10g rpd connections are present in/Oracle_BI1/network/admin/tnsnames.ora file

Step 1:

Navigate to <OBIEE_MW_HOME>/Oracle_BI1/bin/ua.bat . This batch file is the Upgrade Assistant file which launches the upgrade wizard by double clicking on it.

Step 2:

In this page select "Upgrade Oracle BI RPD and Presentation Catalog" radio button. Click NEXT

Step 3:

Here check the "Upgrade Repository (RPD)" checkbox.
Specify the location of the 10g RPD file.
Enter the Administrator credentials of the old 10g RPD file.
Specify the password for the upgraded 11g RPD and confirm it once again.

Step 4:

Specify the Weblogic Admin Console username and password. Port number is 7001 and Click Next..
During the examination process, Upgrade Assistant checks for the following:
The source directory exists.
The source directory is readable and contains a file for upgrade.
Under the Status column, the word succeeded should appear. If instead, the word failed appears, inspect the log file for details.

Step 5:

Check the upgrade summary and click UPGRADE to start the upgrade process.

Once you click Upgrade the process takes place till from Status in Progress changes to Success..

Step 6:

Once the upgrade process completes the process log output is available at <OBIEE_MW_HOME>\Oracle_BI1\upgrade\logs

The new rpd will be named as OldPaint_BI001.rpd and placed at the following path:<OBIEE_MW_HOME>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository.
As part of upgrade, security is also upgraded from 10g to 11g. This includes the Users and Groups.
A 11G version of RPD and WebCat will automatically be placed in the right 11G directory and the NQSconfig,INI file will have a proper entry done. There is no need for any manual edition.

There are a few little things we need to do to ensure our 11G environment is ready to run, These changes are typically done once after the upgrade:

Configure the Administrator user account in 11G:

Since 11G has a very different security management system, the Administrator account, which was managed in the Admin Tool in 10G will need to be configured in the weblogic to ensure access using this account. To do so, go to Weblogic console (under 'My realm') and edit the Administrator user by clicking on it:

Give a new password:

Release Configuration and restart all BI Services and test logging into Web Presentation using this new password. You will be able to log in as Administrator now..Now you have successfully upgraded OBIEE from 10g to 11g...

Hope it Helps!!

Thanks,

Shanmugha Bharathy G B

Exceeded Configured Maximum number of allowed Input Records in OBIEE 11G

Exceeded Configured Maximum number of allowed Input Records

Where you run a analysis which retrieves more number of records say for an example above 80000 the following error will pop up.

In OBIEE default number of rows processed when rendering a table view will be set to 65000. In such cases goto>em>Business Intelligence>Coreapplication>Capacity Management>Performance and select lock & edit configuration.
In the right pane you can see below screenshot and change from 65000 as per your requirement. In this example i have used 100000... and restart your services to take effect.

This may impact your performance if more number of rows are set....

I got this error and i have explained how i fixed it...

Hope it Helps!...

Thanks,

Shanmugha Bharathy G B

Redirecting Home Page to Custom Page in OBIEE 11G

Redirecting Home Page to Custom Page in OBIEE 11G

Normally when you click HOME page in OBIEE it will redirect to the page as shown below.

But clients wants their home page to be displayed when they click HOME button.
All the HEADER icons displayed in OBIEE are based on a header file.
For this

1.Goto>\MiddlewareHome\Oracle_BI1\bifoundation\web\app\res\b_mozilla

In this folder you will have a file naming HEADER.JS

2. Open HEADER.JS file and search for "saw.header.NavBar.prototype.onHome=function(b,a){saw.header.openURL(saw.commandToURL("bieehome"),a)}" tag...

3. Now replace the above tag with your customization link (in this example i have used google.com but you can use your own customization page like "saw.dll?Dashboard&portalPath="custom page link") saw.header.NavBar.prototype.onHome=function(){window.location.href="http://www.google.com

4. Save the changes and bounce OBIEE.

5. Sometimes it will not reflect and in those scenario you will also replace HEADER.JS file in below path \MiddlewareHome\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla

under tmp folder to make changes perfect.

6. Now you have successfully redirected OBIEE home page to your Custom page.

Hope it Helps!

Thanks,

Shanmugha Bharathy G B

DATA WAREHOUSE BASICS

William H. Inmon (born 1945) is an American computer scientist, recognized by many as the father of the data warehouse. Bill Inmon created the accepted definition of what a data warehouse is - a subject oriented, nonvolatile, integrated, time variant collection of data in support of management's decisions. Compared with the approach of the other pioneering architect of data warehousing, Ralph Kimball, Inmon's approach is often characterized as a top-down approach.

Ralph Kimball (Born 1944) is an author on the subject of data warehousing and business intelligence. He is widely regarded as one of the original architects of data warehousing and is known for long-term convictions that data warehouses must be designed to be understandable and fast. His methodology, also known as dimensional modeling or the Kimball methodology, has become the de facto standard in the area of decision support.

DATA WAREHOUSE CONCEPTS

A fundamental concept of a data warehouse is the distinction between data and information. Data is composed of observable and recordable facts that are often found in operational or transactional systems. At Rutgers, these systems include the registrar’s data on students (widely known as the SRDB), human resource and payroll databases, course scheduling data, and data on financial aid. In a data warehouse environment, data only comes to have value to end-users when it is organized and presented as information. Information is an integrated collection of facts and is used as the basis for decisionmaking. For example, an academic unit needs to have diachronic information about its extent of instructional output of its different faculty members to gauge if it is becoming more or less reliant on part-time faculty.

DATA WAREHOUSE DEFINITIONS

The data warehouse is that portion of an overall Architected Data Environment that serves as the single integrated source of data for processing information. The data warehouse has specific characteristics that include the following:

Subject-Oriented: Information is presented according to specific subjects or areas of interest, not simply as computer files. Data is manipulated to provide information about a particular subject. For example, the SRDB is not simply made accessible to end-users, but is provided structure and organized according to the specific needs.

Integrated: A single source of information for and about understanding multiple areas of interest. The data warehouse provides one-stop shopping and contains information about a variety of subjects. Thus the OIRAP data warehouse has information on students, faculty and staff, instructional workload, and student outcomes.

Non-Volatile: Stable information that doesn’t change each time an operational process is executed.Information is consistent regardless of when the warehouse is accessed.

Time-Variant: Containing a history of the subject, as well as current information. Historical information is an important component of a data warehouse.

Accessible: The primary purpose of a data warehouse is to provide readily accessible information to end-users.

Process-Oriented: It is important to view data warehousing as a process for delivery of information. The maintenance of a data warehouse is ongoing and iterative in nature.

Other Definitions

Data Warehouse: A data structure that is optimized for distribution. It collects and stores integrated sets of historical data from multiple operational systems and feeds them to one or more data marts. It may also provide end-user access to support enterprise views of data.

Data Mart: A data structure that is optimized for access. It is designed to facilitate end-user analysis of data. It typically supports a single, analytic application used by a distinct set of workers.

Staging Area: Any data store that is designed primarily to receive data into a warehousing environment.

Operational Data Store: A collection of data that addresses operational needs of various operational units. It is not a component of a data warehousing architecture, but a solution to operational needs.

OLAP (On-Line Analytical Processing): A method by which multidimensional analysis occurs.

Multidimensional Analysis: The ability to manipulate information by a variety of relevant categories or “dimensions” to facilitate analysis and understanding of the underlying data. It is also sometimes referred to as “drilling-down”, “drilling-across” and “slicing and dicing”

Star Schema: A means of aggregating data based on a set of known dimensions. It stores data multidimensionally in a two dimensional Relational Database Management System (RDBMS), such as Oracle.

Snowflake Schema: An extension of the star schema by means of applying additional dimensions to the dimensions of a star schema in a relational environment.

What is datawarehouse?

Datawarehouse is a

Subject-oriented-Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented.
Non-volatile-Once entered into the warehouse, data should not change. This is logical because the purpose of a warehouse is to enable you to analyze what has occurred.
Integrated-Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.
Time-varient-In order to discover trends in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive. A data warehouse's focus on change over time is what is meant by the term time variant.

database system.

What are the steps involved in loading a datawarehouse?
There are 3 steps in datawarehousing,

data-extraction from the OLTP system
Apply the required transformations on the data
Load the data into datawarehouse

What is fact table?
Fact table is a table, which is having the multiple foreign keys of the dimension tables and measures.Fact table is also called as child table having dimension codes and facts/measures.

What is dimension table?
Dimension table is a table,which is having the descriptive information of an entity.This is also called as master table having the primary key.Always the data flow from dimension table to fact table.

What is data-modelling?
The process of storing and retrieving the data from datawarehouse is called as data-modelling.

What is star schema and snow-flake schema?What is the difference between those?
Star is one fact and many dimensions where as Snowflake design is one fact and many dimensions and dimensions can have additional dim relations.No.of joins in the snow-flake schema is more compare to star schema.

Difference between OLTP and OLAP systems?

OLTP(Online transactional process)
Dynamic data
volatile
Normalised data
Efficient for data storing
OLAP(Online Analytical process)
90%static,10%Dynamic data
Non-volatile
De-normalised data
Efficient for data retrieving

What are different types of dimensions?

confirmed dimension
slowly changing dimension
rapidly changing dimension
De-generated dimension

What are different types of fact tables?

Additive fact table
semi-additive fact table
Non-additive or fact-less fact table

What is Federated Query?

A query is fired across multiple sources is called as federated query.

select cust_first_name,revenue from customers,sales

customers->SQL,sales->DB2 data sources

Hope it Helps!