Necto TrainingModule 15: Necto Formulas and Exceptions

Objectives

By the end of this lesson you will be able to: Perform advanced analytics by:

Defining formulas Defining exceptions

Agenda

Formulas Exceptions

Using Formulas

What Are Formulas?

User-defined members are created by formulas

Formulas use Necto’s predefined formulas or MDX functions to create members

Members in the grid can be dragged into the formula definition

Formulas can use dynamic parameters

I’d like to do calculations that are not provided by the cube.For example: examine the difference between two products

Formula Definition ProcessSet a name

Result format

Order of calculation if function within function

Select a formula type

Select and drag members from grid

Change order in formula

Delete member(s) from formula

View Formula

Apply

Choose members not currently in grid

Formula Created – New Member Appears

Click New – to create an additional formula

Member menu + formula options

Math Formula Type

Formula SyntaxSum Sum(…)Aggregate Aggregate(…)Ratio Param1 /Param2Growth (Param2 – Param1)/Param1Difference Param2 – Param1Average Average(…)Max Max(…)Min Min(…)

Wow! I can see not only numbers, but how they relate to each other

Customized Formula Type

Time Formula Type

Function Description

YTD Returns a set of months starting with the first month of the year and ending with supplied month

QTD Returns a set of months starting with the first month in the quarter and ending with supplied month

MTD Returns all siblings of the supplied member starting at the first sibling and ending with the supplied sibling

Free MDX

Select free MDX type

Or start from predefined formula and edit its MDX

Free MDX Editing Options

Check if valid

Insert OLAP command

Set font and size

Format MDX

Insert a placeholder

Floating window

Maximize

Using Parameters in Free MDX

I’d like the end-user to be able to set a value (% change in sales)

Add a formula that will reflect the expected change

Use of Parameters in MDX - Definitions

Title displayed on workboard.# will be replaced with the current value

Name that will be used in MDX

Navigation Component definition

View Component formula definition

Exercise 1 – Working with Formulas

Define a Mathematical formula Define a Customized formula Define an MDX Formula that includes

dynamic parameters

Exceptions

Exceptions

Highlight cell values according to a specified criteria

Apply to measure

Enable or disable exceptions at any time

Exceptions are defined per component per measure

Tooltip shows criteria or description

Description will be used in tooltip instead of criteria (optional)

Defining a Simple Exception

2. Click to add an exception definition

3. Select Exception Type = Simple

4. Define criteria

5. Click to apply

1. Select a Measure

Filtering by the Exception

Filter by… allows filtering of the grid to the Exceptional data

Now you only see the Exceptional data

Defining Exceptions – On Selected Members Only

I have a lot of data in the grid. I would only like to check exceptions in several members.

Bubble-up Exception

A bubble-up exception highlights parent cells when a descendant meets the criteria definition

In general, Sales have met the goal. But individual types of Bottles and Cages and Cleaners have not.

Defining a Bubble-up Exception

Select Exception Type = Bubble up

Select hierarchy level

Free MDX Exception

Can drag items from the grid to the MDX expression

Check if valid

Insert placeholder

Set font and size

Format MDX

Insert OLAP command

Using a Navigation Control With Exceptions

Each month the sales quota changes. Will I need to edit the exception every time?

You can add a navigation control that will allow you to dynamically set criteria value

Defining a Navigation Control of Type Exception

1. Click and drag to add navigation control to workboard

3. Select Type = Exception

2. Click to add parameter

4. Select exception defined in workboard5. Values are automatically populated. You may edit them

6. Select Visualization form

Using the Navigation Control While in Maximize Mode

Exercise – Working with Exceptions

Define a simple exception Define a bubble-up exception Define a navigation control of type

“Exception” Filter any of the Exceptions

Thank youAny Questions?