Date Hierarchy and Date Tables via Direct Query (SAP HANA)

[bselby]

Hi,

I'm having an issue getting the Date Hierarchy to appear on date fields retrieved via Direct Query mode for the SAP HANA database connection (treated as relational source).

If I create the connection in "Import" mode, it recognises the date hierarchy fine:

 If I create the connection in "Direct Query" mode, it doesn't create the hierarchy (even if it is enabled in settings):

I can add my own date table via "new table" and CALENDAR function as suggested in previous threads, and join to my query table, and that seems ok:

 And the storage mode changes to mixed:

However, once I have added this table, whenever I try to edit the stored query in future, it always wants to convert it to Import mode - the Direct Query mode is disabled (this doesn't happen without the date table):

This means when I make a change, the file becomes irreversably import-based.

What is the best way forward here? I don't really want to "lock" my file to the first version of the query saved, because there will always be further changes to make, but I can't seem to enter a date table otherwise.

[v-cherch-msft]

Hi @bselby

The built-in date hierarchy is not available when using DirectQuery mode.I would suggest you create the Year,Quarter,Month,Day columns manually.Then you may right-click the Date column and choose 'New Hierarchy'.Then click the Year,Quarter,Month,Day columns and add them to this new hierarchy manually.

Regards,

Cherie

[bselby]

Hi Cherie,

Thank you for your response.

The problem is that I need the date table joined in order to keep the custom sort order on my line chart (if there is no data in the filtered visualisation for a specific month or months, it doesn't sort correctly as per my other thread).

However, I've since found that I can actually keep the dashboard as Mixed Storage depending on where I adjust the query. In the example picture below, if I adjust the query in the red box, the whole dashboard irreversably changes to Import as I originally stated. But, if I change it in the DAX formula box, it will correctly adjust the query and keep it as Direct Query! As such, the entire document stays as Mixed Storage mode.

This has worked well for me, as I have been able to adjust my query several times without losing the Direct Query mode, and keep my date table intact. The only tedious part is changing the query into the DAX format, but I've found that if I just quickly copy the file, paste my amended query into the red box, let it convert it to the DAX format itself and then copy that into the DAX section on my main document, it saves a lot of time.

If you know of a solution or upcoming fix that would allow me to make changes in the red box without it converting to Import mode, please let me know. Thank you!