topic Re: Sense ETL Methodology in App Development

Sense ETL Methodology

tseebach — Tue, 10 Mar 2015 13:39:05 GMT

So in QlikView we used to have a QVW that creates library of QVDs in a folder. My question is what are we planning to do in Qlik Sense? What will become our new best practices?

Should we create a QVF called Extract, and then put this as the first job in our Task list?
Should we create a separate Stream for these files. And who should access these?
I would also create a separate data connection to allow Self Service users to access these files.
Is there a preferred location and structure to store QVDs?
- I assume we would prefer to place them on a separate drive or SAN
- I would suggest a structure that is somewhat similar to the QlikView Deployment Framework
- But is there a Best Practice in place?
Is there a point in using a Content Library for QVDs? Because it can be synced across clusters, it could be convenient, but I'm unsure how it can handle large frequently updated files.

My answers currently are (And please challenge me and write your method):

Create a QVF called Extract and Transform, to match a 2 tier ETL process. Make these run as the first jobs in the task list.
Place these files in 2 separate streams, with admin rights
Create multiple data connections, to reflect roles that need access these files
I'd store QVD's on a separate drive or SAN. Like
- D:\Qlik\Qlik Sense\Default\Data\Staging < First Tier
- D:\Qlik\Qlik Sense\Default\Data\Model < Second Tier
  - Default refers to a role ie. a Department or Function
No (I might be wrong).

Re: Sense ETL Methodology

tseebach — Wed, 30 Sep 2015 13:10:08 GMT

No one?

I hope to speak with Magnus Berg soon and if he has some value input I post it here.

Re: Sense ETL Methodology

Michael_Tarallo — Tue, 13 Oct 2015 12:57:25 GMT

Hello Torben,

I know it has been a number of months since you posted this, I actually have just been made aware of this post form a colleague here at Qlik. Have you made any progress on the items, best practices mentioned in this thread? Have you sought information elsewhere in regards to your questions? I am curious. If not, I will attempt to gather this information for you, or point this in the direction of some of our consultants to see what they think.

Regards,

Mike T

Qlik

Re: Sense ETL Methodology

Tue, 26 Jan 2016 14:50:36 GMT

This thread really ought to have some answers - all brilliant questions !

I have som thoughts and ideas myself, but would really like to hear from more experienced consultants and/or Qlik.. This is so central, i can't see how it can be ignored for so long

Re: Sense ETL Methodology

Anonymous — Tue, 26 Jan 2016 16:25:09 GMT

Hi Michael,

Did you manage to gather information on this topic? I really need to know how we can have separate ETL, QVD and Presentation layer in Qlik Sense.

Thanks

Waqas

Re: Sense ETL Methodology

Anonymous — Tue, 26 Jan 2016 18:31:11 GMT

‌exactly! Waiting for Michael to reply!

Re: Sense ETL Methodology

philip_doyne — Tue, 26 Jan 2016 22:10:22 GMT

Hello: I am sure we are not supposed to sell on this forum - I have been a member for a long time and a Qlik Partner for 10 years so I should really know (may I beg your forgiveness please moderator! - this is not a habit nor does it form any part of our marketing strategy). I could not resist responding to this especially in view of the dearth of responses. At QlickiT we developed the QlickiT Methodology in 2009 and we use it to ensure that all our consultants work in the same way so it is easy to pick up on other peoples work. The methodology covers: 3 tier architecture; standardised folder structure; standardised format for table/QVD and field names; concatenate or link table model; simple integration of data from multiple data sources to common dimensions and mapping to common dimensions; integration of section access and data reduction; simple consolidation of data from different sources (eg different ERP's in different divisions). We teach this to our clients and training courses open to the public. We have designed these courses to teach the methodology and Qlik at the same time so you leave with an idea of where to start your life with Qlik. The data/ETL bit is, of course common to Qlik and Sense because the script is the same. At the moment we are still running the courses based on Qlik because we have yet to finish converting the material to sense but we have implemented in in Qlik Sense with genuinely no changes (other than the data connectors but you can still go to "legacy mode". You will see I could not publish all the material on this forum but there are more details on our website where you can get details of how to contact us. This will definitely solve all your ETL problems - and modest though we are we do know what we are doing! As it happens - we do have courses running in Rotherham, Yorkshire UK very soon ie next week and we unexpectedly have a couple of places availble if your need is urgent - (Mon 1st to Wed 3rd Feb 2016). Do get in touch www.qlickit.co.uk. A brochure can be obtained here:

http://qlickit.us7.list-manage.com/track/click?u=c561aed217697d04d8ade5df0&id=b2f3be378c&e=249013a5ce

Thanks Philip

Re: Sense ETL Methodology

pablolabbe — Wed, 16 Mar 2016 00:34:35 GMT

Hi Michael,

Do you have any update on this subject ?

Re: Sense ETL Methodology

Michael_Tarallo — Wed, 16 Mar 2016 14:18:54 GMT

Hi Pablo et al, I have attempted to get some more insight for you on this. At the moment, I would assume it is done the same way with Qlik Sense as it was with QlikView. Creating separate .qvf files to create the QVD layers. Also I know that mbg‌ has made some improvements to the Qlik Deployment Framework‌ to include Qlik Sense. Let's see what Magnus has to say as well.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Re: Sense ETL Methodology

Magnus_Berg — Wed, 16 Mar 2016 15:13:40 GMT

Hi Torben, and sorry for late reply. Did not see this until Michael informed me today. As Michael responded you could do the same in Qlik Sense as in QlikView. In QDF you use containers as store for the qvd files (in your case Default is a container). A container is a security boundary containing content, this could be QVD, csv, xls, variables, scripts and more that belongs to each other. In Qlik Sense container is attached using a data connection (LIB), these are secured using QMC. Containers include several folders, each folder has a correlating global variable, that is used in the load script to point out where to put the files. This is a nice way to create generic scripts that can be moved between environments (and from QlikView) without breaking code.

Hope that this helps

Regards

Magnus

To your questions:

should we create a QVF called Extract, and then put this as the first job in our Task list?
Better to have a name standard for extract QVF (could be Data source_Extract)
Should we create a separate Stream for these files. And who should access these?
Create a stream with the container name of the QVF's that relate to this container
I would also create a separate data connection to allow Self Service users to access these files.
Depending on the security you give the connection string, for me I think qvd extracts is better for self service.
Is there a preferred location and structure to store QVDs?
As mentioned above use QDF
Is there a point in using a Content Library for QVDs? Because it can be synced across clusters, it could be convenient, but I'm unsure how it can handle large frequently updated files.
No, content library is not a good place to put QVD files

Re: Sense ETL Methodology

Anonymous — Wed, 16 Mar 2016 15:51:41 GMT

Hi Magnus,

Many thanks for your reply. It's very informative indeed.

I just want some clarification for containers. Could you please explain containers in detail? And if you have any examples for containers, I would really appreciate if I could have a look at it.

Many thanks

Re: Sense ETL Methodology

Magnus_Berg — Thu, 17 Mar 2016 07:21:05 GMT

Hi, please join the Qlik Deployment Framework community group, here we have content and videos regarding QDF and containers. Qlik Deployment Framework

Best regards

Magnus

Re: Sense ETL Methodology

Anonymous — Sun, 20 Mar 2016 22:32:59 GMT

The organization I work for uses a separate stream for ETL QVFs oddly enough called ETL. We have a QVF for extract which builds QVDs and a QVF for transform which uses the extract QVDs to build the QVDs we use in our production QVFs.

Re: Sense ETL Methodology

DataWrangler — Thu, 29 Mar 2018 14:09:06 GMT

Hi Torben,

I recently posted two items of potential relevance to your question; I hope you find them useful:

Cheers,

Jeff R. Robbins

Re: Sense ETL Methodology

sandeepj13 — Wed, 04 Apr 2018 14:10:47 GMT

Thanks Magnus.. It was very helpful.

Re: Sense ETL Methodology

vkish16161 — Wed, 04 Apr 2018 14:22:18 GMT

Michael,

You guys do a great job at Qlik. I have a question in this regard as well.

The documentation suggests that Synthetic Keys are bad and a Star Schema should be achieved all the time. At least, that's what the wording alludes to. The problem is that most of my users (I work for a Large INSURANCE company) don't have any Data Modeling skills.

They've been building great reports using QlikSense but unfortunately their apps are getting rejected by I.T. because of synthetic keys.

Their major gripe is that if a tool is advertised as a Self Service solution, it's should be more accommodating to minor "transgressions" in Data Modeling.

There is an aggressive debate going on between Business and IT. Is there any way Qlik can point to an official documentation that says synthetic keys are O.K but not circular references?

This'll be a major drag factor IMO for Qlik in the near future.

Please let me know if you can help.

P.S - Some of the sample apps in QS Desktop have synthetic keys.

mto‌ rwunderlich‌