Hi All,
I'm preparing reports with the ability show daily status of properties based on the property start and end date. The downside is that the workflow takes 10 mins to finish and the huge size of the alteryx database file.
To increase the workflow runtime, I use the start date for the property to start from a particular date for example 01/07/2015. Please let me know if this is the right approach if i want to build a report to show active properties on a daily basis within a particular period, lets say from 01/07/2015 - 31/08/2016
I have attached the sample workflow
Thanks
Jag
Solved! Go to Solution.
Sounds like you've been watching YouTube: https://www.youtube.com/watch?v=hcAcZ5ZAZPg ( @TatianaS sent me the link to this video)
Suggestions:
IIF(IsNull([End date]),DateTimeLastOfMonth(),[End date])
MAX("2016-07-01", [Start Date])
[Daily] <= MIN([End Date],"2016-08-31")
From the runtime tab, you can enable performance profiling. The largest amount of time is seen in READING data and BROWSING/OUTPUT of data. The multi-row formula replacement changed the formula time from 8.91ms to .42ms. I think that you'll see an improvement in running with larger sets of data with that one change. The generate rows are unequal, because I only generate rows that are contained in the desired time-frame. If that's what you are ultimately trying to accomplish, it is better to NOT generate them in the first place than to filter the results. The GENERATE ROWS was reduced from 3.74ms to .83ms. If my results are not what you wanted, then that difference is moot.
Info: Browse (4): Profile Time: 23.98ms, 41.26% Info: Input Data (1): Profile Time: 21.47ms, 36.96% Info: Multi-Row Formula (3): Profile Time: 8.91ms, 15.33% Info: Generate Rows (2): Profile Time: 3.74ms, 6.44%
Info: Input Data (1): Profile Time: 35.55ms, 55.35% Info: Browse (4): Profile Time: 26.81ms, 41.75% Info: Generate Rows (2): Profile Time: 0.83ms, 1.30% Info: Formula (5): Profile Time: 0.42ms, 0.66% Info: Select (6): Profile Time: 0.32ms, 0.50% Info: Select (7): Profile Time: 0.29ms, 0.45%
Hopefully this will help you.
Cheers,
Mark
Hi Mark,
The video did make me laugh but its true.
1. My data is coming from an SQL database and data goes back to 2010 but since the report is required from 2015 onwards i put in the manual dates. Once the data is prepare i will be exporting the data to a tableau tde file\publish to tableau server. I noticed when the data is exported to tableau tde the file size is much smaller than alteryx database file. What would be the best approach to read data from sql database?
2. For the end date, i like it to automatically increament to the next month ( the active month the workflow is refreshed.) hence the use of the active month end function
3.I used multi-row as I wanted to show blanks for end date if the property is not handed back and have a seperate column for Daily Date which i then use to join with the calendar macro to bring in weeks, months, quarter etc.
4. Yeah use the select tool in the begining to remove unwanted columns
5. Post generation...did not understand this point. Drop which fields?
Regards,
Jag
It makes more sense now.
When you write to a large .tde file, Alteryx seems to go through a two-stage output process in the construction of the dataset. Much of your time may be in the construction of the data extract. You might want to time a workflow that writes to a .CSV and compare the time for the .TDE. I think that you'll see that a large amount of time is spent post-Alteryx processing.
Post generation, if there are any fields that are not needed, they should be dropped. If you are using the fields, then you won't need to drop them. But if you are using a 4 byte field to determine the rental status, you could be creating a boolean field which is 1 Bit (true/false) for Rented and droping the 4-Byte field (1 bit vs 32 bits).
Cheers,
Mark
Hi Mark,
This forumula helped limit the rows
MAX("2016-07-01", [Start Date])
Thanks
Jag