It’s no secret that the US and global economies are facing difficult times. If the economic pundits are correct, we are now working through the most challenging economic decline of most of our lifetimes.
Many of your organizations have already made significant reductions in staffing and spending. The data warehouse/ business intelligence (DW/BI) sector seems to be faring somewhat better than others. In bad times as in good times, organizations desire better visibility into their business and improved decision-making capabilities. Even so, many DW/BI teams are facing staff reductions or at least hiring freezes coupled with significant reductions in budgets for new hardware, software, training and consulting. Perhaps your organization had grander plans, but now you find those plans on hold.
No astute DW/BI manager would be so short-sighted as to ignore these realities in the face of today’s economic environment. But rather than just putting current activities on a budgetary crash diet, it’s time to institute a proactive fitness program that will yield a leaner, more efficient and effective DW/BI program for the long haul. How can you do more in an environment where staffing and funding are constrained? Focus on three key opportunities:
- Cost savings
- Cost avoidance
- Growth
Cut the Flab
In tough times, the obvious place most organizations start to cut is in identifying opportunities for cost savings. This often means significant reductions in budgets as well as possible staff reductions. We’ll assume you are already running very lean; your budgets and staffing have already been evaluated and trimmed. After these obvious cuts, you’ll want to look for other avenues to achieve cost savings.
Revisit the original justification for your DW/BI effort; often a number of cost-savings goals were identified during the project justification effort. Have these goals been realized? If not, why not? Are the savings still available? If so, look for inexpensive methods that would help you achieve these goals.
Often the most significant cost savings assigned to a DW/BI effort are related to sunsetting older analytic environments, resulting in significant savings on hardware, software licenses, maintenance and support resources. Try to determine why these savings have yet to be realized. We can often point the finger of blame at office politics. The new DW/BI environment may be well positioned to support the requirements of the existing analytic environment, yet a group of business users lacks the motivation to make the move. A mandate is required to force these stragglers to migrate to the new environment and the current economic environment may provide just the incentive you need.
It’s easy to just blame politics, but you need to talk to the holdout business users to understand their perspectives. The new solution may be almost everything these users need, but some vital component might still be missing. It could be very easy to solve the issue, perhaps with user training, a little applet or macro, modifications to reports, or possibly a minor change in the data or model.
Even in the best of times we want to sunset old applications, not only because of the operating costs but also because we want a single version of the truth. In these worst of times, we should be able to entice or force the stragglers to migrate away from the legacy environment to the new DW/BI solution and finally achieve the cost savings initially envisioned.
Monitoring and Tuning for Performance
Organizations are also looking to defer planned costs. Perhaps in your surviving budget you have financial resources available to invest in additional hardware or software. Often, with a bit of creative thinking and hard work, these costs can be deferred or in some cases avoided altogether.
Inventory the software licenses you already have in place. Are they all being effectively used? Pay special attention to your BI tool licenses and how they are deployed. Many organizations find that the actual number of licenses in use is far below the number that has been deployed.
- First, understand why the deployed licenses aren’t being utilized. Perhaps there are low cost training or support investments that will help users become more effective, leverage the existing licenses and enable your organization to achieve the anticipated benefits.
- Second, if the licenses are not required where they were deployed, they should be shifted to other users. This realignment of licenses may allow you to defer a planned investment.
- Worst case, if there is no current or future need for the licenses the maintenance can be discontinued and some cost savings achieved.
Similarly, cast a critical eye over the overall performance of your DW/BI environment. Often an investment in performance tuning can push a planned hardware upgrade a number of months into the future. Query performance is highly visible, so start there:
- If you’re not already doing so, start to develop a log of system performance characteristics. Your database systems and operating systems have features that enable you to capture a history of memory, disk, CPU usage, and other characteristics. You cannot tune performance if you don’t understand performance.
- Tune the SQL for all standard reports and other BI applications. This is the lowest-hanging fruit because you can usually modify the query syntax, such as adding a hint, without changing the report’s fundamental design.
- Analyze the queries run in your environment looking for opportunities to improve your indexing and aggregation strategies. Tuning for ad hoc use continues to be something of an art form; you need to balance the value of an additional index against the cost of maintaining that index.
- Work with your hardware/software vendors to ensure that you are leveraging the capabilities already available to you. Ask your vendors to provide known DW/BI best practices and tips.
After you’ve done what you can to squeeze every ounce of performance from the existing environment, consider whether moderate changes to your system design might provide substantial performance benefits. Your fact tables and their associated indexes and aggregates consume the most resources in your DW/BI environment. You may be surprised by how much query performance can be improved by reducing fact table and index size. Review your fact table designs and consider the following:
- Replace all natural foreign keys with the smallest integer (surrogate) keys possible.
- Replace all date/time stamps in the fact table with integer surrogate keys
- Combine correlated dimensions into single dimensions where possible
- Group tiny, low-cardinality dimensions together, even if uncorrelated
- Take all text fields out of the fact table and make them dimensions, especially comment fields. It seems counter-intuitive to make a dimension out of a high-cardinality, low-value field like a comment; but the comment field is omitted from most queries. Removing the large character field from the fact table and replacing it with an integer key can make the fact table perform much more nimbly.
- Replace long-integer and floating-point facts with scaled integers wherever possible.
- If, contrary to our longstanding advice, your dimensions are normalized into snowflakes, collapse each into a denormalized, single-table star dimension table. You can often implement this as an add-on step at the end of the ETL process.
Review your ETL system. Look for performance bottlenecks and identify ways to remove them. ETL teams often overlook indexing approaches in staging tables that might improve overall ETL performance. In addition, revisit the ETL logic from the early days of your DW/BI effort. The ETL team is now more experienced than it was in the beginning. Inevitably your team has identified more efficient and effective techniques than those initially implemented.
Bulk Up the Bottom Line
Most organizations initially justify their DW/BI initiatives citing opportunities to grow the business and improve productivity. The benefits identified are real and tangible, but they’re often hard to quantify. It can be challenging to measure enhanced business results such as increased revenue growth, improved profitability, greater customer acquisition/retention rates, or improved customer satisfaction.
Now’s the time to revisit the proposed growth opportunities to evaluate whether the DW/BI environment has actually helped attain the benefits promised. If the benefits are being achieved, be sure to let the world know! There’s nothing wrong with taking credit for a job well done. Your recent budget negotiations might have gone more smoothly if you had better records of the business value resulting from the DW/BI system. Start keeping those records now.
Most of us will recognize desired benefits that have not yet been fully realized. Investigate why these benefits continue to elude you. This analysis will require the DW/BI team to cast a critical eye over activities and results. Be brutally honest with yourself. Have you done everything required to help the organization achieve these benefits?
DW/BI teams often lose sight of the ultimate goal. They get so focused on getting the data into the data warehouse they forget about making it all the way to the finish line — enabling the business users to easily use the data for decision making. Consider refocusing some of your resources on achieving those benefits that provide significant value to the organization.
Look for low-cost, incremental improvements you can make that offer substantive value to the organization:
- Make sure you have documentation and training in place to help business people to use the DW/BI system effectively.
- Look for areas where the addition of a few dimension attributes or additional metrics to existing schema will enable valuable new analyses.
- Look for opportunities to expose the existing portfolio of data and BI applications to a wider audience, perhaps tapping available software licenses identified as discussed above.
- Work with key, analytics-savvy business users to evaluate the effectiveness of BI applications that have already been deployed. Look for opportunities to improve these existing BI applications to provide keener insights into the business. Seek out ways to better understand the analytic processes utilized by the savviest users in order to capture and extend these benefits to additional BI applications.
- Enrich your data warehouse with new fact tables supporting additional business processes and new analytics.
It’s unfortunate that the downturn in the economy has put many of us on an unwelcome budgetary diet, but we can also get lean and mean by hitting the gym and working out, figuratively speaking. We can focus carefully on our DW/BI environment’s overall health and fitness and become far stronger and healthier for the long run.
