Error :MAPPINGTM_6281ERROR: Partitioning option license required to run sessions with user-defined partition points. Use Case : If we use Sorter and aggregator with sorted input option enabled Solution : Add custom property AggSupprtWithNoPartLic = YES to integration Server
Error :Not able to link ports between two transformations Solution : Click link columns option as shown below .this option is available in toolbar and menu bar .we can use any one .
Key features of Oracle Business Intelligence for Microsoft PowerPoint:
1. View live, refreshable data from the Oracle Business Intelligence Server.
a. Insert BI tables, pivot tables and graphs into PowerPoint or Excel as
refreshable, editable objects.
b. Apply PowerPoint or excel formatting to BI data; formats are retained
when you refresh.
2. Copy and paste BI analyses from Oracle BI Answers or Oracle BI Interactive
Dashboards to PowerPoint or excel . The data, metadata, and view layout are
copied.
3. Secure BI Data from Oracle BI objects in PowerPoint or excel so that users must
log in to view the data. Secured objects can be refreshed.
Installation
1. login to Analytics
2. Under Get Started -> Click on Download BI Desktop Tools ->Click on Oracle
BI for MS Office
3. Click on Run the executable that you have downloaded and follow the wizard to
complete the installation
Configuration
1. Open Microsoft Power point or Microsoft Excel
2. In the Menu bar click on OracleBIàPreference
3. In Preferences window àselect Connections tabàselect New
4. In the New Connection window, fill the following fields
Server Name: Give any name (e.g. RRITEC)
BI Office Server: Enter the URL for the BI Office Server (e.g. RRITEC). You can
enter “localhost” if the BI Office Server is installed on your local machine.
Application Name: bioffice (deault)
Port: 7001
5. Click on Test connection. It should show the following screen, if not check the
connection details and correct them.
Note: This does not test the connection between the BI Office Server and Presentation Services.
6. Click on Oracle BI MenuàClick Login to login to Presentation Services using
the appropriate username(weblogic) and password(RRitec123)
7. You will get the error “Login failed. Please check the username, password
and Oracle BI Office server availability” It is because in the bioffice.xml
(C:\OBI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tm
p\_WL_user\bioffice_11.1.1\hsq62b\war\WEB-INF\) SawBaseURL is pointing to
9704 port. Change it to 7001 and then save the file. Restart the all services from
EM (http://rritec:7001/em) and try login again, it should work.
8. Once you are logged in, you will see the BI Catalog on the right pane of Microsoft
Power point.
0.8 LEVAL BASED MEASURES
1. If a measure column is calculated on one particular level then
it is called as level based measure.
2. Level based measures are useful to calculate share or percentages.
Step 1: Creating Level Based Measure
1. Open RPD in online mode ->right click on sales fact tables ->new object
->logical column ->total revenue ->column source ->select derived from existing column using expression
click on edit expression -> double click
on dollars column -> click on ok -> again ok -> drag and drop total revenue
onto customer dimension -> customer total level -> drag and drop total
revenue into presentation table .
Step 2: Understanding or testing Level Based Measure
1. Go to reporting end and develop a report with region ,dollars , total revenue.
2. Click on results. Notice that total revenue and dollars column displaying same
value for all the rows that mean it is calculating at the level of total customers.
Step 3: Using Level Based Measure in Share Calculations
1. Right click on sales fact table -> New object -> logical column -> name it as
share -> click on column source tab -> select derived from existing column
using an expression -> Edit expression -> double click on dollars column ->
double click on (/) divided by -> double click on total revenue * 100 -> click
on ok -> again ok.
2. Drag and drop share into sales presentation table -> check in changes.
Step 4: Testing
1. Reload server metadata. In above report (developed in step2 ) add one more
column share -> observe the result.
07 creating dimensional hierarchies
Hierarchies are 3 three types. 1. Level based hierarchies. 2. Parent child hierarchies (11g new features) 3. Unbalanced hierarchies (11g new features) Level based hierarchies:
One to many relationship among columns is called as level based
hierarchies.
Eg:
1. Time hierarchies Year -> quarter -> month -> day.
2. Customer hierarchies Region -> district -> sales rep -> customer
3. Product hierarchies Type -> subtype -> generic -> specific. Step 1: Creating time hierarchy Levels
1. Right click on period dimension table -> create logical
dimension -> dimension with level based hierarchy -> expand periods
dimension -> rename period detail as day level.
2. Right click on day level -> new object -> parent level -> type: month
- click on ok.
3. Right click on month level -> new object -> parent level -> quarter
click on ok.
4. Right click on quarter -> new object -> parent level -> year -> ok.
Step 2: Calculating quarter column
1. Right click on period dimension table -> new object -> logical column
-> name it as quarter.
2. Click on columns source tab ->select derived from existing
column using an expression.
3. Click on edit expression .
4. Develop below expression.
Case when “sales”, ”periods” , “month in year” < 4 then 1.
When “sales”, “periods”, “month in year” < 7 then 2
When “sales” ,”periods” ,”month in year” < 10 then 3
Else
4
End.
5. Click on ok ->again ok. Step 3: Mapping columns to hierarchy Levels
1. From day level drag and drop year column onto year level.
2. From periods dimension table drag and drop quarter onto
quarter level.
3. From day level drag and drop month code onto month level.
4. In day level delete all columns except yyyymmdd . Step 4: Defining Logical Keys
Each and every level must contains a key column,it is required
to identify unique values in that level
1. Right click on year level -> year column ->new
logical level key -> click on ok.
2. Right click on quarter column ->new logical level key -> click on ok.
3. Right click on month code -> new logical level key -> click on ok.
4. Click on save. Step 5: Defining Content level
1. Expand periods
2. Double click on D1_calendar logical source -> Click on content ->
set logical level as day level
3. Click on ok.
4. Expand sales -> double click on D1_orders2 logical table source
-> Click on content ->logical level as day level -> ok.
5. Drag and drop quarter column into presentation layer
periods presentation table
Step 6: Testing
1. Load RPD into oracle BI server.
2. Develop a report with year , dollars column.
3. 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.
Note : Similarly create product & customer hierarchy with below
levels as shown in screen shot and test by developing reports . Customer hierarchies
Region -> district -> sales rep -> customer Product hierarchies
Type -> subtype -> generic -> specific.
Hierarchies are useful to
1. Drill down.
2. Drill up.
3. Drill across (or) drill by.
4. Level based measures.
5. Aggregate navigation.
6. Time series measures. 1. Drill down:
1. Navigating from high level to low level is called as drilldown.
2. Above testing process we can consider as
drill down.(year->quarter->month->day) 2. Drill up:
1. Navigating from low level to high level is called as drillup(Day->Month->Quarter->Year) Process:
1. Open RPD in online mode -> double click on day level
-> preferred drill path -> click on add -> select month level
-> click on ok.
2. Check in changes & reload server metadata.
3. Develop a report with yymmdd , dollars.
4. Click on yymmdd -> column properties -> interaction
-> under value ->primary interaction as drill -> click on ok.
5. In results click on any one no. and observe month data. 3. Drill across:
1. Navigating from one hierarchy to another hierarchy is called
as drill across.
Eg: Day -> Region (drill across) Process:
1. Go to Rpd -> double click on day level remove month
column -> click on add -> select region -> click on select -> check in.
2. Reload server metadata.
3. Develop a report with yymmdd , dollars.
4. Click on yymmdd -> column properties -> interaction
->primary interaction as drill -> click on ok -> click on results.
5. Click on any value & observe the output.
4. Making two or more columns as target:
1. Go to Rpd -> double click on day level -> keys ->select
primary key -> edit -> select two columns(Day and
YYYYMMDD) -> enable use for display -> click on ok
again ok -> check in -> reload server metadata
-> test your work.
06 calculations
In OBIEE we can do calculations in 3 ways. 1. Creating calculation based on logical columns. 2. Creating calculation based on physical columns. 3. Creating calculations based on by using calculation wizard.
0.6.1 Creating calculation based on logical columns
1. Open RPD in online mode.
2. Right click on BMM layer sales fact table ->New object -> logical column.
3. Name it as cuts.
4. Click on column source tab.
5. Select radio button Derived from existing columns using an expression.
6. Click on edit expression
Select category -> logical tables.
Logical table -> sales
Columns -> double click on unitord column ->type Minus(-) ->
double click on unitship columns.
7. Click on ok ->again ok
8. Drag and drop into presentation layer sales presentation table.
0.6.2 Creating calculation based on physical columns
1. Expand sales logical table
2. Double click on D1_orders2 LTS.
3. Click on add new column.
4. Name it as cutsp.
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 cutsp ->aggregation tab ->sum
10. Click on ok .
11. Drag and drop onto presentation layer sales table.
0.6.3 calculation by using calculation wizard
1. Right click on unitord -> click on calculation wizard ->next.
2. Select unitship ->next.
3. Disable percentage change.
4. Rename it as Cutsw.
5. Click on next -> click on Finish.
6. Drag and drop into presentation layer sales table.
7. Check in changes.
8. Reload server metadata.
Testing :
Develop a report with year, month code, unitord, unitship, cuts,
cutsp ,cutsw & observethe output. Notes: 1. In logical calculations pre aggregation will take place 2. In Physical calculations post aggregation will take place 3. As a best practice use logical columns in calculations 4. Calculation Wizard is useful to handle NULL values and DIVIDE WITH ZEROerror 5. Calculation wizard is useful to calculate percentages easily.
05 Adding multiple sources
1. Add physical tables to an LTS or LT.
2. If data is not duplicated then that physical table can add to LTS or LT
(As a best practice add to LTS).
3. If data is duplicated then we need to add physical table to LT. In this
case we need to define content logical level. (It is used in aggregate
tables and partition tables chapters ) Note: To add Physical table to LTS, the adding physical table and existing physical table of LTS must have direct join. Process: Step 1 : Import below tables into physical layer.
1. D1_PRICELIST.
2. D1_PROD_DIET_TYPES.
3. D1_PROD_SUBTYPE.
4. D1_PRODUCT_TYPE.
5. D1_SUPPLIERS.
Step 3 : Mapping multiple sources to an LTS.
1. Drag and drop D1_PROD_SUBTYPE physical table onto D1_Product LTS.
2. Similarly drag and drop
D1_PRICELIST,D1_PROD_DIET_TYPES,D1_PROD_SUBTYPE,
D1_PRODUCT_TYPE,D1_SUPPLIERS.
Step 4 : Drag and drop newly added 5 columns onto products presentation table.
0.4 Hands on 4 testing RPD 0.4.1 Checking repository for consistency It is useful to check entire RPD, mainly it will check below points 1. All logical columns are mapped directly or indirectly to one or more physical columns.
2. All logical dimension tables have a logical key.
3. All logical tables have a logical join relationship to another logical table.
4. There are at least two logical tables in the business model: a logical fact table and a logical dimension table. Both can map to the same physical table.
5. There are no circular logical join relationships.
6. A presentation catalog exists for the business model Consistency Check Manager 1. Displays consistency check messages a. Errors: Must be fixed to make the repository consistent b.Warnings: Condition that may or may not be an error We must make sure there are no errors and if possible debug warnings. Note: Till 11.1.1.5 version it used to display best practices in 11.1.1.6 it is internally taken care Process: Do by using any one way
Servers in OBIEE 11G: 1. Oracle BI server: 1. It is a main server.
2. It controls all data security rules.
3. RPD will be loaded in this server . 2. Oracle BI presentation server: 1. It is used to develop reports, dashboards, key performance indicator (KPI), score cards etc… 3. Oracle BI Java host: 1. It is used to display charts (graphs.) 4. Oracle BI scheduler: 1. It is used to schedule reports (Agents). 5. Oracle BI cluster controller: 1. It is useful to integrate ‘n’ number of servers. 6. Web logic: 1. It is web server useful to access reports and dashboards globally.
Above 1 to 5 servers are integrate in OPMNCTL (oracle process management network control).
0.4.2 Loading repository
In loading repository into oracle BI server we have below six steps.
1. Close RPD.
2. Start the web logic server alone or all OBI severs.
3. Configure RPD in EM (Enterprise manager).
4. Start or restart oracle BI server.
5. Confirm RPD loaded or not by checking log file.
6. Confirm RPD loaded or not by developing report.
Step 1 : Close RPD
1. It is not mandatory in OBIEE 11g, where as in 10g it is mandatory.
2. In 10g RPD opened and if we load RPD in Oracle BI Server then we can not
open RPD in online mode with write permissions (it opens only read only mode) Process:
Go to file menu of Administration tool ->click on close. Step 2: Start the web logic server alone or All OBI severs
Go to path BI11g/user_projects/domains/bifoundation_domain/startweblogic.cmd
OR
Start ->all programs ->oracle business intelligence ->start BI services.
Provide user name : web logic.
Password:RRitec123.(RRitec is optional) Note: After installation first time if we start then only it will ask user name and password Step 3 : Configure RPD in EM (Enterprise manager)
Open internet explorer type: http://localhost:7001/em
Provide user name: web logic
Pass word: RRitec123.
1. Click on login.
2. Click on business intelligence ->core application ->Deployment>repository ->lock and edit configuration ->close ->click on repository file browse->Select RRITEC.rpd ->open ->Repository password:RRitec123->Confirm
password:RRitec123->Click on apply.
3. Click on activate changes
Step 4: Start or Restart oracle BI server Method 1: Using EM
1. Click on capacity management ->availability
2. Select BI servers->Click on restart selected ->yes -> after sometime close. Method 2: Using Command prompt
1. Start -> Run -> cmd
2. Type cd E:/BI11g/instances/instances1/bin àenter.
3. To see all the commands of opmnctl type opmnctl ->Enter.
4. To stop the oracle BI server type opmnctl stopproc ias-component=coreapplication_obis1
5. To start the oracle bi server replace stopproc in above command with startproc
Step 5 :Confirm RPD loaded or not by checking log file
1. Go to below path BI11g\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1
2. Open Nq server.log file observe our rpd name in the bottom of the notepad Step 6 :Confirm RPD loaded or not by developing a report
1. Open Intrenet Explorer
2. Type URL http://localhost:7001/analytics press enter
User id: web logic.
Password: RRitec123.
3. Click on new ->analysis -> select subject area supplier sales
4. From subject area pane double click on year, dollars columns
5. Click on results. Note1:
1. Whenever we click in activate changes in backend RPD Xerox copy will be created in repository folder. this process is called as version control of RPD
2. In Nqsconfig.ini file RPD name will be modified.
3. we can find Nqsconfig.ini file in below path E: /bi11g/instances/instance1/config/oracle BI server component/coreapplication_obis1.
0.4.3 Log level
1. Oracle BI Server provides a facility for logging query activity at the individual user level
2. Log levels are available from 0 to 7.
3. In production we use ‘0’ i.e., No log level .This process is useful to improve the performance of environment by reducing log creation work of oracle BI server.
4. In development and testing environments we use log level ‘2’.
5. The query log file is named Nq query.log and is located in the directory Bi11g/instances/instance1/diagnostics/logs/oracle-bi servercomponent/coreapplication obis1. Log level 1 vs log level 2
1. Presentation layer is the only layer visible to end users (Report Developer or clients).
2. In presentation layer we have 4 types of objects. 1. Subject area. 2. Presentation table. 3. Presentation columns. 4. Hierarchy object.
3. Subject area in 11g is called as presentation catalog in 10g.
4. Hierarchy object is newly introduced in 11g
5. Single Subject Area must be populated with content from a single business model; cannot span business models.
6. Multiple Subject Area can reference the same business model. Step 1: Creating Subject Area
1. Right click on presentation layer -><- new subject area -><- name it as supplier sales-><- click on ok.
2. From sales business model select all the tables drag and drop on to supplier sales..
3. Similarly create one more subject area with the name of supplier sales DM. drag and drop periods,products and salesfacts tables.
Step 2: Reorder Columns
· Double click on sales presentation tables.
· Click on columns.
· Select .dollars ,unitord, unitship
· Click on up arrow mark and make sure these are in first,second and third positions respectively Step 3 : Reorder Tables
· Double click on .supplier sales subject area
· Select sales fact table and make sure it is available in bottom. Step 4 : Create Nested folders (or) nested tables
• Prefix the name of the presentation folder to be nested with a hyphen and a space or type -> in description of table
• Place it after the folder in which it nests. Process:
· Double click on supplier sales subject area.
· Click on presentation tables tab.
· Click on add (+).
· Name it as keys and in the description type -> stores key columns and click on ok.
· Again click on add (+).
· Name it as - measures (prefix the table name with hyphen and a space)
· Click on ok
· Make sure these two tables are available under sales fact tables .
02 BMM LAYER · It is also called as logical layer (or) business layer.
· In this layer we will convert Data base technical terminology into business terminology Eg: ACTLEXTND column into Dollars. · BMM layer contains ‘n’ number of business models.
· Each business model called as subject area or data mart.
· In development of BMM layer we have below steps.
1. Creating business model
2. Creating logical tables and logical columns
3. Creating logical joins.
4. Renaming logical tables and logical columns
5. Defining measures. Step 1: Creating Business Model 1. Right click in BMM layer -><- new business model
2. Name it as suppliersales -><- click on ok
Step 2: Creating logical tables and Logical columns · Right click on suppliersales business model -><- new object -><- logical table.
· Name it as periods.
· Click on ok.
· Drag and drop D1_calendar2 physical table onto periods.
Similarly create below three tables and map it Customers -><- D1_customers2 Sales -><- D1_orders2 Products -><- D1_products
Note: Wherever you drag and drop physical table then automatically logical column will be created. More we will discuss in hands on 6 calculations Step 3: Creating logical joins 1. Logical join is useful to define driving table.
2. Logical join is useful to define Inner Join, Left Outer Join,Right Outer Join and Full Outer Join
3. Logical join is useful to define cardinality. more used cardinality is one to many and never used cardinality is Many to Many
4. Logical join is useful to identify dimension tables and fact tables.
5. Logical join allows Oracle BI Server to make the best decision about the exact physical SQL to generate based on the logical query path
Process: · Right click on BMM layer-><- business model diagram -><- whole diagram.
· Click on new join.
· Drag and drop sales onto periods
· Click on ok.
· Similarly create below joins supplier sales to customers supplier sales to products. · Close business model diagram.
Step 5: Renaming columns: · Go to tools -><- Utilities -><-Rename Wizard -><-Execute -><- select Sales Business Model -><- Click on Add hierarchy -><- Click on next -><- again next.
· select All text lower case -><- add
· Select First letter of each word capital -><- add
· Select Change each occurrence of ‘-‘into a space -><- add.
· Click on next and click on finish.
· Save it and click on No. Step 6: Defining measures Each and every measure should associated with one aggregation rule Table Source Data :
Deptno
Empno
Sal
10
101
1000
10
102
2000
20
103
500
20
104
1000
If we are not mentioning the sal as measure column the output is like this.
Deptno
Sal
10
1000
20
2000
20
500
20
1000
If we mention the Sal column as Measure (Sal is measure and is associated with
aggregate rule sum) then output is
Deptno
Sal
10
3000
20
1500
Process:
In the BMM layer expand sales fact table double click on dollars column-><- select aggregation tab -><-select aggregation rule as sum -><- Click on ok. Note1: one business model may map to multiple data bases. Note2: one logical Table may map to multiple LTS Note3: One logical tables source may map to multiple physical tables Note4: one logical column may map to ‘n’ number of physical columns.
