Extend Daily Business Intelligence

Extend Daily Business Intelligence

Custom Application

Create Custom Menu

Create Custom Responsibility

Custom Views

Dimension View

Fact View

Create Dimension

Create Dimension Objects

Create Small Business Dimension Object

Primary Properties

Display Attributes

Data Source

Create XX Women Owned Dimension Object

Primary Attributes

Display Attributes

Data Source

Define report

Primary Attributes

Data Source

Define Report Format

Publish Report to Menu

Create KPI

Enter the Measure Primary Attributes

Additional Attributes

Cause and Effect

Data Source

Create custom Dashboard

Primary Attributes

Define Layout

Publish Dashboard

Create KPI List

Primary Parameters

Create a Drilldown Report

Custom Application

1 (N) Application > Register

2. Enter the following:

Application: XX Custom

Short Name: XXCUST

Basepath: XXCUST_TOP

Create Custom Menu

1) Create custom DBI menu.

a) N: System Administrator > Application > Menu.

b) Enter values:

Field / Value
Menu / XX_CUSTOM_MENU
User Menu Name / XX Custom Menu

d) Menu: File > Save.

e) Menu: File > Close Form.

Create Custom Responsibility

1) Create custom DBI responsibility.

a) System Administrator: Security > Responsibility > Define

b) Enter the following Value:

Field / Value
Responsibility Name / XX CUSTOM DBI
Application / XX Custom
Responsibility Key / XX_DBI_CUSTOM_PAGES_REPORTS
Available From / Oracle Self Service Web Applications
Data Group – Name / Standard
Data Group - Application / Applications BIS
Menu / XX CUSTOM MENU

c) Menu: File > Save.

d) Menu: File > Close Form.

2) Add custom responsibility to user.

a) N: System Administrator > Security > User > Define.

b) Place cursor in User Name field.

c) Menu: View > Query by Example > Enter

d) In User Name, enter ‘XX Executive’ and Menu: View > Query by Example > Run.

e) Place cursor in Responsibility section and (B) Add Record .

f) Enter ‘XX CUSTOM DBI’ to Responsibility field.

g) Menu: File > Save.

h) Menu: File > Close Form

Custom Views

Dimension View

createview ADS_DBI_DIM_V as

select lookup_code ID, meaning VALUE

from fnd_lookups

where lookup_type='YES_NO'

Fact View

Create Custom views for your fact and dimension:

createview ADS_DBI_FACT_V as

select f.approved_date, f.supplier_id, f.creation_operating_unit_id org_id,

f.base_uom base_uom,

f.po_item_id,

f.quantity quantity,

f.purchase_amount_b purchase_amt_b,

decode(f.quantity,0,0,f.purchase_amount_b/f.quantity)avg_price,

decode(f.global_cur_conv_rate,0, f.purchase_amount_b, f.purchase_amount_b) purchase_amt_g,

pov.women_owned_flag ,

pov. Small_business_flag

from poa_dbi_pod_f f,

po_vendors pov

where f.supplier_id=pov.vendor_id;

Note: cust_dbi_fact_v and cust_dbi_dim_v views were pre-created for the purpose of this demo flow.

Create Dimension

1) Launch Dimension Designer

a) Navigation: Daily Business Intelligence Designer > Performance Measure > Dimension Designer.

2) Create New Dimension

a) To create a new dimension, click ‘Create’ button.

3) Enter Dimension Properties:

a) Enter the following values:

?Display Name – this column is the display name for the dimension.

Enter: XX Small Business

?Internal Name – this column is the internal name of the dimension and must be unique.

(System Generated)

?Application – this column is the application that the dimension is registered to.

Enter: XX Custom

b) To save the dimension, click ‘Apply’ button.

Create Dimension Objects

1) Navigation: Daily Business Intelligence Designer > Performance Measure > Dimension Designer> Dimension Objects

Create Small Business Dimension Object

Click ‘Create’ button.

Primary Properties

2) Enter Primary Properties

a) Fill in values for the following:

?Name – this field is the display name of the dimension object

Enter: XX Small Business

?Internal Name – this field is the display name of the dimension object.

(System Generated)

?Application- this field indicates which the application the dimension object belongs to

XX Custom

b) Fill in ‘Dimensions that contain Dimension Objects.’

i) In ‘Available Dimensions’ field, enter ‘XX Small Bus%’ or the name of the dimension built in the Create Dimensions section and click ‘Go’ button.

ii) Highlight the dimension and click ‘Move’ button to the ‘Selected Dimensions’ column.

c) Click ‘Next’ to move to next screen.

Display Attributes

3) Display Attributes.

a) Accept default values:

?Dimension Object Enabled – enables the dimension object for use.

?All Enabled – allows the field to show ‘ALL’.

?All Label – This is the label that will show up for Comparison Graphs available in Balanced Scorecard.

?View-by Label –the label shows up for Comparison Graphs available in Balanced Scorecard. Not used in DBI.

?Prototype Value Prefix – contains the prototype value. Not used in DBI.

?Dimension Object Values Order – Indicates the order in which the values will be displayed in the drop down. Choose one from alphabetically, user code or system code. Not used in DBI.

?Comparison Order – Indicates the order in which the KPI values will appear in the graph and table when Comparison or view by is selected. Choose one from Ascending, Descending or same order as in drop down. Not used in DBI

?Comparison Label Source – Not used in DBI

?Long List of Values –Enable the long list of values.

\

b) To move to the next screen, click ‘Next’ button.

Data Source

4) Set Data Source

a) Enter values for the following:

?Source View/Table - Enter the view or table name that contains the dimension object values. See Prep Instructions for the custom view.

Enter: ADS_DBI_DIM_V

?Source View Object Name – Reserved for future use.

?Default Value

o Fixed Value – all field to have a default value.

o PL/SQL Function. SQL function to get the default value of the dimension object. If selected, you must enter a valid PL/SQL Function.

?Master Dimension Object: Reserved for a future function.

Select Finish

Create XX Women Owned Dimension Object

Primary Attributes

11) Select Create Enter Primary Attributes

a) Fill in values for the following:

?Name – this field is the display name of the dimension object

Enter: XX Women Owned

?Internal Name – this field is the display name of the dimension object.

(system generated)

?Application- this field indicates which the application the dimension object belongs to.

Enter: XX Custom

b) Fill in ‘Dimensions that contain Dimension Objects.’

i) In ‘Available Dimensions’ field, enter ‘XX Small Bus%’ or the name of the dimension built in the Create Dimensions section and click ‘Go’ button.

ii) Highlight the dimension and click ‘Move’ button to the ‘Selected Dimensions’ column.

c) Click ‘Next’ to move to next screen.

Display Attributes

12) Display Attributes.

a) Accept default value for the following:

?Dimension Object Enabled – enables the dimension object for use.

?All Enabled – allows the field to show ‘ALL’. Not used in DBI.

?All Label – This is the label that will show up for Comparison Graphs available in Balanced Scorecard.

?View-by Label –the label shows up for Comparison Graphs available in Balanced Scorecard. Not used in DBI.

?Prototype Value Prefix – contains the prototype value. Not used in DBI.

?Dimension Object Values Order – Indicates the order in which the values will be displayed in the drop down. Choose one from alphabetically, user code or system code. Not used in DBI.

?Comparison Order – Indicates the order in which the KPI values will appear in the graph and table when Comparison or view by is selected. Choose one from Ascending, Descending or same order as in drop down. Not used in DBI

?Comparison Label Source – Not used in DBI

?Long List of Values –Enable the long list of values.

b) To move to the next screen, click ‘Next’ button.

Data Source

9) Set Data Source

a) Enter values for the following:

?Source View/Table - Enter the view or table name that contains the dimension object values. See Prep Instructions for the custom view.

Enter: ADS_DBI_DIM_V

?Source View Object Name – Reserved for future use.

?Default Value

o Fixed Value – all field to have a default value.

o PL/SQL Function. SQL function to get the default value of the dimension object. If selected, you must enter a valid PL/SQL Function.

?Master Dimension Object: Reserved for a future function.

b) Select Finish

Define report

1) Launch Report Designer

a) N: Daily business Intelligence Designer > Reporting > Report Designer

2) To create a new report, click ‘Create’ button.

Primary Attributes

3) Enter Report Attributes

a) Fill in the values for the following:

?Name: Enter: XX Custom Report

?Internal Name: (System Generated)

?Application: XX Custom

?Functional Area: Customer Defined

?Description: This is a custom report

b) Select Continue

Data Source

4) Choose Data Source

Select Existing Data Source radio button to choose a view or a table.

5) Choose data source type.

Choose either ‘view’ or ‘table’. For this example, choose ‘View’.

6) Choose data source.

a) Next to ‘View Name’, (B) Search For View Name.

b) In ‘Search by View Name, enter ‘ADS_DBI_FACT_V’

c) Select Finished

7) Map column to dimension object.

a) Use chart to complete the mappings.

Column Name / Column Type / Measure/Dimension Object Mapping / Display Label / Dimension
PURCHASE_AMT_G / Measure / Purchasing Amount
SUPPLIER_ID / Dimension Object / Supplier / Supplier / Supplier
ORG_ID / Dimension Object / Operating Units / Operating Units / Organization
PO_ITEM_ID / Dimension Object / Purchasing Items / Purchasing Items / Item
APPROVED_DATE / Dimension Object / Day / Day / Time
SMALL_BUSINESS_FLAG / Dimension Object / XX Small Business / Small Business / XX Small Business
WOMEN_OWNED_FLAG / Dimension Object / XX Women Owned / Women Owned / XX Small Business

b) Click ‘Finish’ button.

Define Report Format

8) Edit Parameter Region.

a) To change the parameter region properties, click ‘Edit’ link on the parameter region.

9) Modify parameter layout.

a) Modify Layout

i) Use the up and down arrow to change parameter placement.

ii) Re-label parameters in the Parameter Label fields.

iii) Uncheck ‘Display’ checkbox to hide parameters.

iv) Use Parameter Layout LOV to change the layout.

b) Select the “View By” display check box.

b) Save changes

i) Click ‘Apply’ to save changes.

10) Add Graph

a) (B) New in region below the Parameter region and (B) Edit .

11) Edit Graph

a) Enter values for the following:

?Type: Stacked Bar 2D

?Title: Purchased Amount

?Y-Axis Title:

b) To save changes, click ‘Apply’ button.

12) Add a link to the report by clicking the Edit in the third region on the report. Note that this region is blank. This s because links are optional for reports

13) Click the Add Menu Item button.

14) Select the Daily Payables Intelligence responsibility. Select Go

15) Select Invoice Activity report

16) Select Apply

17) Click Add URL

18) Enter the following

Title: Oracle

URL:

19) Select Apply

20) Select Apply

21) Save report modifications.

a) To save all changes, click ‘Save’ button.

b) Click ‘Finish’ button.

c) Click ‘Add to Menu’ icon next to the Custom Report.

Publish Report to Menu

22) Click flashlight icon to launch pop up search window.

23) Search for menu.

a) In ‘Menu Name’ field, enter ‘00 CUSTOM MENU’ and (B) Go.

b) Click Quick Select to the ‘00 CUSTOM MENU’.

24) To save changes, click ‘Apply’ button.

Wait about 5 to 10 min, then navigate to your custom responsibility to view the custom report.

Create KPI

Responsibility: Daily Business Intelligence Designer

(N) Performance Measurement: Measure Designer

Select Create

Enter the Measure Primary Attributes

Name: XX Purchase Amount

Internal Name:(system defined)

Application: XX Custom

Function AreaCustomer Defined

Description

Select Next

Additional Attributes

Accept the defaults

• Activity/Balance: Select the type of measure, either activity or balance. Activitymeasures consider all the data in a given period, while balance measuresconsider only the last data in a given period for computation purposes.

• Aggregation Method: Select the method for aggregating data: SUM, AVG, MIN, or MAX.

• Unit of Measure: Enter a unit of measure for the measure, such asdollars, units, or people.

• Measure Improvement: Select the method for determining when the measure isimproving. You can choose to show improvement if the measure increases orif the measure decreases. For example, increased Revenue is an improvement;however, a decrease in Service Backlog is considered an improvement.

• Prototyping: Set the prototype method and data ranges for themeasure. Prototype data enables you to test and demonstrate measures beforepublishing them.

6. Click Next.

Cause and Effect

7. The Cause and Effect feature is reserved for future use. Select Next.

Data Source

8. Define a data source for the measure. Enter the following information:

Data Source: XX Custom Report

Source Column Name: PURCHASE_AMT_G

Detailed Report in AlertPO Purchases

Ensure that the Enable Detailed Reports in KPI Region

Select Finish to save your work

You may receive a message you are about to change the Primary Data Source for this measure, therefore the following report objects will be modified.

Select Yes to continue?

Create custom Dashboard

1) Launch Report Designer

a) N: Daily business Intelligence Designer > Reporting > Dashboard Designer

2) Create Dashboard

a) Click ‘Create’ button

Primary Attributes

3) Enter Dashboard Properties

a) Enter the following values:

?Name : XX Custom Dashboard

?Internal Name: (system generated)

?Application: XX Custom

?Function Area: Customer Defined

Select Continue

Define Layout

4) Add Parameters from a report

a) Click on the select link in the parameter region.

5) Choose Report

a) Search for Report

i) Enter the following fields

(1) Name: XX Custom Report

(2) Functional Area:Customer Defined

(3) Type: Report

ii) Click ‘Go’ button.

b) Choose Report

i) Use radio button to choose the report.

c) Click ‘Next’ button.

6) Choose the parameter region.

a) Use radio to select the parameter region of the report.

b) Click ‘Next’ button.

7) Register Parameter Region

a) Click ‘Register as new’ radio button (Recommended).

b) Use default values or enter region properties:

i) Name: 00 Custom Report

ii) Internal Name: (system generated)

iii) Application – XX Custom

iv) Functional Area: Customer Defined

v) Description:

c) To enable the view-by in the parameter region, click the ‘Enable View by in Dashboard’ radio button.

d) Click ‘Finish’ button.

8) Save the Dashboard.

a) Click ‘Save’ button.

9) Add Graph

a) Click ‘Select’ link in the region.

10) Choose Report

a) Search for Report

i) Enter the following fields

(1) Name – Report Display Name

(2) Functional Area – Functional area of the report

(3) Type – Enter ‘Report’.

ii) Click ‘Go’ button.

b) Choose Report

i) Use radio button to choose the report.

ii) Click ‘Next’ button.

11) Choose Graph

Note: In the graph, default ‘View By’ value is shown in the parameter region and will be used as the default when added to the dashboard. The default ‘View By’ can be changed in the parameter region, then click the ‘Next’ button.

a) Use radio button to choose the graph.

b) Choose the View By default in the parameter region.

c) Click ‘Next’ button.

12) Register new graph region.

a) Click ‘Register as new’ radio button (Recommended).

b) Use default values or enter region properties:

i) Name – this is the display name

c) Click ‘Finish’ button.

d) Click ‘Save’ button.

13) Add New Region Group

a) Click ‘New Region Group’ button.

14) Added New Region Group

a) Click select on new region.

15) Choose the Report

a) Search for Report

i) Enter the following fields

(1) Name – Report Display Name

(2) Functional Area – Functional area of the report

(3) Type – Enter ‘Report’.

ii) Click ‘Go’ button.

b) Choose Report

i) Use radio button to choose the report.

ii) Click ‘Next’ button.

16) Choose Table

Note: In the table, default ‘View By’ value is shown in the parameter region and will be used as the default when added to the dashboard. The default ‘View By’ can be changed in the parameter region, then click the ‘Next’ button.

a) Choose the radio button to select the Table region

b) Choose the View By default in the parameter region.

c) Click ‘Next’ button.

17) Register Table Region.

a) Click ‘Register as new’ radio button (Recommended).

b) Use default values or enter region properties:

i) Name – this is the display name

ii) Internal Name – this is the internal name of the parameter region.

iii) Application – this is the application the region belongs.

iv) Functional Area – this is the functional area the region belongs

v) Description – this is the description of the region.

c) Click ‘Finish’ button.

18) Save Changes.

a) Click ‘Save’.

19) Delete Regions.

a) Click the trashcan icon on unwanted regions.

b) Click ‘Save’ button.

20) Complete the dashboard.

a) Click ‘Finish’ button.

Publish Dashboard

21) Add the Dashboard to the XX Custom Menu

Navigate to the custom responsibility to view the dash board.

Create KPI List

1) Launch Dashboard Designer.

a) Navigation: Daily Business Intelligence Designer > Reporting > Dashboard Designer

2) Access Dashboard.

a) To open an existing dashboard, enter the dashboard name in name file and choose the functional area, then click ‘Go’ button. Select the update icon for your dashboard.

b) Create a new region in the second region group.

3) Create KPI List

a) Choose ‘All’ in Functional Area.

b) Choose ‘KPI List’ in Type LOV.

c) Click ‘Create’ button.

Primary Parameters

4) Name KPI List and two KPIs.

a) Enter values for the following sections and fields:

i) Prototype Properties:

?Name: XX KPI List display name of the KPI list : XX KPI List

?Comparison Graph: controls whether the Comparative Performance Graphic is displayed and where it allows peer comparison: None

?Value Column Heading: allows user to choose the value column heading label: accept default

?Show Change Column: controls if the Change Column is displayed: No

ii) KPI List:

?KPI: this field displays KPI label in the row of the KPI region. Note: once the KPI is saved it cannot be changed. : XX KPI Measure

?Drilldown URL: this URL imbedded in the KPI label.

?Prototype Value: the value to be displayed on the prototype.

?Prototype Change: the change value to be displayed on the prototype.

?Increase: this field tells the system if the increase is good or bad. Only works in prototype mode.