Intellicus Enterprise Reporting and Business Insights 18.1

Designing Analytical Objects

0 views June 28, 2020 0

Let us talk about designing the Analytical Object and further building the cube. You can navigate to the Analytical Object from Navigate > Design > Analytical Object (or Navigate > Repository > Report Objects > Analytical). Following are the main sections on the Analytical Object screen (as shown in Figure 1):

  1. Editor or design canvas – the area on the left that represents the design of an analytical object
  2. Query Object Selector – the right-top pane from where you can drag required Query Objects to design the analytical object
  3. Fields tab – the right-middle pane to choose fields of the selected query object
  4. Button palette – action buttons on the top for CRUD and build operations

Analytical Object
Figure 1: Analytical Object

Adding Measures and Dimensions to an Analytical Object

The Editor section shows two sub sections:

  1. A box titled – Measures
  2. A canvas area for dropping Dimensions on

Drag the Query Object with quantitative fields from Query Object Selector into the Measures box.

Now it shows that Measures is ‘sourced’ by the selected Query Object.

Measures on Editor
Figure 2: Measures on Editor

Click Measure and select the source field in the property pop-up as shown in Figure 3.

Click edit icon to turn Edit Mode on. You can click + to add measures to the analytical object definition.

You can also click + in front of the query object name to add a Measure Group. A measure group contains measures which belong to the same underlying fact table.

Measure Properties Pop-up
Figure 3: Measure Properties Pop-up

Drag the Query Object with qualitative fields to the Dimension area.

Dimension on Editor
Figure 4: Dimension on Editor

Now a dimension is created with the dragged Query Object as source.

Next you need to select Hierarchy item and set the Hierarchy name in the property tab of dimension.

Click Level under Hierarchy to select Level item. Specify the Level source field from the source Query Object and set the level name.

An integral part of creating aggregations is to create and maintain the appropriate attribute relationships for your hierarchies.

The joining line appears in red color till the time relation between measure and dimension is not set.

Select the line and set the key joining measure to dimension.

As soon as you set the key, the line turns blue.

We will discuss the properties of measure, dimension, hierarchy, level and attribute later in this document.

Relation between Measure and Dimension
Figure 5: Line showing Relation from Measure to Dimension

You can add multiple measures each sourcing a field from the Query Object.

You can add multiple dimensions by dragging multiple Query Objects on the dimension area. It helps to create levels if you drag the fields inside an existing dimension.

The fields can be added to the Editor by either of the following ways:

  • Dragging and dropping a field from under Fields pane as measure/dimension/attribute/hierarchy
  • Clicking the + sign under Edit Mode to add measure, level or attribute on the Editor area. You can specify a field on the properties pop-up window on clicking the newly added measure, level or attribute.
  • You can click the query object name (for measure or dimension) to list the fields in a pop-up window as shown in Figure 6. Right-clicking the field would add the field as measure, level or attribute

On the same pop-up, click the Edit Query Edit Query icon icon if you want to make any changes to the selected Query Object. The query object can be removed by clicking remove edit query icon.

Query Object Properties
Figure 6: Query Object Properties Pop-up

Click arrow icon or  arrow iconto respectively expand or collapse the measure, level or attribute.

In Edit mode, you can select a field and move them up and down using the arrow keys arrow icon arrow icon.

Click the cross cross icon icon  in front of the respective field to remove a measure, measure group, hierarchy, level or attribute.

A typical designed Analytical Object shall look like below:

Analytical Object
Figure 7: Analytical Object

Query Object Selector

Query Object Selector provides a categorized list of Query Objects accessible to current user. It allows dragging Query Objects from the list to the design area.

The sections in Query Object Selector can be resized by dragging the section borders. Use arrow icon or arrow icon respectively to expand or collapse sections in Query Object Selector pane.

You can open the Query Editor by clicking Edit Query icon in edit query icon front of the Query Object name on the Query Object Selector pane as shown in Figure 8.

Query Object Selector
Figure 8: Query Object Selector with Fields

Specifying Properties of Measures and Dimensions

The properties pop-up window shows and captures properties of selected item on the design area.

The following tables list the properties of measure, dimension and hierarchy/level/attribute.

Measure Properties
Item Property Values Comments
Measure Name Type yourself You can see this name for this measure on the High Speed View and reports.
Function SUM,

AVERAGE,

COUNT,

MINIMUM,

MAXIMUM

DISTINCT COUNT

Select from the list as per need.

Refer the section underneath this table to learn more about Distinct Count measure.

Field Select from the Query Object Fields The list of Query Object Fields appears here.
Format Format String

#,##0.00

This format will be applied on this measure on High Speed View and reports. To know more on Data Formats, please refer Appendix A
Unit Type yourself Used as a suffix for the measure value on High Speed View and reports.
Visible Check/Uncheck Check – You can see this measure on viewer

Uncheck – You cannot see this measure. You can only use it to express another calculated measure.

Default Yes/No Specifies if this is the default measure for this cube. You should have at least one default measure to create an analytical object.
Adjustment Check/Uncheck This is a waterfall type chart related property. If you check this property, then this measure becomes a floating bar on a waterfall type of chart.
Adjustment-Yes

Negative/

Positive

 

Negative/

Positive

If the adjustment property is checked, then you will see this property.

If you set as negative adjustment – the floating bar will be Red in color and will be drawn downwards from previous measure’s top.

 

If you set as positive adjustment – the floating bar will be Green in color and will be drawn upwards from previous measure’s top.

Expression Check/Uncheck Check – This is a calculated measure.

Uncheck – This is a field sourced measure.

Solve Order 0-N You can set any number from 0 to N.

The lower Solve Order Calculated Measures will be evaluated prior to higher Solve Order Calculated Measures.

Distinct Count Measure in Analytical Object

When creating a multidimensional analytical object, sometimes you might need to use the distinct count measure. Aggregating measures now allow you to calculate the Distinct Count for the field data you are aggregating. For example, to know the number of unique users who buy products of certain type. Since one customer may buy multiple products, the distinct count measure will be useful in such cases.

In case of distinct count field selection, it can be aggregated by ignoring hierarchy or considering hierarchy of the selected dimension field.

Example, in case of a hierarchy as Country > State > City; if cube designer creates a distinct count measure for “City” field and City name is common in two states then at Country level, the distinct count can be aggregated to 1 if “Ignore Hierarchy” check box is checked and can be counted to 2 if “Ignore hierarchy” check box is unchecked.

The below image shows Analytical Object in case the hierarchy is ignored.

Distinct Count Measure
Figure 9: Distinct Count Measure

The count of cities with a common name would appear as 1 at the Country level on the report in case the hierarchy is ignored.

Distinct Count Measure-Hierarchy is ignored

Figure 10: Distinct Count Measure example when Hierarchy is ignored

Alternately, the count of cities with a common name would appear as 2 at the Country level in case the hierarchy is not ignored.

Distinct Count Measure-Hierarchy is not ignored
Figure 11: Distinct Count Measure example when Hierarchy is not ignored

Calculated Measures

Calculated Measures are expressions created on existing field based measures.

Calculated measures are NOT calculated during cube build and they don’t consume any disk space.

Calculated Measures are evaluated at the time of slicing and dicing on the result set extracted for current view and analysis.

If you can achieve a measure using an expression, it is always efficient to make it a calculated measure. For example: Field1 + Field2, % of contribution etc.

Calculated measures are evaluated in the increasing order of “Solve Order” value.

Expression Syntax

The basic syntax supported is MDX linear syntax.

Measure values at the same level are accessed by

[Measures].[Field1]

Linear calculation

To Sum field1 and field2 in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

[Measures].[Field1] + [Measures].[Field2]

Previous Row Value

To calculate Running Total of field1 in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

 [Measures].[Field1]

+ ( [Measures].[Field3], prevRowMember() )

Contribution calculation – inner most group

To find Contribution of field1 in field1’s lowest grouped total, in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

 (( [Measures].[Field1] )  /

  ( [Measures].[Field1], currRowMember().PARENT ))

Contribution calculation – outer most group

To find Contribution of field1 in field1’s lowest grouped total, in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

(( [Measures].[Field1] )  /

 ( [Measures].[Field1], currentDim().defaultMember ))

Dimension Properties
Item Property Values Comments
Dimension Name Type yourself You can see this name for this dimension on the High Speed View and reports.
Type Regular

Time

Type of the dimension as Regular.

Type of the dimension as Time.

GIS Enabled Check/Uncheck Check = If this dimension needs to be plotted on a map.
On Incremental Build Processing Mode Options:

 

 

Skip Processed Files And folders

 

Reiterate And Skip Processed Records

This governs the action in case of Incremental Build.

 

Skips processed files and folders while building.

 

 

Skips only processed records under files and folders while building.

Hierarchy/Level/Attribute Properties
Item Property Values Comments
Hierarchy Name Type yourself You can see the name for this Hierarchy on the High Speed View and reports.
‘All’ member caption Type yourself Caption to be displayed on High Speed View to show the summarized value.
Field Select from list Value of the field in case of a Time type dimension.
Calendar Select from list In case of a Time type dimension, for the chosen date field you can select Calendar as ‘None’ to specify Year-Quarter-Month-Day as YQMD, YQM, YQD, etc. under Levels. In case of ‘Standard’ Calendar, along with the levels you can specify the Start and End Dates.

You can also customize the Standard Calendar according to your business needs. For example, an organization may be using a twelve-month Gregorian calendar starting on January 1 and ending on December 31st. However, some organizations may want to use a fiscal calendar that defines the fiscal year used by the organization.

Levels Select from list Choose from YQMD, YQM, YQD, and so on.
Date Start Date

 

 

 

End Date

Specify the first date, month or year in fact table. You can also specify a fixed start date from calendar

Specify the last date, month or year in fact table. You can also specify a fixed end date from calendar

Level Name Type yourself You can see the name for this Level on the High Speed View and reports.
Field Select from list Value of the field in case of a Regular type dimension.
Display Field Select from list Display name of the field in case of a Regular type dimension.
Secure Using Parameter Select from list You can choose from the secured parameters to restrict user to view cube data.
Hyperlink Select the option In case of a hyperlinked field (specified at the analytical object level), you can drill down to open another report or URL on clicking the value of field.
Level Format Select from list If you have specified levels for a TIME type dimension, you can choose a Level Format to display a particular year, month, quarter or day.
Sample Type yourself Specify an example for the chosen Level Format.
Attribute Name Type yourself You can see the name for this Level on the High Speed View and reports.
Field Select from list Value of the field in case of a Regular type dimension.
Data Type Read only Data type of the selected attribute
Secure Using Parameter Select from list You can choose from the secured parameters to restrict user to view cube data.

Specifying Actions from the Button Palette

The various menu actions that can be performed on the analytical object are described in the below table.

Item Action
New Closes currently opened analytical object and provides a new screen for designing an analytical object.
Open Opens a selector dialog to select and open the selected analytical object for editing. To open an analytical object, expand the folder and either double-click or click the analytical object name and click Open.

List View shows the list of analytical objects.

Detailed View shows the detailed view of analytical objects list. You can see details like the ‘Owner’ and the ‘Last Modified Date’ of analytical objects.

Refresh List refreshes the shown list of analytical objects fetched from the repository.

Save Saves any editing work done on the analytical object to the repository. This saves the definition of analytical object only. It doesn’t refresh the cube data.
Save As Saves currently edited analytical object with a new ID (auto-generated) and Name in repository. You can choose a different folder location to save.

Build will be required before end user can see and use this cube.

If you are saving an existing analytical object with a new name, check “Copy Access Rights” under Options to copy the same rights to this new analytical object. You can also add Description to the analytical object.

Settings You can specify the Build Schedule options as:

Once: Initiates the Cube Build activity in the background.

Cube build activity is a background activity. You can leave this screen after initiating the build, leaving the screen doesn’t cancel Build activity.

The Build Status tab shows up when Build starts.

You can initiate Build only once concurrently for a given cube.

If you Open an analytical object and its Build activity is going on, then you will see a disabled Build button.

You may need to wait till build is completed or you can Cancel the Build before you can reinitiate Build activity.

Recurring: You can schedule the build activity periodically within the start and end dates.

Build The options provided under Build include:

Full: To be able to schedule a full build on the saved cube either once or in recurring mode.

Incremental: To be able to schedule a build picking differential data after the last build was run.

Consolidation: Consolidates previous run full or incremental builds.

Cancel Cancels current editing action of New or Open and closes currently opened analytical object.
Delete After a confirmation prompt dialog, deletes currently opened analytical object from repository. This action is not reversible.

Building a Cube

After saving the analytical object, click the Build button to choose either Full, Incremental or Consolidated build (as explained in the table above). The Build Status bar is shown on top of the cube.

Once you click the Build Status bar, you see the below details:

  • Log entries of current data processing details by Report Engine
  • Status: Building, Error, Completed
  • Size on Disk
  • Time elapsed from initiation
  • An eraser icon eraser icon to clear the logs from this screen
  • A Cancel icon Cancel icon to cancel the background Build activity
  • Build History shows the build summary of current and previous builds on clicking the Build Status hyperlink that appears on top of the cube

Build Status
Figure 12: Build Status

When No Build activity is going on, then this bar shows:

  • Last Build Status
  • Size on Disk
  • Time elapsed in building last cube

Note: Any change made to the analytical object requires re-building of the cube.