This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
Hi, any advice is welcome. We're scoping Alteryx, I'm not IT but am trying to clue myself up so that I can ask the right questions. If we use Alteryx to transform our data is it good / best practice to write the transformed data back to a local database first, we have a local Oracle database, or is it usual practice to just feed the transformed data to Tableau Server? The second option doesn't feel right as it is a shared resource. Am I right in thinking that we could load the Alteryx'd data to our local Oracle db then create a datasource on Tableau Server which then points to Oracle s rather than the data sitting on Tableau Server the metadata would provide the link back to Oracle? I can't find anything about examples of architecture. TIA
There's benefits to either approach so it really depends on your organization's preference. Getting cleaned data back into Oracle would benefit any analyst that needs to use it, with whatever tool (not just Tableau). At larger enterprises, end users working in a tool like Alteryx are not necessarily permitted to write to such source systems. Instead they need to work with the DB owners to get the data cleaned up in the first place. In such cases, we can only pump the data into Tableau..
I'll kick this off. Keep it simple. If there is an over-arching data governance process that requires certification of the data assets, writing to the Oracle tables might be desirable. That being said, you probably won't have access to the Oracle tables because of another rule someplace. You'll be in a sandbox and as such, there won't be as much structure or support. If you are flexible and make changes to the contents of the tables, you won't become anyone's friend quickly and your innovations will become limited.
My general strategy is to allow users to do what is friction-less. Once a process is stable, if governance comes in and wants to control or manage change, then let them tell you the rules of engagement. Until then, keep it simple and write your data where you desire. In writing to tableau, I'd keep a copy in yxdb format someplace.
If this helps, awesome. Otherwise it might help by giving others in the community a chance to give their opinions.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
If we use Alteryx to transform our data is it good / best practice to write the transformed data back to a local database first, we have a local Oracle database, or is it usual practice to just feed the transformed data to Tableau Server?
As others have said, there's a few factors to consider but in this scenario I've seen examples of both practices at different clients and they both work well for what they want to do. I would always prefer writing back to the database as Jason said. This means that everyone can benefit from the data changes and it's not just in a Tableau format. When all the cleaned data is in Tableau, it ends up being an extract tool for other purposes and Tableau shouldn't be used for that - especially when you have Alteryx as well 🙂
You might need to get more rights on the Oracle server to create tables &/or be able to change the data within your "tableau" tables.
Am I right in thinking that we could load the Alteryx'd data to our local Oracle db then create a datasource on Tableau Server which then points to Oracle s rather than the data sitting on Tableau Server the metadata would provide the link back to Oracle?
Definitely. You still have the choice of creating an extract on Tableau Server, but you could also do a live connection to Oracle .... or both depending on the situation 🙂 Extracts are typically used if the source database is too slow and that slows down Tableau. Live connections are typically used if the data is being refreshed very often (multiple times per day) and you need the data in Tableau ASAP.
Thanks Jason, our setup is possibly unusual in that it's easier for us to gain access to write back into Oracle than to start cleansing the data elsewhere. My concern is that as our Tableau Server setup is a shared resource we could very quickly become an unpopular department if we start uploading our data sets there, transactional data with millions of rows and unfortunately people want to drill down into the detail.
@MarqueeCrew thanks for your advice! The data governance part made me chortle..! I think access to the Oracle db will be fine but I like the idea of a sandbox approach. We'll be making some pretty big data sources but really only for our own consumption. I'm not clear really on how we'll update the Oracle tables whether we'll append or do a complete refresh each time and then the whole indexing issue springs to mind. I'm not a data warehouse manager and we don't have that skillset in the business. What is the yxdb format you've mentioned, is that a condensed copy? Thanks again.
Thanks @cmcclellan, I think writing back to a db first is my preference. In terms of governance and actually not abusing the share Table Server resource, I think we'll need to see if there are any performance differences but I think if I posed the question to directors involved with the project they'd always want to keep the data on site. Thanks again!