For any Enterprise Data Warehouse (EDW) development for enterprise wide business intelligence applications implementation considerations and guidelines has to be followed. Performance, scalability, extensibility and re-usability of code written during the development phase are critical factors towards success. The intended audience is BI developer community to adhere to the standard best practices as blueprint for any BI development activities conforming to business requirement.
1 Capacity Planning
The following mentioned below are the two important constituents that cannot be ignored while implementing any BI solution. Organization does not realize exact BI definition which is vast indeed. Data warehousing is only one of the major constituent of any BI solution.
Storage is one of the most important considerations that has to be analyzed and predicted for future expansion of any EDW (Enterprise Data Warehouse). If capacity planning is not done properly, you may end up with lot of performance issues and this may be critical to the business as a whole. Normally, DW applications are subsidiary applications as compared to LOB (Line of Business) applications but can be one of the most effective applications in the organization ensuring growth and prosperity. Generally DW applications fall in the category of extended SLAs but not always true. A few organizations have end-to-end DW implementation which acts as catalyst to their Marketing and Sales Department. Although the maturity curve is long yet you can make effective decisions based on trends and predictions.
As the company grows through continuous merger and acquisition it is important to expand the infrastructure which consists of servers, networking components, bandwidth and above all configurations. This directly affects any EDW implementation. Although you can compromise with hardware in development and test environment yet it has to be sophisticated as far as UAT and production environment is concerned. It has been noticed small organizations does not do investments on infrastructure for data warehousing since it is secondary support and no quick ROI is assured. Rather they would invest in routine operational activities or on-line transaction processing system like ERP applications for the organization. It becomes tough to balance and justify the investment made since results are not short term. The EDW maturity curve is too long and there is a phenomenal gestation period. Please refer to an excellent blog by James Serra.
2 Data Retention Policy
Every organization must have a well thought and pre-defined data retention policy. This drives the organization for maintaining historical data. There is always a tradeoff but it is vital for the growth and impacts revenue. If you have to analyze where the company is heading and what are the challenges you have to devise a full proof data retention policy. It is expensive when storage (SAN, NAS or DAS) is concerned but archiving is a good practice for any organization for diagnosing profit maximization and customer loyalty which should be the focus. I feel these are the critical factor for growth of the organization.
3 Defining SLAs
Defining the SLA (Service Level Agreement) is also a vital parameter for service operation excellence. Based on the SLA for the mission critical/critical or extended applications you can entrust the service operation team (support team) to adhere to availability and up-time of the applications. In case of any incidents minor or major has to be rectified and deployed as per the clauses in the agreement. This would involve the coordination of development, test (QA) and support teams. Impeccable Change and Release Management is need of the hour.
4 Extract Transform Load (ETL)
Data consolidation (Data Acquisition or Data Integration) are synonymous words. In an organization it is important to integrate all the business functions and EDW is the only medium for integrating different business functions. Although it cannot be done all at one shot yet there is a possibility for integrating each subject area in a phase wise approach. This process is also known as on-boarding of applications. Agile methodology and phase-in phase-out approach is ideal for any organization either big or small. The final objective is to have “Single version of truth” which is valuable to the organization towards growth and prosperity.
There are variety of proprietary ETL tools (Informatica, Data Stage, BODI, SSIS etc. ) available in the market but it is expensive as compared to bundled product. SQL Server Integration Services is an excellent bundled product which comes with SQL Server 2008 R2/2012 enterprise edition. I am not biased against any tools but as far as Microsoft is concerned, TCO (Total Cost of Ownership) is the tagline and it has evolved as major leader in BI and data warehousing space (Gartner Quadrant).
5 Choice of tools
Please refer to my blog in WinWire Technologies Inc. company website for in-depth explanation.
6 Tracking and Monitoring
This is post-implementation support function also called as sustenance engineering. Data warehousing applications need constant monitoring and tracking. This would ensure timely accurate data in timely manner. There is lot of tools available in the market. Please refer the link for more details.
7 Disaster Recovery
Any company would definitely implement disaster recovery, which is mandatory for mission critical applications. Since data warehouse falls under extended SLA category application, many companies ignore and do not invest much in disaster recovery cross geography. In my opinion, data warehousing is the most critical function for any company’s growth and prosperity. You will have assured positive ROI in long term when you always bank on profitability maximization and customer loyalty.