• Home
• About
• Contact LÛCRUM
• Polls
• Archives

• Business & Leadership
• Business Intelligence
• Featured
• Jobs
• LUCRUM News
• Podcast
• Technology

Using OLAP to Improve Organizational Effectiveness – Part 2

This is the second in my series of 3 posts on using OLAP tools to improve the effectiveness of organizations.  In Part 1 I discussed some background concepts and terminology.  In this part, we’ll talk about some specific examples of how OLAP can have an impact in this area.

OLAP’s Impact on Organizational Effectiveness

How can an OLAP tool help improve an organization’s performance as measured against its objectives?  Answering this question requires a greater understanding of how strategies and tactics are implemented within organizations.  I’ll use a model of organizational effectiveness developed by Michael Beer to illustrate the implementation of strategies and tactics.

The picture below shows a simplified version of a model of organizational effectiveness developed by Michael Beer (Note on Organizational Effectiveness, 10).  Business goals and strategy influence and are influenced by top management.  Management determines and implements the proper organizational design to achieve the organization’s goals.  The design of the organization, in turn, influences human resources attributes of the organization.  Finally, these HR attributes directly impact organizational effectiveness.

This simplified version of Michael Beer’s model is presented again below.  Added to the model though, is the position of an OLAP tool in improving organizational effectiveness.  OLAP technology exerts its influence on organizational effectiveness in three sections of the model:

• Management
• The Measurement and Reward Systems aspects of Organizational Design
• The Coordination aspects of Human Resources.

While the impact of OLAP technology in each of the areas above is slightly different, each is related and shares a common trait: improvement in communication.  Utilizing OLAP tools to improve communication requires a broad audience for their utilization.  OLAP tools are traditionally utilized by analysts and managers.  In this model, front-line employees become critical users of the tool as well.  The wide-scale availability of web-based OLAP tools makes such organization-wide implementations cost-effective.

New Partner: TARGIT!

Have you heard of TARGIT?  TARGIT is a suite of BI Tools geared toward getting you to BI “in the fewest clicks”.  LUCRUM has always been a big believer in doing BI..Faster!  This suite of tools is a great tool in our toolbox.  We encourage you to learn more:  http://www.targit.com/Products/TARGIT_Suite.aspx

Using OLAP to Improve Organizational Effectiveness – Part 1

OLAP tools have been widely available for years and are in use in a large number of organizations.  They are typically deployed as speedy, easy-to-navigate reporting tools.  With a little creativity though, this class of software can also be utilized in a very different manner.

As organizations struggle to communicate their objectives to employees and to align the activities of those employees with the objectives of the organization, they can get help from these same OLAP products.  OLAP software can help by providing the capability to:

1. Improve management’s knowledge of progress on objectives
2. Improve employee coordination on efforts to achieve objectives
3. Communicate the link between employee effort and performance
4. Communicate the link between employee performance and reward
5. Improve employee performance feedback.

In this series of three posts, I’ll talk about the role OLAP tools can play in each of the areas above.  But first, I’m going to start out with an introduction to the concept of Organizational Effectiveness.  This introduction will give us a structure to frame the rest of the discussion.

I am not going to spend any time defining OLAP.  If you’re interested, check here and here for some background and definitions.

Organizational Effectiveness Defined

Effectiveness is defined as simply having the intended outcome.  In an organizational context, the intended outcome is the goal of the organization which is usually expressed in a mission statement.  The Hierarchical Definition of Strategy provides a framework for defining and explaining these concepts and I am going to use it extensively in these posts.

Hierarchical Definition of Strategy

Explaining organizational effectiveness requires a discussion of business strategy and the Hierarchical Definition of Strategy provides a simple framework for this discussion.  The Hierarchical Definition of Strategy is built on the concepts of Mission, Objectives, Strategies, and Tactics (Barney, 10).  I’ve drawn a simple figure below to help explain this model:

An organization develops its objectives based on its mission while strategies and tactics provide specific details regarding the attainment of these objectives.  In the Hierarchical model, the effectiveness of the organization can be determined by simply comparing actual performance to objectives.  Michael Beer summarizes organizational effectiveness in this manner:

“An effective organization is one capable of implementing its strategy … A strategy is implemented effectively when people and groups in the organization work in a motivated, skilled, and coordinated manner on the appropriate tasks.” (Note on Organizational Effectiveness, 10)

In other words, the effectiveness of the organization is determined by its ability to achieve its objectives.

Hierarchical Definition of Strategy – Example

An example will help to clarify these concepts and make them a little more concrete.  Dell Inc.’s Mission Statement is:

“Dell’s mission is to be the most successful computer company in the world at delivering the best customer experience in markets we serve.”

The high level nature of the statement, though necessary, makes it difficult for individual employees to apply it to their daily efforts.  At the next level of the strategy hierarchy, Dell management has likely developed Objectives that will lead to the achievement of this mission.  For instance, we can imagine that Dell has defined an objective to “Provide customer support with a customer approval rating of over 90%.”  This supports their mission of “…delivering the best customer experience…” and provides employees with a tangible performance target.

The final two levels of the hierarchy are related to execution.  Strategy is a means to accomplish an individual objective.  Continuing with our imaginary Dell example, the strategy developed might be “Deliver the fastest, most accurate technical support in the industry.”  This supports their objective in the sense that a firm delivering the fastest and most accurate technical support would very likely receive high approval ratings from customers.  Tactics are execution oriented and exist at the lowest level of detail.  In the Dell example, a tactic may be a requirement that all customer support personnel complete a certain set of technical and communication skill classes.

In the example developed above, Dell’s organizational effectiveness can be determined by comparing actual appraisals of their support services with their objective of a 90% approval rating.

Next Post…

Now that we’ve laid out some concepts and terms, we can move on to the heart of the discussion.  In Part 2, I’ll dive into the details and talk about how utilization of an OLAP tool can help an organization become more effective.

The Value of Slowing Down: Go Slow to Go Fast!!

I once read about a Chinese mathematician who calculated complex scientific formulas by hand using a slide rule. He lamented the rising cadre of scientists who punched formulas into calculators and computers. Although they worked more quickly, the new generation of scientists often lost sight of the concepts behind the calculations.  Without this fundamental understanding, the younger scientists often failed to grasp the significance of what they were doing or apply concepts in new ways to make new discoveries or effective designs.

This story parallels an area in Information Technology called “Business Intelligence.”   Business Intelligence is also known as “Data Warehousing” and “Executive Information Systems” with dash boards or digital cockpits.  The IT organization provides a rich repository of data for the business knowledge workers.   Providing data has become so important; in addition, the tools leveraged have become more and more rich in functionality.  And yet, the number of business users truly leveraging this kind of technology-oriented business information environment lags the productivity that the organization could receive.  Simple questions like:  who are my best customers and why?  What’s my best product and what is its margin contribution?  Why is my market share in a particular geography increasing where in another market it’s declining?   How can I get my business results information faster so I can be more informed on the ever-changing aspects of the market?   A user says, I can make a lot of informed decisions….how can I make even more of them instead of hire more decision-makers?   The business and market questions go on and on and on.

As IT professionals, we are used to being held accountable to deadlines with ever changing resources and requirements.   In the world of Decision-Making, as data warehousing managers, we often are rushing to meet these same deadlines.  Often the deadlines and deliverables overshadow the underlying purpose for building the data warehouse. The good thing about bad times is that they force us to slow down and painstakingly evaluate what we are doing. So, although there are dark clouds ahead, there is a silver lining in the reality of our environment in having to do “more” with “less” resources. 

Here are 3 tips to consider making your Data Warehousing environment even more “ready” for business decision-makers. 

1. Meet with the Business Decision-Makers frequently.   I am suggesting that a weekly meeting at a minimum would be beneficial in order to review their data, listen carefully to understand what data they are really using, and what data they may be leaving behind.   Is the data they are leaving behind the result of not understanding how to use the data, is the data no longer relevant to their decisions, or perhaps the data is too summarized or too detailed?
2. Document the business flow of the data graphically using business terms, not technology metadata definitions.  Distribute the business document to all business and IT users so that everyone really knows how the data is being used in the context of business.   Too often, we revert to memorizing the technical definitions and only use them.  We lose the business context and as new people join the data analysis, the true business definitions are lost. 
3. Proactively have discussions sponsored by IT with the Business Users about the cleanliness of the data and how IT is transforming the data.    Show them the techniques that you are using to cleanse the data and transform it so that there’s a common repository of data that they can use.   The more the Business Users understand what you do in context of the IT problem, the more they will provide their insight into how the data is most meaningful to use. 

Chinese “Business Intelligence” Proverb:  If you plan for one year, plant rice.  If you plan for 10 years, plant trees.  If you plan for 100 years, educate mankind.

Tomorrow’s Forecast

I am always looking for different ways to describe what LÛCRUM does.  Sure there is the standard response of “LÛCRUM helps companies to turn data into useful and actionable information,” but that can be tough to visualize.  Sometimes it helps to use more vivid and familiar examples of things to explain the services we offer.  Think of the weather.  If all of the important weather components were just structured data in table or spreadsheet, it might look like this: 

So sure, I could find what I was looking for…”what’s the temp at 9AM?”  It takes a pair of readers a few seconds, but it’s there.  There are so many other data points, however.  Is it getting warmer or colder?  Is it going to rain today?  Certainly the other data points are there that would help me to make the decision – relative humidity, cloud cover, wind speed – but I may need to consult the company metadata to understand what it all means and if those numbers mean it will be getting hotter or colder.  THIS IS DATA.  Your org has it…you’ve got to make sense of it. 

What LÛCRUM does, is make this DATA meaningful.  We like to call it Business Intelligence or Data Visualization.  Simply stated, we take all of those data points and help you to make better business decisions (or in this case, help you to decide if you should wear a coat or bring your umbrella). 

THIS IS BI!

THIS 

IS 

BUSINESS 

INTELLIGENCE!! 

Taking lots of data and making it meaningful…yeah, that’ s what LÛCRUM does. 

- Jodie

The Future of Business Intelligence

January 2010 Cover

Have you heard of Technology First?  Technology First is a Dayton, Ohio  based industry-led, industry-driven trade association dedicated to:

• Proactively Representing IT in the Region
• Increasing understanding of Technology First and its value
• Recognizing and promoting our membership
• Highlighting niche technology companies

Technology First looks to strengthen technology thought leadership by inspiring innovation, focusing on new ideas and best practices, presenting leading edge industry information that is both strategic to business and technical folks.  They also look to inspire volunteer leadership by encouraging stronger member participation which involves more working committees and develops programming to best meet industry needs.  Additionally, they look to engage in conversations with technology community by leveraging interactive social media.

I was asked to prepare an article on the Future of Business Intelligence.  Imagine my surprise when that article was selected as their cover story this month!  Click here to read.  I’d love to get your thoughts.

Have a great week!

- Jodie

Building the BI Solution for Small IT Shops

Next Wednesday, I get an opportunity to speak with several CIOs of companies with small to mid-size IT shops (less than 20 people in IT).  These CIOs have the unique challenge of maintaining day-to-day operations for their companies while balancing the demands of bringing new technologies to their organizations that will help them to gain competitive advantage.  WHAT A CHALLENGE THESE FOLKS HAVE!!  The same guy that keeps Exchange running is also finding ways to minimize IT spend and figuring out how to integrate a hand-held device into the shop floor, or into a salespersons hand.

My topic for next Wednesday is BI…Business Intelligence.  More specifically, how do you bring data to users who may not be asking using staff that is already stretched to the limit?  As I’ve discussed in prior posts, most find BI to be a big challenge that is expensive to implement and often times fails to deliver what was promised.  This week I saw a small IT shop that tried to solve the data problem “quick and dirty”.  They had the right concepts and brought the right tools to the party, but they were missing the final piece…making the data make sense to the user community.  Essentially once the data had been aggregated it was “dumped” on the end users.  They had data but it still wasn’t providing any insight.

So how do you bring BI in a cost effective, meaningful way to an organization?  I look at it this way:

1. Identify the end need. What do the users want to see when this is finished?
2. Identify the available data. What’s available today that will help solve #1 above?
3. Identify the gaps. Can the gap data “wait”?  Or do we need it before we can make meaning out of #1?
4. Deliver…deliver…deliver (repeat). If the users have a weekly meeting where they will review this data, they should have one new element (minimum) each week until all are presented.  This gains buy in and confirms that everyone understands the end game.

This method works for both small and large organizations.  It takes discipline and time, an on-going dialog between the developer  and user, and most importantly a developer that understands the business challenges and can bring ideas to help solve those challenges.

-        Happy Building!

The Phrase Business Intelligence

I first came across the word “Business Intelligence” at the 1999 Cognos meeting in Toronto when their CEO announced the “new IT category” as part of the leadership strategy.   Their marketing gurus must not have done a manual search or focus group since there wasn’t any indication that anyone really knew why it’s called “business intelligence.”  Let’s look at the historical words in this category of making data more meaningful.  Throughout my 29-year career, Information Technology Professionals have tended to over-complicate what they are trying to accomplish by coming up with descriptive labels that tend to remind me of a NASA space mission.   Back in the Eighties, we called it Decision Support Systems (“DSS”).  In the early Nineties, it was referred to as Executive Information Systems (“EIS”).  Then, with the explosion of relational data base technology, the new movement became coined as various tangible models:   Data Warehousing, Data Marts, Closets, Data Mining, and the like.

From an IT perspective, there are a lot of differences between these definitions throughout the years.  At the same time, how do they really differ from a business executive viewpoint?   Are the decisions in business being made today differ significantly from decisions that were made yesterday?  Does the thinking process differ from an analytical viewpoint?  Does having more data mean that you can make better decisions?  Are decision-makers better off with all of the data that is available?   How does the business executive think about “business intelligence” from an information viewpoint?

Here’s a three-part “Maslow’s Triangle” layered model to think about Business Intelligence from a business perspective.

1.  How’s Business?

First, at the base of the triangle, you have to ask “How’s Business?”   This layer really emphasizes “time over periods” of transactions.    Traditionally, this area is termed “transactional reporting” and simply put, is giving the user their numerical tabulation of data at the end of a period.   What would a business person define as “Best in Class” in this area?   Give me my reports as near-time as possible for the period that I am looking at and be able to sub-category my different business lines, product lines, financial divisions, etc.  Most of the data could be described as the data from “double-entry bookkeeping systems.”  With today’s ERP-style systems, this kind of information is fairly accessible as long as you are dependent on internal data only.   Some data feeds may be external feeds or internal non-structured data sources that still have the same timeframe.  Examples would include “customer satisfaction” data, quality data, and other operational inputs.

For example:

“What is the revenue over the last quarter?”

“How many X was sold in the last week?”

“What is the profit for the month?”

2.  Why?

The next layer up the triangle is simply put “Why?”   Why did the business’ Eastern Region have a 5% increase in sales year over year?  Why did we miss our numbers in the last week of the quarter?  Why did our market share grow in our mature product line in the last 2 quarters?

3.  What If?

The last layer of the triangle is “What If?”   If one can receive their business results from “How’s Business” and then is able to determine “Why” the business performed in this fashion, the “What If?” pinnacle of the triangle will provide a roadmap for the decision-maker to model their potential decisions that they have in mind.

For example, if one knows their financial performance and also sees where the business over-achieved and where it under-achieved, it is able to move resources of the business (management & money) to the area of need.  Whether the strategy is to provide more or less resource is up to the person involved.   The numbers themselves are not going to make the decision.

So, then is “Business Intelligence” an oversold phrase in the world of Information Technology? A “Qualified Yes” and a “Qualified No.”   The challenge today is that the tools actually work and work well if the approach taken is right.   At the same time, recent publications and noted experts all agree that the road to Business Intelligence is cluttered with a lot of failed attempts, a lot of capital spend that isn’t going to be realized from an investment viewpoint, and a lot of disenfranchised users.

I’ll write about this dilemma in my next blog.

Data Mining – Friend or Foe to My Customer Lifetime Value

We just had a new Wal-Mart Super Center open about 3 miles from our house in Cincinnati, Ohio.   They built it on land that used to have a “Big 3” automobile factory on it years ago.   Vacant for at least a decade, it was torn down and then up sprang an Arkansas just-in-time building, the Wal-Mart Super Center.   I normally don’t like shopping at Wal-Mart; I do value low prices although not as much as fast entry/exit and knowledgeable clerical sales help.   As an IT Professional, I do admire Wal-Mart for their storied data mining and sales analytics platform.  You know…the stories of just-in-time shipping of more beer to Florida when a hurricane starts to threaten the Sunshine State.   They’ve been written up in almost every IT journal over the last 10 years as well as every national business publication.

So, I decided to venture into the Super Center 3 days before Christmas to specifically buy a popular board game, Risk, for my three boys.   The store greeter told me “left and down 5 aisles or so.”   I came upon the shelves where Candy Land, Trouble, Life, Monopoly and others were located.   No Risk.   There was a stocker, 45 years old, unshaven…reminding me of an auto mechanic…he seemed out of place and I wasn’t sure whether he’d even talk to me.   He was just walking away from the game shelves.   I ran after him stuttering with uncertainty to see if he knew whether they carried Risk and if I was in the right section?  He stopped and said, “well, yes and no.  We don’t sell much so it isn’t stocked very much.”    I then asked about Candy Land, Monopoly, and Life and their stocking levels.  “We sell tons of them.”  So, that’s it.

My question is…does Wal-Mart really do analytics of all of their customers and prospects?   If it’s the normal retail geography of “1-mile, 2-mile, 5-mile, 10-mile”  I should be very near their sweet spot of 3 miles away!  With their new grocery store there, should I be classified as the 40-something guy that wants to buy micro-beers, brie and gorgonzola cheeses, organic crackers and Risk?

Every media company in the country is publishing articles trending the growth of data volumes doubling and tripling!  That includes Wal-Marts data as well.   Business Intelligence and Customer Analytics are going to be key tools for retaining customers, finding new customers, and overall market share strategies.    Leveraging them wisely through intelligence business questions and common sense will still be integral to the overall platform.   Just because you use Business Intelligence doesn’t mean that you are going to be successful…setting specific goals up-front in the process is integral to success.

The Wall Street Journal article this week indicated that Wal-Mart’s web ordering is gaining market share rapidly.   Perhaps they should have offered a web order entry for me with a quick ship program for Risk?  Perhaps next year….for now, I’m going to venture out again and buy the Risk game at another retailer.

The VLOOKUP Hookup

Companies invest large amounts of money, time, and other resources acquiring and implementing reporting and analysis software.  I’ve seen organizations invest hundreds of thousands of dollars in projects and fail to realize a decent return on their investments.  The point of this series of posts is to educate you about the reporting and analysis capabilities of a tool your organization probably already owns: Microsoft Excel.

In this series of posts, I will discuss a number of these capabilities and will give some concrete examples of how to utilize them.

I will be using Excel 2007 for these examples.  Much of this functionality is also available in Excel 2005, it’s just not as easy to use and does not have some of the more advanced features.

Getting the Data
The first step in any effort is to get some data into Excel.  We’ll start out using a simple static list.  You probably already use lists like this regularly.  If you don’t utilize Excel in this way today, think of the reports that you work with from the various systems that you run your organization with.  In most cases, you could probably either copy andhttp://thefuturevalueofbusiness.com/wp-admin/post.php?action=edit&post=745&message=6 paste or import these reports into Excel to get some data to work with.

In future posts, we’ll cover a much more powerful method of acquiring data by connecting to external databases from within Excel.  For now though, we’ll stick with this simple example.

I’ll be working with the sample database that comes with Microsoft’s SQL Server database software.  This sample database contains information about a fictitious company called Adventure Works.  Below, you can see that I have an extract of order information that I’ve pasted into Excel.

This is the most common manner in which people utilize Excel for reporting purposes: simple lists of data pasted or imported from other sources.  In most situations, this data comes from existing reports or queries.  My example above is a very simple query…you can see that we don’t even have names or descriptions for most of the data.  For example, Column F is showing us the Product ID instead of the Product Name.

The best way to solve this problem is to have the author of the report or query modify it to include the Product Name in addition to the Product ID.  Let’s imagine that this is not a realistic option though; there is a way that we can solve this problem using an extremely powerful Excel formula called VLOOKUP.

Using VLOOKUP

To expand on our situation above, let’s imagine that I have a second worksheet in my Excel workbook.  I have an image of this second sheet below.

The Product ID in column A corresponds to the Product ID in column F on the Orders List.  We are going to use VLOOKUP to take the Product ID in the orders list and lookup the Product Name in the product list.

To make the formulas a little more understandable, I am going to rename the Sheet with the order list “Orders” and I am going to rename the Sheet with the products list “Products”.

On the “Orders” sheet, let’s insert a column immediately to the right of the Product ID.  We’ll label it “Product Desc” in Row 1.  In Row 2, we’ll enter the VLOOKUP formula:

The parameters (the information between the parentheses) tell Excel how to lookup the value we want:

• The first parameter, “F2”, tells Excel what value we are performing the lookup for.  In this case, we are looking up the Product ID.
• The second parameter, “Products!A:B”, tells Excel where to go to do the lookup.  Here I selected the first 2 entire columns on the “Products” sheet.
• The third parameter, “2”, tells Excel to bring back the data in column 2 from the lookup list when it finds a match for the value from cell F2.  I know that’s a confusing sentence at best, but it will make sense in a moment.
• The last parameter, “FALSE”, tells Excel that we want it to return only an exact match for the value we are looking for.  If Excel cannot find an exact match for the Product ID, it will return an error indicator.

The Results
Now, let’s take a look at the results of our formula.  The screenshot below shows what I have now.

This screenshot shows a few rows from the “Products” sheet:

Hopefully you can see how VLOOKUP works now.  Excel took the value in F2 in the “Orders” sheet, 776.  It went to the first column of the range we gave it; that range was Columns A and B of the “Products” sheet.  It scanned through that range until it found a match for 776.  It then took the value in the 2^nd column of the range, column B, in the same row and returned the value in that cell (“Mountain-100 Black, 42”).

One thing I didn’t make clear before that I want to point out now.  VLOOKUP is always going to look in the first column of the lookup range for the matching value.  In our example, the lookup range was columns A and B of the “Products” sheet, so Excel looked in column A for the matching value.  There is no way to tell the formula to look anywhere other than the first column; so you either need to cut and paste the columns to get the right one first, or just change the reference so the lookup column is first.

To complete our list, we can just fill down the VLOOKUP formula in column G to the bottom of our orders list.  Now we can analyze our order data with actual Product Names instead of just Product IDs.

Summary
VLOOKUP is useful in many other situations…you can probably imagine a few other uses for it yourself.  It is very handy to use it as we did in this example though.  Even though we could have accomplished the same goal by having someone in IT modify the query or report, now you can be a little more self sufficient with your reporting needs.

In my next post, I’ll cover a few more features like filtering and date manipulation.  Ultimately, we’ll move on to Pivot Tables and External Queries which provide very powerful mechanisms for analyzing data and can compete with some features offered in expensive reporting software.

Next Page »

Copyright © 2010 LUCRUM, Inc. 7755 Montgomery Rd, Suite 160, Cincinnati, Ohio 45236 TheFutureValueofBusiness.com