The small and medium business space is so often the oxymoron of the enterprise. On one hand, smaller businesses are often agile and innovative, and on the other hand, they can often lack experience, resources, and the discipline to turn complex situations into profitable action. A small business, for example, can find success based solely on the industry and market insight of a great leader--if not the "gut" decisions that built the company in the first place.

At a certain point, though, the shiny veins of gold first discovered by a company peter out, and exploratory guesswork just can't locate new sources of revenue. This is where data-driven intelligence must enter a business.

Sounds simple enough, right? For big enterprises, business intelligence (BI) solutions turn into multi-year rollouts with master data management plans, massive data warehouses, and tens of millions of dollars. For many SMBs, BI is just a glimmering idea.

But it can be real. Especially now. Especially today. Even in this economy, and even with a Power System running IBM i.

For starters, IBM i on a Power System is better than ever from a pure technology perspective--you have great technology that brings multiple deployment options. At the same time, query and reporting tools have been maturing and can now deliver high-end BI-like results on a budget. In fact, the lines between BI and basic reporting are getting blurred, but if there's one commonality to today's intelligence solutions, it's speed. Today's SMB-sized solutions are delivering answers faster than ever.

Let's take a closer look.

Size and Complexity

Midrange computing customers are a diverse lot--we have IBM i customers in manufacturing, distribution, finance, insurance, gaming, education, government, and more, and they come in all sizes and shapes. But there are some factors that determine BI needs, and size is critical.

"We really need to segment the SMB market because, for Forrester, SMB is below $1 billion or below 1,000 employees, and that's a very broad range," says Boris Evelson, a principal analyst of business intelligence for Forrester Research. "In my experience, any enterprise above 500 employees or $500 million has the same types of issues or challenges as larger enterprises, so BI is absolutely at the top of their agenda."

If a company is really small, say with a few dozen employees, and the company is running in a single location with a single line of business or set of products and services, the intelligence needs can be handled pretty easily, often through the organization's existing business software. The real challenges arise with companies that fall into the $200 million in revenue size, with a couple hundred employees, Evelson says. These size companies have often grown beyond a single location; they may have acquired another company that uses a different ERP system, and now they have to integrate and analyze data across disparate data sources. Where big enterprises have large IT groups and budgets to match, SMBs can get caught between hard needs and rock-bottom budgets.

"It's this mid segment of the SMB market where we see most of the struggles," Evelson notes. "It's, 'We know we need something; we don't really have the IT budget; we can't afford expensive BI tools . . . so what do we do?"

Enter the IBM i

DB2 for i, of course, is a relational database that was created more for transactional processing than for reporting analytics.

"When we go to large enterprises, when we go to multi-terabyte data sets, and when we go to high-end complex BI requirements, that's typically where traditional relational databases are probably pushing their limits for BI. When you need to configure it for multiple terabytes, it can be done, but it's a challenge. When I need to continuously adapt to different requirements, that's a challenge. If I need to incorporate both structured and unstructured data, that's a challenge," Evelson explains. "But SMBs that are operating with smaller data sets and simpler requirements, their traditional database like DB2 will probably be all they will ever need for BI."

Still, IBM has put out some interesting results for big BI on IBM i. Recently, IBM produced some SAP-certified benchmarks that showed various Power Systems running IBM i 6.1 as leaders in SAP Business Warehouse and SAP Business Intelligence Data Mart benchmarks. So how is this possible?

According to Doug Mack, an IBM Power Systems platform marketing manager who focuses on BI for the IBM Systems and Technology Group, the answer comes from a decade of refinements. "We have spent the last ten years working on our database engine to crank up query performance, and this has come out in various releases over the years," Mack notes.

In V5R3, IBM's SQL Query Engine (SQE) optimized queries, but there were some restrictions on the types of queries users could run. "In V5R4, we took a number of those restrictions away, so more jobs and more queries can leverage this new optimizer technology," Mack says. Query/400, for example, uses IBM's Classic Query Engine (CQE).

"In V5R4, we added something called autonomic indexing. Prior to that, our DB engine might look at a query and say, 'You know, it would be much more efficient if it had an index.' So, as part of the job it actually built an index. The query might have taken twenty minutes run and nineteen minutes were spent actually building the index. But after the query was done, boom, the index was gone," Mack explains.

"In V5R4, you can keep those indexes around, so next time the query could potentially use that index and the performance improvement could be significant."

In IBM i 6.1, IBM has improved the SQE optimizer with additional self-learning and self-adapting capabilities that make queries more efficient. For example, the optimizer can recognize when queries are frequently run and then check to see if the indexes are still in resident memory on the system. If so, the query will take advantage of it, leading to significantly better performance.

IBM i has two core types of indexes. "One of them is the traditional binary radix tree indexes that most relational databases have, and the other is called Encoded Vector Indexing (EVI), which is really designed for BI-type applications," Mack says, noting that EVIs aren't an effective technology for transaction processing applications.

EVIs use a symbol table and vector array indexing technology to speed up complex data warehouse queries, but, because EVI is built into IBM i, you don't need a warehouse or data mart to take advantage of it.

"EVIs take up much less space than traditional indexes, so you can create many more encoded vector indexes over more columns. It's a no-charge thing, and if you're in a BI environment it can improve performance, but they aren't necessarily built automatically, so there's some work to be done to create the EVIs," Mack explains.

By using some of the tooling in Navigator for i in IBM i 6.1, you can run a process that will provide index advice. "And this index advice will say, 'Based on this query, we think you should create an EVI, and here is the column for the field you should create that index over, and here's a wizard . . . and then run it again and see what the performance impact would be," Mack explains.

"We believe the use of EVIs is way less than it should be," he adds, noting that most of IBM's larger customers who are using data warehouses are utilizing EVI, but it's not yet well known in the midrange market. "EVIs can significantly improve query performance," he says.

Enter DB2 Web Query

While DB2 for i has gotten better, IBM's modern replacement for Query/400--DB2 Web Query--is built to take advantage of IBM's query enhancements. "We had one client who had a query that was taking six hours to run using the old query technology that Query/400 uses. We were able to tweak that query so that it was using the new query optimizer technology and it ran in six seconds. And that's a true story," Mack says, though he notes that not every query or situation can result in such astonishing gains.

DB2 Web Query is both a no-charge upgrade for Query/400 users, as well as an extensible product with buyable components. The point? Potential DB2 Web Query users will want to explore their BI needs because the product's components can be priced based on your server tier, number of users, as well as a run-time user licensing option, plus a developer workbench, and even a software development kit.

"If you look at the core components that we would generally recommend to a customer, the overall costs will range from $5,000 to $70,000 depending on what processing tier you're on. And that could include automated report distribution, the dashboarding functionality, the metadata layer, or the OLAP feature, which is another way of minimizing Query/400 definitions with a single report," Mack notes.

If that sounds confusing, it is and it isn't. "What we're trying to do with DB2 Web Query is to provide a more modernized version of Query/400 for operational reporting but also provide extensions into BI at an affordable midmarket price," Mack explains. "But simply taking an enterprise BI tool and putting an entry level price on it doesn't always address the issues that will make a company successful," he adds. If a tool is too complex, it won't get used properly. If an SMB doesn't have the resources to maintain a big BI package, they won't be able to derive value.

Lots of IBM i customers have been doing operational reporting for years--IT created Query/400 reports that are run weekly or monthly based on business requirements. There are several problems with this for a modern world. First, it creates a dependency on IT to generate queries, which is inefficient and tends to stifle business analysts from asking questions and finding new answers. Second, running queries against a production database can have a negative impact on performance. Third, the lack of a BI methodology can result in conflicting conclusions.

A full-blown data warehouse or data mart located on a specially tuned server is often ideal, but it's just as often out of the realm of affordable possibility for SMBs.

"Ideally, you want to find some in between solution that allows you to get access to your data without a dependency on IT and still ensure that the data is meaningful," Mack says. Typically, if you build a data warehouse, you also build core definitions of data so that a definition of net revenue is always the same.

"In an operational reporting environment, if you don't have something that controls data definitions, somebody from finance could download the data into their spreadsheet and use a particular calculation for net revenue while somebody over in sales downloads to their spreadsheet and they could use a different calculation," Mack explains. "And then no one believes what the data really means."

Enter the Metadata Layer

"In business intelligence, there's this notion of using a metadata layer, and it does a couple of things. One, it helps with standardization of the data meaning, and it really shields the complexity of the database from the end users," Mack says. "So one of the things we wanted to do with DB2 Web Query is to provide this benefit of a metadata layer to get the benefits of a data warehouse without actually having to build an enterprise data warehouse."

Consequently, Mack adds, "One of the aspects of BI that's built into DB2 Web Query is to try to put the power of the data into the hands of the people that need it the most while shielding those people from having to be a data expert and standardizing the data on a metadata layer."

While a metadata layer can help a small business create a single version of the truth, what about performance?

"There are some considerations you should be aware of if you move to a new query tool like DB2 Web Query and run it against production databases, because quite frankly, those databases are very well tuned for transaction processing--you want to help ensure you aren't creating workload conflicts when you start running fairly complex ad hoc queries," Mack warns.

So what are some of the options for smaller organizations?

At its simplest level, an SMB might be able to utilize the improvements in IBM i 6.1 and use IBM or third-party query tools to tap into production data without a big performance hit. At the same time, most every tool available for IBM i has gotten better and easier to use, so you can expect your business analysts to start running more and more queries. These might be good for business, but they might also jam up your Power System.

On solution is to use logical replication to copy production data to a separate partition that will isolate and protection your production data and workloads. You can also tap IBM's capacity on demand options to add processing power to your existing system, or you can upgrade to a new wicked-fast Power System. And yet, there are even more options.

HA + BI = New Opportunities

"The logical replication solutions that are out there for high availability really provide you with an opportunity for an operational data store--a mirrored image of the production databases rather than a data warehouse that may have restructured that data," Mack says.

"So with a logical replication solution, you can kill two birds with one stone because you are not only getting the HA benefits, you are creating an isolated copy of the production databases. Very simply, you can do operational reporting off of the backup box, and you can get all the benefits of not impacting your production system, and you can leverage an underutilized server, plus you can tune it differently," he explains.

Meanwhile, there's a new third-party offering that attempts to create a tidy end-to-end BI solution for the IBM i SMB: the Smart i Appliance for IBM i. The word appliance implies that the solution is something akin to a refrigerator that you just plug in, but really it's more like a gourmet oven/stove combination that requires special power and natural gas hookups, along with a bit of fit-and-finish work. Still, in the land of BI, appliance is a reasonable descriptor for the SMB-targeted Smart i, but even it is assembled by a variety of players: Smart i is put together using services and components from Key Information Systems (the integrator), Systech Solutions (implementation), Talend (open source Extract, Transform, and Load software), and IBM (IBM i hardware plus IBM DB2 Web Query for i).

The whole package is designed to cost less than $50,000.

Smart i was introduced at COMMON in Reno this spring, and it's a new solution with a promising future. The Fashion Institute of Design and Merchandising (FIDM) was a launch partner with the effort, and so far, FIDM says it's working well.

Overall, there's a wide range of query and reporting tools, in addition to bigger BI packages, available to IBM i SMB customers--be sure to check out our BI Tools Roundup in this issue for a list of some of the more active providers in our industry. They often provide solutions that are less expensive and can even outdo IBM's own solutions for easy-to-understand yet extensible BI.

Chris Maxcer (chris.maxcer@penton.com) is news editor for System iNEWS magazine. "The great thing about IBM i and the variety of options for BI for SMBs is the fact that you can forget about traditional BI solutions, which tend to be complex and expensive," Chris says. "Instead, take a look at the wealth of query tools now available and combine them with the latest versions of the IBM i operating system. Toss in a partition for your data, whether it's for high availability or not, and you're darn close to creating a freeway for driving intelligence throughout your organization. The key is to engage your vendors, including IBM, and get the conversation rolling. There's a lot more to intelligence than Query/400, and most vendors are ready and willing to help jumpstart your efforts."

Related Content: Q&A with South Carolina Student Loan

Like many IBM i-focused SMBs, South Carolina Student Loan (SCSL) knew it needed a BI solution, but it also knew it didn't have a big BI budget, nor did it have the IT staff on-hand to handle a complex implementation. And yet, Tom Dunnigan, SCSL's CIO, came to the small company with an enterprise business intelligence background. The trick was finding big business BI with a small business budget.

CM: Can you describe some of the business drivers that led you to look for an intelligence solution?

TD: Soon after joining South Carolina Student Loan in April 2007, there was obvious demand and recognition for management information that could help our executives and others understand the relative health of our company today and in the future. Some high-level discussions included education on business intelligence capabilities for us to gain a common understanding of BI and our business needs. We agreed to focus on business intelligence that addressed our primary business drivers:

• support a self-service model to contain ongoing cost and remove the IT middleman
• allow us to leverage legacy Query/400 objects as needed from our IBM iSeries without mandated up-front conversion cost
• allow us to evolve and grow our BI capabilities (value) in alignment with its cost, as our company discovers its needs
• support real-time delivery of actionable information to affect daily operations and business decision-making
• address the need for information integrity and "a single version of the truth" as Query400 objects and Excel spreadsheets had proliferated across our company with conflicting results

CM: How did you end up using IBM DB2 Web Query?

TD: IBM had just announced the introduction of IBM DB2 Web Query through its partnership with Information Builders (IBI), so we studied and asked lots and lots of questions. In our discussions with IBM, IBI and an IBM Business Partner, Dynamix, we confirmed that IBM DB2 Web Query would satisfy our business drivers and allow our company to continue its discovery of needs through other advanced features like active reports, metadata/synonym, Developer Workbench, and OLAP. With a solid BI software technology provider like IBI joining our strategic partner IBM to deliver DB2 Web Query, many risks were mitigated, the costs aligned with how we believed we could create value, and it has turned out to be a solid BI tool to efficiently meet our needs.

CM: Can you describe some of the results?

TD: To date, we have deployed several dashboards and many reports that are all geared to delivering real-time actionable information to our management team. These dashboards display key performance indicators or KPIs in real-time, along with graphs and charts, that allow management to act. Let me just provide three examples.

• Today, one of our VPs knows that his department is meeting a regulatory requirement daily, real-time through his dashboard. Before DB2 Web Query, he says he would typically have to wait until the end of the month in order to discover if his key metrics were being met.
• Since the credit market meltdown, our company's demand for better forecasting and management of its loan portfolio skyrocketed, and DB2 Web Query is currently providing the means by which we address that need for financing planning.
• Because management can now compare and visualize performance graphically, two of our departments have detected individual performance issues and intervened with additional education and training to help their employees succeed.

None of the examples were possible with our legacy systems and technology.

CM: For small and medium businesses, can you recommend any lessons learned or key tips for getting started down a BI path?

TD: Our strategy was to 1, leverage our legacy investments as best we can; 2, recognize the learning model and behavior of the company; 3, improve our data management capabilities; 4, introduce a self-service model; 5, identify early adopters/stakeholders; 6, deliver short-terms wins with actionable information; 7, celebrate success with our early adopters to gain momentum; and 8, realize cost as business value is created.

Our strategy avoided the up-front, "big sticker" shock and related risk, and our buying decision mitigated further risks by choosing solid technology partners with the product in a very cost-effective way, which is very important for small and medium sized businesses. In addition to these tips, you also have to recognize that every small and mid-size company is going to go through a period of discovery, where new needs will surface that your BI solution will have to accommodate. So, an evolutionary approach with costs aligned closely to your ability to create business value is critical.