Extract for Analytic App Data Source

[Bonediggler]

Experts - 

We have the need to create a lot of pretty simple reports (with minor variations from report to report) out of a sql server db, that end up as row/column Excel files.  For example "give me all claims for provider abc in date range xyz".  To streamline this process I am contemplating scheduling a data extract via Alteryx and then creating an Analytic app to sit on top of it where users can go and put in their own parameters.

My question is, what is the best format for this extract?  A .csv file?  Are there any other options that might have increased performance?

Thanks in advance!

[Claje · Accepted solution]

Depending on the volume of your data, there are a few different options to consider.

The default answer is a .yxdb file - this is an alteryx specific database format that can be written to fairly quickly and which Alteryx can read very fast.  This is the format Alteryx stores data in for functionality like Browse Everywhere, so it is pretty fast.

If you are talking about hundreds of millions or billions of rows of data, you probably want a format that is better optimized for this.

It is more complex, and takes much longer to write to disk, but a Calgary file is a very specialized format which includes the ability to create indexes on your dataset.  This allows for highly performant queries against those indexes, so your read times are much faster with this option.

The other advantage of Calgary is that you can query a calgary file, sort of like SQL.  This means that you can limit the amount of data you need to read into Alteryx to exactly what the user is specifying, which can be significantly faster, as compared to reading from a file, where Alteryx needs to be able to read the data in so it can filter out what you don't want.

Overall, below a certain volume of data, a yxdb will be plenty fast and easy to use and maintain.  If data volume is much larger, Calgary is a great option.

Hope this helps!