4 Problems With Using Multiple Business Intelligence Products

I recently read a good post by Liam Gooding on 5 Steps to Becoming a Data Driven Organisation. It’s worth a read, especially to see him perform a no-holds-barred take down of his prospect (a self-declared data driven evangelist). People think I’m direct. I’ve got nothing on this guy!

But seriously, Gooding does make a good point about the difference between BEING data driven and SAYING your data driven. It’s much easier to say it than do it.

At the same time, from my perspective, you have to keep in mind the difference between Gooding’s startup/technology customers and our mid-market manufacturing/distribution clients. Ninety-nine percent of the time, we’re helping our clients figure out how to make money and handle their complex accounting challenges. Generally, becoming truly data driven isn’t within our mandate.

That said, Gooding does raise one point I’d like to discuss further: the value of using multiple analytics tools. Gooding is all for it. He makes the point that employees operate at different levels when it comes to data. So you need multiple tools suited to multiple audiences.

Now, as readers of this blog, you’ll know that after years of working with dozens of different tools, we now focus almost exclusively on Microsoft Business Intelligence Stack (Excel, SSRS, Power BI, etc.). That doesn’t mean that we fundamentally disagree with Gooding. But we do believe that for our clients (and the types of problems we solve), other tools aren’t necessary.

If a client does want to use multiple tools (and it’s true that some tools are better for certain applications), we make sure they understand what they’re getting into. Because using multiple tools comes with its own set of problems.

In that spirit, here are four problems that can result from using multiple business intelligence products:

1. Multiple Systems Doesn’t Mean an Optimal Solution

It’s true that different tools are good at different things. But when organizations implement multiple tools, it’s rarely because that’s the optimal way to solve the problem. Usually, it’s because they’re desperate to get a solution in place, and IT wasn’t responding. So multiple systems became the answer (if not the best answer).

2. Every Tool Will Require In-House Technical Support

“But not SaaS,” you say. “It runs in any browser. That’s what the salesperson said.”

Ah, the lies of software salespeople. You can add this one to “your users will write their own reports.”

Case in point:  A client signed up for a SaaS BI solution. That solution, to be delivered over a browser, required changes to the browser setup that conflicted with other settings the company needed for security reasons. If you’re using different tools in the same browser, getting all the settings to work together can be a major pain.

3. The More Solutions, the Greater the Security Risk

I totally get that data security is an excuse some IT departments use to keep things under control (and to prevent progress, it seems). But that’s not to say data security isn’t an issue. And when departments look for solutions, they almost always ignore data security risks.

4. It Creates Data Silos

Gooding acknowledges this problem with a large caveat:

One word of warning: be careful to not create more data silos. Make it clear that any new data tracked should eventually have a path into the organisations central data repository. This means buying into tools with modern developer API’s and ensuring data is kept clean.

Our experience at Red Three is that data silos almost automatically result when departments use different tools. Why? Because getting the data back into the central repository is hard work and requires centralized resources—the same centralized resources that were unavailable in the first place, which led people to implement multiple solutions. See the cycle?

What’s your experience? Have different solutions delivered real benefits for you? Or did you wind up having multiple answers to the same questions?

 

5 Big Business Intelligence Problems: Is Your BI Project Doomed to Fail?

As business intelligence consultants, we want our projects to succeed. After all, successful projects are profitable projects with happy customers. When we start a BI project with a new customer, we can never be 100 percent certain it will succeed. But, over the years, we’ve identified five indicators that a business intelligence project will fail. Here they are:

1. You Don’t Have a Strategic Reason for Doing the BI Project

Let’s talk about what I mean by “strategic reason.” A strategic reason isn’t what you wrote on a form to get management approval. It’s not something where you calculated (to four decimal points) that Project X will save Dollars Y and therefore provide Payback Z over many months. A strategic reason isn’t that your project will make life easier for a few folks in the back office.

When the reason for a project is strategic, senior management will INSIST that it gets done—even if it means bringing in other people to do it. A strategic reason doesn’t just cover the costs the project but will provide a payback or benefit multiple times the investment.

Recently, I wrote a post on business intelligence ROI where I discussed the strategic reasons behind several real world business intelligence projects. In each case, these projects HAD to be completed. As a result, when obstacles arose (whether technical or political), we could escalate and get the job done.

I’m not saying you can’t get a few reports written or improve a few processes without a strategic reason. You can. But any larger project will die a slow death without it.

2. You Confuse What You Need With What You Want

We need some things. We want much more:

  • I need to make sure my current customers pay on time.
  • I want to have 20 new customers in the next year.

Business intelligence is no different:

  • You need to get your reports done on time.
  • You need them to tie out.
  • You need to make your investors happy.
  • You want every report to be just so.
  • You want dashboards and cool stuff.

Even if you have a strategic reason for a BI project, you can still get trapped in “wouldn’t it be cool?” or “could you just add?” Which means your business intelligence projects never get done because there’s always one more thing.

Focus on needs and you’ll get results.

3. You Won’t Spend the Time to Review Your Own Data

I tell customers it’s our job to ensure your data is consistent. It’s your job to make sure it’s right.

Over time, with some customers, we do develop a basic intuition about what are “reasonable” numbers in their universe. And we try to only present things we’ve reviewed for basic intelligence (if at all possible). However, we’ll never know your business as well as you.

Further, data always changes. Going through the data once is necessary but not sufficient. If you have multiple ERPs with multiple customer lists, for example, you have to commit to keeping them mapped if you want good data.

4. You Don’t Create Business Processes to Ensure Good Data

Eternal vigilance is the price of good data

—Jim Jefferson (IT manager and Thomas Jefferson descendant)

Seriously, even if you do everything right to get your project going, it can still fail over time if people don’t maintain the processes and procedures necessary to keep data clean. Do you allow people to create new entries in the chart of accounts without review? Do you review coding in your various systems—and retrain folks who make mistakes?

5. You Have Bad Source Data

This is last on the list for a reason. For most of our clients, bad source data isn’t the main issue.

Of course, that’s not always the case. We’ve worked with our share of old stuff, including IBM System/36 with internally described files and Access databases “designed” by folks with no real understanding of what a relational database is (see post, The Pivot Table Gateway Drug). It can get ugly. And it’s extremely difficult to estimate how long it will take to get these projects done.

In contrast, the vast majority of our clients installed their systems in the last 15-20 years—so most are using relational databases. While they’re not perfect, they pretty much work. (The systems may not talk to each other. And their staff may not enter data correctly. But that’s another story.)

You’ll note that bad technology isn’t on the list. Maybe that’s because we use Microsoft Tools. It isn’t the fanciest solution, but it works over and over again. But even when we’ve used other (from Business Objects to QlikView), technology hasn’t been the issue.

Is your BI project doomed to fail? Let me know if you’ve experienced any of these business intelligence problems.

 

How Much Does Business Intelligence Cost?

As a small business owner, I know the importance of understanding costs before starting projects. For example, you may have the best marketing system since sliced bread, but I’m still not going to spend $15K in one go. The risk is too high.

My clients, on the other hand, are all much larger than my company. They generally range between $50 million and $500 million in revenue. (Although we’ve done a lot of departmental level work for multi-billion dollar companies—and their finance folks.) But even they want to know what a business intelligence project is going to cost before they invest. (Finance executives tend to be risk averse. They hope for a return, but mostly they want to know the size of the hit if it doesn’t work out).

There are too many parts to business intelligence to cost them out in detail in one post. But based on the type of work we do at Red Three, I’ve put together basic estimates.

Our Business Intelligence Consulting Rates

Our rates range from $150 to $275 per hour. So, for budget purposes, a rate of $1,500 per day is a good number to work with. Those rates, combined other estimates, make us either frighteningly expensive or the best deal ever.

For small companies, $20,000 for a bunch of reports can induce an attack of angina. We understand that. And we suggest you look for a good contractor or stick to manual processes.

For mid-sized companies, we’re a great deal. Few of our competitors with similar pricing have our accounting and business intelligence expertise. And many competitors price at much higher rates than us. For example, one of our clients found that our estimates (which we considered generous) where less than 10% of what a big consulting firm had charged them for a similar project.

While there are many different kinds of business intelligence projects, I’m going to focus this post on estimating costs of three particular types of work which represent a large part of what we do:

  1. Cost of SSRS Reporting (i.e. writing reports over existing data)
  2. Cost of building a data mart
  3. Cost of building a data warehouse.

Note: All our cost estimates below are for consulting time only. We don’t include hardware and software costs for two reasons:

  1. We use Microsoft stack—including Excel, SSRS (SQL Server Reporting Services) and other SQL Server related tools. If you’re like most clients, you already own these technologies.
  2. If you do have to invest in additional software, it typically represents less than 10% of any project expense.

Another note: We don’t estimate data validation costs because they vary widely with the quality and size of the data.

You can certainly ask us to review your data. And we always do our best to make sure the numbers are consistent. But only you know your data. And your people need to have the time to make sure things are working as expected.

1. Cost of SSRS Reporting—i.e. Writing Reports Over Existing Data

We do a lot of this kind of work. As you’d expect, reports vary from simple vendor listings (for which you probably wouldn’t hire us) to complex project profitability statements (including current project AP and AR status). Over that range, reports can take anywhere from one day to five days to complete, with the average taking about two days (or $3,000 of work). Ten reports take 20 days or $30,000 of work.

You might think you could find a report developer for a lower rate, and you probably could. But the reports we work on usually require sophisticated knowledge of SQL. We’re not just making pretty invoices. So, our developers need to not only understand SSRS, but also database structures, and, critically, the ins and outs of accounting and other ERP data.

There’s one more factor to consider. You might have a high RQ ratio (multiple reports covering basically the same set of data). This means everyone in your company—despite your best efforts—wants (and gets) a slightly different version of the same report. As an analogy, if they were all getting ice cream, they’d all get vanilla—but one person wants chocolate sprinkles, another wants M&Ms on top and a third wants only blue M&Ms. In a high RQ ratio situation, we can sometimes group similar reports into different sets. Then, the initial report might take four days but each additional version will only take one.

So, let’s say that instead of 10 individual reports, you have two groups of five:

2 base reports x 3 days each = 6 days
8 version x 1 day each = 8 days
Total = 14 days or $21,000

Note: We never estimate assuming high RQ ratios until we’ve actually seen the reports.

So for ten reports, you can expect costs between $21,000 and $30,000, depending on report differences and similarities.

Another note:  We write reports in SSRS. But these estimates also held true for Crystal and other reporting tools we used to use.

2. Cost of Building a Data Mart

We recently wrote a post on how much a data mart costs, which we suggest you look at. As a reminder, the purpose of a data mart is to make reporting and analysis easier. The database developer selects, merges and calculates various data so that report writers and business analysts can get to work asking questions and finding answers.

Generally, our time estimate for creating a data mart for one subject area (e.g. financial reporting or project profitability) is between eight and 15 days. The variance will depend on several factors:

  1. How organized the data is in the source system. Again, we’re not integrating multiple systems in a data mart project. The data’s already in one place. But it can still vary greatly in how well it works.
  2. How many dimensions the data mart needs. If your financial reporting data mart is limited to account and business unit, you probably won’t have performance issues. But as we add more dimensions, we have to spend more time optimizing the data set. Data marts are made to optimize reporting. If you end up putting almost every field from your original system into your data mart, you need to rethink your strategy.
  3. The size of the data set. This is pretty obvious—a larger sets means longer load times and more processing issues.

In any case, you can still apply our $1,500 rate to the 8-15 day estimate and plan on $12,000 to $22,5000 for a basic data mart. In practice, most data mart projects are combined with reporting projects. And once a data mart is built, reports are more likely to take one day (not two days) to write.

3. Cost of Building a Data Warehouse

When we use the term data warehouse, we mean a data warehouse over a particular area of your companies data, such as financials, projects or sales (or perhaps all three—which we’ve done before). Once upon a time, people thought of a data warehouse as combining ALL data in one place. We’ve never been involved in such a project (and haven’t heard of too much success in that area either.)

When estimating the cost of building a data warehouse, you need to consider three things:

  1. The nature of the data repository
  2. Time needed to extract, transform and load the data (i.e. getting data into the repository from other systems)
  3. Time needed to create reports over the data.

The Data Repository

Again, we begin with the caveat that most of our work is based in financials, and these estimates are based on financial data—from journal entries to sales to product/ customer/ project profitability. (HR data has a different set of challenges. We’ve worked with HR data, but it’s no longer our focus.)

A data repository is a single place where all your data sits nicely—central and well organized. But it may not be optimized for reporting yet. (That’s the job of the data mart.)

Sometimes, you can use your general ledger as a kind of poor mans’ data warehouse, but let’s say we really do need to build a new repository. We generally allocated 6-10 days for this process or $9,000-$15,000. This includes basic design as well as specifications for loading data into the system

Extract, Transform, Load (ETL)

The reason for building a data warehouse is that your data is scattered across many different systems in your company. You need to get it out of those systems (extract), manipulate it to a standard format (transform) and place it in your data warehouse (load). The size of the job will depend on a number of factors:

  1. How many systems need to be linked?
  2. How many kinds of data need to be extracted from each system?
  3. How many mappings need to be built?
  4. How long will the final transactional transformation take?

Let’s take an example: We want to create a repository of all our financial data. We have three accounting systems around the world, in the U.S., Canada and Sweden. We want to track data at the account/customer/project level.

Here’s a graphic to help illustrate this example:

And here’s what a basic estimate would look like:

A few notes on the above chart:

  1. Extracting data from each system is pretty much a repeating cost. Each system has its own peculiarities so there’s no economy of scale.
  2. We increased the time needed to extract Sweden’s data because the data is in Swedish and needs to be translated.
  3. Mapping does get cheaper with scale. If you create a way to map Esso Canada to Exxon U.S., you can use the same methodology to map Esso Sweden to Exxon U.S.
  4. Transactions always take the most amount of time. The dollars have to be exactly right.
  5. These estimates including a fair amount of testing support. However, your users MUST be available to map and check data.
  6. Data can come from any system, but we do all mapping and transforming with Microsoft tools. We do most of our work in T-SQL, but sometimes we use SQL Server Master Data Services (SSMDS) and/or SQL Server Integration Services (SSIS).

Pulling it all together, you can see that integrating one system into a data warehouse will take between 15 and 21 days or $22,500 and $31,500. You then have to consider reporting and/or data mart costs, which we’ve already covered, above.

So these are the ballpark numbers we provide to clients and prospects when they ask us, “How much does business intelligence cost?” If you’d like to get a customized estimate for any of these services, feel free to contact us.

 

Five Things to Get Your Finance Team for the Holidays

In my previous post, I provided a list of gifts that Finance should give their application developers for the holidays. But, giving should be mutual. So now, as promised, a holiday gift list for Finance from application developers:

1. Accounting Training for Yourself

Yes, we know you like software. You don’t want to be an accountant. But learning even the basics of accounting will buy you SO much credibility with the finance staff. As business intelligence consultants, I can tell you that they’ll be shocked and delighted to find a techie who has some understanding of an income statement or balance sheet and knows the difference between the two. There are many accounting for non-accountants classes out there. It’ll only take you a couple of days. But it will pay dividends for years.

2. A Promise to Reconcile Your Output Before Passing It Off

Large organizations have dedicated software testers. But in the mid-size world where we operate as business intelligence consultants, that’s often not the case. I’m going to assume you’ve progressed beyond the “if it compiles, it must work” school of thought. But even so, I’m asking that with every program you write, you ask yourself, “How do I know the output is correct?” Compare to another system. Compare to a stock report. Compare to a backup spreadsheet (even if you have to build it).

Final signoff is still the business’s responsibility. But it’s still your responsibility to check that the output is correct before passing it off.

3. A Straight Face

Finance users say some pretty silly things. They grab on to technical language they don’t really understand. So, you get lots of requests that start with, “Can you just…?” As in, “Can you just write a query?” “Can you just write a report?” “Can you just build a quick fix?”

As an application developer, you know these requests are the equivalent of “Can you just write a new system?” or “Can you just wave a wand?”

But in the spirit of the holidays, gently guide your users to understand just how much work is involved. Laughing at them (either to their faces or back in your department) isn’t helpful. And the same principles apply when you ask Finance “Can’t we just tell the auditors to come back tomorrow?”

4. A Promise to Invest in Technologies that Help the Business, Not Just Your Resume

In most companies, software applications can be divided into four categories:

  • Category 1: Software that doesn’t work well and needs to be replaced
  • Category 2: Software that works well but needs to be cleaned up
  • Category 3: Software that works okay but isn’t cool
  • Category 4: Software that works just okay but is really cool.

Today, everyone working in corporate America is thinking about his/her next position. It’s the reality of the economy. And developers want to stay current to optimize their job prospects. But at the same time, they work for a business. So, they need to make sure they don’t confuse categories 1 and 2 (i.e. software that needs to be replaced versus software that needs to be cleaned up). And they should always aim to get stuff that works, not stuff that’s really cool.

5. Anything But Sci-Fi Paraphernalia

Sorry, but Finance types just aren’t that hip. They’d like to be. They’d like to hang out the cool kids in sales and product development. But having sci-fi paraphernalia on display isn’t going to help their cause. So leave it alone.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult