Building Effective Business Intelligence Processes :: SQL Server 2008

Building effective Business Intelligence process is not quick job to achieve and how I see that is referred to as the softer side of business intelligence (BI) project implementations aka the business processes and staffing issues at the heart of any BI solution.

If you go into market for a BI product  then there are many available having each of their own features that can classify as an effective solution. But most of the BI projects are complex, involving many people, numerous processes, and a lot of data! So data is more important before you design the solution and not an easy option to ignore the potential process and people challenges inherent in BI projects is to risk jeopardizing your entire project. Here comes the best practices and few of the lessons that I have learned within my past BI projects, such in dealing with the process and people issues that inevitably crop up in BI projects. Microsoft has invested good time and resources to get such an handful resource from their website having the real-world experience implementing BI projects and involved many organisation as a part of BI solution which have found that using known and proven processes as you envision, plan, build, stabilize, and deploy your projects reduces their complexity and lessens your overall risk.

Whenever I start a BI based project then it is a practice I follow in following the standard software development life cycle for some of the formal models such as Microsoft Solutions Framework (MSF) and you can refer to Download details: MSF for Agile Software Development Process Guidance  page. The template I follow from this MSF Process Template for Agile Software ... is very useful and involve an effective project team: the skills various team members.

So BI project means you would be involved in building one or more back-end data stores (OLAP cubes, data mining models, or both). These cubes and mining models often have to be designed from scratch, which can be especially difficult when these types of data stores are new to the developers and to the enterprise. Next, appropriate user interfaces must be selected, configured, and sometimes developed. In addition to that, concerns about access levels (security in general), auditing, performance, scalability, and availability further complicate implementation. Here is the reference from MS Technet resource on the scenario-driven (or use-case driven) and context-based approaches of MSF Agile are particularly suited to BI projects for three reasons:

• Because of the scoping challenges we mentioned in the preceding section

• Because of the need to provide the context-specific (or vertical-specific) approach for the stakehollers so that they can see what BI can do for their particular type of business

• Because of their inherent agility

The guidance that I obtain from Microsoft documentation and resources is phenominal, as it helps a lot at the envisioning phase. I had an interesting discussion and agreement from an organisation plan to involve data mining process in every project though it may not be directly related in usual BI project. It always helps to note every aspect of data related activities on the DESIGN phase. Don't forget to include all source data and plan for some type of access to some subset of data for everyone in the company. It is good that BI toolset within SQL Server 2008 version was at best designed to support this idea of BI for everyone. Just to refer from Microsoft's site on what kind of BI solutions are offered:

• Create high-performance Analysis Services solutions with optimized cube designers, subspace computation, and MOLAP-enabled writeback capabilities
• Implement enterprise-scale Reporting Services solutions through new on-demand processing and instance-based rendering
• Build flexible and effective reports with the new Tablix data structure and rich formatting capabilities
• Expand reach, and empower more users through optimized integration with the 2007 Microsoft Office system

Now you should consider on time and resources, taking into account of all user group types and initiation of discussions with all the data users within the organisation. Not only that your project should involve the discovery step is to find and document the locations of all possible source data, including relational data stores such as Microsoft SQL Server, Oracle, and DB2; nonrelational data such as .CSV files from mainframes, XML, Microsoft Office Excel, Microsoft Access; and so on. Make sure you refer to every IT user to hunt the data within their local desktop/laptop such as My Documents folder, a common practice to save a file than to a network resource. NOW it comes the prioritize of business problems that will always affect the project from design level.

Finally the planning phase comes, that will accumulate the solution to the problems having a detailed design plan in hand. It may not as is possible and appropriate for your particular BI project, don't forget to think about development and test environments. As an Solutions Architect I stress on using best modelling tools and documentation methods within the project level. Why it is important, selecting a modelling tool helps to model the OLAP cubes and mining models. I tend to get to meet Architecture professionals that are perfect in traditional data modeling tool, such as Visio or ERwin for this process and I always debate about using SQL Server Analysis Services too, or you can use SSAS itself, funny that I get to meet agitated feedback and I refer this BI Webcast event series.

Whenever I talk about documentation, most of the users feel like a trouble-some job. I would say rather not, as documenting the existing and new processes will be advantage to build a data dictionary, in other words your own notes and definitions. You don't need big or flashy tools, MS Word or Excel is very handy here or whichever tool you are comfortable by specifiying the usual language of the business. You should capture an information by conducting interviews with representatives from the various role groups—that is, executives, analysts, and so on. Define the data source structure names which includes translating database table and column names into a common analogy. Don't forget to include any process of translating unstructured (beyond relational) metadata such as XML element or attribute names.

Ok, I'm not yet finished and I don't want to complete this mammoth task of building effective BI solution in 1 post. There is much more to share my experiences and knowledge, which I'm enjoying at the every level of accomplishment, keep watching this space!

.