This layer helps to implement business logic on the tables. The tables in this layer are called as logical tables whereas tables in physical layer are physical tables. Each logical table have one or more source of the physical table/view or in some cases logical table itself. Fetching data from physical column will be always faster than fetching from logical column, but with physical tables we do not have enough feasibility to implement the logic hence the logical tables.
It is best practice to join tables in BMM layer using Complex join. Since we may have multiple sources for a single logical table it would be easier for the BI server to fetch data.
To create new BMM RC(right click) anywhere in the layer and select New Business Model. Give the appropriate name and drag/copy the tables to new bmm from physical layer. After the tables are imported, the dimension tables should appear in white and facts in yellow by default. To create joins between two tables RC on two tables selected go to business model diagram > selected objects > select complex join at top on new window > drag from dim (dimension) table to fact.
It is the best practice to have hierarchies created for all the dimension tables although not required. Hierarchy created in bmm layer helps users to drill down in the reports, example: country>state>city. Drill down is the option in reports to make state appear in a report when country is clicked.
Tuesday, August 31, 2010
Monday, August 30, 2010
Physical Layer
It is recommended that Foreign key join is to be used to join tables in Physical layer also to create alias tables for every base table.
Foreign Key join is used to connect two tables using primary key and foreign key present in the tables. Alias acts as a pointer. Alias is the copy of the base table. New columns cannot be created in alias tables. Any changes made to base tables are automatically reflected in Alias tables. It is recommended that joins are created among the alias tables.
To open Admin tool and create new rpd file --- Programs> OBI>Administration
Go to File>Import from database> select DSN created >select tables needed.
After the tables are imported, we can see the database tree structure with connection pool and schema. Schema consists all the tables imported. Connection Pool contains the information for Call interface (ODBC, OCI etc), schema in database etc. OCI is recommended for Oracle database.
Then create alias tables for the tables imported. RC (right click) on table in Physical layer > new object> alias. Create join between two tables (ofcourse fact and dimension) by selecting two tables and RC and go to physical diagram> select objects only, which pops up a new window. Select Foreign key join at the top and select dimension table and then drag to fact table. Specify the primary and foreign keys and click OK.
Fact is the table which stores the metric.Dimension table stores the attributes related to facts.Fact table contain measures, foreign keys of other tables (dimensions). A fact surrounded by the dimension tables and if the fact is directly connected to all dimension tables then it is Star schema. Similarly if we have any sub dimension connected to dimension but not directly connected to fact, that can be refered as Snow flake schema.
View:
A View can also be created in Physical Layer. A view is created to combine dimension and mini dimension to a new dimension. A view is used to display different columns from two or more tables.
RC on Schema > new physical table and in type select 'select' and write required query. Then go to tab columns and enter all the columns mentioned in query.
Ex: select city, country, row_id, sales from customer, sales where customer.row_id=sales.customer_id;
Foreign Key join is used to connect two tables using primary key and foreign key present in the tables. Alias acts as a pointer. Alias is the copy of the base table. New columns cannot be created in alias tables. Any changes made to base tables are automatically reflected in Alias tables. It is recommended that joins are created among the alias tables.
To open Admin tool and create new rpd file --- Programs> OBI>Administration
Go to File>Import from database> select DSN created >select tables needed.
After the tables are imported, we can see the database tree structure with connection pool and schema. Schema consists all the tables imported. Connection Pool contains the information for Call interface (ODBC, OCI etc), schema in database etc. OCI is recommended for Oracle database.
Then create alias tables for the tables imported. RC (right click) on table in Physical layer > new object> alias. Create join between two tables (ofcourse fact and dimension) by selecting two tables and RC and go to physical diagram> select objects only, which pops up a new window. Select Foreign key join at the top and select dimension table and then drag to fact table. Specify the primary and foreign keys and click OK.
Fact is the table which stores the metric.Dimension table stores the attributes related to facts.Fact table contain measures, foreign keys of other tables (dimensions). A fact surrounded by the dimension tables and if the fact is directly connected to all dimension tables then it is Star schema. Similarly if we have any sub dimension connected to dimension but not directly connected to fact, that can be refered as Snow flake schema.
View:
A View can also be created in Physical Layer. A view is created to combine dimension and mini dimension to a new dimension. A view is used to display different columns from two or more tables.
RC on Schema > new physical table and in type select 'select' and write required query. Then go to tab columns and enter all the columns mentioned in query.
Ex: select city, country, row_id, sales from customer, sales where customer.row_id=sales.customer_id;
OBIEE Admin Tool
OBIEE Administration tool is where all the back end process of reporting is done. This is the place where business logic, security etc is implemented on physical data. RPD is a file containing all the metadata of the logic/security etc is stored. It basically has 3 layers.
1) Physical Layer: This is where connection pools are created and physical tables are imported in to rpd from datawarehouse.
2) BMM layer: Here actual business logic is implemented on logical tables and it is the best practice to join tables using complex join in BMM layer.
3) Presentation layer: This layer is the visible to the user in the front end to create reports/requests. Security is implemented in this layer.
The flow is Physical>BMM>Presentation. We will discuss each layer in the coming posts.
1) Physical Layer: This is where connection pools are created and physical tables are imported in to rpd from datawarehouse.
2) BMM layer: Here actual business logic is implemented on logical tables and it is the best practice to join tables using complex join in BMM layer.
3) Presentation layer: This layer is the visible to the user in the front end to create reports/requests. Security is implemented in this layer.
The flow is Physical>BMM>Presentation. We will discuss each layer in the coming posts.
DSN Creation
After installing OBIEE on your local machine, a DSN has to be created which helps in creating connection pool in OBIEE Admin tool to connect to the database. Following are the steps to create System DSN.
Creating DSN
Go to Control Panel>administrative tools> ODBC
Go to System DSN(tab)>add>oracle xe(whichever database)
Enter name for your DSN, tns anything, User id (Schema) and test connections>ok
Note: If OBIEE is installed on 64 bit OS following link would be helpful
http://forums.oracle.com/forums/thread.jspa?threadID=869482
Creating DSN
Go to Control Panel>administrative tools> ODBC
Go to System DSN(tab)>add>oracle xe(whichever database)
Enter name for your DSN, tns anything, User id (Schema) and test connections>ok
Note: If OBIEE is installed on 64 bit OS following link would be helpful
http://forums.oracle.com/forums/thread.jspa?threadID=869482
First Post
This blog is my notes of OBIEE tool. The content posted in this blog expresses the views of the author only and is for knowledge purpose only.
Subscribe to:
Posts (Atom)