CubeServ Blog
Stay up-to-date on the data-driven business with tools for analytics from SAP & Co. and do not miss any news, downloads & events.

The Time Dimension in the SAP Data Warehouse Cloud

With Data Warehouse Cloud Version 2020.14, SAP released the feature to create time dimensions.

Why is the time dimension so important?

Beforehand, when loading data into SAP DWC there had to be many aggregation levels of a date column, e.g. for a separate column «Quarter», or «Year», if there was the need to have multiple levels of time aggregation in the story.

Some of us are used to SAP BW, where the single date field could be leveraged not only to the corresponding date field in the target object, but also to other date fields like Quarter and Year. This automation is now available in the Data Warehouse Cloud too. With only one date field you can filter with other date levels in your model as well, if you use the new time dimension. It automatically casts the day information into year, quarter and as well month and provides prebuilt levels of time hierarchies on top to select from in the story mode.

So it’s a pretty nice feature we didn’t want to miss in the DWC, so let’s make it available in your DWC tenant as well!

Access the time dimension in your model

To leverage your story with variable time axes you first have to add the time tables and its dimensions into your space and model.

Go to your space and click on the “Time Data” card to create time tables and dimensions.

In the next screen you’ll see all objects DWC is creating:

  • time table (contains date data)
  • time dimensions (Year, Quarter, Month, Day)
  • translation tables (en, de, fr, es)

You can adjust the business names of the objects and configure the date range. Technical names and the calender type are predefined.

By clicking on “Create” they are now available in your space.

In the data builder click onto a dimension view and you’ll see the SQL statement and all configurations SAP is delivering its time objects with. Note that there are also hierarchies in the day and month dimension. Enter them by clicking on the staircase sign in the properties.

Now jump into your analytical dataset which you want to enrich or upload the data into the DWC.

 

In my case I started from scratch and uploaded a csv file through the data builder. The file contains a column called «Day » with the already correct recognized data type «Day».

Make sure your preferred date column is not of type string otherwise it will not work!

Click on « Deploy » in order to proceed.

Next, navigate to the data builder and select « New Graphical View ». Add your table by drag and drop to the canvas and click onto the view. In the properties, add the time association that fits best to your date column (day, month, etc.).

I’ve picked the day dimension as I had a day column in my data file.

In the upcoming window you’ll see the new date dimension. The system wants to know, which column of the table shall be connected to the new associated day dimension.

Bring both together by dragging and dropping the column “Day” onto “Date” and you’re ready to go for the story builder.

Please keep in mind that their data type has to be of the type “Date”!

If you would like to enhance your data model that is uploaded as an aDSO from BW, it may be possible that your date object is currently recognized as a string in DWC.

In this case you first have to include a calculated column to cast the datatype string into datatype date (current workaround).

Click on the remote table and add a calculated column including the formula « TO_DATE(<column>,’YYYYMMDD’) », which converts a calender day string into a calendar day date type as it is demonstrated here:

Now you have a new column with the proper datatype. From here you can go on and create the association to the time dimension as explained earlier.

Keep in mind: The association to the time dimension has to be implemented directly in the analytical dataset – not before on the entry level view of the table. Therefore, I proceeded and switched the view into an analytical dataset.

Story building

Don’t forget to fulfill all prerequisites for the usage of the time dimension in the story modeler:

  1. Switch the type of the view to Analytical Dataset.
  2. Allow consumption.
  3. Deploy your model.

Create a new story and select the data model. Choose a chart, add a measure of your preference and add the associated date column of the model to the section Dimensions (design panel).

Now you can see the outcome of including the time dimension.

Slide over the day dimension and you’ll recognize that the hierarchy is available. Click on the staircase and further on « Set Hierarchy… ». 

All the hierarchy levels are now available here automatically.

Depending on the hierarchy setting, e.g. «Year, Quarter, Month, Day» the levels of the hierarchy are affected.

I’ve chosen the hierarchy «Year, Quarter, Month, Day» here. With every level down the hierarchy, the data gets more granular and you can dig into the details. All this with adding only one date object.

We at CubeServ have already written various articles on this topic.
Use the following links to gain further insight into the SAP Data Warehouse Cloud.

Vereinbaren Sie jetzt Ihren Expert Call. Wir freuen uns über Ihre Nachricht.

Leontina Baitinger

SAP BI/BW Consultant @ CubeServ # SAP DataWarehouse Cloud # SAP Analytics Cloud
Subscribe our Newsletter

Keep up to date on SAP Analytics Cloud, SAP Data Hub, and Big Data, and do not miss any news, downloads & events.

Author
Expert Team
Benedikt Bleyer
Adrian Bourcevet
Roland J. Merz
Urs Mock
Drazen Pavlovic
Julian Rudack
Thorsten Stossmeister
Aginthan Thavarajasingam
Jan Wiesemann