Product Profitability Dashboard

(1)

OBIEE11g Practical Guide for Dashboard Building

“Product Profitability Dashboard”

History:

Version

Description Change

Author

Publish Date

0.1 Initial Draft

Kuldeep Mishra

1

st

_{Jul 2012}

0.1

1

st

_Review

_{Amit Sharma}

₅

th

_{Jul 2012}

BISP is committed to provide BEST learning material to the beginners and advance learners. In

the same series, we have prepared a complete end-to-end OBIEE Dashboard design document. The

document briefs you practical approach to create Dashboard, Analysis, Filters, Maps, KPI, scorecard

Gauge and Prompts. The document assists OBIEE11g learners to explore the various features. The

document simplifies OBIEE11g. In the first part of tutorial it is shown creation of Brand Analysis

Dashboard. The subsequent release of the case study will cover many new advanced features of

Dashboard building. Join our professional training program to learn from the experts.

(2)

Purpose

This tutorial covers steps to create OBIEE11g Dashboard, Dashboard Prompts, Filters, Analysis, Charts and

Pivots. Finally it shows how to integrate them into a single unit.

Time to Complete

Approximately 120 min.

Overview



OBIEE11g provides rich functionalities to create Dashboard and Users requests in multiple formats i.e

Chart ,



Tabular, Pivots, Filters, Gauge, Maps, Scorecard etc.



Dashboard: Single user interface to show the complete company stats in common windows.



Analysis — Business Users makes adhoc queries to satisfy various business questions by simply drag

and drop the objects.



Filters/Dashboard Prompts— Enable users to filter the records. It could be based on user inputs.



Pivot: Cross Tabular layout of the information.



Chart: Graphical view of the business data.



Gauge: An alternate way to display info where we need to project data Actual Vs Target.

Software and Hardware Requirements

The following is a list of software requirements:



Oracle Database 11g

(3)

ClickEdit Formula button

(14)

ClickOk.

iv)After click Ok, we see prompt for column ‘En’. Label Select Language, OperatorIs equal to / is in, User InputRadio Button .

vi) In options we are able to set various other options. In Radio Buttons ValuesSpecific Column Values from drop down list.

(15)

vii)Click Select value sign, the select values window will display.

Click on Edit button.

(16)

ClickOk. ix)After click ok

ClickOk.

x)Here we see the selected values on Specific column values drop down list.

(17)

After click on Select values window will display ,Here we select a value .

Click Ok.

xii)Here we see the selected value on specific value drop down list. Select Radio button widthDynamic ,Set a variableRequest variable from dropdown list and variable name

(18)

ClickOk.

xiii)After ClickOk ,In prompt window we see the created prompt and it’s view to the display window.

Dashboard Prompt “Select Metric”

Step1):- Now we create another dashboard prompt . i) Click sign and select Column Prompt.

(19)

iii)After selected Column Prompt, Select Column window will display. then choose the Subject Area columns from Select Column window.

ClickOk.

Dashboard Prompt “Set Source DB Table”

iv) Now, the column New Prompt window will display. Here we specify the following properties. ClickEdit Formula button

(20)

ClickOk.

iv) After click Ok, we see prompt for column as ‘B’. Label Set Source DB Table, OperatorIs equal to / is in, User InputRadio Button .

v)In options we are able to set various other options. In Radio Buttons ValuesSpecific Column Values from drop down list.

(21)

vi)Click Select values , Select Values window will display.

(22)

ClickOk.

viii) After ClickOk we see the values in selected pane.

ClickOk.

(23)

(24)

Click  select value , after click on Select Values window will display.

Select a value and ClickOk.

xi)Here we see the specific value ,Radio Button width Dynamic , Set a variable-Request Variable and write variable name as Dynamic_Product_Table.

(25)

xii)After ClickOk ,In prompt window we see the created prompt and it’s view to the display window.

Click on Save button.

xiii)After click on Save as button, Save as window will display here we give the name of prompt and show the location where the prompt save.

ClickOk.

Dashboard Prompt “Currency”

(26)

i)Go to NewDashboard Prompt and SelectSubject Area (Sample Sales as previously) then the dashboard prompt window will display.

ii)Click sign and SelectCurrency Prompt.

iii)After click on currency prompt the New Prompt window will display.

Here we set LabelCurrency and Choice List WidthDynamic

ClickOk.

(27)

iv)After click ok, we see the created currency prompt and also see the view of prompt in display pane.

v)Now ClickSave as button ,then the Save as window will display.

ClickOk.

Creating Pivot “Custom Group”

Step 3):- Now, we create the first report of Product Details dashboard called as Custom Group. i)Click on NewAnalysis then chose subject area from Select Subject Area.

(28)

ii)Select tables column from left side Subject Area pane within Criteria.

iii)In Base Facts(Revenue)Edit Formula.

(29)

v)In Edit Column Formula we change Column Heading, CheckCustom Heading and Custom HTML Markup , Aggregation RuleSum.

(30)

ClickOk.

vi)After set all properties in criteria ClickResult tab and here we see result in below image. ClickEdit View option on pivot table.

vii)After click on edit view option on pivot table, the edit view window will display. Here we set properties like RowsProducts (Product Hierarchy),ColumnsTime(T05 Per Name Year),MeasuresBase Facts,

ExcludeMeasure Labels. In columns Click summation and Select After to show grand total.

After set all properties ClickDone.

(31)

ix)After click on edit view on title ,the edit view window will display. SetTitle as Custom Group, Started TimeDisplay date and time.

ClickDone.

(32)

xi) Now Click on save button ,the save as window will display to save the report.

ClickOk.

Creating Pivot “Target Completion”

Step 4):- Now, we create the second report of Product Details dashboard called as Target Completion.

i)Click on NewAnalysis then chose subject area from Select Subject Area pane. Here , Subject AreaSample Sales..

(33)

iii)In Base Facts(Revenue)Edit Formula.

iv)CheckCustom Heading ,Change Column HeadingActual, AggregationDefault(Sum), Column Formula”Base Facts”.”1-Revenue”/1000000.

(34)

ClickOk.

v)In Base Facts(Actual)Column Properties.

vi)In column properties SelectStyle tab and set properties.

vi)In column properties ClickData Format, CheckOverride Default Data Format , Custom Numeric Format#.00M

(35)

ClickOk.

vii)In Base Facts(5-Target Revenue) Edit Formula

viii)Edit Column Formula window will display, Here we CheckCustom Heading change Column

(36)

ClickOk.

ix)In Base Facts(Target)Column Properties.

(37)

ClickOk.

xi) In column properties ClickData Format, Treat Numbers AsCustom, CheckOverride Default Data Format , Custom Numeric Format#.00M

(38)

ClickOk.

xii)In Base Facts(Revenue)Edit Formula

xiii)In Edit Column Formula, CheckCustom Heading ,Column HeadingCompletion , Aggregation ruleNone , Set Column Formula.

(39)

xiv)In Base Facts(Completion)Column Properties.

xv)In Column PropertiesData Format tab ,CheckOverride Default Data Format ,Treat Text AsHTML from drop down list .

ClickOk.

(40)

xvii) In Edit Column Formula, CheckCustom Heading ,Column Heading% , Aggregation ruleDefault(Sum) and Set Column Formula.

ClickOk.

xviii)Now , We see the criteria as below image.

(41)

xxii)ClickNew View and SelectPivot Table.

(42)

xxii) Now we set the properties in edit window. As same in below image. In Pivot table promptsTime(T05 Per Name Year) , RowsProducts(P4 Brand) and Click After, ColumnsMeasure Labels ,MeasuresBase Facts(Actual ,Target ,Completion),Simple Calculation%..

(43)

xxiv) Go to criteria Simple Calculation(%)Column Properties

(44)

Select column from add condition .

After select column from add condition. New condition window will display. Select OperatorIs greater than ,Value115.

ClickOk.

(45)

ClickOk

xxvi) After ClickOk, We see the first condition in conditional format tab. Once again click on add condition and select the column on which make a condition .

(46)

.

ClickOk.

After Click on ok. Edit Format window will display and here we set the properties color and background color.

ClickOk.

After ClickOk, We see the both first and second condition in conditional format tab within the column properties.

(47)

ClickOk.

xxvii)Now, ClickResult tab and see the result.

xxviii)ClickEdit on Title. The title window will display ,write the title of report in title box ,SelectDisplay Date and Time from Started Time drop down list.

(48)

After set properties Click.Done. xxix)Go to result and see the result.

(49)

ClickOk.

Chart “Target Completion Chart”

Step 5):- Now, we create the Third report of Product Details dashboard called as Target Completion Chart.

i)Click on NewAnalysis then chose subject area from Select Subject Area pane.Here , Subject AreaSample Sales.

Similarly, chose previous report (Target Completion) criteria.

and set all properties. After set all properties ,we see the properties as

(50)

ii)ClickNew ViewGraphLineDefault.

(51)

iii) Click Edit on Graph. The edit graph window will display.

iv)Measures(Lines(Vertical Axis))% , Line(Group By(Horizontal Axis))T04 Per Name Half , Line(Vary Color By (Horizontal Axis))P4 Brand ,ExcludeT05 Per Name Year, Actual, Target

(52)

vi)Click Edit graph property, Graph properties window will display ,here we select General tab. In general tab Canvas Width450 , Canvas Height200 , Legend LocationRight .

ClickOk.

(53)

ClickStyle and Conditional Formatting ,

(54)

ClickScale Markers ,Scale Markers window will display. Here we Click Customize Scale Marker, The customize scale marker window will display. Scale Marker as Static Value 100.

(55)

ClickOk.

After set all properties we see scale marker window.

ClickOk.

ix)Now, ClickTitles and Labels and set it’s properties TitleCompletion % ,In Labels ClickVertical Axis Labels

(56)

ClickNumber Format tab, CheckOverride Default Data Format and set all properties.

ClickOk.

Here we see the vertical axis label . ClickHorizontal Axis Label.

(57)

ClickOk.

Here we see the Vertical and Horizontal axis labels.

ClickOk. x)ClickResult tab and see the result.

(58)

Now Click on save button ,the save as window will display to save the report.

(59)

Bubble Chart “Revenue Demographic”

Step 6):- Now, we create the second report of Product Details dashboard called as Revenue Demographic. i)Click on NewAnalysis then chose subject area from Select Subject Area pane .Here , Subject AreaSample Sales.

ii) Select tables column from left side Subject Area pane within Criteria.

iii)ClickEdit Formula in Base Facts(Revenue)

iv)After click on edit formula the edit column formula window will display. CheckCustom Heading ,Aggregation RuleDefault(Sum) and set the Column Formula.

(60)

v) Now , ClickResult tab then ClickNew ViewGraphBubble.

vi)After selecting bubble we see the result .

vii) Click Edit View on graph. The Edit View window will display. In Bubble Graph(Measures)XY (Horizontal Axis)23 Avg # of Orders by Customer, XY (Vertical Axis)C21 Customer Age, XY (Bubble Size)1-Revenue ,

Bubbles(Bubbles)C7a Marital Status(Dense Lookup) , Vary Color By C7a Marital Status(Dense Lookup), Excluded46 Avg Order Process Time.

(61)

viii)Here we see the graph view ,ClickEdit graph properties

ix)After click on edit graph properties ,Graph Properties window will display, In General tab we set the properties like Canvas Width350, Canvas Height220, Legend LocationTop from drop down list.

(62)

ClickOk.

x)Now, ClickStyle tab and set all properties. Click on Style and Conditional Formatting.

(63)

Click to add Custom Formatted Position. ClickColor drop -down list.

After click on color drop-down list.

(64)

xi)Now, ClickScale tab and set properties Axis limitsSpecify ,Minimum20, Maximum70, Tick typeSpecify, CheckShow Major Ticks6, CheckShow Minor Ticks1.

(65)

ClickOk.

xii)After ClickOk, we see the vertical axis label as 10px.

ClickOk.

(66)

xiv)ClickEdit View on title. Here we set title and subtitle.

After set all properties ClickDone. xv)After ClickDone ,We see the result.

(67)

Now Click on save button ,the save as window will display to save the report.

ClickOk

Creating Pivot “Converted Revenue”

(68)

i)Click on NewAnalysis then chose subject area from Select Subject Area pane .Here , Subject AreaSample Sales.

iii)ClickEdit Formula on Counts(31 # of Orders(Cnt Distinct))

iv)After click on edit formula the edit column formula window will display. CheckColumn Headings and Change column Heading from 31 # of Orders (Cnt Distinct) to # of Orders , Aggregation RuleDefault.

(69)

v) ClickEdit Formula on Fx conversion(70 Average FX Rate to Pref Currency)

vi)After click on edit formula the edit column formula window will display. Here we CheckCustom Headings change Column Heading70 Average FX Rate to Pref Currency to Average FX Rate , Aggregation

RuleDefault(None) .

ClickOk.

(70)

viii)The column properties window will display SelectData Format tab ,In this tab set all properties

CheckOverride Default Data Format, Treat Number asNumber from drop down list , Negative FormatMinus -123 from drop down list ,Decimal Places3, CheckUse 1000’s Separator.

ClickOk.

(71)

x) Edit Column Formula window will display CheckCustom Heading, Change Column Heading 120 Converted Rev (Index Col), Aggregation RuleDefault(Sum).

ClickOk.

xi)ClickColumn Properties on FX conversion( Converted Rev)Column Properties

xii)The Column Properties window will display , In Style tab ClickColor drop down list. Color Selector window will display here we chose a color.

(72)

ClickOk.

(73)

xiv)Now, ClickData Format tab and set the properties , Check Override Default Data Format, Treat Number asNumber from drop down list , Negative FormatMinus -123 from drop down list ,Decimal Places2, CheckUse 1000’s Separator.

ClickOk.

xv)After set all properties of columns we see the criteria. Now ,ClickResulr tab.

(74)

xvii)ClickEdit View on table. The edit view window will display. Here we set the properties.

(75)

xix)Click Summation in columns and Measures and SelectAfter.

xx) In summation after SelectAfter option then we see grand total in table result and check sign in summation .

(76)

After set all properties ClickDone to save the properties. xxi) Now we see the result.

(77)

xxii)ClickEdit View on title. Write TitleConverted Revenue on title box , Started TimeDisplay date and time.

ClickDone to save the properties.’

xxiii)After click done we see the result as below.

(78)

ClickOk

Creating “Dashboard Annotation”

Step 8):- Now, we create the second report of Product Details dashboard called as Dashboard Annotation. i)Click on NewAnalysis then chose subject area from Select Subject Area pane .Here , Subject AreaSample Sales.

(79)

iv)The New Filter window will display .OperatorIs equal to / Is in , ValueAnnotations from drop down list.

ClickOk.

v)We see the filter in criteria.

(80)

vii)ClickNew ViewOther ViewsNarrative

viii)After Select Narrative we see it in below image .

ix)Now delete table and ClickEdit view on narrative.

(81)

xi)Set all the properties.

After set all properties and ClickDone.

(82)

xiii)After click edit view ,the edit view window will display here we set TitleDashboard Annotation ,Started TimeDisplay date and time.

After set all properties ClickDone. xiv)Now, the result is shown as below.

(83)

xv) Click Format Container on narrative. Horizontal AxisLeft .

ClickOk.

(84)

xvii) Now Click on save button ,the save as window will display to save the report.

ClickOk

Step 9):-Now we create a dashboard, Previously we created a dashboard called Simple Dashboard within Practice Dashboard folder. Now we create a new page in practice dashboard called as Brand Analysis.

(85)

ii) Click and SelectEdit Dashboard.

(86)

then ClickOk.

iv) Here we see the Product Details tab. Here we also see the Dashboard Objects .

(87)

vi) Drag and Drop reports from shared folders left pane to right pane. Here we see all reports which we created above related to this dashboard. Here we also created on Action Link .

(88)

vii)Go to dashboard Annotation and ClickEdit Analysis and SelectRename.

viii)The rename window will display, here we set the name as See Dashboard Commentary, CheckUse Dashboard Object name as link text.

ClickOk.

ix)Now ClickAction Link properties tab.

(89)

xi)After Click on Select Action option ,the Select Action window will display and here we select Annotate action.

ClickOk.

xii) After ClickOk. The Edit Parameter Mapping – Annotate window will display.

(90)

ClickOk

xiii) Now, ClickProperty tab of See dashboard Commentaries and SelectDisplay ResultsLink - In a Separate Window.

xiv)In Target Completion ChartEdit AnalysisShow ViewGraph1.

(91)

xvi) In Converted Revenue(Title 1)Edit Analysis and SelectShow ViewTitle 1.

(92)

Product Profitability Dashboard

OBIEE11g Practical Guide for Dashboard Building