December 29, 2016

(SW01) Actually?

As mentioned in my previous post, I believe "Steel Wheels can be improved".  Here is the first trick, and though this is extremely simple, it is nonetheless, a "must do" to ensure your demo is credible.

As you pull open Steel Wheels, you'll notice that the dates for the orders in the Steel Wheels order database range between 2003 and 2005.  (Look at the top row in the sample report "Leading Product Lines (pivot table)) or below nostalgic screenshot I grabbed from the net. (More nostalgia available here, here and here if you are in the mood).




While this shows that Pentaho has been around for some years and earned it's stripes as a company :-), from a demo perspective, it doesn't really give a polished view. At the speed Analytics products evolve, you really cannot afford to give a demo with data from over 10 years ago. Can you?



Clearly this can be adjusted. And to avoid that you need to think about update scripts to correct the dates, here is the logic I have used in the past to actualize the dates in Steel Wheels.

The following columns in the database contain date information (apart from whether they are actually date fields).
  • dim_time.time_id
  • dim_time.year_id
  • orderfact.time_id
  • orderfact.order_date
  • orderfact.required_date
  • orderfact.shipped_date
  • orderfact.payment_date
  • orders.order_date
  • orders.required_date
  • orders.shipped_date
  • orders.payment_date

You can easily update these by running the following some basic SQL functions. I've come up with some simple logic that uses basic date functions for hibernate. Using the max payment data in the Sample Database as a reference, and up it based on the difference with the current year, year(today).

--DIM_TIME TIME_ID
update dim_time
set time_id = to_char(
                      add_months(
                                 to_date(time_id,'YYYY-MM-DD'),
                                 (select (year(today) - year(max(paymentdate)))*12 from payments)
                                 ),
                            'YYYY-MM-DD');
update dim_time
set year_id = year_id + (select (year(today) - year(max(paymentdate))) from payments);

--ORDERFACT TIME_ID
UPDATE orderfact
SET time_id = to_char(
                      add_months(
                                 to_date(time_id,'YYYY-MM-DD'),
                                 (select (year(today) - year(max(paymentdate)))*12 from payments)
                                 ),
                            'YYYY-MM-DD');
--ORDERFACT ORDERDATE  (date)
update orderfact
set orderdate = add_months(
                           orderdate,
                           (select (year(today) - year(max(paymentdate)))*12 from payments)
                          );
--ORDERFACT REQUIREDDATE  (date)
update orderfact
set requireddate = add_months(
                              requireddate,
                              (select (year(today) - year(max(paymentdate)))*12 from payments)
                             );
--ORDERFACT SHIPPEDDATE  (date)
update orderfact
set shippeddate = add_months(
                             shippeddate,
                             (select (year(today) - year(max(paymentdate)))*12 from payments)
                            );
--ORDERS ORDERDATE
update orders
set orderdate = add_months(
                           orderdate,
                           (select (year(today) - year(max(paymentdate)))*12 from payments)
                          );
--ORDERS REQUIREDDATE  (date)
update orders
set requireddate = add_months(
                              requireddate,
                              (select (year(today) - year(max(paymentdate)))*12 from payments)
                             );
--ORDERS SHIPPEDDATE  (date)
update orders
set shippeddate = add_months(
                             shippeddate,
                             (select (year(today) - year(max(paymentdate)))*12 from payments)
                             );
--PAYMENTS PAYMENTDATE
update payments
set paymentdate = add_months(
                             paymentdate,
                             (select (year(today) - year(max(paymentdate)))*12 from payments)
                             );

If you have used Pentaho before, you'll know the various ways to apply the updates, but for those who are new to Pentaho and want the easy path, you can also simply execute the following PDI job.


Once executed the logic, refresh your Mondrian cache (execute the following URL),

http://localhost:8080/pentaho/api/system/refresh/mondrianSchemaCache
and you should see the data in your report actualized.



Please note that while your data is now actualized, some of the reports, still contain hard coded references to the old years which might need some manual intervention, namely the following (URLs only valid if your are running your EE trial on localhost:8080):
If anybody wants to modify those reports and submit a non hard-coded version, feel free to contact me :-). I'll gladly publish the new report versions here, or link to where ever you posted them. In the meantime you can download the above SQL update script and PDI transformation here.

Enjoy!