The Custom Analytical Queries app is a tool used for reporting and analysis that provides the fields required to design a query. You select the required fields and set filters for your query. You can add restricted measures, calculated measures, and preview the query results.

Today in this blogpost we will discuss about using Custom CDS Views app to create data sources for analytical scenarios and the CDS view modelling rules that you need to consider for these specific scenarios.

Analytical Cube

A cube represents a multi-dimensional data model consisting of attributes derived from dimensions and measures.

You can use the View Browser app to find a suitable data source for your cube. The View Browser is a Fiori application with which you can quickly and easily search and browse data sources of analytical queries. After you have found a data source you want to use, you continue in the Custom CDS Views app.

For more information, please check SAP Help Portal | Creating a Cube.

If the Custom CDS View should be exposed as Cube please notice the following:

• The primary data source cannot be a Dimension.
• There must be at least one measure exposed. That means at least one field where the Aggregation is selected (and not None) on the Field Properties tab.
  • All other non-measure fields are master data. They are automatically part of the grouping logic.
• Make sure that for the calculation of measures all required currencies and unit of measures (including their Master Data View associations) are exposed.

Analytical Dimension

Dimension consist of a unique set of master data values (attributes) that identify and categorize a data set. They represent the edges of the cube. To derive meaning from the attributes in a cube, a value must be qualified by at least one attribute of each dimension defined in the cube.

If the Custom CDS View should be exposed as Dimension please notice the following:

• It must have a Representative Key field.
• It must not contain any parameters.
• It must not contain any language dependent texts.
  • A Text Association should be selected for the representative key field, if a language dependent text is available.
• In case of time-dependency the key field with time semantics is annotated accordingly on the Field Properties tab, e.g. BusinessDate.To. In addition a non-key field with semantics BusinessDate.From is required.
• All key fields except the Representative Key field and the date/time fields must have an association to a Master Data View.
• Non-key fields can be either measures or master data.

Both Scenarios

• All numerical fields must be measures. That means one of the aggregations Sum, Max and Min must be selected. Other aggregations are currently not supported by the Analytical Engine.
• Notice that the aggregation Sum is using the datatype of the measure. In the case of a calculated fied, you might need a CAST to a larger datatype to store the aggregated sum.
• Selected language fields will usually not be available in a query as they are treated seperately in the analytical runtime (typically filtered with the system language). If you need them to be available, consider casting them.
• All character-based fields cannot be measures, they are master data fields and thus an aggregation cannot be set.
• Exceptions are non-negative numerical, date or timestamp fields. These can be either a measure or master data.
• For exposed master data fields an association to a Master Data View can be maintained on the Field Properties tab.
  • The mapping on the Association Properties tab must use the Representative Key of the Dimension view.
  • All key fields of the Dimension view except date fields of semantics “BusinessDate.To” and language codes have to be mapped.
  • If a Master Data View is maintained, a value-help, display properties, texts and hierarchies are available in the Custom Query.
  • A Master Date View is mandatory if the master data field is used in the mapping to the Master Data View of another master data field.
• Only Associations with cardinality “Zero or One [0..1]” shall be used. That means all key field of the associated data source must be mapped on the Association Properties screen.
  • Exceptions are time-dependent fields and language code.
  • In general adding further associations might influence the aggregation SUM logic!
  • Do not expose the fields of the Associated Datasource that are used in the association properties. Use the fields of the primary datasource instead.
• Fields of type string cannot be used in analytical data sources. You will be able to add them, but they cannot be used (limitation of the analytical engine).
  Unfortunately, CDS does as of now not support to cast these fields either nor is it possible to do string operations on them with CDS functions.
• Character based fields with a length larger than 250 cannot be used as they are not supported by the analytical engine. In order to use such fields, you can instead use a calculated field to cast the original field to a length of 250. Be aware that this may lead to problems if the content only differs in the last characters that are cut off by the cast: “cast( FieldName as ABAP.SSTRING( 250 ) )”
• UTC timestamps are not supported in analytical CDS views as characteristics and will therefore not be displayed correctly. Refer to SAP Note 2737769 for details. In order to use such fields as characteristics, you can instead use a calculated field to cast the timestamp: “cast(<DS>.<UTC_TImestamp_DField> as TZNTSTMPS)”
• Be aware that all analytical custom CDS views will only read current data from HANA memory. See SAP Note 2869647 for more details.

Hope this information is useful for you! I would greatly appreciate if you could share your feedbacks and thoughts in the comments.

Also, I encourage you to browse for other Community Topics that may be useful for you.