The first aspect in establishing a data warehouse, and the reason to practically have a BI initiative in the first place, is the analysis of business requirements for analytics capabilities. The first challenge is in the prioritization of the strategic analytical requirements, which usually provides the highest return on investment over operational reporting requirements, typically providing a lower ROI. Key factors to consider that impact the overall effort and scope of the project include: assessing organizational readiness, effective communication of upcoming changes to all affected, managing scope of users changing their own initial requirements and breaking apart the project into manageable phases with easily attainable milestones.

Mapping key business requirements to benefits achieved by BI is where key metrics to be improved upon need to be determined. Beneficial changes to KPIs such as increased revenue, improved profit margins and reduced costs need to be considered in the data infrastructure requirements. A risk mitigation strategy should be built into the overall equation.

Business value can be achieved by addressing those high value, high priority BI requirements and translating them into a sound data design and/or model.

A proven and sound data warehouse development methodology combined with a collaborative approach with the goal of giving ownership of the BI application to the business people has proven to be most successful. By building a more self-service oriented data architecture, the user community becomes incrementally empowered and productivity rises.

Implementing the back-end ETL process is usually done using an ETL tool such as SQL Server Integration Services (SSIS). Depending on the condition of your source files (integrity, location, history), the complexity of the required transformations and the functional limitations of the ETL tool, you may have to write some custom code to enrich the ETL tool functionality.

Standard procedures, guidelines, and conventions must be in place during the BI modeling phase. Establishing modeling policies is crucial for the DW and BI components and the relationships among them to function properly. The BI model gives way to the business applications which are the business processes that access data from the data warehouse through reports, ad-hoc queries, business dashboards, and so on. The latter allow business execs to act on the information to effect change in the company.

Ongoing management of the interdependent components of the BI initiative should overlap all phases of the data warehouse project to derive the highest business value. Often, companies are internally working on multiple projects in parallel, making it a challenge to manage the DW/BI project while respecting timelines, milestones, and realigning requirements with changing business needs. Until you know what the data warehouse effort will be, you cannot create a project plan and commit to any deadline.

After the new DW/BI project is implemented in production, the project team performs a post-implementation review. Lessons are learned from every project and there is usually opportunity to streamline the development approach by practicing more process re-use, avoiding redundant tasks, improving communication, improving estimation methods, identifying gaps in infrastructure, and defining critical success factors for the next project.