Tuesday, July 28, 2015

Treading Water, Sharks Circling

This post is brought to you by my long time teammate and good friend Doug Mack. Besides his many other areas of expertise, Doug leads the business intelligence / analytics portion of our DB2 for i Center of Excellence.

____

It seems like sharks are everywhere in the news these days, with all the reported sightings and of course the Sharknado movies (an interesting concept by the way!). The data centric world is like that too... sometimes you get the feeling of being chased by "data sharks"!

It occurs to me that if you are your company’s “go to person” for data (think urgent RPG coder or Query/400 report writer), you’re probably doing all you can to keep swimming faster than the business analyst “sharks” requesting that slab of data! While it's completely understandable that the analysts urgently need this data to sense what's going on in the business, you’re just trying to keep your head above water.  


It doesn’t have to be this way. While it might take some disciplined project planning and work to get there, you can alleviate YOUR workload and create an environment that either allows end users to run their own reports whenever they want (this can be called “Self Service BI”) or you can automate the execution and publishing of reports and dashboards, including visualization on their handheld device of choice. Now you can sit back on the beach with a tropical umbrella drink while the sharks wander off.

Trying to tackle data analysis has been around since, well, Query for the System 36! But in our team’s experience, many folks are still sinking under the burden of older tools and processes that prevent the kind of buoyancy and agility required to meet business demands. Lacking a suitable budget for big bang projects doesn’t help either.

To this end, IBM introduced DB2 Web Query some time ago. While it’s true you can use it to modernize and re-engineer your Query/400 reports - that is really underselling Web Query’s capabilities. You can not only create that self-service environment and streamline report publishing; you can also address data transformations that exploit this tool at a whole new level. With the introduction of DB2 Web Query DataMigrator ETL Extension, clients can now automate the often difficult task of consolidating, transforming, and optimizing the data for business intelligence applications. This supports the creation of a data mart or data warehouse that completely isolates and secures the data.  In many of the client situations we encounter, the data anomalies that make analytics difficult can be simplified with a data mart architecture and ETL (Extract, Transformation, and Load) tool.

Did you know that since 2008 IBM has shipped at least a limited use license of DB2 Web Query to almost every IBM i customer. But in many cases, those licenses are still at Version 1 of the product, which was essentially replaced with Version 2 in 2012. And just recently, IBM introduced the latest version, 2.1.1. In other words, you very likely own licenses to an older version of Web Query, but you’re missing out on some tremendous enhancements, including wizard analytics, geographical map visualizations, personal dashboards, mobile device support, and the aforementioned DataMigrator ETL extension.

Want to get some shark repellent? Why not take a look at some of the features of DB2 Web Query V2.1.1, and learn how to create that optimized “self service” environment to keep the frenzied school at bay!

To learn more about V2.1.1, check out the end user and wizard analytics video demonstrations here.

To find out how to acquire, install, and set up the product, you can also view the set of getting started videos on that same wiki page! 

____


Thanks Doug! And let me add, if you are interested in making a difference with data sooner than later, consider setting up that sandbox I talked about.
 

Monday, June 15, 2015

Time for a New Challenge

My long time teammate, prolific writer and periodic DB2 blogger Kent Milligan has an exciting announcement to share...

____


After spending the first 25 years of my IT career working on the IBM i, iSeries, and AS/400, I’ve decided that it’s time for a new challenge and I have accepted a new position working for the IBM Watson Group. It was a difficult decision to make because the IBM i community - both inside and outside of IBM – has been a great family to be part of.

It’s been very rewarding to work with clients all over the world for the past 25 years and help them use DB2 and SQL technologies to solve real business problems. I’m thankful for these real world interactions that have increased my knowledge base beyond just DB2 by all of you opening my eyes to all aspects of the IT solutions that companies depend on.

Although I’m sad about leaving the IBM i world, I am excited about applying my data centric skills and knowledge to solving a different type of problem – improving the quality of care and health of patients fighting diseases. Specifically, I’ll be working on the Watson for Clinical Trial Matching solution which helps a doctor sift through the pool of available cancer trials and quickly identifies potential matches for a patient. One of my first projects will be helping add support for new diseases to the Clinical Trial Matching product.

As I start the challenge of trying to get up to speed on a new technology, cognitive computing, I ironically find myself following the same advice that my “old” DB2 for i Center of Excellence team has been recommending to IBM i clients.  Build knowledge by getting educated by experts, start with a small project to gain experience, and iterate. 

I’ll still be wandering the halls of the Rochester Lab, so I plan to regularly check up on all of you to make sure that you’re “doing more with data” on IBM i by leveraging the latest SQL and data-centric technologies. So, goodbye and keep up the good work!

____


I would like to formally thank Kent for his contributions to the success of our clients and our team. We are all better because of him!

Wishing you all the best with your next challenge my friend.


Friday, June 5, 2015

Yes, You need a Sandbox

I recently ran across a very insightful article By Bob Becker of the Kimball Group. If you have not heard of "Kimball", as in Ralph Kimball, he is arguably one of the best known founding fathers of data warehousing and the data model known as "star schema". Mr. Becker's article caught my attention because it asks and answers the question "Does your organization need an analytical sandbox?".

Take a moment to go read the article here.  Then come back and let's think more about it.

_____


After my first read through the article, my reaction was: "of course!", and then I thought about all of my IBM i clients around the world and wondered if this applies to them, and if so how would it apply. And maybe more importantly, can this idea be simplified to help move an IBM i shop forward towards providing more value sooner than later.

The first observation that caught my eye is this:

"...in today’s competitive world, organizations need to be more nimble. They want to quickly test new ideas, new hypotheses, new data sources, and new technologies."

This echoes what my team has been saying to executives and technical leaders for many years. You must establish an organization that uses modern tools and modern methods to develop applications that are flexible, extensible, scalable and timely. Employing best practices in the science and art of DB2 data centric design and development is the foundation.

The second observation that caught my attention is this:

"A key objective of the analytic sandbox is to test a variety of hypotheses about data and analytics. Thus, it shouldn’t be a huge surprise that most analytic sandbox projects result in “failure.” That is, the hypothesis doesn’t pan out as expected. This is one of the big advantages of the analytic sandbox. The data utilized in these 'failures' didn’t and won’t need to be run through the rigor expected of data contained in the data warehouse. In this case, failure is its own success; each failure is a step towards finding the right answer."

Folks, this is profound. It is what I refer to as "lose to win".  Think about it. Better yet, do it.

The third observation that resonated with me is this:

"Having the right skills in house is critical to the success of the analytic sandbox. The users of the analytic sandbox need to be able to engage with the data with far fewer rules of engagement than most business users. They are users capable of self-provisioning their required data whether it comes from the data warehouse or not. They are capable of building the analytics and models directly against this data without assistance."

One topic I continue to shout out from my soap box is this: you absolutely need a DB2 for i database engineer.  Better yet, you need a database engineering team. These are the men and women who will have the skills, knowledge and mission to use the sand box effectively and efficiently - with a positive ROI.

_____


During the iAdvocate session I hosted with Jon, Susan and Paul at the Spring 2015 RPG & DB2 Summit conference, we tossed around the idea of using a skunkworks project to learn, innovate and demonstrate value sooner than later. We also discussed the benefits of keeping such a project initially off the radar screen and NOT asking for permission. Once there is a eureka! moment, the team can go into marketing mode and share the results and/or show off the prototype - thus winning support and moving into the realm of official projects.

Granted, what I just said is not necessarily natural to most IT folks, and there is likely some new skills required - but hey, this is what we are here for.

One alarming trend I also shared with the Summit conference audience is that traditional IT organizations are increasingly being bypassed. The business units are embarking on data centric projects without assistance from IT. While this phenomenon is a topic for another time, let's just say that bypassing IT, and the wisdom embodied within, is not likely a good long term strategy. Never-the-less, it is happening. Why? In my opinion, it's because IT is not meeting the real or perceived requirements of the the business units. How about we change that...

_____


So, what's my simple idea?

Build out a small, modern and independent DB2 for i sandbox.

Figure out how to buy, beg, borrow or steal some newer IBM i infrastructure that is outside of the normal control and governance scheme. By the way, this is NOT a development box. I consider designing and developing code a "production" activity critical to the day to day business operations, and as such, the development system is part of production. We don't want to mess with that.

Obtain and install the latest version of DB2 for i (that would be what's inside IBM i 7.2 + TR2), stand up some modern development tooling (IBM Data Studio for example), and add in DB2 WebQuery plus the DataMigrator ETL extension.

Get educated on relational database fundamentals (i.e. data modeling, SQL DDL and DML, set-at-a-time, etc.) and become familiar with DB2 Web Query and basic ETL.

Now, here's the fun part... go buy one of the business users a cup of coffee. Find out what they are doing with the data they are extracting from the production system. You know, the big data set that is pulled down to their PC periodically via QUERY/400 and manipulated with Excel. Learn their process for analyzing and formatting the data. Then go back to your DB2 for i sandbox and experiment. Try to replicate what they are doing.

Fail. Try again. Fail. Try again. Eureka! it works.

Now, enable the information to appear on a tablet. Congratulations, your information is mobile.

Ok, time to get some informal but important feedback...

Go buy the business user another cup of coffee and show off the results of your experiment. If they are excited about your prototype, encourage them to spread the news. If they provide some meaningful suggestions for change, take it and iterate. This will help to strengthen the new relationship and build trust, not to mention giving you more opportunity to learn and practice.

The bottom line is this: be proactive, be a hero!

_____


If you want to discuss the "sandbox" idea further, or if you need help it pulling it off, please let me know.  We are here to help make you successful, and more valuable!



Tuesday, March 24, 2015

Dealing with Blank Checks in DB2

"I think IBM i developers believe they must help DB2..."

hmmm, unfortunately this is sad but true.

The science of database must be understood before the art of database can be practiced. This includes the relatively simple task of comparing a column's value to a literal.  How hard can it be to compare and test for blanks? You know, the spaces sitting in a empty not null character column.

Well, apparently it's more difficult on some platforms than others, and SQL coders jump through hoops and attempt back flips to "help" the database engine.

For more information and coaching on the topic, please see Kent Milligan's excellent article on the topic here.

If you want to get better at understanding the science of DB2 for i, and proficient at practicing the art of relational database with IBM i, then please reach out - we're here to help you become successful.

Friday, February 27, 2015

Introducing DB2 Web Query DataMigrator!

IBM has announced a new product offering in the DB2 Web Query family portfolio.

DB2 Web Query DataMigrator ETL Extension (i.e. DataMigrator) provides extract, transformation, and load (ETL) capabilities integrated with DB2 Web Query and DB2 for i.

Users looking to isolate their query workloads from production environments, or needing to automate the consolidation of data from a variety of data sources will be interested in this solution.

DataMigrator allows you to automate the process of extracting data from any DB2 Web Query supported source database, the process of transforming or cleansing the data and loading the data into an optimized DB2 for i reporting repository. Depending on the design and use of that repository, the industry refers to this as an operational data store, data mart, or data warehouse.

While leveraging the same graphical look and feel of DB2 Web Query, there are also integration points with DB2 Web Query. For instance, synchronize meta data defined either in DataMigrator or DB2 Web Query.

For more information, refer to the following documents:

IBM US Announcement

IBM Europe, Middle East, and Africa Announcement

Frequently Asked Question document

If you need assistance with architecture, design or implementation of your analytics environment running in IBM i, please reach out - we are here to make you successful!

Wednesday, January 28, 2015

Using the Blueprint

In the previous post, Dan Cruikshank illuminated the concept of creating a database "blueprint" through the process of data modeling.  Continuing my conversation with Dan, I asked him about using the blueprint, i.e. data model.  Specifically, using SQL to access the data (the answer is YES!) and whether or not it makes more sense to use embedded SQL in a traditional high level language like RPG, or to use separate, and modular Stored Procedures.

Dan's answer and insight follows...

__________


The answer to the question as to use SQL Stored Procedures (or External Stored Procedures) versus embedded SQL in a monolithic program depends on the environment in which the application exists. What that means is if you are developing both host centric as well as external (i.e. web or mobile) applications which need to perform common IO functions, such as consuming result sets, inserting, updating and deleting rows from and to a common set of tables, etc., then using stored procedures would be my recommendation. If all development is strictly host centric (should not be in this day and age) then embedded SQL would be ok, but not best practice, in my opinion.

From an application methodology perspective, we tend to recommend a Model/View/Controller (MVC) approach for design and development, where the data access and user interface are separated from the high level language code. In the case of data access this would be done via stored procedure calls.

This is not a performance based recommendation; it is more about re-usability, reduced development costs and shorter time to market. Done properly, better performance is a byproduct. Not to mention higher value returned to the business!

In addition, with the added SQL DML support for result set consumption in IBM i 7.1, it is now easier for the host centric applications (i.e. RPG and COBOL) to share a common set of procedures which return result sets. From an SQL DML perspective, prior to 7.1 this was limited to insert, update and delete procedures although accessing a result set from a stored procedure was available via CLI calls (not widely used in traditional IBM i shops).

__________


If you need additional knowledge, skills or guidance with what Dan is sharing, please do not hesitate to reach out.

Friday, January 16, 2015

The Blueprint

This post is the result of a recent conversation about the importance of proper data modeling with my long time friend and teammate Dan Cruikshank.

For the readers who are not familiar with Dan, he is the person who invented the concept of database modernization and the methodology to pull it off in a responsible way; one of the many contributions Dan has provided to his appreciative clients around the world. We euphemistically refer to the IBM i database modernization process as "the Dan Plan".

Dan, the blog is yours...

______


About 15 years ago my wife and I jumped on an opportunity to buy a used home that was in foreclosure. The price was right, the location was fantastic; unfortunately the house was in rough shape.

There was no flooring in the main rooms; several of the walls had holes, who knew what the infrastructure was like.  We were looking at years of reconstruction and possibly 10’s of thousands of dollars in cost.

As we were going through the closets we discovered a set of the original blueprints. Suddenly years of work was now looking like weeks, and at a cost of 1’s of thousands of dollars.

About this same time my career at IBM took on a new slant. I was beginning to see that many of the performance issues I was then dealing with all seemed to be rooted around the same cause – a poor database design. IBM Rochester was launching the new SQE engine, which boasted brand new SQL capabilities that took advantage of the IBM i integrated relational data base "DB2 for i". Unfortunately many of the IBM i heritage customers were still using traditional record level access, let alone having a database that was properly designed for SQL set based access.

“Oh woe is me”, cried those customers who were now faced with a reconstruction nightmare – how to bring their applications and data into the new millennium without taking years of effort or spending millions of dollars on “modernization”. 

“If only we had been more diligent on documenting our applications”, lamented the growing number of CIOs who were now tasked with groveling for more budget dollars. “If only we had a blueprint!” they cried.

Never fear, there is a silver lining in this story. Hidden away, in a secret closet within the Rational suite of development tools, is something called the Data Perspective. The Data Perspective comes with Rational Business Developer, InfoSphere Data Architect and other bundled products; and it is included in the free (yes free) download of IBM Data Studio.

Within the Data Perspective is the Data Design Project tool. Using a Data Design Project, a database engineer can reverse engineer an existing set of tables (or DDS files) into a graphical physical data model, in other words a blueprint! This can be done for an entire schema (library) or for only those core files required to support the business.

But wait, there’s more.

Unlike the pencil drawings of yore, or the collection of shapes in a presentation tool, with a touch of a button the database engineer can generate the SQL Data Definition Language (DDL) statements from the physical data model. And, let me catch my breath, the DDL will be generated no matter if the originating source was DDS or SQL DDL. That is too cool.

And I almost left out the best part – the compare tool.

Imagine if I could have taken those original blueprints of the house, changed them and then pushed a button and my home would magically be transformed to match the blueprint. Not possible with home re-engineering projects but it is available with the Data Perspective. I can compare the graphical model or blueprint to the physical implementation and the tool will generate the appropriate ALTER, DROP and/or CREATE DDL statements, in either direction. I can apply emergency changes to the DB2 for i database and then sync up the model.

Of course having a blueprint is one thing, getting the re-engineering process right is another.

All projects are going to require some boots on the ground. In other words, the developers who have to make the changes. These "engineers" will require a little more detail, especially when ripping apart existing programs to expose the inner workings.

Oh joy, there is another secret closet in the Rational Developer tool box – The Visual Application Diagram maker. This device can take RPG or COBOL code and present it in graphical form. And what’s more, the engineer can click anywhere in the diagram to display the underlying code.
 
Whether you’re a database engineer or application developer, it is now time for you to take your skills to the next level. If you are not using the Rational tools then acquire them now. If you are using them, then don’t be afraid to explore some of those secret closets.

And if you're afraid of the dark, please reach out, we can provide some hand holding.