Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

This page presents the theory and best practices for using SAP BusinessObjects Universes with SAP Analytics Cloud.

Please use this page in conjunction with this step-by-step example

 

Optionally enhance Universe

Re-using existing Universe

  • No need to create a derived universe (as previously recommended)
  • Previous major limitations in the Live Universe Connector have been resolved
  • No need to re-create any existing Business and Data Security Profiles
  • Best Practice Recommendation: Use existing Universe
    • Some extreme use-cases may require a dedicated universe
  • Some limitation still apply and these are discussed next…

Optionally enhance Universe

  • Some aspects of a Universe are not fully consumed by SAP Analytics Cloud yet

Not currently consumable as ‘result’ objects:

  • Dimensions of data type ‘DateTime’
    • Date’s are supported, just not ‘DateTime’
    • Possible solution: Create new ‘Date’ object
  • Dimension/Measure Attributes (details)
    • Possible solution: create new dimensions or measure
  • Measures with projection function ‘database delegated’
    • Possible solution: create a measure calculation in Analytics Cloud that does the same

One language

  • The final model that is created in SAP Analytics Cloud to the BI Platform supports only one language
  • @Variable('PREFERRED_VIEWING_LOCALE’) will not change, it is fixed to the language used in the SAP Analytics Cloud connection to the BI Platform

Query Panel filter pane general capabilities

Supported:
  • Adding new filters including filtering on ‘date’, ‘datetime’
  • Nest conditions with “AND” and “OR”

 

Current limitations:
  • Add a combined queries (union, intersect, minus)
  • Create new subqueries, database ranking, query on query
  • Create new prompts
    • Though prompts are supported as @prompt in objects’ SQL

Possible Solutions:

  • Create Additional universe Business Filters including the
    • @prompt
    • Sub query logic
  • Combined Queries (union, intersect and minus)
    • Either multiple SAC models, one per ‘query’ and then ‘join’ prompts in SAC
    • Place the combined query in a derived table

Query Panel filter pane supported query filter operators

Query Panel filter pane list of values capabilities

Supported:
  • Displaying list of values

Current limitations:

  • Objects with a Primary Key are not currently supported

Possible Solutions:

  • Temporarily remove Primary Key
  • Or create new BusinessObjects

  • It is the objects’ SELECT that generate the list of values within the ‘Data Explorer’ interface as well as when adding filters to stories or input controls, rather than the associated ‘List of Values’
  • List of values are supported with @Prompt

Measures

  • Must have at least 1 measure in the universe
  • Optionally ‘Move’ measures to the root folder
    • Measures within SAC appear with their full ‘path’ and this can be distracting
    • Consider moving measures, out of the ‘Measures’ folder to the Universe root folder
Measures appear in their folders within SAP Analytics Cloud which is a little distracting:
Moving measures to the root makes them easier to consume in SAP Analytics Cloud:

Customise Measures

Display Formats
Are currently ignored by SAP Analytics Cloud
All measures have a decimal format 0.00
As an example ‘Rows’ could only be a whole number and never a decimal
SAP Analytics Cloud has number formatting options at the Story level as shown above that may present the values in a more meaningful manner

Advanced Object Properties for ‘Objects can be used in’

  • Both options ‘Results’ and ‘Conditions’ are respected
    • If you use an object where ‘Conditions’ is not allowed then you’ll get a ‘Unable to retrieve contexts’ error
    • We’re working to resolve misleading error message. Please let us know if we missed any others
      • Tip! If you get ‘unable to retrieve contexts’ it could mean ‘a problem occurred somewhere with the query’. And not be context related!

Filtering data

  • Use the ‘Model’ query panel as earlier described
  • Story level Input Controls and ‘Advanced Page Filters’ provide the means to build complex filters, but these still may be short of existing Universe filters
  • Additional ‘derived’ universes, with fixed complex filters, may be needed to fulfil complex filter functions
  • Filtering on measures requires a universe prompt (roadmap item)
  • Possible Solution: Push the logic into Business Layer Filters and use @Prompt where necessary

Business Layer Filters

  • Mandatory Filters with and without Prompts are supported
    • Includes mandatory filters defined with Native SQL (@Prompt) and Business Layer prompts
    • Enables a universe to filter on a particular dataset or business use-case
  • Optional prompts are currently not supported

@Prompts

  • Query panel for live connectivity doesn’t provide an opportunity to define prompts
  • Instead they must all be defined with @Prompt or @Variable in the SQL
    • Business Layer (SELECT, WHERE) or in the Data Foundation

Limitations:

  • Cannot use a formula to set dynamic default value
  • Optional prompts are currently not supported
  • Cannot use List of Values on Objects with Primary Keys in the modeller query panel. Objects with Primary Keys are otherwise supported.

  • Single values answers, not multiple, for
    • Hardcoded list of values (so the selection mode is always ‘mono’, ‘multi’ is ignored)
    • When constrained mode is ‘Free’
    • Applies to Business Layer and Data Foundation
  • If multiple values are needed, use a list of values on an object
  • For multiple free text entry use multiple prompts with ‘OR’ in the SQL

List of Values referred to by @Prompt

  • Needed to provide multiple value answers
  • Supported:
    • All the types of ‘List of Values’
    • Multi-column
    • Cascading prompts (that then form a hierarchy)
  • Limitations:
    • Custom Hierarchy List of values, no delegated search etc.
  • Best Practices for List of Values
    • Keep the list short to provide good performance in the user interface

Multiple and Single Answers to @Prompt

Prompt data-types

All types are supported:
  • Alphanumeric (A), Number (N), Date (D) and Datetime (DT)
  • Date provides a calendar pop-up
  • Datetime requires data entry

For ease of use,
  • use Date over Datetime

Universe Prompts  in SAP Analytics Cloud modeller

Universe Prompts used in the query are visible in the SAP Analytics Cloud modeller
  • Select ‘Variables’
  • Currently no options available

How Prompts work within a Story

Upon creating a new story, all universe prompts are shown

Different Variables at chart/table and Story level

  • Set a visualisation to use either story or chart variables
    • Allows for different visualisations to use different variables
    • Once a Chart level variable is set, the user will no longer be prompted, the prompt is at the Story level only

Dynamic Text to show prompt/variable values

  • Dynamic text can be added to show prompt/variable settings
    • Works at both Story and Visualisation levels

  • Result is two visualisations, each using a different prompt/variable
  • No need to create a model per set of variable values, rather re-use the existing model and change the variable on the story/visualisation

 

Story Level Linking Variables

Multiple models using the same prompt will result in a prompt for each
Linking Variables means just the user is prompted once, not multiple times
It assumes the list of values are identical for both prompts, there is not ‘merging’ of values unlike with other live connection types
Linking provides ‘All data’ and ‘Intersecting data only’

Validate & Optimise

Integrity Check

  • If you made any changes validate universe
    • Run an integrity check on the universe before publishing
    • Resolve any issues

Validate query you intend to use as the basis of the SAP Analytics Cloud model

  • With either BI Launchpad or Web Intelligence Rich Client

  • Login as the user you will use from SAP Analytics Cloud

  • Create a new Web Intelligence document & new query off the Universe

  • Recreate the query that will be used for the model

Validate universe performance


  • Add the ‘Query Summary’ to see the duration and number of rows returned
  • The user will experience the query duration the first time they use the model in a Story
  • After this, Web Intelligence is their cache and the database query will only be issued upon a forced ‘refresh’
  • If query duration is too high need to tune either the query or the universe to improve performance

Validate BI Platform authorisations

  • Save the document into Favorites Folder
    • A ‘BOELiveProviderTempFolder’ folder will be create here by SAP Analytics Cloud
    • This folder is used to store temporary Web Intelligence documents created through SAP Analytics Cloud workflows to access data
    • The name of the folder is defined in boe.properties:
      boe.temporaryDocumnetFolder=BOELiveProviderTempFolder
  • Delete the document

Optimise Universe Performance

  • Considerations
    • Restrict the universe to a limited time period, or to a particular use-case
    • Add filters/prompts as needed until desired performance is obtained
  • Optimisation features you’ve not yet implemented, such as:
    • Use of summary tables with @Aggregate_Aware
    • In-line derived tables with BOUNDARY_WEIGHT_TABLE
      • These add a filter to tables before then being joined to other tables
      • Limitations
        • Can’t be used at the same time as @Aggregate_Aware
        • Can’t be used on a derived universe, need to ‘include’ the universe so to set table rows count
  • Move WHERE clauses into the FROM with FILTER_IN_FROM

Create Models in SAP Analytics Cloud

Model name

  • Use a meaningful name and description, but follow a convention
    • No spaces for model names allowed!
    • Model names can not be changed, but you can copy a model
    • Consider a naming convention to identify which SAP Analytics Cloud connection is used by the model

Model Query Panel

Group dimensions

  • Group dimensions
    • Makes it easier to use the model for end users
    • Don’t need to use the same folder structure as used in the universe

Model Preferences

  • Copy the Universe description to the Model description in one click
  • Screen also shows:
    • System name
    • Universe name
    • Any Universe Context used

  • Optimize Story Building Performance
    • Default is off
    • Best Practice: In general this should be enabled, especially when the data set is large
    • Performance for story building will be noticeably improved once enabled

 

  • Tip
    • When a new model is needed, copy an existing model to save rebuilding the query from scratch

Story Creation

Link Variables

  • ŸAny prompts defined in the models can be linked so a user answers the prompt once, not multiple times
  • Linking Variables means just the user is prompted once, not multiple times
  • It assumes the list of values are identical for both prompts, there is not ‘merging’ of values unlike with other live connection types
  • Linking provides ‘All data’ and ‘Intersecting data only’

Restricted Measures

  • Create new Restricted Measures without the need to create new BusinessObjects
    • ŸFor example ‘Web Intelligence Events’, which are ‘Events’ where a Dimension ‘Object Type’ has a value ‘Web Intelligence’
    • ŸThe logic is pushed down to the Web Intelligence server

Calculated Measures

  • Create new Calculated Measures without the need to create new BusinessObjects
    • ŸFor example ‘Events / Sessions’
    • ŸMany Formula Functions, Conditions and Operators are available
    • ŸPress CTRL + [SPACE] for the pop-up help
    • ŸThe logic is pushed down to the Web Intelligence server

Multiple Models per Universe

  • ŸA single story can host visualisations from multiple models
  • ŸEach model may or may not be based off the same universe
  • ŸAny prompts defined in the models can be linked so a user answers the prompt once, not multiple times

Mixing Multiple models with Linked Variables and Chart level variables

  • ŸThe Variable Linking must be performed after the Chart/Table level Variable is set
  • ŸThe Chart/Table Variable is ‘fixed’, the user is not prompted
    (regardless if linked or not)

Supported Smart Assist (Predictive) Capabilities

Supported:

  • ŸSmart Grouping on Bubble and Scatter Plot Charts
  • ŸTime-series forecasting on time-series (not on a line chart)

How to enable

  • ŸAs System Administrator select ‘System-Administration’ and then ‘System Configuration’
  • ŸEnable the business toggle ‘Live Data Models: Enable Smart Grouping and predictive forecasting in Time Series Charts’
  • ŸTake note of the tooltip, as data is uploaded to SAP Servers in order to run the algorithms
    • Enable Time Series Forecast and Smart Grouping on Live Data Models. Data processing occurs on SAP Servers’

Smart Grouping

Ÿ

  • ŸClick on ‘Smart Groups’ to change the number of groups
  • ŸGive the Groups a label, such as ‘Store Group’

  • Video

Time-series forecasting

  • ŸRequires a ‘date’ object, not ‘datetime’
  • ŸEnable by selecting ‘Automatic Forecast’
  • ŸThere is currently no hierarchy support on time. For example clicking on the date hierarchy just shows ‘Day’, no: ‘Month’, ‘Quarter’, ‘Year’
  • ŸAdvanced Options enables you to override the automatically selected algorithm and force the ‘triple exponential smoothing’ algorithm

Iterate

  • ŸNeed to adapt the Universe and the SAP Analytics Cloud model to meet changing business requirements
  • ŸTypically this will be defining complex filters, filters on measures and prompts
  • ŸMultiple Models based upon a single Universe is likely

Platform Recommendations

  • Create calculated measures / restricted measures requires BI 4.2 SP4 Patch 3
  • General performance improvements requires BI 4.2 SP5 Patch 1
  • Performance improvements specifically for large data sets requires BI 4.2 SP5 Patch 4

Useful Wiki links

This wiki is referenced by a blog. Typically please comment on the blog, not directly on these wiki pages.

  • No labels