CalPlanning. Smart View Essbase Ad Hoc Analysis

(1)

CalPlanning

Smart View

Essbase Ad Hoc Analysis

CalPlanning

 Overview

 Introduction to Smart View & Essbase

(2)

(3)

5 CalPlanning | Phase 3 Training CalPlanning Smart View Basics February 2014

Prerequisites

Prerequisites:

• Completed UC Path Basic and Intermediate MS Excel classes (or equivalent)

and experienced in

• Using Excel functions

(∑, avg, cell references, etc. )

to create formulas

• Filtering data

• Inserting charts

• Comfortable working with and analyzing large data sets (ex. creating pivot

tables, going behind the dash board in BAIRS)

• Attended ‘Intro to CalPlanning and Reportin’ training session and/or

currently running and analyzing CalPlanning reporting

• Differentiate between CalPlanning dimensions and know when to choose

appropriate members in the POV to access data

• Understand the impact of the One Entity hierarchy on unit’s organizational

structure

 Overview

 Introduction to Smart View & Essbase

(4)

(5)

(6)

(7)

13

CalPlanning | Phase 3 Training CalPlanning Smart View Basics February 2014

 Overview

 Introduction to Smart View & Essbase

(8)

4 Step Approach to Smart View Essbase Ad Hoc Analysis

Fill &

Drill

Format

Plot

• Which Dimensions & Members do I need to include in my

POV to access the data to answer my question?

• Where should I plot my Dimensions to make analyzing my

data easiest?

• Is my grid ready to fill?

• Does my data answer my question? What level of detail

do you want to include?

• How do you want to present the data?

Number formatting, fill colors, bold, outlines, italics, Excel formatting and conditional formatting

• Can I use this layout again?

• Can I leverage this analysis across other Dimension

Members: Accounts, Entity levels, Funds, Scenarios, Versions, Periods?

1

2

3

4 _{Save &}

Cascade

Connecting to Essbase in Excel

1) Open

MS Excel

.

2) Go to the Smart View ribbon and click the

Panel

icon.

3) In the Smart View Panel to the right, click on

Shared Connections

. You

(9)

17

Connecting to Essbase in Excel

(cont.)

4) Choose Oracle

Essbase

in the “Select Server to proceed” dropdown

5) Drill into the server node to find

CalRptg → CalRptg

(10)

Let’s practice Getting Started in Smart View!

Smart View Ad Hoc Analysis

Exercise 1

 Overview

 Introduction to Smart View & Essbase

(11)

Objective

After this section, you should be able to:



Describe Step #1 Plot in the 4 Step Approach to Smart View

Essbase Ad Hoc Analysis



Set your Smart View Options



Create an Essbase Ad hoc query



Understand the features of the Essbase Ribbon



Disconnect from Essbase

4 Step Approach to Smart View Essbase Ad Hoc Analysis

Fill &

Drill

Format

Plot

• Which Dimensions & Members do I need to include in my

POV to access the data to answer my question?

• Where should I plot my Dimensions to make analyzing my

data easiest?

• Is my grid ready to fill?

• Does my data answer my question? What level of detail

do you want to include?

• How do you want to present the data?

Number Formatting, Fill colors, bold, outlines, italics, Excel Formatting and Conditional Formatting

• Can I use this layout again?

• Can I leverage this analysis across other Dimension

members‐accounts, entity levels, funds, scenarios, versions, periods?

1

2

3

(12)

Smart View Options – Settings

It is important to understand

how to set Options in Smart

View in order to take advantage

of the

Essbase

features.

Smart View Options – Settings (Cont.)

The

Options

menu in Smart View allows you to set application

preferences.

Different

Options

are available based on whether you connect to

(13)

(14)

(15)

(16)

(17)

33

Essbase Ribbon



Refresh

‐ updates the current grid from the data source



POV ‐

displays/hides the Point of View dialog

box

(18)

If you get this common Error, remove Suppress Missing

and Zero

• If you get this common error when

selecting the Ad Hoc Grid, it means that

your Smart View Data Options are

suppressing “

No Data /Missing

” and

“

Zero

.”

• To fix this, simply click on the Options Box

in the Smart View Ribbon and Select

“

Data Options

”. Uncheck the box for

Suppress Rows “

No Data/ Missing

” and

“

Zero

.”

• Click on

Ad Hoc Grid

and your POV and

data intersection will appear.

Ad Hoc Analysis – Demo

(19)

(20)

(21)

(22)

Adding and Moving Dimensions between the POV Tool Box

and Ad Hoc Grid

To add dimensions:

Left or Right‐click and drag them from the POV toolbar to the

rows or columns of the grid.

To remove dimensions:

Right‐click and drag them from the grid to the POV

toolbar.

Notes:

Be sure to click on the dropdown arrow when dragging. Dimensions can

only be added when connected to a database.

Row example

Column example

POV – Selecting Meaningful Intersections

When creating your own ad‐hoc query, begin by asking yourself what question

the report will answer. Then consider:

• What intersection or POV will return the correct data?

• Year:

2013‐14

• Scenario:

Forecast

• Version:

Working

• Total Entity: Your division. As an example 1_BOALT

• Fund:

Current Funds*

• Pgm Code:

Program Code*

• Chart1:

Total Chart1*

• Chart2:

Total Chart2*

• Period:

YearTotal

• Account:

CalPlan Primary Account Hierarchy.

Drill into the hierarchy to see

(23)

(24)

47

Recently Used Connections

Once you have made a connection to Smart View, you will be able to

connect using the “

Recently Used

” connections.

(25)

49

 Overview

 Introduction to Smart View & Essbase

(26)

4 Step Approach to Smart View Essbase Ad Hoc Analysis

Fill &

Drill

Format

Plot

• Which Dimensions & Members do I need to include in my

POV to access the data to answer my question?

• Where should I plot my Dimensions to make analyzing my

data easiest?

• Is my grid ready to fill?

• Does my data answer my question? What level of detail

do you want to include?

• How do you want to present the data?

Number Formatting, Fill colors, bold, outlines, italics, Excel Formatting and Conditional Formatting

• Can I use this layout again?

• Can I leverage this analysis across other Dimension

members‐accounts, entity levels, funds, scenarios, versions, periods?

1

2

3

4 _Cascade

Save &

(27)

(28)

(29)

(30)

59

 Overview

 Introduction to Smart View & Essbase

(31)

Creating Ad hoc Analysis Reports – Excel Formulas and

Conditional Formatting

Use Excel formatting and formulas!

You can insert formulas and format like you normally would in

any other spreadsheet.

Common Examples include: Bold, cell color, fonts, cell

underline, math functions.

 Overview

 Introduction to Smart View & Essbase

(32)

(33)

(34)

Cascading in Smart View!

Smart View Essbase

Ad Hoc Analysis

Exercise 6

& EXTRA CREDIT

 Overview

 Introduction to Smart View & Essbase

(35)

69

Technical Overview

PC Users

Users will access the

CalPlanning web‐based workspace

at:

(36)

(37)

(38)

(39)

(40)

79

Smart View Ribbon

When you connect to any of the MS Office applications you should see a

Smart View Ribbon:

GROUP BUTTON DESCRIPTION

Panel Allows you to open the Smart View Panel Connections Displays active or recently used connections Undo/Redo Allows you to undo or redo your last operation Copy Data Point/Paste Data Point Allows you to copy a data point or paste a previously copied data point Functions Allows you to Open the POV Manager or build a function formula Refresh Refresh the data on the current worksheet from the data source Submit Data Submits data entered via Smart View Options Allows you to set how data is displayed Help Opens Smart View online help Sheet Info Displays general and connection information for the worksheet More Additional worksheet options Start Edit Data General

Essbase Ribbon

This ribbon opens once you have connected to Essbase

GROUP BUTTON DESCRIPTION