Category Archives: Datawarehouse

Get list of dates using recursive Common Table Expression (CTE)

Many times we need a list of consecutive dates or time at runtime mainly for a recursive ETL process. This code provides a list almost instantaneously with only the start and end dates being the only input. The output is in Date format but is easily extended to a DateTime format.

For best results, you can use this in a CTE as part of the solution so that it does not take up space in the database.

The MaxRecursion option ensures that the recursion does not go on forever, however the Per Minute and Per Second versions will definitely overrun the MaxRecursion option.

Note: The code for each one are similar, the only difference is the first parameter of the DATEADD function which determines the recursive detail.

Daily
Monthly
Yearly
Hourly
Per Minute
Per Second
Continue reading

Top 10 Tips when synchronising your database and Mailchimp

Okay, sooner or later someone is going to need to synchronise all those people who registered on your company website with an email service provider to do a bit of marketing. In my case, the latest email provider to come my way is MailChimp. MailChimp is a very popular email service provider, which was used primarily by small businesses who could not afford the typical email giants like Experian and CheetahMail. However, the industry has changed and now they service many major companies.

To do so, you will need to use the MailChimp API. The latest as of the time of writing is v2.0 and can be found here: http://apidocs.mailchimp.com/api/2.0/

On to the tips!

1. Try not to update incrementally using dates
2. Do a full list compare between the MailChimp lists and your own database
3. Re-subscribing requires 2 actions
4. Use 1 or 0 for boolean parameters
5. Remember localisation
6. Remove left and right space paddings and tab characters for email address before uploading or comparing email
7. Do not forget to handle stray emails
8. Use Webhooks to unsubscribe people at real-time
9. Remember to close the loop by unsubscribing people from your database too
10. Keep records of invalid emails – and do something about them

Continue reading

Top 11 Qualities of a DataWarehouse Implementer

Top 11th Quality: Curious to explore different designs

There are many ways to achieve the same thing. Before actually getting your hands dirty, think and do some high level designs, then choose the best one. They need not be perfect, and the beauty of it is the more experience you have, the faster you come up with designs. The downside of experience is to stick to the same design all the time without asking if you can do it better.

Continue reading