Date not grouped in story with Datasphere model

[jlopezm]

Hello SAP Analytics Cloud and SAP Datasphere experts.

I have made an analytics model following this flow:

Replication Flow > Table > SQL View > Analytic Model

Within that process, I have a table that stores a field (ERDAT) as String since that is how I get it from the SAP table. In the SQL View I transform it into a date with the to_date() function. And this is done correctly, however, when I go to view it in the SAP Analytics Cloud history and I want to, for example, set a filter, they do not appear grouped as when I create the model directly in SAP Analytics Cloud:

but by individual dates:

I don't know how I can change this setting in SAP Datasphere. Or if it is something to change in SAP Analytics Cloud.

Best regards

[jlopezm · Accepted solution]

Thanks @N1kh1l  for the start of what was the solution for me. Besides creating the SAP DS Time and Date dimension I had to follow a few more steps, which I don't know if they are too many or if any are left over, but they worked for me:

I created a new association in the table

And we link by dragging the two fields of the table and the Time Dimension - Day :

We will use the Date field (String) since SAP provides the data in String type YYYYYMMDD and not in DATE format. Save and deploy.

In the SQL view (fact) we will associate the SQL view with the dimension in the details side panel, in the Associations section:

Here we must also drag to make the association:

In this case we do it by the Date field since we have transformed, from the SQL view, the initial data from String to Date. Save and deploy.

Finally, in the analytical model, when creating it and adding the fact, we have to make sure that we add this field:

When you do so, a drop-down will automatically be added to the date dimension:

If we look at the detail we can choose all the attributes that we want to be loaded

Then we simply save and deploy.

[N1kh1l]

@jlopezm 

I think just converting the string to date will not create the necessary hierarchies. I think you would need to create the SAP DS Time and Date dimension.

https://community.sap.com/t5/technology-blogs-by-sap/understanding-time-tables-dimensions-in-an-sap-...

https://developers.sap.com/tutorials/data-warehouse-cloud-graphical5-timetables..html

Hope this helps !!

Nikhil

[jlopezm]

Thanks for the answer. I have managed to create the SAP DS Time and Date dimension, but what I can't get my model to take the hierarchy to SAC. I have a table where I have included the association by the ERDAT field. I also made the association in the SQL View using the association field and also joining it in the SQL query. But none of this is working for me to get the hierarchy to appear in SAC. would you know what step I am missing? Thank you very much!