Software Development Standards Enhance Your Data Warehouse ROI
March 13, 2004
Architecture Standards | Athena Solution |
May 13, 2004
Software Development Standards Enhance Your Data Warehouse ROI
March 13, 2004
Architecture Standards | Athena Solution |
May 13, 2004

Technology and Product Standards Keep Us On Track

published in Information Management, TechnologyToo 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)
Business intelligence (BI)
Data Integration
Data Governance
Master data manegment (MDM)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.

Comments are closed.