2018-12-31

Blue Prism robots and a new year

A year later not looking forward to my 66th birthday, still do not know what to do with my life, especially the professional part of it. Last year I decided to be an independent IT consultant and learning new skills primarily JavaScript and front end of web development. I became a consultant, that is a huge step when you are sixty-five and that I’m very happy for it. Unfortunately very little JavaScripting almost nil, on the other hand I did some C# and Python, at least Python came as a big surprise for me. A new client only do Python code so I had to learn, can’t say I’m a master but proficient enough. I also have taken som baby-steps or rather an old man slow stumbling into AWS, cloud is certainly one way into the future actually it’s already here and I expect some Azure too this year.

The big thing for me 2019 is Blue Prism, the so called robots are really the new hype in the corporate world, not by IT professional, but by ‘business people’ especially the finance guys seem to think robots are the next thing in IT. Some very clever marketing have disguised screen emulation as robots. The so called robots emulates a screen and replace keyboard and mouse with scripts. For me this is the last resort when you have no other means to connect IT systems and there is no APIs or other integration tools that work and the need for automation is great then ‘robots’ is a viable system integrator. Robot scripts are more complicated than interfacing between APIs and they are brittle. The screen is a very complex ‘interface’, actually the screen needs HI, AI is not really there yet, so complex scripting is needed to bridge the gap between HI and AI.
During the 1980ties I did a lot of screen scripting (IBM mainframe 3270 scripting), mainly for stress testing online manufacturing systems. I learned how hard screen scripting is, you need to take everything into account, error messages, pop ups, timeouts, not to mention screen resolution etc. The mainframe TTY screen environment was a lot simpler than present GUI screens.
    
3270 screen

The Blue Prism robots are much more advanced than the 3270 screen emulator I used in the past.

I really hope Blue Prism robots also are fun to work with. Here I come stepping into the future, I will have a good start 2019, I sincerely hope your 2019 will be a good year.

Happy new year to all of you😊


2018-12-24

Merry Christmas

to all of you from me. Now I ready for the Christmas Party tonight. I bring the cured or pickled herring and vodka
an Absolut must on a swedish Christmas Eve dining table.
A traditional pickled herring


This year I made three different kinds of herring, we will be some +15 grownups so there will not be much over
for tomorrow, which is a pity, the pickled herring is perfect on swedish crisp bread, the day after the Christmas party.
Tonight I will eat and drink too much that is the same procedure as every year.  Later this week I will sum up
my first year as a consultant, it was a different year indeed. But now I go home to my sister and meet up
with the rest of the family. This year is a special, we have a newly born Walter, the first in a new generation
of the family. I’m the grandma’s brother, I do not think there is an english word for that relation,
“old uncle” maybe.
I hope we all will have a nice holiday no matter how we celebrate it.

2018-12-11

PHPers help save the world

I happen to believe Global Warming is for real, and have done so for last twenty years. I also believe we all (in the the richer countries) can do small things to lower the emission of greenhouse gasses.  I also believe we PHPers all over the world can do a great thing for our world, migrate to PHP 7. Watch this video with Rasmus Lerdorf. If we all follow his advice we can make a substantial contribution for the Earth. And that without sacrificing anything. You will increase your programmer skills by migrating to a significantly better programming language, increase your users response times at the same time you help save the Earth, that is what I call a win-win situation. You do not even have to believe in global warming, it's still a good deal.

Spread the video to all you know, and start now migrate all your systems to PHP 7. You become a better programmer with happier users and as a side effect you help saving the Earth.  

2018-12-02

Next generation of business intelligence - 2.

Don't get me wrong, in the previous post on NG BI, I'm expressing myself a bit condescending about the big guys BI systems in particular SAP's business warehouse. That does not mean I think those systems are bad, some of these are actually quite good. My remarks are just a bantering expression of the fact these guys are still struggling with problems I addressed some 25 years ago. It would be preposterous of me to claim my BI system is better than the big guys systems, but I created a system the second version 2006 which in some aspects still is better than what the big guys have today. You should also note my Data Warehouse is only the back end, which is what a Data Warehouse should be, that is why it is called a 'data warehouse'. This was not so when I started design my Data Warehouse at the end of last century, back- and front-ends where often tightly coupled. At the time the data models from Kimbal et al. dealt successfully with shortcomings of the hardware mainly small and expensive memory. But the spintronic revolution gave us large and inexpensive memory and I broke free of the cube data models, made full use of inexpensive large memory multiprocessor computers as early as 2000. The big guys still seems to be locked into the data cubes though. SAP uses HANA to speed up processing which is good, but it's not only speed, the cubes models is restraining the developers by it's complexity. Actually I once read a paper of one of founders of SAP very close to my original data warehouse vision. He had a zillion € to spend on his vision, I had 2000€. I came up with my Data Warehouse, he came up with HANA. (I have search for his document issued somewhere around 2008, but I have not found it.) HANA is good, very good indeed, but it is not the whole answer, there is more to it than raw hardware speed.

One often overlooked aspect of Business Intelligence is organization. Most companies use an organization model suited for ERP systems, with traditional development, test, QA and production systems, with hierarchical demand, analyst and development teams. This is good or sometimes necessary for ERP systems, but the inertia of such organization is disastrous for BI activity. All and every company tell you they have a close to the user BI organization with agile development methods, but if you look behind the fancy words you often find an old ossified organization where users if not have died waiting for requested reports have forgot about them, when the reports finally arrive.

Poster boy

Finally I’m appreciated for my true virtues. Poster Boy for protecting intellectual property!

Actually I’m very protective of customers intellectual rights, i.e. intangible assets that allow our
company and our customers to maintain a competitive edge and which contribute to the ability
to effectively conduct business.

2018-11-30

Next generation Business Intelligence

In this commercial for SAP Business Warehouse generation one zillion42, they claim as they did in the previous one zillion41 generations, now for the first time data can be combined to make astonishing revelations never possible before. Give the guys a few more years and they claim the same thing for the onezillion43rd time. To be fair all the other big guys do the same thing. When will these superduper data or business warehouses, data lakes, landing zones be on par with my 2006 generation 2 Data Warehouse? I suspect it will take some time, all these data warehouses have one intrinsic problem, the data model, you know these extended snowflake Kimball superduper cubes, that makes it complex to develop apps, slow to retrieve and super slow to update the information. Have you ever followed the data from source systems to useful applications in the monolithic data warehouse systems of the big guys? If not do that and then ask yourself, can this not be done simpler, faster and more cost effective? Addressing this may create the real next generation of Business Intelligence.  But still the big guys are patching on data models conceived in the 1970ties.
   

2018-11-13

Mojibake blues

UTF-8 coding conversions is a never ending source of grievance and agony. Lately I been fighting with
UTF-8 in Python 2.7. I needed to export data from Oracle and MS SQL Server databases and write the
data to CSV files. Dead simple except these foreign non- english letters. Of course the normal CSV
writer did not work.
After quite some googling I found the UnicodeWriter class, that I could use as a replacement for the
normal CSV writer. UnicodeWriter promised to solve all my cute foreign letter troubles. It is the
writerrow method that is supposed to magically solve all encoding problems.
class UnicodeWriter:
   """
   A CSV writer which will write rows to CSV file "f",
   which is encoded in the given encoding.
   """


def writerow(self, row):
       self.writer.writerow([s.encode("utf-8") for s in row])
       # Fetch UTF-8 output from the queue ...
       data = self.queue.getvalue()
       data = data.decode("utf-8")
       # ... and reencode it into the target encoding
       data = self.encoder.encode(data)
       # write to the target stream
       self.stream.write(data)
       # empty queue
       self.queue.truncate(0)


Well it did not. All my foreign characters was as distorted as with the regular CSVwriter. I googled a
thousand different solutions, which promised to solve my problem, none did. I also found many requests
for help ‘This drives me bonkers, I have tried lots of suggested options none works.’ Knowing you are
not alone makes you feel better but it does not solve your problem. After hours of googling and testing
I come up with a solution of my own that worked for me. I had to replace the first line of code in the
writerrow method:
def writerow(self, row):
   self.writer.writerow([s.encode("utf-8",'ignore') if str(type(s)) == "<type 'unicode'>" else s  for s in row])
   data = self.queue.getvalue()
   data = data.decode("utf-8")
   data = self.encoder.encode(data)
   self.stream.write(data)
   self.queue.truncate(0)

The thing that really annoys me is you (or at least I) always have to do trial and error debugging to
solve UTF-8 encoding problems. You can read any number of UTF-8 descriptions and any number
of ‘how to solve UT-8 encodings’, in the end you have to resort to trial and error debugging. I know my
solution will not solve all Python 2.7 UTF-8 encoding problems. I’m happy if it solves one problem and
maybe can be inspiration for some to solve their UTF-8 problems.

2018-09-24

Checking the database take 2

Last week I tried to DBCHECK all tables in the Data Warehouse. I did the medium check,, but it took too
long. This time I did a QUICK check, and it was definitely quicker than MEDIUM, just one and a
half hour for 3583 tables. But I had some unexpected errors:
Exec CHECK TABLE PTDDW.PTD_OTIS_OPEN_DOCUMENTS_2018-01-05 QUICK;
   
Note SQLSTATE=42000, ERRORNO=1064    
Note You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '-01-05 QUICK' at line 1
There were some 20 tables with hyphens in their name, otherwise the quick check was a success,
no other errors. I have to add missing backticks.
<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<schedule>
  <job name='checkTables'>
     <forevery chunks='10' parallel='yes' rowdirectory='no'>
<sql>
    SELECT a.table_schema DB,a.table_name TB
    FROM information_schema.tables a
    WHERE  a.TABLE_TYPE = 'BASE TABLE';
</sql>
</forevery>
    <myiterator piggyback='forevery'/>

    <sql>
CHECK TABLE `@DB`.`@TB` QUICK;
    </sql>
  </job>
</schedule>
Now the remaining question is - Is the quick check meaningful for me. I have to read the manual.   

2018-09-20

Checking the database

I’m writing a series of posts about migrating out Data Warehouse Database from MySQL version 5.6 to 5.7
(ultimately 5.8). Yesterday I did a test to check the present database, I wrote a script checking all tables
(little less than 4000) in 10 parallel streams, it did not go well, after some 4 hours and still 2500 tables to
check I cancelled the check tables script. Some tables took more than 30 minutes to medium check.
Maybe I’m checking to many in parallel, anyway I decided not to go for a medium check but a quick check
I test this Sunday night.
<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<schedule>
  <job name='checkTables'>
     <forevery chunks='10' parallel='yes'>
<sql>SELECT a.table_schema DB,a.table_name TB
   FROM information_schema.tables a
   WHERE  a.TABLE_TYPE = 'BASE TABLE';
</sql>
     </forevery>
     <myiterator piggyback='forevery'/>
     <sql>
CHECK TABLE @DB.@TB MEDIUM;
     </sql>
  </job>
</schedule>


Why do I do this? Before the migrate it is good to know that all tables are ok, but I cannot wait some 10 to
15 hours for the check to finish.

2018-09-10

Upgrade MySql 5.5 temporals

This weekend I converted all temporals in the Data Warehouse from pre-version 5.6 to the version 5.6 internal
format. It affected about 600 tables, the conversion went hassle free ‘ALTER TABLE FORCE’
on every of those 600 did the trick. Happily I went to the office this morning. First thing that happened after
starting the PC  I got a chat message ‘We have a problem with an application all part names are missing
and there are no vendor info either. I almost died, what had I done this time? I had checked as carefully
I could for any errors during the temporals conversion and there was none. When i had calmed down a bit I
digged into the application and found the material master for a factory was empty. At the same time
the BI developer who contacted me called and told me the material master is empty and it is because we
archived the old material master, it is actually used when we update the material master, can we restore it.
Oh that's a relieve, this time I didn’t fuck up.

It turned out the old material master was from my own old ERP system, that was replaced by SAP 2006.
It came as a big surprise it was in use. We restored the old material master and rerun the material master
update job and we were back in business. But we will remove the old material master from all jobs and
then archive it again as part of the preparation for the Mysql 5.6 to 5.7 conversion.


The weekend temporal conversion was simple but it took quite a lot of reading, finding what the problem
was how to identify tables affected and then run this long running conversion. This SQL script found affected
tables and created ‘ALTER TABLE’ statements for each of the tables:
SET show_old_temporals = ON;
SELECT distinct concat('ALTER TABLE ',a.table_schema,'.',a.table_name,' FORCE;')
FROM information_schema.columns a
join information_schema.tables b on b.table_schema = a.table_schema and b.table_name = a.table_name
WHERE a.column_type LIKE '%time% /* 5.5 binary format */' and b.TABLE_TYPE = 'BASE TABLE'
;
Then it was just a matter of cut and paste these statements into an ITL job and run it:
<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<schedule mustcomplete='yes' logresult='yes' >
  <job name='alterTables' type='sql'>    
   <sql>
     ALTER TABLE FEVENTEST.PD013PTD_STOCK FORCE;                             
     ALTER TABLE FEVENTEST.PD014PTD_OPEN_ORDERS FORCE;                       
     ALTER TABLE FEVENTEST.PD015PTD_REC_ORDERS FORCE;                        
     ALTER TABLE FEVENTEST.PD016PTD_STOCK FORCE;                             
     ALTER TABLE FEVENTEST.PD017PTD_INV_ORDERS FORCE;                        
     ...
   </sql>
  </job>
</schedule>
By this conversion I hope the 5.7 upgrade conversion time is cut by a lot.