topic Re: SAP connector for Qliksense in App Development

SAP connector for Qliksense

Thu, 16 Apr 2015 09:39:22 GMT

Do we have SAP connector for Qliksense ?

Re: SAP connector for Qliksense

Anonymous — Thu, 16 Apr 2015 13:02:57 GMT

Hello,

The latest version on SAP Connector is 5.9 which was released last week.

It does not mention a connectivity to Sense yet but I believe it is on the roadmap.

Serhan

Re: SAP connector for Qliksense

quriouss — Thu, 30 Jul 2015 14:07:55 GMT

I am using the built-in functionality in Sense Desktop to connect to SAP-BW by OLE DB. It's working well for my purposes (but obviously this won't connect directly to SAP tables, which I assume you are trying to do).

Re: SAP connector for Qliksense

quriouss — Thu, 30 Jul 2015 14:11:08 GMT

Also take a look at this thread What is the data integration capability of Qlik Sense ?. which will give you some tips on using QVSource win Qlik Sense.

Bear in mind (1) that is an old thread and likely the situation has improved, and (2) QVSource is a separate purchase/licence.

Re: SAP connector for Qliksense

chrishayes — Wed, 30 Sep 2015 18:22:54 GMT

I am trying to connect to SAP BW in Qlik Sense using OLE DB but am running into issues. Can you share how you set up your connection?

Re: SAP connector for Qliksense

quriouss — Thu, 01 Oct 2015 13:14:15 GMT

I'm happy to help, but hat sort of issues are you having?

My system is:

Qlik Sense server (also works with Desktop version)
SAP Business Explorer (BEx tools) installed on the server (or on the same machine as Desktop)
- This is the software which gives you SAP Query Designer, Analyzer, Web Application Designer, etc.
- This will also install the SAP BW OLE DB driver.

The connection string I use for the server ("prompt=4" avoids the logon dialog box opening) is;

OLEDB CONNECT32 TO [Provider=MDrmSap.2;Data Source="SAP_SYSTEM_ID";SFC_CLIENT=100;SFC_LANGUAGE=EN;Prompt=4]

I would try going to create "New Connection" within Qlik Sense, and selecting the OLE DB provider and seeing what options it gives. Another tip is to start with the desktop version on a local machine and create the connection - it helped me because I knew when it was trying to bring up a dialog box; if running remotely there is no way to know and it would just hang.

Hopefully the above outline will be enough to get you connected to your system. If you want help writing a query let me know (I'm no expert but I got it working!)

Re: SAP connector for Qliksense

quriouss — Thu, 01 Oct 2015 13:16:00 GMT

Also, maybe take a look at this thread where I ask (and answer!) a similar question to what I outlined.

Connecting to SAP BW using BEx OLE DB provider?

Re: SAP connector for Qliksense

chrishayes — Fri, 02 Oct 2015 13:15:15 GMT

Well it appears that I am able to connect but I cannot see any tables. I am using Qlik Sense Desktop and trying to connect to a SAP BW/BPC server. We are not using BEx. I tried adding "Prompt=4" to the connection string, but I still get the popup window.

Re: SAP connector for Qliksense

quriouss — Sun, 04 Oct 2015 21:11:09 GMT

Yeah, you won't be able to see any tables, you have to write the script by hand.

I had to do some trial-and-error to work out exactly how to get it working but one of the best tools I found was to use Microsoft Report Builder (free, part of SQL server) to connect to my BW server over XMLA and build the outline query there, then copy and paste into Qlik.

basically though you need to gather all the technical names for the characteristics and the key figures then decide how you want the data to be presented.

I'll post an example script for you tomorrow; once you've got the outline then the cleaning up of the data is fairly logical, but you *will* have to edit the script to convert the internal SAP format into a legible end-user format.

Re: SAP connector for Qliksense

Anonymous — Mon, 05 Oct 2015 06:48:39 GMT

Hello Christopher,

You need to use SAP Connector. Otherwise even if you connect, you may not be able to see the cube structure properly since cubes are handled by SAP on BW level.

- Use SAP Connector with QlikView Desktop to create QVDs.

- Use QVDs to generate Qlik Sense front end.

Serhan

Re: SAP connector for Qliksense

quriouss — Mon, 05 Oct 2015 09:35:21 GMT

Yeah, that's one route but have you seen the price??? I'm all for the low-cost workaround!

Consider the following BW query. Note the technical names for the Characteristics and the "Enterprise ID" for the key figures (this is a long alphanumeric, not the technical name of the KF in the cube).

The script to load this query into Qlik would be;

SELECT NON EMPTY { [Measures].[DKYHxxxxxxxxxxxxxx],

                                      [Measures].[DKYHyyyyyyyyyyyyyy]} ON COLUMNS,

NON EMPTY              { [0CALDAY].[LEVEL01].ALLMEMBERS *

                                      [ZACCOUNT].[LEVEL01].ALLMEMBERS }

                                    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME

                                      ON ROWS

FROM [INFO_PROVIDER_TECHNICAL_NAME/QUERY_NAME] ;

(The '*' between the characteristics is the same as CROSSJOIN in MDX, but I find it neater (and I try to avoid doing any joins anyway))

This will load the data into Qlik, so in Qlik you'll see columns labelled "DKYHxxxxxx" and "DKYzzzzzzz" and "0CALDAY" and "ZACCOUNT" which will probably not be useful for you. You would also look at the data returned from SAP for the correct format. For example our account numbers are either 10 or 11 digits long but SAP will export them as 16-digits padded with leading zeroes.

So you probably want to have a LOAD statement beforehand and the whole script will look like this;

Load

              "[Measures].[DKYHxxxxxxxxxxxxxx]" as "Commission Paid",

              "[Measures].[DKYHyyyyyyyyyyyyyy]" as "Tax Due",

              Year(Date#("[0CALDAY].[LEVEL01].[MEMBER_CAPTION]",'DD.MM.YYYY')) as "Year",

              Month(Date#("[0CALDAY].[LEVEL01].[MEMBER_CAPTION]",'DD.MM.YYYY')) as "Month",

              MID("[0ACCOUNT].[LEVEL01].[MEMBER_UNIQUE_NAME]" ,7,16) as "Account Number" ;

SELECT NON EMPTY

                        { [Measures].[DKYHxxxxxxxxxxxxxx],

                          [Measures].[DKYHyyyyyyyyyyyyyy]} ON COLUMNS,

              NON EMPTY

                        { [0CALDAY].[LEVEL01].ALLMEMBERS *

                          [ZACCOUNT].[LEVEL01].ALLMEMBERS }

              DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [INFO_PROVIDER_TECHNICAL_NAME/QUERY_NAME] ;

Also, just to add there is a limit of usually 1 million cells that can be returned from SAP. I have more data for this so I add 0YEAR as a free characteristic and wrap my load script in a loop;

Load

<blah,blah,blah>

For vYear = 2005 to 2015

<SELECT statements as above>

FROM [INFO_PROVIDER_TECHNICAL_NAME/QUERY_NAME]

WHERE {[0YEAR].[$(vYear)]};

next vYEAR

Anyway, I think that's enough for now - let me know how you get on. As I said earlier, I'm no expert but I learnt just enough to get my project working. This may not be the most elegant way of doing things but it works for me!

Re: SAP connector for Qliksense

chrishayes — Mon, 05 Oct 2015 12:28:18 GMT

Will the QlikView Connector work with Qlik Sense?

Re: SAP connector for Qliksense

quriouss — Mon, 05 Oct 2015 12:37:25 GMT

I'm not sure, but before you go down that route I replied to this thread separately but my reply is still waiting to be moderated (I assume because it has an embedded image & script).

Summary: I am managing quite well without the SAP connector, but read my reply when it appears and I'd try that first (unless your project has the budget for the SAP connector, in which case go right ahead!)

Re: SAP connector for Qliksense

chrishayes — Tue, 06 Oct 2015 18:56:35 GMT

Interesting idea to pull the data in QlikView, export to QVD, and then load into Qlik Sense. I don't know if that will work for us, but it may be an option to explore. Thanks.

Re: SAP connector for Qliksense

chrishayes — Tue, 06 Oct 2015 18:58:06 GMT

Thank you this is very helpful!

Re: SAP connector for Qliksense

Anonymous — Wed, 07 Oct 2015 08:47:06 GMT

Hello,

It definitely works. We implement real-life projects with this mixed approach.

SAP Connector for Qlik Sense will definitely arrive. This has been told in Qonnections event in USA. The time is not known though but I believe, since it is an urgent necessity, it should be soon.

Serhan

Re: SAP connector for Qliksense

Anonymous — Wed, 02 Mar 2016 23:22:26 GMT

Hi Serhan,

When we load the qvd into qliksense we don't need the connection string?

Re: SAP connector for Qliksense

Anonymous — Thu, 03 Mar 2016 10:52:29 GMT

Hi,

Yes you can create it in QlikView first and then move script into Sense.

Serhan

Re: SAP connector for Qliksense

quriouss — Thu, 03 Mar 2016 14:35:22 GMT

Once the data is saved as a QVD then there is no connection to SAP and you don't need the connection string any more.

You can reload the QVD just as a normal file.

Re: SAP connector for Qliksense

Anonymous — Thu, 03 Mar 2016 15:32:46 GMT

Hi,
When I try to load the .qvw file in qliksense, why do I get this error? This .qvw is getting the data from different qvds.