SAP Analytics Cloud source : Snowflake datawarehouse Live connection

[mfoeken]

Hi,

Has anybody come across Snowflake datawarehouse as a source for SAP Analytics Cloud (SAC) live connection?

If yes,

1. Was APOS used as a connector between Snowflake and SAC? If no, what connector/adapter was used?

2. Was hierarchies from snowflake consumed as expected in SAC data model? Were you able to use live connection data models to stories and straight away use the hierarchies from snowflake in stories?

3. the date hierarchy. Was that created in snowflake and how did it behave in SAC via live connection? Were you able to get the SAC date functionalities? range/member , Current year/previous year, month etc.

4. Is there a way to merge SAC data models outside a story and use that object as a source for a story?

Regards,

Cleo

[mfoeken]

Hi Cleo,

With one of my our customers we are exploring using APOS Live Data Gateway (LDG) to connect in a live manner from SAP Analytics Cloud to Snowflake. Regarding question 2 and 3: i can't comment yet. Regarding question 4: no, you can't merge. You can use blending/linking or simply create a data model to combine your data in for instance snowflake and consume this as a single model.

Kind regards,

Martijn van Foeken | Interdobs

[former_member757096]

Hi All,

I am working for a client which use the Snowflake and SAC with APOS in between.

Answering your questions one by one.

1. Was APOS used as a connector between Snowflake and SAC? If no, what connector/adapter was used?

A. Yes APOS is used as connector between SAC and Snowflake.

2. Was hierarchies from snowflake consumed as expected in SAC data model? Were you able to use live connection data models to stories and straight away use the hierarchies from snowflake in stories?

A. Hierarchies do not behave as is does in case of CDS views or Bex queries when working with Snowflake. When connected to Snowflake via APOS, the data is flattened and converted to 2 dimensional semantic layer where the existing hierarchies(if any) is lost or not interpreted. We are still struggling to get the hierarchies like GL account, etc as we have in CDS views to be created in Snowflake/APOS/SAC. The connection is live from SAC and Snowflake, but how Snowflake refreshes the data is upto them.

3. the date hierarchy. Was that created in snowflake and how did it behave in SAC via live connection? Were you able to get the SAC date functionalities? range/member , Current year/previous year, month etc.

A. The date hierarchy is one of the most important issue that we faced as in SAC the dates are considered as plain text with no range/member for creating restricted measures like YTD, MTD or applying a bar chart to show last 12 months data. But APOS had provided a great solution to that! They are currently supporting some of the highly used Databases, like MS SQL server, Db2, etc. APOS has a feature of converting the date field to date dimension using certain formulas respective to the database you connect. While providing support, APOS had created a file containing the transformation formulas, we imported that to LDG, and created a Time Dimension on that particular date field. It was awesome! now we have the date field and the additional month, year, and day field as time dimensions. We use those dimensions for calculating restricted measures or ranges in any widgets. Great job APOS!

4. Is there a way to merge SAC data models outside a story and use that object as a source for a story?

A. Possibly not, moreover SAC don't allow to blend one data model built on CDS to that of one built on snowflake or whatsoever. We had similar requirement, but this one is not yet achieved.

Hope my answers help.

Regards,

Niloy Sil

SAC Consultant.

[former_member6443]

Hi Sourav,

I suspect this can be done using the generic ODBC or Camel JDBC Driver in SDI.

Thanks

Afshin

[SouravC]

Hi All - Did anyone explored to move data to/from Snowflake to SAP SAC using SAP DWC ?

[former_member302630]

We are actually considering (not tested) doing all calculations in snowflake (so that we don't need the SAC date functionalities? range/member , Current year/previous year, month etc. ).

So we calculate in a semantic layer in snowflake YTD and MTD etc. So a view layer on top of tables in snowflake.

But if I create a story filter on fiscalyearperiod field in SAC , how does it communicate with snowflake via APOS?

The ask is kind of like what we used to do in BEx customer exit. but that could be invoked even when value was passed by SAP Webi to BEx.

[former_member6443]

Hi Martin,

We leveraged SDI and wrote our own adapter using the development kit and then we had the could get live connectivity to Snowflake . Not pretty but this way we got the best of both the worlds HANA and Snowflake.

APOS was not an option for us.

Cheers

Afshin

[former_member302630]

Hi Martjin,

We find 2 majors issues now when taking data via APOS to SAC (for us source is snowflake as mentioned and a live connection). Dates and general hierarchies like GL Account or Plant hierarchy.

In APOS we get any date as "DATE" data type (10 length). But in SAC , when we access the data model via story, we don't get the SAC date functionalities? range/member , Current year/previous year, month etc.

https://answers.sap.com/questions/13469063/apos-live-data-gateway-connection-data-source.html

Here Alan Golding, Solution manager APOS seems to suggest that the right version may solve the issue.

Regards,

Cleo

[former_member6443]

Hi Cleo,

Was APOS used as a connector between Snowflake and SAC? If no, what connector/adapter was used?

I used the Snowflake connection simply by downloading the Snowflake jar file and putting this entry in the driverconfig.properties file i.e. the Snowflake=MyDirectory\snowflake-jdbc-3.13.5.jar.

I was able to use Live & Import Model to do most of the work. The usual Live Connections restrictions did apply .

Regards

Afshin

[mfoeken]

Hi Afshin,

May I ask how you connect to Snowflake using a Live connection in SAP Analytics Cloud?

Kind regards,

Martijn van Foeken | Interdobs