upload

OBIEE 10g Installation



Browse the source file folder server-> Oracle_Business_Intelligence -> setup.exe. Click on setup.exe file which you will give the screen like below:
Before Installing, make sure that your hardware and memory of the system is enough to install OBIEE 10g. If you want know the hardware requirement of the system refer the below document
http://docs.oracle.com/cd/E10415_01/doc/bi.1013/e10417.pdf
At least of 2GB disk memory is needed to continue the installation.
Browse the disk and select the folder that where you want in install otherwise leave it to the default location if the memory is available in the drive.

In the 3rd screen select the installation type that which you want to install. If you want to install all the components of OBIEE then select ‘Complete’ and click ‘Next button.


By passing the above screen we will get the below screen there we have to give JDK location and Administrator password of the OC4JAdmin and click ‘Next’ Button. Please remember the password of OC4JAdmin, because it will be used when you will open the enterprise manager.
In the next screen just click ‘Next’ Button
From the below screen we can choose the language which you want to see the presentation error message. By default the language is ‘English’
 
By passing the above step we will get the summary of what are all components going to be installed. Just click ‘Next’ Button

By passing the above step the installation of OBIEE components will start. Wait for some time because it is time taken process.                                  
                                                     
Wait with the above screen until you will get 100% finished and the ‘Next’ button will be enabled. It will come the below screen
                                               

By clicking the ‘Next’ button you will come to the below which is giving the summary of installation. From below window you can learn how to start OC4J by manually and automatically. 
By clicking ‘Next’ with the above screen you have come to the end of the installation which will be asked for restart your computer. Just restart your system to set the component. After restarted your system check all the components are installed in your machine in ‘All Programs’.

Report with Union

 have a report with UNION of 3 criteria and the result i am getting is one of the columns is a union of 3 columns like Age Group,Gender and Race and here the problem is i need to show them in order Race first, Age Group, Gender now it is showing in sorting order even though i cleared sort on all the columns 65+,75+,85+, Asain, Black,Female,Hispanic,Male instead i want the column to show me Asian, Black,Hispanic, 65+,75+,85+,Female, male also in that table i want to make the table line thicker after race category and Age Group




1) In each of your three separate queries, add another column.

2) Create BINS according to how you want the final report be sorted: Asian = '1', Black = '2', etc...

3) Sort on this column in the combined query.

Since each query will have the proper number assigned to the group, the report will sort as you wish...

Aging report


How to create ageing report by using following columns in below provided format.Pleaselet me know the solution.

Columns:Customer,Debit,Credit,Days past due

Format:
Days(0 to 180) |Days(180-365) | Days(>365)|

Customer|Credit|Debit|Credit|Debit|Credit|Debit|

abc | 3344 |12000 |400 |36000 |100 |12000 |
xyz | 1340 |12300 |400 |3600 |300 |11000 |



case when cast(Days as int) <180 then 'Days(0-180 days)'
when cast(Days as int) >= 180 and Days < 365 then 'Days(180-365 days)'
when cast(Days as int) >365 then 'Days(>365 days)'
end

Initialization Block

Groups

Users

Metrics

Timestampadd

Timestampdiff

Evaluate Function

Cast Function

Authentication or Authorization

Log Level

Session Variables

Repository Variables

Variables

Presentation Layer

logical level

Fragmentation Content

Star and Snowflake Schema

Fact and Dimensional table

Outer Join

Logical Table Source

Logical Table

Logical Column

Hierarchy

BMM LAYER

Joins

Physical layer


1)   I would like to know in which cases we will use ' select object(s) only ' , ' object(s) and direct joins ' and ' object(s) and all joins ' of physical layer to join tables, since i am new to this field ( 2 month's ) please can any one explain me...
select object(s) only
If u select the tables dim1 dim 2,fact1 from physical layer and u need to know the join conditions between those selected tables then we ll go with above option
object(s) and direct joins
Select tables and click on "object(s) and direct joins" u ll direct joins between the tables
object(s) and all joins
For this we will get all the joins associated in physical layer 


2)   When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

After importing tables in physical layer if you modify those tables in database those changes will not be reflected in physical layer. You don’t have to import tables again if you add only one or more columns. You can simply create physical column in rpd with the same name and data type as of database. In the same way if you delete any column in database table then you can delete that column from RPD.


3)   Is Fact table is necessary in Physical Layer. I don't have any fact. i have only 5 Dimensions. How can join the tables in the physical layer.
No join in physical layer . Drag and drop 5 dim tables from physical layer to bmm layer and create a dummy table  say fact and join 5 dim tables to dummy table  then drag 5 dim tables from bmm layer to presentation layer. Create reports


4)   I am trying to import tables from database to the physical layer. when we select objects to add to physical layer which boxes to we need to check below. I know we need to check 'Tables' and 'Keys'. Other than these two do we check any other box
that two box (tables and keys) are sufficient for import tables to repository.



5)   When I try to do a foreign key join in Physical layer a message pops up saying ' A matching table key doesnt exist in table. Do you want to create one'. Sould I just say 'yes' to it. Any idea why this message is coming?
f you want create manually a foreign key, you should click 'Yes'. That message appears when OBIEE can't establish an automatic relationship between keys of tables you are joined.



6)   After I have created all business model and the presentaion layer,the table structure in the database is changed.
In order to synchronize the physical layer with the database do i need to create a new physical layer again?or is there any other way I can do it.


n rpd,
go to Tools > Utilities > Update Physical Layer
click on Execute...

give the source name and execute.
anyway, keep backup before doing this..
The wizard does not add columns or tables to the repository that exist in the back-end database but not in the repository. Additionally, the Wizard doesn't update column Key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values don't match, the wizard updates the type and length of the column in the repository.

I want the wizard to add the columns to the table in the physical layer and remove the column from the table if the column doesn't exist in the database.


7)   I 've created a duplicate table in physical layer , When i check view data on duplicate, getting an error. (Table or view doesn't exist). How to get out of it.

1 When you create a duplicate in rpd, it wll create a new table having #1 in the end. For eg: if I create a duplicate of Sales then in rpd it will create as Sales#1 and when u do a update count or view data on duplicated table it wll run on "sales#1" and since sales#1 will not be there in ur db it will throw an errror that table or view does not exist. So, better to create a alias and use it instead of duplicate.
2 When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur. 
Creating an alias creates a copy of the table in metadata that will be referenced in SQL with a new alias name. The alias name in SQL will be derived from the ID given to the alias in the metadata. 

The important point is that you are not creating an object in the metadata that requires you to create a new object in the physical database. If you duplicate a table in metadata, then that new table must map to a separate table in the physical database. If it doesn't exist, you will need to create it or you will get a SQL error


8)   I have 5 dimension tables. I need to take 5 reports individually for those tables. Do i need to create dummy table in the physical layer for fact? Please suggest me...

9)   i have two tables in physical layer , and two tables are different and dont have any joining condition . All i want is i want to see those two tables data indvidually in Answers .
You can create a dummy table and make joins in BMM layer.
Because you need to have a fact and dimension join condition, otherwise you will get an error.


10)                Physical Layer Naming Convention 

_F for Fact
_D for Dimensions
All in UPPER case

For Alias use CLOSING DATE (W_DAY_D)

11)                physical layer have some data. but i want to decrease the data in the physical layer without delete. how u can do. how many ways are there to do this process? 

Create opaque view in physical layer so that restrict the data in view itself


12)                How to I update the table in the physical layer in the OBI admin tool when:

1. the data in the underlying database table changes (meaning the records/rows are added or deleted in the database)
2. the fields in the underlying database table change (meaning columns are added or deleted)
3. the size of the field changes (meaning the type or length of the columns change)
4. only the table names/column names in the underlying database change.

In which of the above cases do I have to import again through the connection pool. If I have to import again do I have to create physical joins in the physical layer and complex joins in the BMM layer and mapping to the new table as LTS and any expressions in the column mappings again?
1. the data in the underlying database table changes
(meaning the records/rows are added or deleted in the
database):

You don't need to take any action here unless you are concerned with query caching. In that case use 'event polling table'. Lookup documentation for event polling table.

2. the fields in the underlying database table change
(meaning columns are added or deleted)

You will need to manually add those in physical layer and propogate to business and presentation layer.

3. the size of the field changes (meaning the type or
length of the columns change)

Go to utilities - update physical layer and select the affected presentation layer.

4. only the table names/column names in the
underlying database change.
You will need to manually change those in physical layer. No need to and propogate to business and presentation layer ans its is automatically taken care of.

In which of the above cases do I have to import again
through the connection pool. If I have to import
again do I have to create physical joins in the
physical layer and complex joins in the BMM layer and
mapping to the new table as LTS and any expressions
in the column mappings again?
No need in any case to import manually. 


if i create a new column of a table in physical layer of a table.This new cloumn is not show in database.How can i synchronize with database ???


he physical layer only refers to what already exist in the DB. 

If you have a (new) column in a table in your DB, then you can create a new physical column in the respective physical table in physical layer so BI server knows about it.

However, it is not possible to create a column in your database by just creating a physical column in physical layer. 

2 After creating the column in the database, simply import the column(s) using 'File > Import Database..'. This will bring up the Import screen. Navigated thru Database, Schema, and Table to select the Column(s) you need. After you have selected the column(s) click on 'Import'.


What difference does it make in defining the keys in the physical layer in the following two ways.

I have a fact table with the following columns
PERKEY
PRODKEY
MKTKEY
UNITS
DOLLARS

As the name suggests perkey, prodkey and mrkkey are the keys in the table.

Approcah 1:
Select the table.
Right click and in properties of the table click on keys tab and
select new key and
check all the three columns.
Approach 2:

Select the table
Right click and in properties of the table click on keys tab and
select new key and
check perkey only
again select a new key
check PRODKEY only
again slect a new key
check matkey.
In this way you will find the three keys in the properties
Approach 1: will give u just one key which is actually combination of 3 cols from the table acting as composite here. Like country, state, city ...all these 3 cols will together form a foreign key in fact and will be used together during join conditions

Approach 2: will give u 3 keys each based on 3 individual cols and are used seperately using join conditions.

One more way is to auto create keys at the time of join definition which will give u a key based on number of cols used in join conditions.


Duplicate table and Alias table in the physical layer 


> aliases are created to break the circular joins (which are not supported by obiee)
-> and, as per apps standards no operation can be done on base tables which are imported directly to physical layer. Only aliases are joined and will be used in BMM layer
-> useful to re-use the table more than one time , best example: W_DAY_D in apps rpd

2 Have you gone through the link saichand provided?It is well explained in that thread.Duplicate will carry all joins of original table.Alias will not carry any joins.Moreover if you create any column in original table.It will automatically reflect in alias whereas in duplicate it wont.
Why is it necessary to create a view in the physical layer when we can create the table or a view in the database?
Does it affect performance in any case?

The view in the physical layer is not transparent to the database.. it's opaque.

This view (rpd) is isolated with the database.. 
if we want we can deploy this view in database.

But, all time we may not have necessary permissions to deploy this view or to create this view manually in db. Customer may not accept this all time.
At that time we go for view creating in physical layer of rpd. 

There's always performance issue using view. Using direct tables, execution time will be less...

when we are having very complex reports, we go for writing views in physical layer... but remember, by writing views in rpd, it may not support the ad-hoc query, which is one of the objectives of Oracle BI.



I make one View of 10 tables , and import this view IN PHYSICAL LAYER . Is it necessory to make fact table in Business Model and Mapping layer?.


create two individual logical tables in bmm layer with your view as only source. Now set Complex joins to make a logical table as fact. 
In this way you avoid creating aliases and self-joins.



 Diffence between physical layer and BMM layer 


The Administration Tool is a Windows application that allows the Oracle BI Administrator to create and edit repositories.

Physical layer: Represents the physical structure of the data sources to which the Oracle BI Server submits queries. 
ie, Connection Pool-contains information about the connection to the database. 
You can specify the Joins between the tables and you can change the Data Types of the Columns.

BM & M layer: Represents the logical structure of the information in the repository. 
ie, Uou can create Dimension, Hierarchies, Measures, Calculations, Aggregations and Time Series

Presentation layer: Represents the presentation structure of the repository. This layer allows you to present a view different from the Business Model and Mapping layer to the users. 


2 The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information about the physical data sources.

The most common way to create the schema in the Physical layer is by importing metadata (table, view, …) from databases and other data sources. If you import metadata, many of the properties are configured automatically based on the information gathered during the import process. You can also define other attributes of the physical data source, such as join relationships, that might not exist in the data source metadata.


In the Business Model

In the business model, OBI recommends using complex logical joins instead of foreign key logical joins.

When Complex joins are used in the business layer then they are really acting as placeholders. They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will then decide the best joins to use to meet the requirements.






I already have oracle physical table in Physical layer imported last month. But the physical table definition changed . It has 3 extra columns. How do I get those 3 columns in physical layer in RPD? Do I have to import the table again?


you can create columns physically or can import. 
While importing select only those columns to be imported...
It'll import only new (selected) columns into physical tables..

2 Instead of importing it again to physical layer ...just create those three columns with same name as in "database" and "data type " too....click on view data check whether u are able to get the newly added columns data or not 


When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

Display percentage on stacked bar graph



Can anyone please tell me as to how to display percentages on a stacked bar chart. All the time it displays actual values.
For example ..

18%

3000 | | | | | 
70%

2000 | | | | |
12%

1000 | | | | |


Q1 Q2 

I hope I could depict it. 
Thanks



2) The one in {OracleBI}\web\app\res\s_oracle10\popbin. Did you restart the presentation server and javahost?

regards

John
http://obiee101.blogspot.com

How do we Create report with Single table


To report on a single table: just drag/drop the table twice from the physical layer to the busines model layer. Then define a complex join (just drag/drop a line) between tablex and tablex#1. Define a key on the dimension table (the white colored folder).

You can then put one of the tables in a presentation folder and report.

TimestampDiff and TimestampADD( Difference of two dates) in OBIEE


How to get a difference between two dates (in terms ) of days,weeks,months what every it may be

The below formula gives you no.of days between day date and current_DateDays difference: 
TIMESTAMPDIFF(SQL_TSI_DAY, Time."Day Date",CURRENT_DATE)

The below formula gives you no.of months day date and current_Date
Months difference:
TIMESTAMPDIFF(SQL_TSI_MONTH, Time."Day Date",CURRENT_DATE)The below formula adds months to day date columnToadd 12 months to a date column:
TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Day Date")

similarly we can write the formulas using different intervals based on the date format in the column
Here are the intervals :
SQL_TSI_SECOND, 
SQL_TSI_MINUTE, 
SQL_TSI_HOUR, 
SQL_TSI_DAY, 
SQL_TSI_WEEK, 
SQL_TSI_MONTH, 
SQL_TSI_QUARTER, 
SQL_TSI_YEAR.

Degenerate Dimension in OBIEE



A degenerate dimension (DD) acts as a dimension key in the fact table, however does not join to a corresponding dimension table because all its interesting attributes have already been placed in other analytic dimensions.

Sometimes people want to refer to degenerate dimensions as textual acts, however they're not facts since the fact table's primary key often consists of the DD combined with one or more additional dimension foreign keys.

Degenerate dimensions commonly occur when the fact table's grain is a single transaction.


How to Implement in OBIEE :

Refer: http://obieetalk.com/oracle-bi-ee-101341-%E2%80%93-modeling-degenerate-dimensions-%E2%80%93-fact-attributes

Reference:http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf

Real Time Questions

Real Time Questions

XML Transformation

video

Filter Transformation

video

Using Debugger

video

Popular Posts