8 Replies Latest reply: Sep 22, 2015 1:21 PM by Alex Byrd RSS

    Writing back to a data source

    Chris Cotter

      Hi,

       

      I've just started using QlikSense and we are planning on using the Enterprise version in future. As part of what we're trying to do, we need to be able to write data back to a data source, i.e saving a sample subset of data to be used at a later date by QlikSense or some other backend system. While I understand QlikSense is a data visualization tool and what we're trying to do goes very much against what QlikSense is intended for, it is necessary for us to be able to do this in some way.

       

      Ideally, we'd like to avoid having a separate UI for any of the extra functionality we need. So I'm wondering if there is some way to do this through QlikSense? I haven't seen any extensions that do anything like this and I feel if we were to create one, it would most likely be a hack and it doesn't seem like a good approach.

       

      So I'm wondering if there is any recommended way to solve this problem? Reading the documentation, the only thing I could see that *might* be possible (and correct me if I'm wrong) would be to build a Web UI around QlikSense, using the proxy service API to authenticate when the user logs into our UI and have QlikSense as another "page" in our UI that the user can use for data visualization and BI and the extra functionality would be on separate pages of their own. We'd want to have the full creation of sheets, stories etc available in this UI, not just adding charts or objects that had been created in QlikSense outside of this UI.

       

      Am I on the right track here? Is this possible or is there a better way? Any input or recommendations on this would be greatly appreciated.

       

      Regards,

      Chris

        • Re: Writing back to a data source
          Michael Tarallo

          Hi Chris, let me put a few sets of Qlik eyes on this for you. However, you might also get some answers from our helpful and great community members too.

           

          We have an extensive API set, so I would imagine this could be done.

           

          jpe or jog or bmz - can you provide some insight on this for Chris?

           

          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: Writing back to a data source
              Jonathan Poole

              I agree with Stefan below, a web service is helpful and  manages things for more traffic and users.  One method to evaluate this approach is to trial a Qlik partner solution. One that i've worked with before that has an installation program , documented help video, is BWISE. BWISE affords you the ability to write record specific information back to a source.  This may involve commentary about individual sales transactions in the dashboard, what if measure values etc...  

               

              The architecture involves:

               

              - a UI extension object to capture the user inputted information  (a basic object is built via the installation wizard)

                   (the basic extension is often further customized for specific uses)

              - a web service to writeback the user inputted information to a SQL repository (last i checked they did oracle or sql server)

              - a multi-table data schema in the SQL repository that becomes populated with the user inputted data

               

              Once that is setup, you  have the ability to reload the writeback information and associate with your core data source and display both pieces in tabular or graphical format using all the usual qlik objects.

               

              You could do frequent reloads on a schedule or use a direct discovery read of the write-back repository to reduce the latency. 

               

              Writeback Extension Installer

            • Re: Writing back to a data source
              Stefan Walther

              Hi,

               

              by principle you can use the same approach I was using some while ago in QlikView:

               

              http://www.qlikblog.at/1798/posting-data-from-qlikview-to-other-systems-using-the-ajax-client-and-extensions/

               

              Main difference is that it's much nicer and cleaner to develop this in Qlik Sense. I was a while ago starting to developing a prototype in Qlik Sense but haven't published, yet. But I am happy to take this as an occasion to continue my prototype and maybe some people can put their efforts together to create something re-usable.

               

              Regards

              Stefan

                • Re: Writing back to a data source
                  Chris Cotter

                  Hi Stefan,

                   

                  Thanks for this, this looks very much like what we'd like to do. We're still gathering all the requirements at the moment but I think what you outlined in your blog would cover a lot of it. I think we'll need the "man in the middle" approach as there will undoubtedly be some validation, etc required before we save anything.

                   

                  I'm still bringing myself up to speed on developing for QlikSense and it'll be a few weeks before we have the full requirements, what's the best way to continue with this once we're ready? Is there a repo for your prototype or something along those lines?

                   

                  Regards,

                  Chris

                • Re: Writing back to a data source
                  Sinan Ozdemir

                  Regarding writing back to a data source, I did it once with a stored procedure. And here is a way more simplified excerpt of what I did:

                   

                  For the sake of simplicity, I created a table with only one dimension:

                  Capture.PNG

                  Right now, the table is empty:

                  Capture.PNG

                  In QlikSense, I ran the below script to insert data into Demo table:

                  Capture.PNG

                  As a result of running the above script, the value in variable, vCompany is inserted into the table:

                  Capture.PNG

                  For larger datasets and files, you can use loops to go through each dimension and write them back to the tables in databases. I know you are looking for an API, but this is just to show you the labor intensive way of doing it.

                   

                  Thanks

                    • Re: Writing back to a data source
                      Stefan Walther

                      Just as further clarification regarding my earlier post:
                      I made very good experience in using a completely different approach by having a "man in the middle", so a web-service which can be used to receive data and write it to a data-source. The handling to send/receive data between QlikView/Qlik Sense is then taken care of by and extension.

                      We have implemented this approach at several larger customers ... but sure, you need to know that only in a few cases it worked out of the box, some customization (different database, security, etc.) was nearly always necessary.

                       

                      Regards

                      Stefan

                      • Re: Writing back to a data source
                        Chris Cotter

                        Hi Sinan,

                         

                        Thanks for your response, we might need something a little more complex as mentioned in my response to Stefan's approach but it's great to see that this kind of stuff can be done so simply!