We have recently completed a short project for a non-profit client implementing Business Intelligence (BI) system using the Microsoft BI stack. We thought we'd share our experience and share some key learnings from delivering the BI project.
Just to provide a brief background for the project, our client uses Microsoft products in its operations including Windows Servers, SQL Server Databases, MS Office and SharePoint 2013. The requirements at a high level for the BI project were to implement a SQL Server Analysis Services cube that would enable the automation of creating the quarterly report pack, comprising of a detailed report of the organisation key performance measures for the funding organisation. It generally took the in house business analysts (BA) 2 to 3 weeks of effort to compile the data from multiple sources and prepare the pack. A lot of manual effort was involved.
Another requirement was that the BI System would be used to answer ad hoc queries from different departments within the business that normally required manual effort on part of the BA to extract the data from the relevant systems and then analyse and manipulate to produce a report to answer the queries. Sources of data include internal databases (non Microsoft) and external sources providing a range of formats to import in to the BI system include text files, MS Excel, relational databases, MS Access.
Our client had attempted to create a BI solution. They had created a number SQL Server Integration Service Packages and a star schema based SQL Server 2012 data warehouse database. To produce the quarterly reports pack. However, the process was still manual, essentially running the packages manually against the data sources to extract and transform the data into the required format and load it into the data warehouse. The end output was the production of data, manipulated to meet the requirements to enable creation of the report pack, in an Excel spreadsheet exported out of the data warehouse database and handed to the BA to use to prepare the report pack.
Zebra Crossing Ltd (ZC) were initially engaged to review the existing system against best practice and to provide any further recommendations. An audit was carried out by ZC and recommendations to optimise to the package and databases were made. We were then engaged to enhance the manual system into an Enterprise grade BI Solution.
As part of the BI Solution, ZC reviewed existing assets for reuse against the new requirements as well as the existing infrastructure and software to leverage these assets and investment to create and deliver a BI Solution within the clients limited budget and timeframes. The client was also undertaking major IT changes in parallel, including moving their infrastructure into the cloud with Microsoft's Online offering around CRM, Office 365 and Azure features, which had an impact on the solution design. This involved looking at Power BI, Microsoft's cloud based BI solution as part of the BI solution to be moved into the cloud eventually.
ZC worked within this fluid backdrop to leverage and reuse the assets and infrastructure to create a cost effective BI Solution using the Microsoft SQL Server and BI platform stack given the organisation's investments that already existed. The solution was created using:
- SQL Server 2012 (BI Edition), including BI capabilities:
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services (multidimensional cube) (SSAS)
- SQL Server Reporting Services (SSRS)
- SharePoint 2013 (BI Centre and BI capabilities)
- Microsoft Excel
- Report Builder
For development of the solution, we used free tools:
- Visual Studio Online
- SQL Server Data Tools 2012 & Visual Studio Integrated Shell (SSDT)
- SQL Server Data Tool - BI (SSDT-BI)
ZC also catered for the potential to move the solution into the cloud. Some key highlights of the value we added were by:
- Introducing a software development process to create and maintain the solution given our software development experience.
- The source code was put under version control using Microsoft cloud based Visual Studio Online which is free for 5 users.
- The existing SSIS package were reviewed and enhanced to be deployable as a complete unit to any environment, using the project deployment model.
- The SSIS packages were enhanced to be automatically configurable based on the target environment (Development, Test or Production) via use of variables, package/project parameters and Visual Studio build profiles.
- They were also reconfigured to enable multiple developers to work with the component, removing restrictions that hampered this from occurring.
- Designing the BI solution components to be deployable as packages i.e. SSIS .ispac package , SQL Server .dacpac , SSAS .asdatabase, SSRS .rdl, enabling the separation of developer and support staff access to the various environments and ability to create installation packages that can be versioned and archived for audit and maintenance.
- Controlling costs by using free development tools SQL Server Data Tools with Visual Studio Integrated Shell and SQL Server Data Tool for BI. These contain the Visual Studio IDE (integrated Shell) and project templates to create database, SSIS, SSAS and SSRS components.
- Reviewing and enhancing the data warehouse to support the project requirements and optimising it for the SSAS model, including technical enhancements following best practice guidelines in establishing a solid data warehouse database.
- We used the Kimball bottom up approach which is best suited to the project situation.
- Creating the semantic model using the SSAS Multidimensional cube approach to support the Ad Hoc reporting process. This used the data warehouse as the data source.
- As part of the technical design process we assessed the Tabular model and Power BI and chose the most cost effective and flexible solution. We went with the multidimensional cube option as it had the most mature capabilities and was the best fit for our client's needs and constraints at the time.
- Also, with the ability for Power BI to connect to a multidimensional cube made it simple to tick the box for moving the solution into the cloud in future.
- Analysing and identifying the enhancements needed to update the data warehouse and SSIS packages to support the creation the quarterly reporting pack to in an automated system.
- Automatically scheduled SSIS ETL process via SQL Agent to load and process source data into the data warehouse and SSAS cube from source. Data source were from internal live systems as well as external systems with varied frequency of availability. The ETL process was designed around these constraints.
- Implementing appropriate security across all layers to protect the data and processes and control access to users from IT support as well as general business users including use of Window AD groups, service accounts and SharePoint Secure Store.
- Creating detailed documentation of the solution, including a comprehensive and tested guide to Setup the SharePoint BI Centre and deployment guides which is a non-trivial process by itself due to the number of BI parts and dependencies. This included installation and configuration of Excel Services, SSRS Integration, PowerPivot, PerformancePoint and Secure Store.
- Recommendation of best fit reporting tools to use, geared to the users' skill levels with technology. There is a myriad of client tools which can be confusing to make sense of. Choosing the correct tool and understanding this up front is important . For our client, this included MS Excel for Ad Hoc Queries, SSRS for the quarterly reports (Report Builder) and SharePoint PerformancePoint for dashboard and score cards.
- Integration of all BI capabilities into SharePoint 2013 BI Centre to create, secure, share and collaborate across the organisation.
- As part of the process, we discovered areas where source systems could be improved to improve data quality and highlighted this with our client.
The project process highlighted the following important points:
- Data is the lifeblood and requires the appropriate due diligence for a BI system to be successful, feasible to implement, useful to users and reliable. The quality of data affects implementation of the SSAS Model and what can be leveraged.
- A BI project, as usual, will place a glaring spotlight on data quality and quality of source systems ability to capture valid data and can take time to resolve. Sometimes, as in our case, it's not possible to fix data due to cost, effort, access to source system etc. and so a decision needs to be made as to whether the data you have is good enough for it's intended BI purpose or whether a data quality project needs to be undertaken.
- The data warehouse needs to be solid and well designed and thought through. To achieve this, the reports and answers users seek must be validated and designed up front. This will impact direct on the data ware house schema and design. The requirements around the ad-hoc queries made this challenging as these could be anything! A such the BI system needed to geared to handle this.
- Test cases and sample data needs to be available to validate the information being produced by the system. Support is required from the Business, in our case the in-house BAs were the subject matter experts that assisted with testing and validating the end outputs from the BI System.
- End uses and users must be kept in mind so that the most appropriate design, setup and reporting tools can be used. They could be of varying skill levels and have varying level of familiarity with the data. Thought is needed around how best to categorise and meet the BI needs of these users.
- Building in the capability for the system to grow with the organisation as it matures in this space must be considered.
In the end, a BI system is used to make decisions that affect the operation and success of a business. It is imperative that the design, build and test process to create and maintain the system is trusted, secure and reliable to enable this. Following best practices, using a mature toolset such as Microsoft's BI tools, backed by a disciplined development process that is flexible and can grow as an organisation matures in its BI capabilities is highly recommended towards attaining this goal.
A BI system is an investment that evolves and grows over time and needs to be implemented and maintained as such, rather than thought of as a one off project.