Creating demos is not always as straightforward as it seems. Sometimes it’s even more difficult than a real-life solution as you have to think of a business case and find the appropriate data. From the technical perspective, you want a dataset that is as complete as possible to handle different data types.
Besides sales figures, we also want to display geographical data, hierarchical views and time series. It’s hard to find datasets that accommodate that.
Fortunately, we don’t have to figure it all out by ourselves. Microsoft has done a remarkable job with their AdventureWorks database. We’re using this database for some time now for demo purposes.
AdventureWorks is a fictitious company that sells bicycles and cycling accessories. Microsoft provides both a transactional database and a Data Warehouse for this company. At Just-BI we are mainly using the DWH. To facilitate access from the BusinessObjects fronten tools, we created a universe on top of it.
One drawback of AdventureWorks is that the data is not up to date. Looking around, most scripts that tried to tackle this issue simply didn’t update all the tables containing date fields. Also, other scripts I found didn’t update the dates dynamically. You had to choose the number of days manually.
That’s why I decided to write my own stored procedure to update the database regularly. The resulting script updates all the time-based fields in the database based on the difference between the last order date and the current date. Numeric value fields that represent dates are also taken care of. So, both 2015-04-13 and 20150413 are updated by the script.
To enable modification of the data, all primary and foreign key constraints are removed. After updating the dates, the primary and foerign key constraints are re-created.
You can schedule the stored procedure to run on a regular basis. The stored procedure returns a summary of columns that were changed. For each column, it shows the first date (ending with Min) and the last date (ending with Max). The values prior to running the stored procedure are shown as DateBefore and ValuesBefore and the values after running the stored procedure are represented by DateAfter and ValueAfter.
Feel free to use it yourself. You can download it from Github: //github.com/just-bi/adventureworksupdater.
But please create a backup first as I don’t assume any responsibility for weird things happening to your database!
Any suggestions for improvements? Feel free to leave a comment behind.
Good luck and happy demoing!
