Too many companies have implemented the “software du jour” – frequently switching to different hardware; operating systems; databases; applications; programming languages; extract, transform and load (ETL) tools; and reporting and analytical tools. Granted, no large company is going to have a homogenous environment; this is justifiable, as software and vendors change, grow, merge and disappear. However, this doesn’t negate the need to be disciplined when we select data warehouse (DW) or business intelligence (BI) tools. We first need to develop an architecture, then make technology and product choices that support it.

You should develop standards in different tool categories or establish a preferred product list to increase both the ROI of your IT investments and the value of your IT organization. Too often, tools are selected on a project-by-project basis, which doesn’t work for several reasons. First, taking a project viewpoint versus an enterprise viewpoint may result in a tool that is only useful for one project. Second, by placing the burden of tool selection on each project, you’re spending time, resources and money on tool selection instead of your project. Third, restricting the products used allows IT to develop expertise with them. Finally, the most obvious benefit is lower licensing, training and support costs.

If I were a hardware or software vendor, I would steer you to my product by suggesting you make it your standard and migrate all your applications to it. When I was younger and more naive, I would have suggested that you perform product evaluations, select the best tools in each category and migrate all your applications to the selected tools. Both scenarios assume that you can select the “best” product, that you have the time, people and budget to perform product evaluations, and that your business can afford to drop everything it is doing in order to migrate all existing applications to new standards.

Realistically, you need maximum ROI from standards, but minimum disruption to your business. “If it ain’t broken, don’t fix it” is advice to follow. Your business should not spend money for technical purity unless if there is a business benefit.

When selecting products, you should first take a fast path approach. Formal product evaluations may look appealing and objective, but they’re generally political, arbitrary and easily bogged down in irrelevant details. On the other hand, tapping the experience of people who have used these tools or gaining experience through proofs of concept (POC) is a valuable approach to determine how well the products will work.

Second, consider both industrial-strength enterprise and departmental products. The data warehouse and large data marts can use the industrial-strength tool while smaller data marts, reporting and business intelligence projects can use the departmental tool. There is a trade-off between features and costs. The industrial-strength tool may be used to build your core infrastructure, but would be overkill for the department that is hitting a wall with their self-built Microsoft Access solution. The department doesn’t have the time, skills or budget for developing and maintaining solutions built with complex tools.

Finally, once standards have been selected, don’t jump into a massive migration project right away. Devise an approach by which the migration occurs as new projects develop, or when it makes sense to significantly overhaul applications.

It should be a best practice to use common technologies and products in each major DW/BI functional area – databases, ETL tools and BI tools.

Most companies have already standardized on databases such as Oracle and IBM DB2. These databases are great, but sometimes it is pragmatic to use a simpler, less expensive database for data marts. Microsoft SQL Server is often a cost- and resource-effective choice for smaller databases. This is a glass half-empty versus half-full argument. IT will often view Microsoft SQL Server as half-empty – not as robust or powerful as their enterprise solution. True, but does the business need that power for their application? The business views the choice as half-full (or maybe even overflowing) because SQL Server often replaces an existing Microsoft Access database or dozens of individual databases. An all-or-nothing choice regarding using the enterprise database may force the business to continue using a system that is not providing all the information they need but is, at least, cost-effective.

If at all possible, aim for a single ETL tool for data sourcing and transformation – it’s especially useful from control, traceability and meta data management perspectives. If you’re not able to use the same tool for both, get a second tool before resorting to hand coding.

The ETL category lends itself well to the two-tier approach to tool selection. It should be a no-brainer to use an ETL tool to create a data warehouse, but often these tools are not the best choice for creating data marts because they are expensive to purchase and use. The creation of data marts is less complicated than the creation of data warehouses, and thus may not require the most sophisticated ETL tool. The heavy-duty tasks of data sourcing, data cleansing, data reconciliation and data quality occur when the DW is being loaded. On the other hand, because data marts usually source data from the DW, the data integration requirements are fewer. The risk, however, is that if an IT group is not experienced in ETL tools, they fall back on manual coding.

BI tools can be divided into several categories: production reporting, ad hoc querying and analysis, multidimensional analysis and data mining. Many companies have purchased at least a half a dozen of these products over the years. On a project-by-project basis, tools tend to be selected based on what category best fits the current requirements. Today, the top vendors generally offer products that support all categories, so you’re not forced to select multiple vendors to support the different tool categories. It never hurts to examine and compare vendors’ product suites versus their individual tool category offerings.

There are several considerations that might influence your selections. First, if you have a significant number of business users who are actively using a BI tool from a top-tier vendor, then consider making that vendor your standard. Second, many companies are considering implementing packaged analytical applications from ERP or BI vendors. These applications often come bundled with BI tools. Although your first priority in evaluating these packages is their business value, you also need to consider the impact on your architecture and BI standards.

Finally, Microsoft Excel is the primary BI tool of business users – they know it and use it, and its functionality is always expanding. Embrace and incorporate spreadsheets in your solution. Too many projects ignore spreadsheets at their peril. A business user will only use an enterprise BI tool if it offers significantly more value than Microsoft Excel.

Next, you need to plan your architectures – data, information, technical and product. Selecting different tools for each project often leads to higher costs and less responsive solutions for your business users.