Date fields changed to NVARCHAR via SLT

[patrickbachmann]

Hi folks,

By default, for all of our SAP tables that are replicated into HANA, each date field is converted to NVARCHAR.  I've also read that this can be changed in SLT.  ie: we could change this behavior to map ALL dates in ALL tables that are replicated, to true DATE type for example.  But obviously this mapping to NVARCHAR is done for a specific reason.  We were thinking this reason is related to compression performance and/or fuzzy searching optimization perhaps but I've scoured all of my documentation and have yet to find a definitive explanation.  Can somebody confirm why dates are converted to NVARCHAR and what the advantage is versus a true date format?

Thanks!

-Patrick

[Former Member · Accepted solution]

Hi Patrick

As per me the SAP DATE format does not match with the DATE format of ANSI SQL 92(HANA uses these standards). The most closely it resembles to is NVARCHAR(8). This could be the reason why it is being used instead of SQL DATE format.

Also I had worked a bit on ABAP for HANA, where the same phenomenon was seen. Its actually required because when the HANA artifacts(Analytic Views, Attribute Views, etc.) are exposed to ABAP layer via Dictionary views(DDIC Views). The Dictionary views will not accept SQL DATE format as input.

To maintain the compatibility with ECC could also be the reason to have it as a NVARCHAR(8).

-Shreepad Patil

[patrickbachmann · Accepted solution]

I left this open for a while as I was hoping for even more additional feedback regarding the NVARCHAR benefits but no more responses it seems so I am closing.  Thanks for your helpful responses guys!

-Patrick

[former_member184768 · Accepted solution]

Hi Patrick,

A word of caution, advice and request.

If you are planning to use the Date control in MSTR for the date filtering, then please check the query generated by MSTR. Most likely it will use to_date function in the where clause.

This filter is not pushed down and may have a negative impact on the performance. You can check the generated query with the Visual plan and then modify the query removing to_date function. It might still work but the performance might be quite good.

Would request you to check this and confirm.

Regards,

Ravi

[former_member184768]

Hi Patrick,

In my opinion, the "Database agnostic" feature of SAP application layer would have resulted in creating the date fields as NVARCHAR. I think if the date is stored as "DATE" type then internally it might be stored as Integer (numeric value) which would be better from storage perspective.

We are also facing the similar issue, but decided not to tamper with SAP table structures. It has issues when it comes to using functions like date difference based on table date (with dates in NVARCHAR), but then we use the conversion functions (at the cost of performance).

Coming to HANA, M_TIME_DIMENSION anyway has SAPDATE (in varchar) and SQLDATE (in Date) format. So the mapping should be done there.

In cases where the data type has to be DATE, like for Currency Conversion, it is mapped to SQL date.

For reporting perspective in MSTR, so far, there was no issue with respect to NVARCHAR dates.

Regards,

Ravi

[patrickbachmann]

Hi Ravi, thanks for the response.  Just keeping the thread open a bit longer to see if anybody else responds then will close out soon.

-Patrick

[former_member184768]

Hi Patrick,

There is an additional complexity to the date behavior. If you use to_date() function in the WHERE clause to convert the value from NVARCHAR to DATE, then the filter is NOT PUSHED DOWN.

In MSTR reporting, you might wish to use the Calendar control to select the dates. The MSTR generated query formats the dates with to_date function. Please check the query generated by MSTR and check the execution time / plan. Modify the query by removing to_date() and check the execution time and plan.

You may observe that with to_date() the filters are not pushed down and the OLAP engine generates much higher number of records and passes to the Calc engine. The filtering happens in the Calc engine, but then it has negative performance.

Please let me know your observation.

Regards,

Ravi

[patrickbachmann]

Ravi, to answer your question about the missing prompt functionality, in actuality we are having problems with Microstrategy for all types of prompts.  It seems the are all ignored by Microstrategy when setup on the HANA side so we are setting them up directly in Microstrategy.  We are told this issue is fixed in Microstrategy 9.3.1 which we are one release behind.

-Patrick

[former_member184768]

Hi Patrick,

We checked it with 9.3.1 and it works fine. All the input parameters are mapped to the generated prompts.

Regards,

Ravi