community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!

Constant refresh of data extract

Atom

Dear All,

 

I was trying to find a way to solve my current question, but so far haven`t found clear answer. 

The case is: I have some reports to be prepared based on the data from Oracle db, which means that the extract will take quite a long time. I was thinking to create a local DB files which can be further used in excel e.g. This way I can join, process and analyse the data much faster than querying the report everytime. 

So basically the idea is to kind of replicate the tables from Oracle to local machine. I would like to understand what is the best and fastest way to update the local DB file (e.g. I need updates every day). Is it possible to just update the file with missing NEW records, rather than running the whole extract (if we are speaking about YTD sales data)?  

 

Sorry if the topic was already open somewhere, please navigate me there. 

Thanks in advance, 

Aleks

Meteor

Aleks,

 

 

Great question.  The best way to do this is to utilize the Alteryx yxdb format.  The yxdb will allow you to read and write very quickly and can make your workflows run much faster.  You can do a one-time load to a local yxdb and thereafter use a method similar to what is shown below.  The example below has a query against an oracle database for the past 7 days and the records are going to be appended (unioned) to that same local yxdb at the end of the workflow.  If you are going to update this daily, just run your oracle query as sysdate - 1.  It's important to use a unique tool prior to the new records being loaded to the local yxdb to avoid duplicate records.  Let me know if you have additional questions.  

yxdb.PNG

 

 

 

Meteor

The above solution is better suited to your needs, but I do something very similar for data requring irregular updates by puting them into containers so I can switch off the slower process.

In this case below, the bottom container is very slow, and is usually turned off unless I have new raw data and need to update the yxdb.

The Union tool is set to prioritize the Built item over the Cached one (as I want the newest data the raw data is updated), but I only need to turn that container on once to update it.

 

Capture.PNG

Atom

Thanks a  lot for the suggestion. That could work on one of the reports where the data is adding up. But I have another extract which is changing - e.g. AR table where the open amount is changing with the time. any ideas how to index it and make oracle just to update the documents affected? 

 

Also, even YXDB is really a bit faster and much less in terms of the weight, it`s not interfering with Excel directly, and currently I want to connect it directly to Excel as the template include manual steps which can`t be done by alteryx (or at least not with my current knowledge level :) ) 

 

Thanks

Aleks

Labels