Disclaimer

davidmobbs · ‎12-12-2018

Disclaimer

The connector presented here is a proof of concept. While we are hoping it can add value to your SAP BI deployment, it has not undergone full certification in the SAP BI Platform. As such, it is not supported by SAP.

Snowflake

Snowflake is a cloud datawarehouse. It promises simplicity of deployment (as always in the cloud) and best-of-breed performance.

In the past few years it has been gaining traction, and we have recently received requests from joint customers who would like to use the SAP BI Platform to report off data from Snowflake.

Configuring the SAP BI Platform to connect to Snowflake

Following these requests, the Snowflake and SAP teams have collaborated to produce a working POC (proof-of-concept) of an SAP BI connector to Snowflake. Our efforts concentrated on ODBC, because:

in the WebIntelligence stack, relying on a native connector is the most effective option as it avoids switching back and forth between Java and native code

The Snowflake team favored the ODBC driver for the customer request at hand

Our POC was done on the Windows platform. Snowflake also provides an ODBC driver for Linux, but attempting to consume it would require extra configuration steps that are out of the scope of this blog post.

Configuration steps

The connector needs to be configured on both SAP BI Platform client and server machines, so the steps below need to be performed twice, once on the client machine and once on the server machine.

Go to the dataAccess\connectionServer folder in your SAP BI Platform setup.

In the setup subfolder, create a file:

named snowflake_odbc.setup on the server machine

named snowflake_odbc.32.setup on the client machine

Put the following contents into the file using your favorite text editor (contents is identical for both files) :

<?xml version="1.0" encoding="UTF-8"?><Product Name="Snowflake Access Pack (ODBC)" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../config/core/setup.xsd">

    <Version Type="Component">3.4.0.0</Version>

    <Version Type="Build">14.3.0.2963</Version>

    <Driver>

        <NetworkLayer Name="ODBC"></NetworkLayer>

        <Directory>odbc</Directory>

        <DataFileName>snowflake</DataFileName>

    </Driver>

</Product>

Go back to the dataAccess\connectionServer folder, and into the odbc subfolder. Create a snowflake.sbo file, with the following contents:

<?xml version="1.0" encoding="UTF-8"?><DriverConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../sbo.xsd">

    <Defaults>

        <Parameter Name="Family">Snowflake</Parameter>

        <Parameter Name="SQL External File">sqlsrv</Parameter>

        <Parameter Name="SQL Parameter File">snowflake</Parameter>

        <Parameter Name="Description File">odbc</Parameter>

        <Parameter Name="Strategies File">sqlsrv</Parameter>

        <Parameter Name="Driver Capabilities">Query</Parameter>

        <Parameter Name="Extensions">snowflake,odbc</Parameter>

        <Parameter Name="Array Fetch Available">True</Parameter>

        <Parameter Name="Array Bind Available">True</Parameter>

        <Parameter Name="Empty String">EmptyString</Parameter>

        <Parameter Name="CharSet Table">odbc</Parameter>

        <Parameter Name="Query TimeOut Available">True</Parameter>

        <Parameter Name="Quote Identifiers">False</Parameter>

        <Parameter Name="Optimize Execute">False</Parameter>

        <Parameter Name="Use DataDirect OEM Driver" Platform="Unix">No</Parameter>

		<Parameter Name="Force Execute">Procedures</Parameter>

    </Defaults>

    <DataBases>

		<DataBase Active="Yes" Name="Snowflake">

            <Libraries>

                <Library>dbd_wodbc3</Library>

                <Library>dbd_odbc3</Library>

		<!--  To use UnixODBC 2.3.0 for 'MS SQL SERVER ODBC datasource' comment out previous 2 line &amp; uncomment next 4 lines and set value of &quot;Use DataDirect OEM Driver&quot; as No -->

                <!-- Library Platform=&quot;Unix&quot;&gt;dbd_uxodbc23&lt;/Library -->

                <!-- Library Platform=&quot;Unix&quot;&gt;dbd_wuxodbc23&lt;/Library -->

				<!-- Library Platform=&quot;Unix64&quot;&gt;dbd_wux32odbc23&lt;/Library -->

                <!-- Library Platform=&quot;Unix64&quot;&gt;dbd_ux32odbc23&lt;/Library -->

            </Libraries>

            <Parameter Name="Extensions">snowflake,odbc</Parameter>

            <Parameter Name="CharSet Table" Platform="Unix">datadirect</Parameter>

            <Parameter Name="Driver Name">SnowflakeDSIDriver</Parameter>

            <Parameter Name="SSO Available" Platform="MSWindows">False</Parameter>

	        <Parameter Name="Driver Capabilities">Query,Cancel</Parameter>

        </DataBase>

    </DataBases>

</DriverConfiguration>

Also create a snowflake.prm file with the following contents:

<?xml version="1.0" encoding="UTF-8"?><DBParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../dbparameters.xsd">

	<Configuration>

        <Parameter Name="DB_TYPE">GENERIC</Parameter>

        <Parameter Name="ORDER_BY_REQUIRES_SELECT">YES</Parameter>

        <Parameter Name="GROUP_BY_SUPPORTS_COLUMN_INDEX">NO</Parameter>

        <Parameter Name="JOIN">YES</Parameter>

        <Parameter Name="INNER_JOIN">INNER JOIN</Parameter>

		<Parameter Name="EXT_JOIN">NO</Parameter>

		<Parameter Name="UNION">UNION</Parameter>

		<Parameter Name="UNION_IN_SUBQUERY">NO</Parameter>

		<Parameter Name="INTERSECT"></Parameter>

		<Parameter Name="INTERSECT_IN_SUBQUERY">NO</Parameter>

		<Parameter Name="MINUS"></Parameter>

		<Parameter Name="MINUS_IN_SUBQUERY">NO</Parameter>

		<Parameter Name="DISTINCT">NO</Parameter>

        <Parameter Name="CONSTANT_SAMPLING_SUPPORTED">NO</Parameter>

        <Parameter Name="ANALYTIC_CLAUSE"></Parameter>

        <Parameter Name="PERCENT_RANK_SUPPORTED">NO</Parameter>

        <Parameter Name="RANK_SUPPORTED">NO</Parameter>

        <Parameter Name="ANALYTIC_FUNCTIONS"></Parameter>

        <Parameter Name="SEED_SAMPLING_SUPPORTED">NO</Parameter>

        <Parameter Name="FULL_EXT_JOIN">NO</Parameter>

        <Parameter Name="LEFT_EXT_JOIN">NO</Parameter>

        <Parameter Name="LEFT_OUTER"></Parameter>

        <Parameter Name="RIGHT_EXT_JOIN">NO</Parameter>

        <Parameter Name="RIGHT_OUTER"></Parameter>

        <Parameter Name="GROUP_BY_SUPPORTS_COMPLEX">YES</Parameter>

        <Parameter Name="GROUP_BY_SUPPORTS_CONSTANT">YES</Parameter>

        <Parameter Name="GROUP_BY">YES</Parameter>

        <Parameter Name="HAVING">YES</Parameter>

        <Parameter Name="ORDER_BY">YES</Parameter>

        <Parameter Name="ORDER_BY_SUPPORTS_COLUMN_INDEX">YES</Parameter>

        <Parameter Name="INTERSECT_ALL">NO</Parameter>

        <Parameter Name="MINUS_ALL">NO</Parameter>

        <Parameter Name="UNION_ALL">YES</Parameter>

        <Parameter Name="LIKE_SUPPORTS_ESCAPE_CLAUSE">NO</Parameter>

        <Parameter Name="SELECT_SUPPORTS_NULL">NO</Parameter>

        <Parameter Name="SUBQUERY_IN_FROM">YES</Parameter>

        <Parameter Name="SUBQUERY_IN_IN">YES</Parameter>

        <Parameter Name="SUBQUERY_IN_WHERE">YES</Parameter>

        <Parameter Name="CALCULATION_FUNCTION">YES</Parameter>

	</Configuration>

	<DateOperations>

		<DateOperation Name="YEAR">{fn year($D)}</DateOperation>

		<DateOperation Name="MONTH">{fn month($D)}</DateOperation>

	</DateOperations>

	<Operators>

		<Operator Arity="1" ID="ADD" Type="Numeric">+</Operator>

		<Operator Arity="1" ID="SUBSTRACT" Type="Numeric">-</Operator>

		<Operator Arity="1" ID="MULTIPLY" Type="Numeric">*</Operator>

		<Operator Arity="1" ID="DIVIDE" Type="Numeric">/</Operator>

		<Operator Arity="0" ID="NOT_NULL" Type="Logical">IS NOT NULL</Operator>

		<Operator Arity="0" ID="NULL" Type="Logical">IS NULL</Operator>

		<Operator Arity="1" ID="SUP" Type="Logical">&gt;=</Operator>

		<Operator Arity="1" ID="INF" Type="Logical">&lt;=</Operator>

		<Operator Arity="1" ID="EQUAL" Type="Logical">=</Operator>

		<Operator Arity="1" ID="DIFF" Type="Logical">&lt;&gt;</Operator>

		<Operator Arity="1" ID="STRICT_SUP" Type="Logical">&gt;</Operator>

		<Operator Arity="1" ID="STRICT_INF" Type="Logical">&lt;</Operator>

		<Operator Arity="1" ID="IN_LIST" Type="Logical">IN</Operator>

		<Operator Arity="1" ID="NOT_IN_LIST" Type="Logical">NOT IN</Operator>

		<Operator Arity="1" ID="MATCH" Type="Logical">LIKE</Operator>

		<Operator Arity="1" ID="NOT_MATCH" Type="Logical">NOT LIKE</Operator>

		<Operator Arity="2" ID="BETWEEN" Type="Logical">BETWEEN  AND</Operator>

		<Operator Arity="2" ID="NOT_BETWEEN" Type="Logical">NOT BETWEEN  AND</Operator>

	</Operators>

	<Functions>

		<Function Distinct="False" Group="True" ID="Minimum" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>min($1)</SQL>

		</Function>

		<Function Distinct="False" Group="True" ID="Maximum" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>max($1)</SQL>

		</Function>

		<Function Distinct="False" Group="True" ID="Average" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>avg($1)</SQL>

		</Function>

		<Function Distinct="False" Group="True" ID="Sum" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>sum($1)</SQL>

		</Function>

		<Function Distinct="False" Group="True" ID="Count" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="All"></Argument>

			</Arguments>

			<SQL>count($1)</SQL>

		</Function>

		<Function Group="False" ID="ASCII_code" InMacro="False" Type="String">

			<Arguments>

				<Argument Type="Char"></Argument>

			</Arguments>

			<SQL>{fn ascii($1)}</SQL>

		</Function>

		<Function Group="False" ID="Character" InMacro="False" Type="String">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn char($1)}</SQL>

		</Function>

		<Function Group="False" ID="Concat" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn concat($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Left" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn left($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="LeftRemove" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn ltrim($1)}</SQL>

		</Function>

		<Function Group="False" ID="Length" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn length($1)}</SQL>

		</Function>

		<Function Group="False" ID="Locate" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn locate($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Lowercase" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn lcase($1)}</SQL>

		</Function>

		<Function Group="False" ID="Repeat" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn repeat($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Rightpart" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn right($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Rtrim" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn rtrim($1)}</SQL>

		</Function>

		<Function Group="False" ID="Substring" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

				<Argument Type="Numeric"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn substring($1,$2,$3)}</SQL>

		</Function>

		<Function Group="False" ID="Uppercase" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>{fn ucase($1)}</SQL>

		</Function>

		<Function Group="False" ID="Absolute" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn abs($1)}</SQL>

		</Function>

		<Function Group="False" ID="Arc_cosine" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn acos($1)}</SQL>

		</Function>

		<Function Group="False" ID="Arc_sine" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn asin($1)}</SQL>

		</Function>

		<Function Group="False" ID="Arc_tangent" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn atan($1)}</SQL>

		</Function>

		<Function Group="False" ID="Angle_Tangent_2" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn atan2($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Cosine" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn cos($1)}</SQL>

		</Function>

		<Function Group="False" ID="Ceil" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn ceiling($1)}</SQL>

		</Function>

		<Function Group="False" ID="Exp" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn exp($1)}</SQL>

		</Function>

		<Function Group="False" ID="Floor" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn floor($1)}</SQL>

		</Function>

		<Function Group="False" ID="Log" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn log($1)}</SQL>

		</Function>

		<Function Group="False" ID="Mod" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn mod($1,$2)}</SQL>

		</Function>

		<Function Group="False" ID="Pi" InMacro="False" Type="Numeric">

			<SQL>{fn pi()}</SQL>

		</Function>

		<Function Group="False" ID="Random" InMacro="False" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn rand($1)}</SQL>

		</Function>

		<Function Group="False" ID="Sign" InMacro="False" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn sign($1)}</SQL>

		</Function>

		<Function Group="False" ID="Sine" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn sin($1)}</SQL>

		</Function>

		<Function Group="False" ID="Sqrt" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn sqrt($1)}</SQL>

		</Function>

		<Function Group="False" ID="Tangent" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="Numeric"></Argument>

			</Arguments>

			<SQL>{fn tan($1)}</SQL>

		</Function>

		<Function Group="False" ID="Character_prompt" InMacro="True" Type="String">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>@prompt($1,'A',,,)</SQL>

		</Function>

		<Function Group="False" ID="Numeric_prompt" InMacro="True" Type="Numeric">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>@prompt($1,'N',,,)</SQL>

		</Function>

		<Function Group="False" ID="Date_prompt" InMacro="True" Type="DateTime">

			<Arguments>

				<Argument Type="String"></Argument>

			</Arguments>

			<SQL>@prompt($1,'D',,,)</SQL>

		</Function>

	</Functions>

</DBParameters>

Finally, create another snowflake.prm into the extensions\qt subfolder, with the following contents:

<?xml version="1.0" encoding="UTF-8"?><DBParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../dbparameters.xsd">

	<Configuration>

		<Parameter Name="GROUPING_SETS">No</Parameter>

		<Parameter Name="AUTOMATIC_CAST">No</Parameter>

		<Parameter Name="ACCEPT_COLUMNS_IN_VIEW_DECLARATION">No</Parameter>

		<Parameter Name="WITH_CLAUSE_SUPPORTED">Yes</Parameter>

		<Parameter Name="Fct_IfThenElse">CASE WHEN( #1 ) THEN #2 ELSE #3 END</Parameter>    

		<Parameter Name="OWNER">Y</Parameter>

		<Parameter Name="QUALIFIER">Y</Parameter>

		<Parameter Name="REFRESH_COLUMNS_TYPE">T</Parameter>

		<Parameter Name="CHECK_OWNER_STATE">Y</Parameter>

		<Parameter Name="CHECK_QUALIFIER_STATE">Y</Parameter>

		<Parameter Name="KEY_INFO_SUPPORTED">N</Parameter>

		<Parameter Name="OUTERJOINS_GENERATION">NO</Parameter>

		<Parameter Name="EVAL_WITHOUT_PARENTHESIS">N</Parameter>

		<Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd'}</Parameter>

        <Parameter Language="ja" Name="USER_INPUT_DATE_FORMAT">{!d 'yyyy-mm-dd'}</Parameter>

		<Parameter Name="USER_INPUT_NUMERIC_SEPARATOR">.</Parameter>

		<Parameter Name="CONCAT">||</Parameter>

		<Parameter Name="COMMA">|| ' ' ||</Parameter>

        <Parameter Name="TUPLE_SEPARATOR">_</Parameter>

	</Configuration>

</DBParameters>

Restart your client tools and SAP BI Platform server, the connector should now be available.

Final words

This is only a POC, so it is a quick a dirty copy with minimal edits of an existing connector (SQL Server). It works, though, and I do hope it will work for your use case.

However, the quick and dirty copy means that SQL function names in Information Design Tool may not match exactly what Snowflake supports, and that there may be glitches in the SQL generation.

So... Feel free to share your feedback using the comments !

Do also feel free to tweak the configuration files. They are 100% independent of the other connectors, so you can experiment with improvements to this connector without jeopardizing your other connections. If you do identify improvements to these files, please share them in the comments.

Connecting SAP BI Platform to Snowflake

Disclaimer

Snowflake

Configuring the SAP BI Platform to connect to Snowflake

Configuration steps

Final words

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win