The Downsizing of Business Intelligence By The Uprising of Handy BI Tools

BI used to be exclusive in the computing power of mainframe and mid-range computers

Times have changed. Business Intelligence (BI) are no longer exclusive to the expertise of hard-core IT professionals – Data Analyst, Report Designer or BI Specialist. Today the Accounting people, the Finance guys or an IT Admin staff with exposure to generation reports can design and develop BI-oriented reports. Thanks to the new generation of BI tools today, that BI is ‘bought down to the masses’ and no longer restricted to the IT elite with high degree of qualification. Not long ago before the rise of the millennials, the processing of data to produce BI reports are limited to the CPUs of mainframes and AS400s.

What are the catalysts of change? The vastly improved computing power of PCs and the ‘user-ability’ (user-friendly) of BI tools.

BI in PC

What are these software that can run BI in your own PC?

First, there is MS-Excel. Even as de facto spreadsheet with millions of users, Excel added BI flavor in its engine to extend its powers way beyond the bounds of spreadsheet computing Excel leaps into BI class with three essentials to complete the new arsenal – Power Pivot, Data Modeling and Power Query. Data Model is the data-modeling tool while Power Pivot is the resulting slice and dice computation derived from table relationship defined in Data Model, and Power Query is the data transformation and integration service.

Equally competitive with other BI tools in the market today, WEKA is a freeware!

And there is WEKA in our midst. WEKA, Waikato Environment for Knowledge Analysis, a data-mining tool that implements machine-learning algorithms. WEKA, developed by University of Waikato in New Zealand, is popularly used by universities New Zealand and Australia. Here is piece of software no different from sophisticatedly expensive BI tools in the market today and yet it is just floating in the NET awaiting your download!  WEKA is distributed under GNU General Public License, simple means a freeware!

PowerOLAP’s integration with Excel makes it the most learnable BI tool among Excel users

Another gatecrasher to break into BI world in microcomputer platform is PowerOLAP. PowerOLAP’s interface with Excel stands out among all the other PC-based BI solution. Most BI software today are adorned intricately with dashing dashboard and fancy colorful graphics. However, at the end of the day decisions are made through the lens of an Excel. Bluntly said, Senior Management readily sees the benefits of an investment in the sheet of figures in an Excel-like environment.

Microsoft’s new entry to BI market made the most pivotal impact. Bill Gates’ disciples (faithful Microsoft users) and the PC world are roused with surprise when Microsoft introduced its own BI tool. Anchoring on extend and embrace philosophy, Microsoft’s new software will reign supreme by targeting the widest niche – the PC segment of the software market, when it launched Power BI, a BI tool for the PC platform. However, the object of surprise is not the handy BI solution. Considering that Microsoft has the history for monopolizing the market and unreasonably pricy software licenses, the chill surprise is that Microsoft is giving away Power BI free!

Thanks to the emergence of power-bred BI products – PowerOLAP, Excel’s Power BI, Power Query, Power Pivot and Data Modelling, and WEKA sparked the uprising for downsizing that BI computing finally lands on your laptop

Posted in Uncategorized | Leave a comment

The Power of Two

The Power of Two

MS-Excel and Notepad Come In Handy in MS-SQL Server  Database Migration 

Move over  Pentaho, Talend.

ExcelIconOne interesting projects I got involved is the MS-SQL Server database migration to the new primary database. St. John Ambulance-South Australia (SA) management has decided to merge data with St. John Ambulance-Queensland (QLD) to upgrade its information system and catch up with ‘On-Line’, Net-ready trend. In lieu of arduous and complex ‘back to drawing board’ approach to redesign the database,  St. John Ambulance (SA) chose a leap-frog approach by adopting to the schema and database design of QLD database. Obviously, QLD is a net-ready database easily adaptable to on-line systems and web server that is functionally efficient to be viewed, modified and added over the Net.

I did not have any sophisticated ETL software but I know an old NotepadIcontrick can do the job. Enter MS-SQL Server and Notepad. I installed MS-SQL-Server on local (my laptop) with QLD database. It was a skeletal database containing only tables that are be involved with data transformation. Here comes Notepad. Notepad is my standard tool in handling data in text file format. It’s a medium for layover text file when data are dumped from the source MS-SQL database (SA) After  data are transformed for the target database (QLD), Notepad once again is the place-holder for data in text format prior to upload to MS-SQL Server.

ExcelPIC

Here comes MS-Excel, This trusty, versatile tool is the staging area where data are manipulated to align with the schema of target database. The contents of a table of source database (SA) lay across the columns of the worksheet with in a way that each field of the table corresponds to a column in the worksheet. The rows of each column (data fields) in the worksheet are the respective records, or contents, of the fields.

A column in the spreadsheet is inserted after each column that contains field contents of source database. The new columns contained macro definitions to convert the field contents of the source table (SA) into the schema of the target database (QLD). The new set of data yielded from the macro functions is converted to a text file. And finally in a breeze , the resulting text file in a Notepad is uploaded to MS-SQL Server in a  breeze.

Leonardo da Vinci once said ‘Simplicity is the ultimate sophistication.’ Thus who needs the complexity of MS-SQL Integration Services, or Talend when the simplicity of the power of two – Notepad and Excel can do the job!

Posted in Tech Talk and Toys | Leave a comment

The Renaissance of Change – The New Market Economy, Megatrend Technologies and Business Trends

What’s in a change? Position yourself as a professional working for an organisation, connected in any form of technology, personal and professional, and living in an economic condition induced by global market factors.

What initiates change? Is the smart phone you are using is a consequence of business imperatives? Does the current state of the world economy produce new business rules or new business trends? Or is the megatrend technologies (smart phones, tablets,  social networks,  etc.) we are absorbed in are derivatives of business trends? We have the three horsemen of change – The Global Economy, the Emerging Technologies and new Business Trends.

They are the pillars of change. Each horseman paves way to new beginning from personal level to a global scale. Change that affects on the way we live – our professional life, our daily lifestyle, our tech-style or in our fashion-style. Or change that reverberates worldwide affecting business practices, megatrend technologies and the global market.

But who sparks change – is it Mr. New Business Trends or Mr. Megatrend Technology? Or it may be Madam Global Economy? The fact is each one influences the other, and they change each other. But who changes who? Where does it begin?

 Today’s Global Economy – the New Normal Market

First let us begin with today’s economic landscape. The Oxford Economics calls it the ‘New Normal Economy’. The giant economies, lead by the US that dominated world markets in the last three decades are now afflicted unending woes with debt and banking turmoil. We are seeing the worst performance of the green bucks, now on the verge of collapse.. In contrast, the ‘stable economies’, China, India and Brazil among them, in the past are now the new pace-setters of market dominance in the global stage. This is the New Normal Economy, the global market growth is shifting to these emerging markets.

 Does technology drive business change?

In shift to the New Normal Economy, did the emerging technology have the hand in the change? Were the new business trends catalysts in the shift? The answer is we first ask the right question in the right order. We’d rather ask, does the accelerated use of new technologies an impetus to new business practices? Yes! The on-line scenery, the On-line way of doing business’ in the business world cannot be denied.. We submit our registrations, whether from making a booking in the cinema or purchasing a plane ticket, over the Net. Technology creates a seismic change in business practices. Major newspapers are closing shops, otherwise shifting to on-line paper. The age-old paperwork business practice is replaced by paperless technology approach.

 Do business imperatives spark new technologies?

Not so long ago, sales team made their presentation using catalogues or pamphlets stored in the attaché case. They wanted however an electronic and portable materials that they could carry around with them so presentation could be carried out randomly and instantaneously. In those days, that only time an elegant presentation could be made possible is a planned sales meet-up with prospective clients in a function room equipped with a huge overhead projector.

Fast forward to the present a laptop would be a bit of overkill, relatively bulky, heavy and big. A laptop is a computer with processing power for computing. A laptop is not exactly what they need. What they need is a much simpler device that produces good graphics and that is light and handy. No extra computing power needed. Thus, they need a tool is conducive and adaptive to making spontaneous sales pitch (and surely less complex than a laptop). Enter the tablet. Almost a panacea, the device any sales person would dream to arm themselves with in their sales battles.

Would the device be devised by the great Steve Jobs if business did not demand for its existence? The tablet is just one of those devices. Hence, business imperatives drive new technologies (rather than technology change).

 Events drive business change

If there was no Internet, the supreme catalyst for change, would the way of doing business be as revolutionary as today?

During the 1990s, before Internet was wrecking havoc on standard business practices, I was appalled as I was fascinated when global business was undergoing radical change. ‘Globalization’ and ‘Business Process Reengineering’ were the stupendous buzzwords. The world economy was transforming into a one global basket for goods and services. Businesses must be able to compete globally to survive. ‘Globalization’ positioned ‘Reengineering’ on the pedestal. Reengineering was about change itself. It called on reinventing current business processes and practices to be able to effectively compete in the new global market economy.

This business phenomenon captivated me and I was challenged by my own curiosity what caused the global business transformation (in the absence of new breakthrough technologies). I was immersed by the question that Reengineering and Globalization became my Thesis (incidentally I was taking my MBA at that time). Through my research this is what I eventually wrote in my Thesis:

The end of the Cold War weakened political tension among nations and reduced the possibility of armed conflict. Potential military confrontation has paved way to economic cooperation. With the easing of political tensions the belief that a nation’s security depends on its military capability no longer stands.  The paradigm has changed. Consequently,  the future of a nation rests on her economic prosperity rather than military superiority. The state of world affairs was transformed from a tense political-security situation into a progressive economic cooperation. Trade barriers are falling and trade laws were liberalized thus foreign investors are free to compete nationally with any country. The fragmented economy of isolated markets that used to be became one vast global market. 

 My standpoint,  a 2-cent worth

 The change in business practices could derive from state of global economy and not necessarily due to the intervention of technology. Does technology drive world economic order to change? No. Political events, market forces and economic cooperation among nations can change the landscape of world economy..

Thus, when you experience a change in your job say, a restructuring of business processes in your organisation, then technology could be the culprit.  When new technologies emerge to potentially change or exploit the way of doing business, then business itself might have demanded its creation. When the climate of doing business has changed that business rules have to be changed or reengineered if it is to remain competitive and survive the new market, then the shift of global economic conditions is the catalyst rather than just technology.

 In summation when there is change, in personal or professional level, involving technology, business or the economy think of the three  horsemen of change. One of them has sparked the change.

Posted in The Global Economy - the New Normal Market | Leave a comment

Noisy Children in the Church

Child Crying I hate This SermonWhenever I am in the Church early for the Mass, I always catch myself unconsciously looking around for any child nearby where I am seated. Always! Later, when I am conscious enough to peruse this strange behavior, I soon realized why.

Have you ever been interrupted with an annoying child, toddler or infant in your prayerful contemplation during the Mass, or in any solemn religious service? I mean when the child is lengthy crying, shrieking or noisily prancing and playing around in the church. If I get distracted (well I am sure I am not the only one), my impulse is to stare at the child and hope my gesture would Child Ssshhhhcatch the attention of the parents. Or if I can spot the parents glance at them. Often the parents would do something to pacify the child. But what annoys me further is that the mother would do something very minimal to stop the crying or the yelling. Obviously, it was just a cosmetic effort rather than being earnest to restrain the child

It’s exasperating when the child continues with his distractive ways because most of the time the parents are not doing enough to contain the child. Much to the chagrin from obviously annoyed Mass-goers, a few gazes and stares to the mother more often do not help at all. One Saturday Mass however, inside the SM Mega Mall Chapel, the continued yelling and screaming of the child finally caught the attention of the priest at the precise moment during Trans-substantiation (the highlight of the Mass).

Child CryingThe priest openly (and I would say boldly) reminded the flock that parents must be responsible for their children. If necessary, the priest continued, the child outside of the Chapel. It was prudently said and timely because he said it just before the Sign of Peace part of the Mass. I felt like cheering inside and I caught myself praying for the priest and wished we had more priests like him!

My take? I understand that for a child the world is his playground. Children and Parents in the ChurchRegardless of time and place he would always find time or create an opportunity to play as instincts would nudge him to. Inside the Church, where a multitude of weary souls are longing for a moment of peace and silence even for just an hour, his playground has to cease to exist. The distraction must be stopped. But the parents would argue that you just cannot control a child’s nature thus, we cannot do something about it. That is the crux of the matter. The problem here is not the child’s innocence but  the intervention of adults’ exploitative minds (of the parents). They exploit the child’s innocence and use it to justify their inaction.

That’s why God created mothers- to shelter her child from all harm…as well as to prevent her child from taking away God’s precious moment with prayerful souls reaching out to Him. If a mother cannot, or more aptly, won’t prevent her child from continuously distracting the Holy Mass then she has limited herself as a worthy mother to her own child

But I wonder what goes in the mind of a mother when she does almost nothing for her misbehaving or screaming child during the Mass Does the mother insensibly blinded with the premise that  ‘he is just a child’ therefore adults must bear?

Enlighten me, mothers. Share your thoughts

Posted in Flight's Plays, Plight and Pleas | Leave a comment

Financial Solutions: The Manila Battleground

Target Manila

For the players this has been the battle cry.

Software Vendor - Software MOduleThe vibrancy of competition for financial solutions market in the Philippines shows no signs of abating. With the country’s sound economic fundamentals and robust growth, investors flock in, cash keeps flowing in and investment confidence rises and investment awareness among the masses grows. Big and small investors would try various investment schemes.

Banks and investment centers, legally obliged by compliance and regulation to have a software solution to manage their investment products, constantly need newer, better and more suitable technology solutions. With the bigger market slice up for grabs, software providers aggressively promote their products even as new players, promising better products, tossed in.   Bottom line, an immense competition and it’s going to be fierce and perennial.

Though investment houses are abound, it will be the Banks that would set the pace. Banks are the bigger hunters for financial solutions. Cash-rich Banks are bigger, offer more liquid investments, more trusted and more popular with the masses for their stability and prestige.  Moreover, Banks have partially captured old and new wave of public investors’ market, as they are their own depositors or clients.

My personal estimate is that there is about thirty to fifty banks headquartered in Manila including fifteen of the biggest and most competitive banks in the country today. Backbone (or should I say bank-bone) systems Trust, Treasury, Trade Finance, Risk Management, Fix Income, Capital Market are just like the nerve centers of financial organizations thus, the demand for such systems is fundamental.

Vendor-dependency does not end either upon technology purchase or after technology acquisition. New accounting standards and compliance issues must be fitted in the system. Technical maintenance requirement, version upgrades, business trends, technology refresh, new business rules must be constantly adopted by the system. Thus the need for the software solution and the vendor-dependency to match becomes more incessant. Consequently, the financial solutions market in the Philippines becomes lucrative business for software providers but fiercely competitive.

As the Philippine economy upswings validated by the third Investment grade rating by Moody’s (after Fitch and Standard & Poor’s), investment consciousness among the masses is on unprecedented rise since IPO boom in mid 90s. Banks and Investment houses need new systems to handle old and new investment products that once again breathed into life – UITF, Trust Fund, PERA, Retail Treasury Bonds. An influx of new vendors is expected to join the bandwagon but the major players are likely to dominate the market. Giant Software vendors – MiSys, CCK, and SunGard, who have entrenched a significant foothold in the local market through customer-based foundation, are expected to make a killing.

In my stint as a Project Analyst working in a Bank, I have the pleasure of crossing path with these vendors. Here’s my three cents worth of thoughts.

 MiSys

MiSys is the global leader in financial software industry. Locally, MiSys iconMiSys rings the loudest bell in the banking industry as far as providing core banking solutions. MiSys popularity with the local users for its vast market hold makes it the most popular banking software vendor in town. Opics Plus, Misys’s Treasury solution and used by two major banks in the Philippines, is based on Service-Oriented Architecture (SOA) and XML Web Services. A brilliant architecture appropriately blends with the current technology trend that allows applications of different platforms to communicate with each other.

What I’d like to see for MiSys, with its vast local clients, is that it would implement a reduced and standard pricing scheme when customization costs are shared by clients directly benefitted by the customization. I have known some great guys from MySys over the years –  Tim Horsley, Kelvin Loh and Noli Romualdez among them. They’re the MiSys’ go-to-guy kind of guys but otherwise unassuming, friendly and casual.

SunGard

They probably are the nicest people in the business. SunGard can SunGard iconboast of its stability, it’s financial strength, for a reason. SunGard, a $4-Billion technology Services Company, primarily focus on wealth management, has a wide dominance as a technology service provider in the global market. Locally however, they have some things to work on with the users. A new team is created to address the issue. And that’s good news.

Trust is a lucrative investment scheme in the Philippines business in the Philippines. UITF varieties, Corporate and Personalized Trust products and Agency Services, Investment Management Account services among others are the most promising yet safest investment instruments in the Philippines. SunGard’s Trust solution, TAPS, specializes on these investment instruments. If the customization efforts for TAPS could effectively align with the local way of doing business, the users’ desired way then TAPS is gaining ground. I don’t want to see TAPS just elevate to higher ground. I want to see TAPS blast off!

CCK

CCK has been quite around in the market and aggressively competing SunGard and MiSys. It’s interesting to note that Guava, CCK’s Treasury system, uses an untraditional database technology called Gemstone, an Object-oriented Database Management System (OODBMS) as oppose to the mainstream Relational Database Management System (RDBMS). OODBMS uses Object-oriented model where data is stored in a chain of objects, and objects are data elements containing instructions to represent real-world objects. There are claims that OODBMS produce better performance especially when handling complex data inter-relationship involving multi-media files.

But RDBMS stands tall to nearly all business-driven application where data structure are easily represented by tables and keys in a way that it’s easier to understand by end-users and developers alike. The main battle for CCK (for Guava system) is its challenge to mainstream recognition of RDBMS as the standard database system. RDBMS is predominantly used by 99% of client-server database users. The underlying challenge of using OODBMS is its complexity and skills availability.

Let me end by sharing what the precious insight in Project Management. Incidentally, it was from CCK guy named Frank Cavaleri (This guy was such character. He was always on festive mood especially when feasting over a sumptuous meal. ‘l love food!’ as Frank would say with gusto). Frank said that in project implementation management, when you are unsure along the project line that what you are implementing might not be the best method or the best alternative, or when you cannot find the finest method, implement it anyway.

At the end of the day, it’s better to have implemented the less best than to have none at all. On a project scale, it’s better to have implemented a project which might not be in the best form as conceived rather than having a project that remains unimplemented;

Good project wisdom, don’t you think?

 

Posted in Financial Solutions Market - Battleground Manila | Leave a comment

BI, Anyone? A Handy Business Intelligence Approach

More often when people bumped the terms ‘Data Warehousing’, ‘Business Intelligence (BI)’ or ‘Data Mining’, they think of state-of-BI Photo - Steps In Circlethe-art tools and complex tech stuff comprehensible only to tech geeks.  They squirm just by the thought of it  and they let their thoughts jump into an escape hatch with a parting words ‘Let the tech guys worry about that.’

 The fundamental difference

BI has many facets. Personally, having work with databases BI is data analysis in-depth. We normally analyze data by observing the behavior of numerical data in progression over time delimited with time itself and other components associated with the numeric object such as sales over time, number of units produce over time, distance covered over time, etc. To visualize the analysis, we plot the data on the graph traditionally on two-dimensional approach the way we see data in an Excel spreadsheet.

As an example, say Branch A of ABC Bank received a total deposit amounting to $12000 for the month of January 2012, Branch B received deposits amounting to $34000 in Jan 2012, Branch A had $9000 deposits on February 2012, Branch B earned $11000 deposits on Feb 2012 and so on. We list down the branches in the column (Y-axis) of a spreadsheet against the months on rows (X-axis) and the intersection of X and Y axis is the corresponding numeric value. The intersection of the two dimensions (X and Y axis or Rows and Columns) in the graph is the corresponding numeric value..

In BI perspective, the numeric value is called the Measure (or Fact Table) and these are the amount of deposits $12000, $34000, etc. ExcelPICEach of the numeric value is bounded by two dimensions namely November 2012 (called the Time Element dimension) and Branch A (called the Branch dimension). Thus, traditionally we analyze data on a two-dimensional approach the way we see numeric data in a spreadsheet – the amount of deposit bounded by the X-axis (Row) and the Y-axis (Column). Hence, say $12,000 amount deposited on January 2012 for Branch A.

What if two more dimensions are added – Account Type and Customer. So to complete the statement with the added dimensionsPowerOLAP Cube it would be – A total deposit amount of $12000 was received by Branch A on the month of January 2012 using Savings account (Account Type dimension) and deposited by Client 1 (Customer dimension). With a total of four dimensions, we can no longer use the typical 2-dimensional spreadsheet obviously because a spreadsheet could not accommodate more than three dimensions. (Yes Sir, a typical spreadsheet can accommodate a three-dimensional graph where a third dimension is plotted on a page or worksheet). A set of numeric data with more than three dimensions can only be plotted on a graph using a cube structure.

 Hence the difference, as oppose to analyzing data in a typical two-dimensional approach BI is an in-depth analysis of data with three or more dimensions constructed into a multi-dimensional structure called cube. Specifically, a part of BI where the analysis of data is based on a cube structure is called On-line Analytical Processing, otherwise known as OLAP. Consequently, in a multidimensional cube structure, a cube could be tweaked to drill down or drill up from a particular slice level. To drill-down, It means you may want to see the breakdown of deposits done on Branch A on a Weekly (or even on a daily) basis. To drill-up, you may want to  see the deposit made for Branch A on annual basis

 BI Applied,  A Practical Approach

In a Bank where I once worked, monitoring the performance of branches was a serious business.. Though some departments like Trust and Treasury could independently generate income, branches are the basic income-generating units of the Bank. For a Bank with roughly 300 branches, a simple approach to profitability is pamper these 300-strong branches to operate productively. Hence more data instruments were needed for analytical efforts in identifying improvement opportunities and inherent weaknesses of branches

As I saw it, this was a potential area where an OLAP solution could effectively provide a mechanism that could be used to analyze the performance of branches. I started with a question – ‘Where is the highest (or lowest) concentration of deposits/withdrawals based on geographical and time parameters? Now what if two more variables are added to the equation namely Account Type (i.e. Cash deposits, Check deposits, Savings withdrawal, TD withdrawal, etc) and individual client (with properties on gender, income-level or profession). With these four parameters in an OLAP app, it might be able to spot a potential trend or relationship.

I finally came up with a simple Online Analytical Processing (OLAP) solution aptly named Branch Performance Matrix. A simple yet relevant approach measures the deposit or withdrawal capacity branches in terms four parameters just mentioned. It would give insight to a decision-maker for a case in point in any of the two cases below.

Case 1

Branches covering South Western region of Mindanao have a high volume of deposits during the month of December. It defies the country-wide seasonal trend when this time of year withdrawals are dominate due to shopping trend for Christmas season. Upon scrutiny, South Western Mindanao is an agricultural geographical region and the clients of these branches are dominantly farmers. At this time of the year harvest has just been made and these farmers are saving their funds for the next planting season when they would need money to buy seedlings, fertilizers, etc.

Case 2

On the month of June, a wave of withdrawals is expected to be made as it is the opening of school year when cash are needed to pay for tuition fees. However some branches in Metro Mania area, the amount withdrawn are significantly low. Upon scrutiny the clients of these branches, as shown by attributes of Client dimension, are well to dot who live in exclusive villages nearby. Because of their affluence, we could infer that they have adequate cash availability.

A lot of approaches can be made in designing an OLAP app  to identify a potential trend in the frequency and volume of deposit or withdrawal made from branches. The Branch Performance Matrix is just one of the ways.

Posted in Tech Talk and Toys | Leave a comment