OBIEE 11g

OBIEE 11g

ORACLE BUSINESS INTELLIGENCE ENTERPRISES EDITION (OBIEE) 11g

BY KARTHIK

Datawarehouse Concepts1) Data:Any meaningful information is known as dataData is two types:1) Transactional data2) Analytical dataTransactional data:- It is runtime data or day to day data It is current and detail data It is useful to run the business It is stored in OLTP(online transactional process) Example; - ATM Transactions, share markets etc.Analytical data:- It is useful to analyze the business It shows historic data and summarized it It is stored in OLAP or DW Example: - Banks, organizations etc2) Database:-Collection of interrelated data is known as database.3) Data warehouse:-Datawarehouse is a1) Subject oriented2) Integrated3) Non volatile4) Time variant

4) Datamart:-It is subset of datawarehouse that is designed for a particular line of business, such as sales, finance, marketing or HR.

5) Dataware housing:-The process where we can store and retrieve the data is called dataware housing.In dataware housing we have ETL tools and Reporting tools.6) Factless fact table:- A factless fact table does not contain any facts or measures; it contains only dimension codes and flag columns. Though it does not contain any measure columns still we perform analysis on this table by flag columns. Example :Number of accident for a months Number of policy has been closed this month.7) Conformed Dimension:-1) If a dimension table is shared by multiple fact tables then it is known as conformed dimension.2) The only way to retrieve the data from multiple fact tables in dataware housing is through conformed dimension.3) Example:-Time/calendar dimension is normally used in all star schemas so can be designed once and used with many fact tables across a datawarehouse. 8) Degenerated dimension:-Table consisting of descriptive columns, measure columns and dimension codes are called as degenerated dimension.9) Federated query:- If a query is hitting or firing on multiple data sources, that query is called as federated query. Federated queries will degrade the performance.

10) Schema:- It is a collection of related objects. Objects are tables, columns, indexes. There are two types of schemas in dataware house. They are1) Star schema2) Snowflake schema1) Star schema:- It is a centralized fact table surrounded by multiple dimension tables. In a star schema every dimension will have a primary key. In a star schema, every dimension table will have any parent table. No sub dimensions in star schema.2) Snow flake schema:- It is centralized fact table surrounded by multiple dimension tables and some of the dimension tables in schema will not have direct join with the fact table. In a snow flake schema, a dimension table will have one or more parent tables. In snowflake schema you can see some sub dimensions. Number of joins are more performance is less.11) Primary key:-Unique + not null constraint is called as primary key.Example: - 1) Prod ID in Product table 2) Customer ID in Customer table.12)Foreign key:-Reference of primary key is called as foreign key.Example: - Prod ID, Cust key in ORDERS table.13) Composite key:-A key formed by combining at least two or more columns is called composite key.

Example: - EMPID Project ID Hours worked 0101100 0102200 02 017514) Foreign key join:- It is a equi join We always use = (equal) operator. While giving new foreign key join cardinality, driving table and type of join are disabled. Mostly used join in physical layer.

15) Complex join:- It is used in BMM layer. In complex join we use, = operators. In complex join cardinality, driving table and type of join are enabled. Less used join in physical layer (SCD type2).16) Slowly changing dimension (SCD):- It is a dimension value those values changes over a period of time or not frequent changes. We have 3 types of SCDs Type 1:-it will get you the latest information. Type 2:-it will maintain all the history of data. Type 3:-it will get the latest and the immediate previous information.17) Dimension table:- Dimension table consists of descriptive columns. Primary key is present in dimension table. Primary keys are used to joining purpose. Example: - Descriptive columns: - Product name, customer name etc. Example: - Primary key columns:-Product ID, Time ID, CUST ID etc..

18) Fact table:- Fact table consists of Facts and Measures. Foreign key columns are present. Multiple foreign keys are known as dimension codes. Depending on fact table we can analyze the business. Facts and measures are known as KPIs. Example:-sales, profit, loss, unit ordered etc.Installation of OBIEE 11g involves 5 steps:1) Installation of Loopback adopter2) Install JDK3) Installation of database4) Installation of RCU (Repository Creation Utility)5) Installation of OBIEE softwareInstalling a Loopback Adapter on Windows XP-32 BitMy System configuration: Ram 8 GB and windows XP Installed on virtual machineOpen the Windows Control Panel.1. If you are in Category view switch to Classic view2. Double-click Add Hardware to start the Add Hardware wizard3. In the Welcome window, click next4. Follow the screen shots one by one

In the Properties dialog box, click Use the following IP address and do the following:A. IP Address: Enter a non-routable IP for the loopback adapter. For instance: i. 10.10.10.10 ii. 192.168.x.x (x is any value between 0 and 255)b. Subnet mask: Enter 255.255.255.0.c. Leave all other fields empty.Click OK

Close Network Connections.Restart the computer.Add a line to theSYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hostsfile with the following format, right after thelocalhostline:

IP_address hostname.domainname hostnameWhere: IP_addressis the non-routable IP address Hostnameis the name of the computer. Domain nameis the name of the domain. Note: Domain name is optional. In the following screen shot. I just used my Computer Name. Check the network configuration:

Lookback Adopter setting on windows 7

One of the most missed configuration issue when installing Oracle Database on Windows systems is failure to configure the Loopback adapter prior to installing the software on DHCP clients. Many Oracle products such as Database Control, Database Listener, Weblogic Server either standalone or bundled in JDeveloper use the machine IP address and or host name during the installation and may fail to function properly when either changes.

You can prevent this issue by configuring the Loopback Adpater on your DHCP machine prior to installing Oracle products. This document will detail the setup and configuration on Windows hosts in particular this install was done on Windows 7 Ultimate.Run the Command Prompt using Run As Administrator and enterhdwwiz.exeto start theAdd Hardware Wizard.

Click the Next button to continue.Select theInstal the hardware that I manually select from a list (Advanced)and click the Next button to continue.

Scroll down and select Network adapters and click the Next button.

On the Select Network Adapter screen select Microsoft for the Manufacturer and select Microsoft Loopback Adapter for the Network Adapter. Click the Next button to continue.

On the confirmation screen click the Next button to install the Microsoft Loopback Adapter.

Once the installation is complete click the Finish button to exit theAdd Hardware Wizard.

Now we need to configure the adapter. In the Control Panel open the Network and Sharing Center. Click the Change adapter settings on the left hand side of the panel.

On the Network Connections screen select Local Area Connection with the type of Microsoft Loopback Adapter. Right click the adapter and select properties.

On the Properties pages select Internet Protocol Version 4 (TCP/IPv4) and click the Properties button.

On the Internet Protocol Version 4 (TCP/IPv4) Properties page enter IP information for an IP address in one of the private address space (10.x.x.x , 172.16.x.x or 192.168.x.x). Care should be taken when choosing your address that it is also not in the same address range as your work or home network settings.

Once you have entered the IP information click the OK button to continue. You will be taken back to the Properties page for the Local Area Connection, click the Close button to finalize the changes.

ORACLE11G INSTALLATION STEPS:Navigate to Oracle_11g DirectoryNavigate to the folder C:\Oracle_11g\ database and run the exe file setup.exe.

Click Run.

Leave EMail blank. Un-check Receive Security Updates tick-box. Click Next.

If integration with Oracle Support is required, it can be configured later. Click Yes.

Select Create And Configure A Database. Click Next.

Select Desktop Class. Click Next.

Note Global Database Name for example here it is orcl in some installations it may be different. Enter Administrative Password admin123 Enter Confirm Password Click Next. NOTE: Please remember the password.You can give password as per your wish but please make note of all the passwords.

Check the summary. Click Finish.

Depending upon the specification of the desktop wait for approx 30 mins

Depending upon the specification of the desktop wait for approx 30 mins

Database Console can be configured later. Click OK.

Note down any information required from the Database Configuration Assistant summary. Click password management unlock scott and give password tiger and confirm password, unlock sh give password as sh and confirm it, for sys give password sysdba, for system give password as manager.

Click Close.

RCU InstallationNavigate to RCU BIN DirectoryNavigate to the folder D:\RCU\bin and run the batch file RCU.bat.

RCU Welcome Screen

Create Repository Schema

Provide Database Details and SYS Password Host name : localhost Port : 1521 Service Name : orcl Username : sys Password: sysdba

Click on ignore

Schema Prefix Check oracle business Intelligence

Select the Repository Type

PLATFORM and MDS Schema Passwords Give passwords for all schemas for example Password: admin123 Confirm password: admin123You can give password as per your wish but please make note of all the passwords.

Tablespace metadata summary

Tablespace metadata summary

Tablespace check

Pre-Creation Summary

Schema creation in progress

Schema Creation Summary

OBIEE 11g INSTALLATION STEPS

Username: weblogicPassword: welcome123Confirm password: welcome123You can give password as per your wish but please make note of all the passwords.

Connect string: localhost:1521:orclBIPLATFORM Schema Username: DEV_BIPLATFORMPassword: admin123This password is which we have given in rcu installation step4.Same password you need to give here also.

Click on Finish.Default web page will be opened close it.

Next copy tns filesFrom C:\app\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN from this location copy tns names fileAnd paste in2) C:\OBIEE117home\Oracle_BI1\network\admin And3) C:\OBIEE117home\oracle_common\admin

And copy one of these locations (2 or 3) and paste inC:\OBIEE117home\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\ user-> Right click-> edit and paste the path and save it.Now restart the system once and enjoy practicing ALL THE BEST. OBIEE 1) Connection pool:- It is an object it is useful to store connection related like username, password and databases. Specifies the ODBC or native data source name. Allow multiple users to share a pool of data source connections.Creating RPD and Importing metadata:Go to start->all programs->OBIEE 11g->BI Administration->File->New Repository->Repository Information->RPD name->Location->Import metadata: YES->RPD password->Retype password->next->Select Data Source->connection type->OCI 10g/11g->Data source name: orcl->User Name: supplier2->password: supplier2->Next->Select Metadata Types->Next-Select Metadata objects->expand supplier2->select tables D1_CALENDARS2, D1_CUSTOMER2, D1_ORDERS2, D1_PRODUCTS->Import selected->Give the connection pool name->Finish.

2) Physical layer:-

Physical layer is a connection between database and repository by using connection pool. We can import the data from the different sources, number of connection pools, aliases like tables and columns, duplicate tables, opaque views and physical joins etc Physical layer objects:-1) Database2) Connection pool3) Schema folder4) Physical tables5) Physical columns6) KeysPrimary Keys in Dimension Tables:D1_CALENDARSyymmddD1_CUSTOMER2NewkeyD1_PRODUCTSProductkeyForeign Keys in Fact Table:D1_ORDERS2Period key, Custkey, ProdkeyProcess:In physical layer double click on each table-> select keys tab->define primary key->. (For D1_CALENDAR2- yymmdd, D1_CUSTOMERS New key, D1_PRODUCTS-Productkey are primary keys)->select schema folder supplier2->On the Top from tool bar menu, select physical diagram ->select New Join->Join tables from Fact to Dimension tables->Depending on foreign key and primary key relation join the tables. -> D1_ORDERDS2 period key, prod key and cust key are foreign keys, join foreign keys with corresponding primary keys in Dimension tables). Join type in physical layer is Physical Foreign key join or Equi join. This Join is used only in Physical Layer. Properties of Equi Join are always operator will be = Cardinality, Driving table and Type of join will be disabled.

3) BMM layer:- It is also called as a logical layer. Actually we implemented the logic here. All the logical changes are done in BMM layer. We can do the calculations; create the logical tables and columns. Establish the joins like physical and logical or complex joins, dimensions, level-based measures, time series functions etc.. BMM layer objects:-1) Business model2) Logical tables3) Logical columns4) Logical table sourceIn BMM layer Right click->New Business Model->Name it as Sample BMM->ok->Drag and drop all tables from physical layer to Business model->Notice that fact tables are noticed with # symbol->If you want to rename tables right click on tables and columns you want to rename->If you want rename all at once use rename wizard by going to tools->utilities rename wizard->Execute->select BMM layer->select tables->Add->Next->Next->select the rule->Add->Next->Finish To define measures on Fact table fact columns->double click on fact column->aggregation tab->select sum->ok Type of join used in BMM Layer is Logical join or Complex join. Properties of Complex join are operators will be ,= Cardinality, Driving table and type of join are enabled in BMM layer.4) Logical table source(LTS):-It is a source for logical table from which table columns are we import from physical table.Logical table sources define the mappings from a single logical table to one or more physical tables. The physical to logical mapping can also be used to specify transformations that occur between the Physical layer and the Business Model and Mapping layer, as well as to enable aggregate navigation and fragmentation.Logical tables can have many physical table sources. A single logical column might map to many physical columns from multiple physical tables, including aggregate tables that map to the column if a query asks for the appropriate level of aggregation on that column.5) Presentation layer:- We can show only the content which we want to show the end-user. We can implement authorization and authentication implicit fact column and sort order column etc.

Presentation layer objects:-1) Subject area2) Presentation table3) Presentation column4) Hierarchy objects.6) Reorder tables:- Double click on subject area Select the presentation table Click the buttons move up (or) move down.7) Reorder columns;- Double click on presentation table Select column Click on up arrow or down arrow.8) Creating nested folder:-For creating nested folder or table the fact table will be last table in subject area. Double click on subject area Click on presentation tables tab Make sure that the table in which you going to create nested folders should be in the bottom. Click on add(+) Name it as Measures and in the description type give -> and click ok Again click on add(+) Name it as Keys and before name give as (space) Click on ok9) Testing RPD:-It is useful to check entire RPD logically some of the rules are Directly or indirectly logical columns should be mapped with physical tables otherwise it throws an error. If we have not defined keys it shows an error. Minimum two tables are required to join tables in BMM layer (1dimension, 1 fact table).Consistency check manager:-Display consistency check messagesa) Errors: Must be fixed to make the repository consistent.b) Warnings: NO NEED TO WORRY.10) Servers in OBIEE 11g:- 1) Oracle BI Server:- It is a main server It controls all data security rules. 2) Oracle BI presentation server:- It is used to develop reports, dashboards, key performance indicators, scorecards etc.3) Oracle BI Javahost:- It is used to display charts.4) Oracle BI Scheduler:- It is used to schedule reports.5) Oracle BI cluster controller:- It is useful to integrate n number of servers 6) Weblogic server:- It is a web server useful to access reports and dashboards globally.Above 1 to 5 servers are integrate in OPMNCTLOPMNCTL: - oracle process management network control.11) Loading Repository in online:- 1) Close RPD:- Go to file menu of administration tool -> click on close. 2) Start weblogic server alone or all obi servers C/obiee/userprojects/domain/bifoundationdomain/weblogic. 3) Configure RPD in EM Open internet explorer page Give URL http://localhost:7001/em Provide username, password . Click on login. 4)Click on business intelligence -> core application->Deployment->repository->lock and edit configuration->close->click on repository file->click on browse->select RPD->click on open-> give repository password->confirm password->click on apply->click on active changes. 5) Start or restart oracle BI serverClick on availability->select BI server->click on restart selected->yes ->after starting the BI server click on close. 6) Confirm RPD loaded or not by checking log file go to the pathC/obiee/instances/instance1/diagnostics/logs/oracle BI server component/core application_obis/nqserver.log.Open nqserver.log file observe our RPD name in the bottom of the notepad. 7) Confirm RPD loaded or not by developing a report Open internet explorer Type URL http://localhost:7001/analytics provide username, password. Click on new->analysis->select subject area->from subject area pane double click on columns year and dollars->click on results.Note:-1) Whenever we click on active changes in backend RPD Xerox copy will be created in repository folder.2) In nqs config file RPD name will be modified.c/obiee/instances/instance1/config/oracle BI server component/core application_obis/nqs config12) Modify rpd in online mode:- First check out the object Modify metadata Check in Next save RPD Use copy as option to override original RPD (optional) Reload server metadata.

13) check in and check out:- These options will be available in only online mode. Taking an object out of oracle bi server is called as checkout. Whenever an object is checked out then automatically that object will be locked that means no other user can be modify that object. Once we done modifications we need to check in to reflect these changes into oracle bi server. To reflect these modifications in reporting end we need to reload server Meta data.14) Online verses offline:-Offlineonline

1) Before loading RPD into oracle BIServer.1) Once RPD is loaded into oracle BI server.

2) Most of the development will be done here. 2) Enhancements (small changes) work will be done in online mode.

3) In offline mode before implementing MUDE, only one user can work on RPD file. 3) In online mode n number of users can Work by implementing MUDE (Multi User Development Environment).

4)Cache not available4) Cache available only on online mode.

5) Cannot set log levels in offline mode.5) Can set log levels in online mode.

15) Log levels:- It will show how much amount of information should be written in log file. We can set log levels only when RPD is in online. I.e. no log level, to improve the performance of environment. In development and testing environments we use log level 2. Log level 0 gives no log found. Log level 1 gives Logical query only. Log lever 2 gives physical query and logical query. Log level 2 gives repository name, subject area, number of rows returned to the client. In production we use 0 log level.

Process to set log level:Open RPD in online mode->click on manage->click on identity->click on action->set online user filter->specify wild char(*) click on ok->select user (weblogic) double click on it ->set log level->click on ok.Set log level 1 and develop a report in analytics->go to administration->manage sessions-check view log from bottom second.Set log level 2 and develop a report in analytics->go to administration ->manage session-check view log from bottom second.And also check in C:\Obiee\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis\NqQuery.log.

16) Web catalog:-1) It is a predefined folder structure, it is useful to store reports, dashboards, kpis, score cards, user profiles etc..2) Filters will be stored in a sub folder called as _filters.3) Dashboards will be stored in a sub folder called as _portal.

Creating web catalog:-1) Configure EM:- Under presentation catalog Go to end of the catalog location path->after catalog type name as: CATALOG1223->click on apply->click on active changes->click on close.2) Restart oracle BI presentation server Go to capacity management->click on availability->select oracle BI presentation server->click on restart click on ok.3) confirm catalog in catalog folder:- Navigate to below folder c/obiee/instances/instance1/bifoundation/oracle bi presentation component/core application_obis/catalog.Note:-1) Whenever we start oracle bi presentation server it will read instance config.xml file.2) Instance config.xml file locationc/obiee/instances/instance1/config/oracle bi presentation services component/core application/instance config.xml.Paths:-1) Repository location:-C:\Obiee\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis\repository2) Weblogic server location:-C:\obiee\user_projects\domains\bifoundation_domain/weblogic batch file.3) Log file path:-C:\Obiee\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis\Nqserver.log.C:\Obiee\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis\NqQuery.log.

4) Nqs confiq file path:-C:\Obiee\instances\instance1\config\OracleBIServerComponent\coreapplication_obis\Nqsconfig.5) Catalog folder:-c\obiee\instances\instance1\bifoundation\oracle bi presentation component\core application_obis1\catalog.

6) Instance config file path:-c\obiee\instances\instance1\config\oracle bi presentation services component\core application_obis1\instance config file.Types of components:-1. Window based (or) thick (or) non browser based components. Administration tool Catalog manager Job manager Start BI services etc...2. Web based (or) thin (or) browser based components. Enterprise manager Analytics Console BI publisher.17) Adding multiple sources:-Normalized table:- It is a small table data will not be duplicated.Normalized table structure:- Consists of many tables where data has been split or normalized. Is used for inserts and updates. Does not work well for queries that perform business data analysis. It is an OLTP data structure.Denormalized table structure:- Follows a business model and is easier to understand. Has data that may be duplicated in several locations in a database. Can take the form of a star schema. Provides better query performance. It is an OLAP data structure.

1) Adding Multiple sources to LTS:-1. Import pricelist, product_type, product_subtype, product_diet_types, tables to physical layer.2. Give joins to Products->product_diet_types (dietcode=dietcode) Products->product_sub_type (subtype code=subtype code) Produc_sub_type->product_type. (typecode=Typecode)Whenever you are dragging and droping new columns from physical layer to logical layer drag and drop on LTS.Whenever you are dragging and droping existing columns from physical layer to logical layer drag and drop on Logical Table.

3. Select product_diet_types column and drag and drop to products LTS.4. Select product_sub_type column and drag and drop to products LTS.5. Select product_type column and drag and drop to products LTS.6. Add new columns to presentation layer.7. Take criteria columns item type, type code->observe output.8. Observe logical query.

2) Add Multiple LTS to LT(logical table):-1. Delete item type column in BMM layer.2. Drag and drop product type table into products logical table.3. New LTS is created in products table.4. Add new column to Presentation layer.5. Take criteria columns item type, type code->observe output.6. Observe logical query.18) Dimension Hierarchies:-Hierarchies are 3 types1) Level based hierarchies2) Parent child hierarchies3) Unbalanced hierarchies (ragged, skipped)1) Level based hierarchies:- One to many relationship among columns is called as level based hierarchies. Level based hierarchies can also contain parent child relationships.Ex:-1) Time hierarchies: - year->quarter->month->day.2) Customer hierarchies:-Region->district->sales rep->customer.3) Product hierarchies:-Type->subtype->generic->specific.First on Times table in Logical layer create Quarter Column.Go to LTS of the Times logical table-> select on column mapping->click on add new column->Give the name as Quarter->ok-> click on edit expression ->use case when statement ( First write as CASE WHEN and then select MONTH_IN_YEAR from columns as given below)CASE WHEN "ORCL".""."SUPPLIER2"."D1_CALENDAR2"."MONTH_IN_YEAR"expand calendar dimension ->rename calendar detail as day level. Right click on day level -> new object -> parent level -> type: month ->click on ok. Right click on month level -> new object ->parent level ->quarter ->click on ok. Right click on quarter -> new object ->parent level ->year-> click on ok. For customer table hierarchy is Region->District->Salesrep->Names For Product table hierarchy is Type->subtype->Generic->Specific

Assign columns to hierarchy level:- From day level drag and drop year column onto year level. From calendar dimension table drag and drop quarter onto quarter level. From day level drag and drop month code onto month level. In day level delete all columns expect yymmdd and day name. In the detailed level of hierarchy primary key column must be there.Defining keys:- Right click on year level -> year column ->new logical level key ->click on ok Right click on quarter level ->new logical level key ->click on ok Right click on month code->new logical level key ->click on ok Click on save. Same process for the other hierarchies also. If any of the levels are having multiple columns at the level make sure only one column must be used for displaying. For that double click on the level->keys tab->uncheck the use display for the column with you dont want to display. For example In Times hierarchy detail level Day and yymmdd column you can see,here we need day name should be displayed but not the key column so uncheck key column.Define no of elements at this level option: We need to define this on each level to get the optimized query.From level to level this should be in increasing order values and distinct values must be given.For this you need to double click on each and every level in general tab you can Define no of elements at this level option.If you know exact elements at this levels you define them.Ex: For year level--1Quarter-- 10Month15Day-- 20

Defining content level:-1) Expand calendar.2) Double click on calendar logical source ->set content -> set logical level as day level.3) Click on ok.4) Expand sales->double click sales logical table source->set content->logical level as day level->ok. Same process for the other hierarchies also.

Test the process:- Load RPD into oracle bi server. Develop a report with year, dollars column. Click on year->column properties->then interaction->select primary interaction as drill->click on ok->click on results->click on 1998->click on quarter->click on month.Hierarchies are useful to:-1) Drill down2) Drill up3) Drill across (or) drill by4) Level based measures5) Aggregate navigation6) Time series functions.

Drill down:- Navigating from high level to low level is called as drilldown. Above test process we can consider as drill down. Year->quarter->month->day.Drill up:- Navigating from low level to high level is called as drill up Day->month->quarter->year.

Process for Drill up:- Open RPD in online mode-> double click on day level->preferred drill path->click on add->select month level->click on ok. Check in changes & reload server metadata. Develop a report with year, dollars Click on year->column properties->interaction->under value->primary interaction as drill->click on ok. In results click on any one number and observe month data.Drill across:- Navigating from one hierarchy to another hierarchy is called as drill across. Day ->region

Process for drill across:- Go to RPD->double click on day level remove month column ->click on add->select region->click on select->check in. Reload server metadata Develop a report with year, dollars. Click on year->column properties->interaction->primary interaction as drill->click on ok->click on results. Click on any value and observe the output.

19) Calculations:-In obiee we can do calculations in 3 ways1) Creating calculation based on logical columns.2) Creating calculation based on physical columns.3) Creating calculations based on by using calculation wizard.1) Creating calculation based on logical column:-1. Open RPD in online mode.2. Right click on BMM layer sales fact table->new object->new logical column.3. Name it as Pendings LC.4. Click on column source tab.5. Select radio button Derived from existing columns using an expression.6. Click on edit expression.7. Select category->logical tables, click on logical tables->sales.8. Columns->double click on unitord column->type (-)minus ->double click on unitship columns.9. Click on ok -> again ok -> Drag and drop into presentation layer sales table.

2) creating calculation based on physical column:-1) Expand sales logical table.2) Double click on sales logical table source.3) Click on add new column.4) Name it as pendings LC.5) Click on ok.6) Click on edit expression.7) Double click on unitord->type (-) minus->double click on unitship.8) Click on ok->again ok.9) Double click on pendings LC->aggregation tab->select sum.10) Click on ok.11) Drag and drop onto presentation layer sales table.

3) Calculations based on by using calculation wizard:-1) Right click on unitord column->click on calculation wizard->click on next.2) Select unitship->click on next.3) Select percentage change.4) Rename it as pendings CW.5) Click on next->click on finish.6) Drag and drop into presentation layer sales table.7) Check in changes-> reload server Meta data.8) Test the process:- Develop a report with year, month code, unitord, unitship, pendings LC, pendings PC & observe the output20) Working with analysis:-Develop a report with columns year, region, and dollars place cursor on any column and observe below properties.1) Sorting2) Edit formula3) Column properties4) Filters5) Delete

Column properties:- Column properties are useful to set below options.1) Style2) Column format3) Data format4) Conditional format5) Interaction6) Write back.

Conditional format:- It is useful to format data based on some condition. Click on dollars->column properties->condition format->add condition->click on dollars->select operator as: is less than -> value as: 1000000 -> click on ok -> select background color as red-> click on ok again ok again ok->click on results.Edit formula:- It is useful to create any report level calculations.Process:- In above report click on criteria -> add one more dollars column -> click on edit formula develop below expression. Rename second dollars column as taxCase when sales factsdollarsclick on table view, edit, drag and drop tax column from excluded to columns & measures.Hide: - It is used to hide the column from the final report. For E.g.: - costumer need month to be displayed in calendar month format. If we click on results with month in criteria. It will show alphabetic order. To get required format select month code and month in criteria, click on results it will show in calendar format but costumer doesnt need month code, we can do this by hiding column in column properties in that select column format, enable hide option.Exclude: - To remove the column from answers.

Bin: - It is used to group the rows. For E.g.: - To view columns under state as south present in south region. We can bin by using edit formula in that select bin tab and give the values and give bin name click ok.Filters:-Filters are used to restrict the data.Delete:-Used to delete the columns.Writeback:-Updated data write back to the database

Views1) View is a template2) View is useful to present data.3) Title & table are by default views.4) N number of views will be integrated in compound layout.5) We have 15 types of views those are1. Title 9. Column selector2. Table 10. View selector3. Pivot table 11. Legend4. Graph 12. Narrative5. Gauge 13. Ticker6. Funnel 14. Static text7. Map 15. Logical SQL.8. Filters1) Title:- It is useful to display name of the report, logo of the client & run date and time of report.2) Table:-1. Develop a report with year, month code, region, type, and dollars. Click on results.2. Click on table edit view drag and drop region into table prompts, type into sections.3. Click on year sigma symbol->select after->click on columns and measures sigma-> click after->click done. 3) Pivot table:-1. Pivot table is useful to cross tab the data rows to columns and columns to rows.2. We can give the aggregation rule in the pivot table.3. Develop a report with columns year, month code, region, sales district, type, dollars click on results.4. Click on new view->click on pivot table delete table->click on pivot table edit->drag and drop region into table prompts.4) Gauge chart:- It is nothing but speed meter. It is designed to compare one dimension with one measure.5) Funnel chart:- It is useful to compare one dimension with two measures and those two measures must be actual versus targets.6) Filters:- It is useful to capture filter conditions dynamically.7) column selector:- Dynamic representation of one column into another column is called column selector.8) view selector:- It is useful to select views dynamically.Filters1) Filters are useful to restrict the data in obiee2) We can develop filters in below ways1) Creating filter using criteria column.2) Creating filter using subject area column.3) Use a saved filter in a report (reusability purpose).4) Use one report output as filter in another report.5) Using variables.6) Using sql expression.7) Using group filters.8) Add a column filter prompt to a request.9) Add an image filter prompt to a request.10) Add a variable filter prompt to a request.1) Creating filter using criteria column:-1. Develop a report with 3 columns year, region, and dollars.2. Click on year filter type value as 1998.3. Click on ok and click on results.2) Creating filter using subject area column:-1. Develop a report with 3 columns year, region, and dollars.2. Click on filter button in filter pane->click on more columns.3. Expand product->select product type column->click on down arrow mark of value.4. Select beef, bread, and cheese.5. Click on ok.6. Click on results.

3) Use a saved filter in a report:-1. Click on new->click on filter.2. Click on supplier sales->from subject area pane click on year->type 1998.3. Click on ok.4. From subject area pane click on region->from drop down select central and east.5. Click on ok.6. Click on save.7. Select any folder->name filter as 1998 region->click on ok.8. Click on new->analysis->suppliersales->double click on year, region, dollars.9. Navigate to our saved filter->select it and click add (+).10. Click on ok->click on results.

4) Using one report output as filter in another report:- Develop a report with month code column:1. Click on month code column->click on edit formula.2. Type max (periods,monthcode) ->click on ok.3. Click on save->select any folder.4. Name it as current_month.5. Click on ok.Develop another report:-1. Develop another report with columns year, month code, region, dollars.2. Click on month code filter->select operator as is based on result of another analysis.3. Click on saved analysis browse->select our current_month report.4. Click on ok->again ok.5. Click on results.5) Using variables:-1. Develop a report with year, month code, region, dollars.2. Click on month filter.3. Click on add more options.4. Repository variables->type repository variables as current_month.5. Click on ok->click on results.6) Using sql expression:-1. It is equivalent to database sub query concept.2. It is a new feature in obiee 11g.3. Develop a report with year, month code, month, region, dollars4. Click on region filter->click on add more options.5. Sql expression->develop an expression.6. Select supplier salescustomersregionfromsupplier saleswheresupplier salescustomersregion=central.7. Click on ok.8. Click on results.

7) Using a group filters:-1. Develop a report with year, region, type, dollars. Develop three filters1) Year=1998.2) Region=central.3) Type=beef (or) bread.2. Click on first AND operator.3. Observe that is changed as OR & 1st & 2nd filters (year, region) are now called as group filter.4. If you want ungroup click on 2nd filter.5. Edit filter group->click on ungroup.8) Add a column filter prompt to a request:-1. User friendly filter or runtime filter is called as prompt.2. Develop a report with region, sales district, dollars.3. Click on prompts->column prompt.4. Sales district->click on expand option.5. Select limited values by region.6. Click on ok->click on preview.7. Select central->click on ok.9) Add an image filter prompt to a request:- Whenever we are running the report, image will be displayed. If you click on any part of the images then that part corresponding data will be displayed. Here we are creating image prompt on Region column.

Process1:- (Using Internet)1. Develop a report with Region, District and Dollars column in analysis.2. As we are creating image prompt on Region column from advanced tab copy Customers. Region query and paste it in a notepad.3. In Google search for good usa map image ,view original image->copy the image URL4. Now select Prompts tab in analysis click on + -> select image prompt->Paste the image URL which we have searched from Google->Now in HTML image dialog box copy the usa map coordinates which are given in usamap.txt material.

5. Click on Extract image from HTML6. Copy customers. Region from note pad to column section->Type as below Area title columns value Select Central customers.Region Central Select Eastcustomers.Region East Select Westcustomers.Region WestProcess 2: (Using Intranet)For this first we need to have Inet pub folder in C drive.Copy one usa map image in Inetpub->wwwrootNow copy the image name->open any one of the browsers->type http://localhost/usa-map.jpg-enter->now copy that image URL1. Develop a report with Region, District and Dollars column in analysis.2. Now select Prompts tab in analysis click on + -> select image prompt->Paste the image URL ->Now in HTML image dialog box copy the usa map coordinates which are given in usamap.txt material.

3. Click on Extract image from HTML4. Copy customers. Region from note pad to column section->Type as below Area title columns value Select Central customers.Region Central Select Eastcustomers.Region East Select Westcustomers.Region West10) Variable prompt:- It is useful to store user response in one variable and we can call this variable in any calculations, filters in any view etc... This variable is called as presentation variable. Look and feel wise it is a new concept in obiee 11g.Process:-1. Develop a Report with Region, District and Dollars column in analysis.2. Click on prompts tab-> + -> select Variable prompt->Give name for presentation variable as selection_PV->copy variable name->ok3. Now go to criteria on Region column tool bar menu click on filters->click on Add more options->select presentation variable and now paste variable name here.4. Click on Results tab->you will see as No results5. Left side below from view section select Title view-> edit view in the tile give as @{ selection_PV} this is for dynamic display of title in the report.6. Now again go to Prompts tab-> click on preview and enter the value as Central-> you can view Central related Districts as well as Title also.7. Similarly type other region Names.

23. Dashboards1. Dashboard is a template.2. It is useful to integrate n number of reports.3. End users (clients (or) customers) will be able to see only dash boards.4. Dash boards are two types.1) My dashboard.2) Shared dashboard.My dashboard:-1. It is personalized view of the data.2. My dashboard will be created automatically whenever one user is created.3. We can access my dashboard by clicking dashboard->my dashboard.Shared dashboard:-1. Shared dashboard is a public or corporate dashboard.2. It can be accessed by any user in the world but they need to have valid permissions.Creating shared dashboards:Step 1:-1. Go to new->click on dashboard->provide name as batch123_db.2. Select location as batch123folder/shared folders/batch123/dashboards.3. Select add content later->click on ok.Step 2:-1. Develop a request with columns region, district, dollars.2. Click on region filters.3. Select operator as is prompted.4. Click on save it and name it as d1.Step 3:-1. Similarly to above develop a report with region, year, and dollars.2. Is prompted on region column.3. Save it as name it as d2.Step 4:-1. Develop a report region, type, dollars .2. Is prompted on region.3. Save it as d3.

Step 5:-1. Integrating reports into dashboards.2. Click on dashboards->click on batch123_db3. Click on edit.4. Under catalog expand shared folders.5. Drag and drop d1 and d2 into work area save it.6. Click on add dashboard page, name it as page2->click on ok.7. From shared folders batch123 drag and drop d3, click on save.8. Click on run.Dashboard prompts:-1. These are useful to filter dashboard data as per client requirements.2. These are dashboard level user friendly filters.3. These are 3 types of prompts. They are1) Column prompts.2) Image prompts.3) Variable prompt.

1) Column prompt:-1. Click on new->dashboard prompt.2. Click on supplier sales->click on new column prompt.3. Select region->ok again ok.4. Click on save.5. Select batch123 folders name it as region prompt.6. Click on ok.Integrating dashboard prompt into dashboard:1. Click on dashboards->batch123_db.2. Page options->edit dashboard.3. Shared folders->batch123.4. Drag and drop region prompt on top of the sections.5. Click on save.6. Click on run.7. In dashboard prompt select central8. Click on apply.9. Observe the result in page 1 and page 2.10. If you want to affect dashboard prompt only for one page.11. Then click on dashboard prompt properties.12. Scope->page->click on save->click on run.13. Select east->click on apply.14. Notice that page is affected but page 2 is not affected.2) Image prompt:- Whenever we are running the report, image will be displayed. If you click on any part of the images then that part corresponding data will be displayed.Process1:- Develop a report with column region, district, and dollars.1. As we are creating image prompt on Region column from advanced tab copy Customers. Region query and paste it in a notepad.2. In Google search for good usa map image ,view original image->copy the image URL3. Now in analysis->New->Dashboard prompt-> click on + -> select image prompt->Paste the image URL which we have searched from Google->Now in HTML image dialog box copy the usa map coordinates which are given in usamap.txt material.

4. Click on Extract image from HTML5. Copy customers. Region from note pad to column section->Type as below

Area title columns value Select Central customers.Region Central Select Eastcustomers.Region East Select Westcustomers.Region WestProcess 2: (Using Intranet)For this first we need to have Inet pub folder in C drive.Copy one usa map image in Inetpub->wwwrootNow copy the image name->open any one of the browsers->type http://localhost/usa-map.jpg-enter->now copy that image URL1. Develop a report with Region, District and Dollars column in analysis.2.Now select Prompts tab in analysis click on + -> select image prompt->Paste the image URL ->Now in HTML image dialog box copy the usa map coordinates which are given in usamap.txt material.

3. Click on Extract image from HTML4. Copy customers. Region from notepad to column section->Type as below Area title columns value Select Central customers.Region Central Select Eastcustomers.Region East Select Westcustomers.Region West3) Variable prompt:- It is useful to store user response in one variable and we can call this variable in any calculations, filters in any view etc... This variable is called as presentation variable. Look and feel wise it is a new concept in obiee 11g.Process:-1. Develop a Report with Region, District and Dollars column in analysis.2. Go analysis->New3. Click on Dashboard Prompt-> + -> select Variable prompt->Give name for presentation variable as selection_PV->copy variable name->ok4. Now go to criteria on Region column tool bar menu click on filters->click on Add more options->select presentation variable and now paste variable name here.5. Click on Results tab->you will see as No results6. Left side below from view section select Title view-> edit view in the tile give as @{ selection_PV} this is for dynamic display of title in the report.7. Now again go to Prompts tab-> click on preview and enter the value as Central-> you can view Central related Districts as well as Title also.8. Similarly type other region Names.Dashboard objects:-We have below dashboard objects.1. Column2. Section3. Link or image4. Alert section5. Action link6. Action link menu7. Embedded content8. Text9. Folder Column & section are useful to align content as per requirement.

3) Link or image:-It is useful to navigate from one dashboard to another dashboard or report or webpage by using a link or image.Process:-1. Drag and drop link or image into dashboard.2. Click on link or image properties->click on browse.3. Select any one report in the images.4. Fmap:images/report-bad-percentage.jpg where fmap=c:/bi11g/instance/instance/bifoundation/oracle bi presentation server component/core application_obis1/analytics res/s-mobile/style/images.5. Click on ok->click on save.6. Run click on image->click on return.Embedded content:- It is useful to insert one webpage into another webpage It is useful to get latest information from any website to dashboard.Process:-1. Drag and drop embedded content.2. Dashboard object into work area.3. Click on embedded content properties.4. Type http://www.gmail.com5. Click on ok->save.6. Run.Text:- It is useful to provide some comment lines or to write scripts.

Process:-1. Drag and drop text object into dashboard.2. Type welcome to report.3. Preview->ok.

Folder:- It is useful to showcase saved content in a dashboard. It makes end user to access reports & dashboards easilyProcess:-1. Drag and drop folder object below text object.2. Click on folder properties->browse.3. Select shared folders->batch123->ok->save->run.

Combining the similar request:-In this concept we will use below set operators like1. Union2. Union all3. Intersect4. MinusTo work with set of operators we need to make sure1. Number of columns in two requests must be same.2. Corresponding columns data types must be came.3. Union will not provide duplicates.4. Union all will provide duplicates.

In this concept we can develop a report from two different reports which are coming from different subject areas or same subject area.Process:-1. From subject area pane click on customers, dollars.2. Click on dollars column filter3. Select operator is between->value 5000 & 15000.4. Click on ok.5. Click on combine results.6. Select supplier sales DM->click on customer, dollars7. Click on dollars column filter8. Select operator is between->value: 10000, 20000->ok->results.BI DeliversOracle BI delivers are useful to create schedules (or) agents.1) Configuring schedule table2) Delivery devices3) Delivery profiles4) Agents5) Alert section dashboard object6) Job manager.1) Configuring schedules:- In obiee 11g configuring of oracle bi scheduler server will be done with the installation of obiee 11g product (in 10g it is manual process). With the installation scheduler tables are by default installed in DEV_BIFOUNDATION schema. Scheduler tables are1) S_NQ_JOB2) S_NQ_PARAM3) S_NQ_INSTANCE4) S_NQ_ERR_MSG2) Creating delivery device:- Device will be created b each & every user his own. As a developer we need to share required document to clients. Here delivery devices are email, cell phones, web pages or web browser, pager, devices capable of handling standard based communication. For this you need to have SMTP server (simple message transfer protocol).Process:-1. Click on weblogic->my account.2. Click on delivery options->select device3. Name: batch123.4. Type as html email, mail id:batch9966@gmail.com->ok.5. Similarly add phone by select device phone.3) Delivery profiles:-Normally each and every employee will have two types of profiles.1) Office profile2) On road or personal profile.Process:-1. Click on web logic->my account.2. Delivery options->delivery profile.3. Create delivery report.4. Name it as officials.5. Select mail id, high priority, phone number as normal priority.6. Similarly create another profile.4) Agents:-Agents are software based engines that execute requests and generate responses to appropriate people and devices.Agents containsPriority and visibility,conditional request schedule recipients, delivery content, destination.Process:-1. Develop a report with columns year, region, and dollars.2. Click on save.3. Select batch123 folder name it as agent report.4. Click on ok.5. Click on new->under actionable intelligence.6. Click on agent->click on schedule.7. Select frequency as once.8. Click on select data & time; give 2 mins forward from current time.9. Select delivery content.10. Select analysis->click on browse->select agent report->click on ok->click on save into batch123 folder with the name of first agent.11. Beside home click on alerts and observe our first agent ->click on it observe output.5) Alerts dashboards object:-It is useful to capture all active alerts.Process:-1. Click on dashboard->click on batch123_db.2. Page options->edit dashboard3. Drag and drop alert section in the 1st position of the column4. Click on save5. Click on run observe our 1st agent6) Job manager:- It is windows based component. It is useful to monitor the status of agents.Process:-1. All programs->obiee->job manager.2. Go to file menu3. Open scheduler connection.4. Provide administrator name as web logic, password:weblogic123->click on ok5. Select web logic user & observe all our agents & their status conditions.Aggregate tables1) Aggregate table show summarized data2) Aggregate tables are useful to increase the performance3) These are also known as level based partition4) Data in dimension and fact sources is stored at detailed level5 Here data needs to rollup or summarized during analysis6) Detailed tables contain many rows and takes time to read and calculate7) Aggregate table contains fewer rowsFor example we can take one year sales, here we consider two tables one is detailed table another one is summarized table. Detailed table contains 365 days sales 365 records it takes time to retrieve data 3or 4 minutes but summarized table contains only 4 quarters data it takes one minute to retrieve data.Process:-1) First import ordersagg1, months, salesreps and producttype to physical layer2) Give joins in physical layerorders_agg1->prod_typeorders_agg1->monthsorders_agg1->salesreps3) Copy physical columns to logical columns4) Define content level tab for customersDouble click on customers ->select content tab-> select logical levelCustomers->customersDouble click on salesreps lts->select content tab-> select logical levelsalesreps->salesreps5) Define content level tab for monthsDouble click on calender lts->select content tab-> select logical level->calender->dayDouble click on months lts->select content tab-> select logical levelMonths->month6) Define content level tab for product_typeDouble click on products lts->select content tab-> select logical levelProducts->specificDouble click on salesreps lts->select content tab-> select logical levelproduct_type->type7) Define content level tab for sales fact tableDouble click on orders lts-> select content tab->select logical level forCustomers->customers, calender->day, products->specific8) Define content level tab for sales fact tableDouble click on orders_agg1 lts-> select content tab->select logical levelsalesreps->salesreps, product_type->type, months->month9) Double click on customer table->select salesrep key & delete10) Double click on times table->select monthcode key & delete11) Check in changes -> save-> copy to original rpd12) Set loglevel 213) No changes in presentation layer14) Reload servermeta data15) Generate report with year, dollarssimilary year,quarter,dollars.Year,quarter, month, dollars.Year,quarter, month, day, dollars.Observe output.16) Go to->administration->manage sessions->see last but one log.Aggregate or normal table data.

Partitions and Fragmentations1) It is a process of splitting large tables to small tables, it may be a fact or dimension.2) partitions are 4 types1) Fact based2) Value based3) Level based4) ComplexFact based partition:- Data is partitioned by fact when different fact data is stored in different tables Actual sales and targets are fact level partitions.Value based partition:- Data is partitioned by values then data is split into separate tables according to the values of the dataLevel based partition:- Data is partitioned by level when the same facts are stored in separate tables at different levels of aggregation. Detailed sales are summarized.Complex based partition:- Combination of level based and value based partition is called as complex based partition.Process:- Value based partition:-1) Select distinct substr(name,1,1)from d1_customer2 where name click on content tab-> edit expression"suppliersales"."customers"."customer" content tab->edit expression"suppliersales"."customers"."customer">='N'10) Check in changes->save11) Reload server meta data12) Generate report withcustomer,dollars.13) Customers, give filters is less than 'C'14) Administration->view log->see queryTake customer, dollars without filtersObserve queryIt gives cust1 union all cust2Variables1) Variables are containers which hold specific values2) Value in variable is not constant3) It is going to change depending on the expression, time, database updates4) Variables are useful to handle dynamic scenarios5) We have 4 types of variables1) Repository variables2) Session variables3) Presentation variables4) Request variables6) Repository and session variables are known as global variables7) These two variables created in rpd and used in rpd and web catalog8) Presentation and request variables are created in dashboard prompt and used in web catalog.Repository variables:-1) These variables also called as oracle bi server variables.2) This variable values will be updated whenever server is restarted or started3) Repository variables are 2 types Static repository variables Dynamic repository variables4) Syntax for Repository variables is VALUEOF(variablename)Static repository variables:-1) If we want to use a constant value in n number of places then we will go for static variables2) Example:-Database name, user name of connection pools.

Process1:-1) Create a static variable to filter data for current year2) Go to manage-> variable->select on static variable3) Right click->new repository variable->name it as current_year4) Give Default initialize:1999->click on ok5) Check in changes->save->reload server metadata6) Develop a report with year,region and dollars7) Click on year filter->click on add more options->select repository variable8) Give repository variable current_year->click on ok9) Observe results ->1999 data will be present.Process 2:-1) Go to Manage->variables->action->new->Repository->variable->click on it.2) Give name it as partition_value3) Give Default initializer as 'N'->click on ok4) Double click on cutomer1 LTS->content tab->edit expression->logicaltables->customers->nameVALUEOF("partition_value")Click on ok.5) Double click on cutomer2 LTS->content tab->edit expression->logicaltables->customers->name >= Repository varialbles->VALUEOF("partition_value")Click on ok.6) No changes in presentation layer7) Check in changes->save->copy as original rpd8) Reload server metadata9) Generate report with name, dollars10) Give filter to name less than C11) Observe logical queryDynamic repository variables:-1) The values in the dynamic variable are going to change according to the database query, which is written in initialization block.Initialization block:-1) It is a database query container, multiple values are written in initialization block2) Repository variables are called oracle bi server variables3) This is variable values will be updated whenever server is restarted or started.For example I have two customer tables I want to know 1st customer table max value and 2nd customer table starting value we use dynamic repository variables.Process:-1) Open rpd->go to manage->variables->action->new->repository->initialization block->name:IB_PF2) Click on Edit data source3) Type query select min(substr (name,1,1))from customer24) Click on browse, select connection pool->click on ok5) Click on test->click on ok6) Click on Edit data target->new->name:V_PF7) Provide default initialize as 'A'8) Click on ok->close variable manager9) Double click on customer1 lts->content tab->edit expression10)"duplicate"."customers"."name"click on ok11) Double click on customer2 lts->content tab->edit expression12)"duplicate"."customers"."name">=valueof(v_pf)->click on ok13) No changes in presentation layer14) Check in changes->save-> Reload server metadata15) Generate reports with name,dollars,give filter name to lessthan 'C'16) Observe output. goto administration->manage sessions->view log. Output coming from cust1 table.Session variables1) logout-login time is called as session2) Any application will support n number of sessions3) Session variables will be populated separately for each & every session4) Session variables are two typesi) System session variablesii) Non system session variablesSystem session variables:-1) These are pre defined session variables used by oracle bi server for specific purpose such as authenticating users, we have below system variables.(case sensitive)1) User2) Display name3) Group4) Web group5) Log level6) Roles7) Permissions8) User locate9) Time zone2) These variables are useful in special cases such as authenticating user. These variables should not use for any other purpose.(as a static variable name or dynamic variable etc)3)Using system variable in report column go to reporting end.develop a report with year,year->click on 1st year edit formula->remove predefined periods year->click on variable->session->user->click on ok ->again ok->click on results4) Develop a report to display current year data throughout that report life

Non system session variables:1) These are application specific customized variables2) By default in obiee11g users,groups,roles are integrated while installation3) Roles are newly introduced in obiee11g4) Groups and user are created in webbased component is known as console based on LDAP server5) Roles are created by using Eneterprise Manager.6) For best practice always group name should end with s and roles should not end with s7) Set of users are called as group8) Set of permissions is called as role.

Console:-1) It is a webbased component2) LDAP is internally integrated in console3) LDAP means light weight direct access protocol4) LDAP is a third party server used to manage users and groups5) In obiee11g LDAP is integrated while installation.

Roles Groups UsersBI CONSUMER -------BI CONSUMERSBI AUTHOR ---------BI AUTHORSBI ADMINISTRATOR --BI ADMINISTRATORS------WEBLOGICData security:-1) Session variables are used to implement data security2) It is useful to hide some of the data based on user login.Developing data security using system & non system variables:Scenario:Develop data security to see customer corresponding region data.Process:-1) Create user2) Create group3) Assign users to group4) Create a role5) Assign group to role6) Synchronize roles in admin tool7) Create dedicated connection pool8) Create initialization block and assign values to non system variables9) Create a data filter on role10) Test

Creating user1) Open Google chrome type URLhttp://localhost:7001/consoleProvide username: weblogicPassword: weblogic1232) Click on security realms->my realm->user & groups->click on users tab->click on newName it as AmerigoPassword: weblogic123Confirm password weblogic1233) Similarly create Alley Dog user->click on okCreating group:-1) Click on groups->new->name it as customers->click on okMapping users to group:-1) Click on amerigo users->click on groups->select customersClick on arrow click on save2) Similarly map another user alley dogCreating a role:-1) Set of permissions or policies are called role2) Open EM->click on business intelligence->core applicationSecurity->single sign on->configure & manage application roles->click on createProvide name as: cust->click on add group->click on group name arrow markSelect customers->click on arrow click on ok

Scenario:-Develop data security to see customer corresponding region dataProcess:-Open RPD in online modestep1:-Create dedicated connection pool:-1) In physical layer right click on orcl->new object->connection pool name it as IB_CPData source name: orcl, username: supplier2, password: supplier2 ->click on ok, again okStep2:-Creating non-system session variable & initialization block1) Manage->variables->action->new->session variable: V_USER_REGION.Default initialization: 'A'.2) Click on new->name it as; IB_USER_REGION->edit data source->Select region from D1_CUSTOMER2 where name=':USER'->click on browseSelect IB connection pool-> click on select->click on ok again ok.step3:-creating data filter1) Go to Manage->identity->select BI repository->application roles->double click on customers->Click on permission->click on select->click on ok2) Under data filter->click on mouse->click on edit expression->select customer table->Double click on region->type=select session variables->double click on V_USER_REGION->Click on ok again ok->close3) No changes in presentation layerCheck in changes->savestep4:-TestingLogin to analytics as a weblogic user develop report with region, salesrep, dollars. Click on save with the name of data_security.Reload server metadata. LogoutLogin with Amerigo user, navigate above saved report & observe output.Similarly observe login with Alley dog observe output.Time series functions1) Time series functions are useful to calculate performance of business by comparing historical data with current data.2) Time series functions are 3 types1) Ago2) Todate3) Period rolling [new feature in obiee11g)3) Chronological key:-1) It is a level identifier, used to identify the particular time value.2) It is key column or primary key column.3) To define time values uniquely double click on time dimension hierarchy enable times4) Double click on year, quarter, month, day levels select keys tab, give chronological key.1) Ago:-Comparing current data with old dataEx:-1month ago, 2months ago.2) Todate:-We get current dateEx:-YTD, QTD, MTD3) Period rolling:-This is to aggregate the previous month to current monthEx:-3 months rollup, 5 months rollup3Months rollup=previous 2 months + current month(-2,0).1) SyntaxAgo:-Ago ()2) Todate:-Todate ()3) Period rolling:-Pediodrolling (,)Steps:-1) Ago-month:-1) Right click on sales fact table->new object->logicalcolumn->click on it->give name:MonthAgo2) Click on column source->Enable Derive existing columns using expression3) Click on edit expression->under category click on functions->under functions click on time series functions4) Under time series functions click on Ago.5) For measures click on logical tables->click on sales->click on Dollars6) For levels->Time dimensions->calendar2dim->month->no.of periods->1->click on ok->again click on ok7) Drag and drop month column to presentation table

2) Year to till date:-1) Right click on sales fact table->new object->logical column->give name: YTD2) Click on column source->enable derived from existing columns using an expression3) Edit expression->under category functions->under functions ->click on time series functions->under time series funtions click on to date4) Todate ("supliersales","sales"."Dollars","supplier-sales"."calendar2dim"."year")5) Click on ok->again ok->drag and drop ytd to presentation table.3) Period rolling:-1) Period rolling ("suppliersales","salesfacts"."Dollars",-2, 0)2) Drag and drop above into presentation layer sales facts, check in->reload server metadata3)Develop a report with year, month code, dollars, month ago, variance percentage,ytd,3 months period rolling.

Page 11