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

Authors: Dhiraj Salian ( dhiraj.salian@sap.com) , K.Prasanthi ( prasanthi.k@sap.com )

Introduction

In this document, we present steps to create hierarchy view and text view for dimension views and associate them in fact view. This document consists of five sub parts to create dimension views for channel, product, region, fiscal year and fact view for billing document. This document expects you to have some experience with CDS view such as creating CDS view, creating associations and checking output of queries in query monitor. If you are not familiar with these 3 things please go through these topics before reading ahead.

CDS view for dimensions and facts is developed as follows:

  • CDS Views for the dimensions channel, product and region is text associated.
  • CDS Views for product and region is hierarchy associated.
  • CDS View for fiscal year provides fiscal year, fiscal quarter and fiscal period attributes in a specific format.
  • CDS View for billing document has Net Amount as key figure and is associated to the dimension views channel, product, region and fiscal year.

 

The five sub parts of the document are as follows:

  • Create dimension view with text association (Channel)
  • Create hierarchy view using a single dimension (Product)
  • Create hierarchy view using two dimensions (Region)
  • Create fiscal year dimension based on calendar date (Fiscal Year)
  • Create billing document items fact view

Create dimension view with text association

In this part we create a dimension view for channel. The dimension view for channel is associated with the text view for channel. We show how to change the text description on the view without modifying text at table level. Before creating dimension view for channel, create text view for channel. The important annotations for text views are as follows:

@ObjectModel.dataCategory: #TEXT

@Analytics.dataExtraction.enabled: true

@Semantics.language: true

@Semantics.text: true

Here is the text view for channel:

 

If we want to change the text description without updating the table, we can introduce a case statement which will change the text description in text view. Here is the modified text view for changing the text description in the view. The text description ‘RRB’ is changed to ‘Resource Related Billing’ without modifying the data in the table.

Here is the modified text view for channel:

 

Now we create a dimension view for Channel and associate with its text view. The annotation used to associate the dimension view with text view is @ObjectModel.text.association. The other important annotations are as follows:

@VDM.viewType: #BASIC

@Analytics.dataCategory: #DIMENSION

@Analytics.dataExtraction.enabled: true

@ObjectModel.representativeKey: 'Channel'

Here is the dimension view for channel:

 

Here is the output for channel dimension view:

 

Create Hierarchy View using a single dimension

In this part we create dimension view for product. The dimension view for product is associated with text view for product. Like channel dimension, the text view is created for product dimension view.

Here is the text view for product:

 

Now we create a dimension view for product. This dimension view will be associated to its text view and hierarchy view later.

 

After creating the dimension view,  we create the hierarchy view for product. In hierarchy view it is important to have the relation between the child and parent. This relation can be created in the view itself depending on the data available. In our case, we  convert the dimension data in the view itself to a parent-child relation data as follows:

prodh

100001000111100

1000111100

11100



childNode

parentNode

100001000111100

 

1000111100

100001000111100

11100

1000111100

 

Here is the hierarchy view created for the dimension view of product. The important annotations for creating hierarchy view are as follows:

@VDM.viewType: #BASIC

@ObjectModel.dataCategory: #HIERARCHY

@Analytics.dataExtraction.enabled: true

@Hierarchy.parentChild.name: 'PROD_HIER'

@Hierarchy.parentChild.label: 'Product Hierarchy'

@Hierarchy.parentChild:

{ recurse: { parent: 'parentNode', child: 'childNode'},

siblingsOrder: { by: 'childNode', direction: 'ASC' },

  • orphanedNode.handling: #ROOT_NODES,

rootNode.visibility: #ADD_ROOT_NODE_IF_DEFINED

}

@Hierarchy.parentChild.multipleParents: true

 

Now we modify the dimension view for product by associating it with text view and hierarchy view.

 

Here is the output for product dimension view:

 

Create Hierarchy View using two dimensions

In the previous hierarchy view, hierarchy was created within a single dimension. Now there may be situations where we should create hierarchies within 2 dimensions, for e.g. Hierarchy between country and region. In this part we will use two fields from table kunnr that is vkorg (Sales Organization) as country and bzirk (Sales District) as region.

In our case we will be converting the two dimensions into parent child relationship as follows:

vkorg

bzirk

 Australia

APAC

Germany

Europe

United Kingdom

Europe

childNode

parentNode

 Australia

APAC

Germany

Europe

United Kingdom

Europe

APAC

 

Europe

 

                                                                                                   

Since we don’t have a single column which can be utilized directly as childNodes for the parent-child relation, we will create cds views which will act as tables for our dimension, text and hierarchy views.

Here are the cds views for the geo table and geo text table:

 

 

Like product dimension we will create dimension view, hierarchy view and text view for geo dimension.

Here are the codes for geo dimension view, text view and hierarchy view respectively.

 

 

 

Here is the output for geo dimension view:

 

Creating fiscal year dimension based on calendar date

In this part we create calculated fields based on the calendar date in fiscal year dimension view. In some scenarios, the customer wants to have fiscal year, fiscal quarter and fiscal period in a different format. For e.g. financial calendar for the customer is April to March and format requested is as shown in the table below:

Calendar Date

Fiscal Year

Fiscal Quarter

Fiscal Period

2018-01-01

FY17/18

Q4_2018

P10 Jan_2018

2018-04-01

FY18/19

Q1_2018

P01 Apr_2018

2018-07-01

FY18/19

Q2_2018

P04 Jul_2018

2018-10-01

FY18/19

Q3_2018

P07 Oct_2018

 

To utilize such a fiscal calendar format we will create a fiscal year dimension with calendar date as key and calculations fiscal year, fiscal quarter and fiscal period as its attributes. These attributes will then be utilized in the fact view for billing document item.

 

Here is the output for fiscal year dimension view:

 

Create billing document items fact view

In this last part we integrate all the dimensions we created into fact view and publish it as OData. The important annotations for the fact view are as follows:

@VDM.viewType: #CONSUMPTION

@Analytics.dataCategory: #CUBE

@Analytics.dataExtraction.enabled: true

@OData.publish: true

Here is the fact view for billing document item:

 

 

 

  • No labels