Business Intelligence Roles and Team Composition

Paul Turley,
SolidQ Mentor, SQL Server MVP

July 2014

On Business Intelligence projects of all sizes, the composition of the team is a crucial element for success. What is the organization of a functional BI team? How do the individual roles and disciplines contribute to a successful outcome?  How is team composition different for self-service and business-owned BI projects?  The best answer for an organization will depend on several factors including corporate culture, communication and management styles.

Douglas McDowell, CEO of SolidQ North America, shares this insight:

“In any project, it is easy to lose focus of the real vision and milestones to arrive at the final goal. This is so critical in BI projects because efforts can easily be deemed a success by IT measurements (good infrastructure, performance, coding, change management, etc.) but a failure to the business users. When talking about BI teams I often retell the story of the three masons chipping granite. Each mason was asked what they were doing. The first mason complained he had to hammer on this stupid rock. The second mason carefully explained he was creating a block to be used on a wall. The third mason smiled, sighed and exclaimed—with arm raised high—that he was building a cathedral.

BI is a cathedral.

And every team member plays a vital role in its strength, majesty and beauty. Or can contribute to its failure. A BI team that has a clear vision and remains focused on that vision throughout their tasks will work with synergy, enthusiasm and creativity that can never be nurtured in a scattered group of individuals, regardless of their talent and experience.

Keep your focus on the vision for BI, build your cathedral.”

We’ve spent many years in different types of organizations building and working with these teams to fit into different business cultures and project situations. Many factors contribute to the roles necessary to form a functional BI team.  Business culture is a difficult thing to categorize and profile but it’s a huge factor.  I’ve been in many interesting discussions with my peers, engagement managers and solution architects at SolidQ, trying to describe the unique environments of client projects; what worked well, what didn’t and why.  Few things bring organizational idiosyncrasies to the surface as effectively as a challenging BI project, making for an interesting observation of social behavior.

Successful BI teams are usually small, and work in short cycles; consequently, they deliver content so that business users and stakeholders can provide feedback without waiting for long development cycles. Big, formal organizations tend to have horizontal structure. As a result, teams conform to established communication styles, and status reporting structure.  In reality, excessive processes and management overhead can stymie progress; hence, a nimble BI team can be a welcome change within a staunch organization.  Comparatively, more flexible, less-structured teams work when project objectives are clear and organizational politics are minimal.

Whether the project is driven primarily by the IT group or by the business, the essential elements of a BI project are the same, although the specific focus areas of your project will affect the structure of your team.

Business Sponsorship is Critical

Having a strong business sponsor gives the project purpose and direction.  Very often, the initial driver for a new project isn’t the business leader and ultimate data consumer but an IT manager or ambitious developer – and this can be a disaster waiting to happen.  The inevitable outcome of this situation is that project mastermind puts himself in a position to try to impress the boss or to prove all the good that can come out of the project work.  After a few false starts, budget over-runs or schedule delays; everything will be perceived to be the fault of the project owner rather than whatever caused the delays.  If IT goes to the business decision makers trying to sell them on a BI project, it’s doomed from the beginning.  Don’t do it.  If you’re in this position, meet with the business leader and ask him to assume the stakeholder role.  Establish your responsibilities, project deliverables timetable, and a regular meeting schedule to report project status.  Use this relationship to build a safety net to prepare for the worst-case scenario.  Having a technical manager drive the project development can be a good experience when the business also has some skin in the game.  If some business units are on-board and others are not, let the primary business stakeholder be a champion to drive adoption.

The ideal primary stakeholder is typically an authoritive business leader; perhaps a business executive with final approval and some high-level success criteria and objectives.  He or she may have some specific requirements for the BI solution such as an executive dashboard and KPIs to be viewed on a mobile device.  Equally important, the leader may want their peers and others to have similar capabilities.  Frequently, executives often don’t have the availability to take on the business stakeholder job so, in many cases, this position may be assigned or delegated to a leader at a different level in the organization.  In any case, it’s critical that this person is fully-invested in the project and understands the importance of this role. Fernando Guerrero, Global CEO of SolidQ, talks about the importance of team leadership during the discoveries that naturally occur as projects unfold. He says;

“Implementing a BI project often surfaces internal data quality issues, which might have been produced by inappropriate internal processes, or inconsistent accounting principles, for example. This is an important source of stress between team members from the business side and the IT side. The business sponsor plays a crucial role in this case, dismantling the potential blame game that this situation might generate, and help the entire team focus on the benefits that the new system generates, leaving the current problems as something from the past.”

In many businesses, data analysts are becoming the primary consumers of BI data. The line-of-business data consumer is most likely the user in the trenches of the organization who is charged with dealing with all the loose ends like un-balanced accounts and invoices.  An almost inevitable outcome of the first project delivery will be that the users will want more capabilities.  It’s very important to establish boundaries around the initial set of features based upon well-defined business and technical requirements.

The diagram below depicts a traditional IT-driven BI project.  Note the direct relationship between the Business Project Sponsor, responsible for defining and approving the business requirements, and the BI Solution Architect, who is responsible for managing the corresponding technical requirements, leading the project team and making directional decisions about the way the solution will be architected.

Other specific IT technical assignments will vary based on the size and scale of the project but every BI project contains these roles to some degree.  In larger projects, roles may be expanded into titles like Data Warehouse Architect and Data Mart Developer.

ETL Developer
Develops the packages and database objects used to load data from source systems into staging tables and transforms data into data mart structures.

ETL development is often underestimated and can turn out to be more complex than imagined.  Data source and quality issues that are resolved at the ETL development stage will reduce the downstream work for model and report designers but they can be complex and time-consuming. Making investments to reduce data quality issues early-on can reduce bottle necks as data flows to downstream semantic models and reports. Balance this directive with an appropriate quality bar so the team can reach a “good enough” state and move on. Improved quality can be achieved through cautious iterative design.

DBA/IT DATA Owner
The organization’s DBA and operations support play a critical role and should be involved in many aspects of the BI project design.  Aside from managing servers and databases used in the solution, administrators will grant access to source systems, create and assign service accounts and help troubleshoot connectivity issues for developers, testers and users.  The DBA should have time allocated to support the project and participate in necessary meetings.

Members of this role tend to have a narrow focus on specific tasks and fulfilling requests. Subsequently, don’t expect your DBA to envision the big picture and to fill-in the gaps without specifics. Utilized effectively, they can help provide discipline, structure and necessary project support.

Model Designer
Designs and develops semantic models using technologies like SQL Server Analysis Services (tabular or multidimensional) or Power Pivot.  The Model Designer may also design the data mart relational schema or this task may be assigned to a dedicated data mart schema designer.  The level of effort for data mart design will depend on project scale and the organization’s maturity, and whether an enterprise data warehouse or master data hubs exist.

Model design efforts consist of defining the hierarchies and relationships between data entities and attributes used in reporting. Calculations in the model use specialized expression languages like DAX and MDX to define measures and key performance indicators.

Model designers need to see the solution at two different levels; creating features from immediate requirements while preparing to add future capabilities to meet long-term goals that may not be defined in detail.

Dashboard/Report Designer
Data visualization and reporting typically falls into categories of pre-defined dashboards and reports that are developed for users to run, or self-service reporting capabilities.  Both require up-front work to allow business users to easily access and browse meaningful business data.  Conventional report design involves using tools like Reporting Services to build pixel-perfect reports with features to allow data to be filtered, to drill-down to details and to drill-through to other reports.  Ad hoc design features may require iterative semantic model enhancements to enable capabilities in a self-service reporting and browsing tool like Power View or Excel.

Good report and data visualization designers are perfectionists by nature. Encouraging creative report design can lead to some nice usability features while time-boxing the delivery of important requirements. Business users will rarely provide detail report requirements in language a report developer needs to complete the design. Use mock-ups and proof of concept reports to validate requirements and get feedback.

BI Solution Architect
The Solution Architect is the orchestra conductor who keeps things moving along and engages team members at the right level.

BI projects often require problem-solving at a deep level but they also present opportunities for practitioners to find very creative and interesting ways to engineer solutions.  This dynamic can send the project in all kinds of chaotic directions and it is imperative that a team leader is able to provide guidance to prioritize efforts, and trade-off decisions, while maintaining an overall perspective allowing others in in the trenches to work their magic. One of the greatest threats to most BI projects is side-lining development in order to deal with legitimate emergencies, or obsessively pursuing distractions. Ultimately this causes delays and possible budget constraints. .

In a large project, the Solution Architect may be a singular role with efforts focused on staffing, requirement negotiation and meeting management.  In a small project, it may simply be a designation for a senior member of the team to balance with other duties.

Politics, Methodology & Management

There’s a lot of value in getting team members to participate in different areas of BI solution architecture and development activities.  Developers can “get stuck” trying to solve problems on their own and should have regular check-in points to report status and should work in an environment where they’re encouraged to do code-reviews with other team members.  If team members feel like they have a voice to help solve problems and affect decisions, they’re more likely to have a sense of ownership and work closely with their peers.  However, too much democratic process in a BI project can get in the way if it’s not orchestrated and disciplined.

Erik Veerman, a tenured BI Solution Architect with SolidQ, believes that it’s not only important to assign people into the right roles for their skills but equally important for them to be in a work environment conducive to their work style. He says:

“Clarity of project roles are critical on any project. Sometimes we forget this in BI when either we undertake a scrum project approach or we do a more traditional approach. The fact is, everyone has different technical and soft skills and abilities to deal with project impediments. Some of us like to design and get distracted by support, others like multi-tasking and are able to process lists of tasks. Troubleshooting is easy for some, but for others, they would rather pull their hair out. Even more, some developers thrive on focusing, getting in the “zone” but can lose tons of efficiency and get really frustrated when distracted. The problem is, if we don’t recognize the way different people work and then put them in the wrong seat on the bus, we’ll end up having project issues, quality problems and disgruntled people leading to turnover and affecting the ability to be successful on projects. BI projects demand different types of roles, more than traditional development projects, and we need to be very mindful of putting the right people in the right roles.”

Agile development methodologies and less-formal iterative management patterns work well on BI projects but it’s important to understand where BI project dynamics differ from other project types.

BI Projects Are Unique

BI is both a linear and an iterative process having some elements that can be managed like software development projects and others that can’t.  For example, it’s difficult to create a daily-build of all BI solution elements.  It’s true that BI solutions consist of multiple projects which seems to fit neatly into the Visual Studio “solution” paradigm, but the fact is that some components of BI solutions (such as an SSDT database project, SSIS, SSAS or SSRS project) just don’t work that way and the standard build configuration in Visual Studio often just gets in the way.  For example; SSAS projects often require manual deployment and certain project types like SSAS Tabular, don’t support multiple developer code updates and changes can’t be merged using the Visual Studio tools.  The concept of “build”, “deploy” or “process” in one tool is either irrelevant or has a different meaning in another tool.

Larger BI projects require multiple servers with many dependencies between them.  Decommissioning, moving or even applying maintenance patches to a server can be very disruptive, upsetting the delicate balance of an unfinished BI component with its interdependencies.

BI projects often require more prototyping cycles resulting in multiple branches of code.  The effort to manage this chaos and keep team members informed about these changes is another to be owned by the Solution Architect or designated team member.

Roles in Business-Driven BI Projects

In the new era of self-service and business-driven BI, a new project paradigm is beginning to see new found success.  New roles have surfaced that change BI project dynamics and make the business-controlled approach a feasible approach for some organizations.

Data Analyst
The profile of the typical business user has changed in the past few years.  The role of Data Analyst or Data Scientist define this user as more than just a consumer of data but a capable practitioner of data analytics.

Data Steward
The Data Steward is the keeper of master data and this role is critical to the long-term success of a business solution. DBAs guard the security and maintain the storage of our databases and the Data Steward ensures that a single, reliable master list of records is available for users and analysts.  Some companies have employed Master Data Management software to assist this role but more often than not, the Data Steward is a coordinator between source application databases, developers and the DBA.

Business-driven BI solutions are emerging in some organizations who are redefining the role of IT, perhaps as more of an operational system support group rather than developers of applications and BI solutions.    This also encompasses the help desk, network support and data center operations. If your organization is such, you can substitute the appropriate title in-place if the term “IT”.

Maylene Buckendorf is a Manager of Acute Care Architecture Analysts in the Healthcare Intelligence group at Providence Health and Services.  Providence manages a large network of hospitals, clinics and healthcare facilities in the western US.

After years of experience with “traditional” BI and reporting projects, with executive direction, Providence embraced a different strategy.  In Maylene’s words:

“Working in a large organization with many established departments and a limited IT team encourages new IT skills but also decreases IT’s chance for specialized content knowledge. Because of this, it works best for us if the evolution of BI solutions starts in the business. Several BI tools allow the business to do this without IT involvement. Until the business has reached consensus, IT does little help. When the business is ready to “write things down” or document their rules into specifications IT can solve a multitude of technical needs, including automation, security, durability, enterprise use, and support. 

Allowing the business to “thrash” out their rules and requirements with little IT involvement ensures a meaningful solution. If the business needs IT intervention before meeting consensus, our IT team is cautious. We build quickly knowing that the solution may be thrown out. However, this work is important; proof of concepts and failed work gets the business closer to what they really want.”

We’ve seen many organization making various levels of commitment to own and build their BI solutions without the almighty hand of the IT experts looking over their shoulders and holding their hands through the process. For some, it didn’t work. It was too much commitment to see a solution all the way though the ocean, dessert and forest – and where ever complex projects take us before we either give up or blaze through it and deliver something awesome. Working with a large multinational manufacturing company last year, they were committed to farm the analytic work out to the financial controllers, buyers, sales people and warehouse managers. We had training and support lined up but the client just wasn’t ready to go there. Part of the BI maturity continuum involves the business getting to a point where they’re hungry and motivated to solve some of their own problems. Some are anxious, some could care less, a few learn how to use the tools and become passionate and about all the wonderful things they can do with data.

True Story – a Case Study

At the 2014 PASS Business Analytics Conference, Kaman Industrial Technologies – a billion dollar aerospace company – showcased a completely business-developed BI solution with scorecards serving up data for hundreds of users in the organization from the CEO to the Sales department. After evaluating many packaged BI products and IT-developed reports, a small team delivered the entire solution using Power Pivot in Excel and SharePoint. The team consisted of only three individuals which included one part-time technical developer, one part-time project sponsor and designer, and one on-demand consultant.

Mike Miskell, Director of Process Management and self-described “Excel Guy”, led the project and coordinated executive support. He described the business challenge with this analogy: “Our business is a thousand miles wide and a quarter inch deep; and the challenge is – how do you find the opportunities to improve?” Rob Collie, a well-known consultant and Power Pivot expert, provided design guidance and trouble-shooting support.

After struggling with a number of complex industry leading BI and reporting products, they decided to change their strategy and “keep it simple”. Using more familiar tools like Power Pivot and Excel, they were able to build core solutions and then train and support users to solve many of their own problems. The business was struggling with several hundred Excel workbooks measuring business metrics that gave many different results. According to Rob, “An ironic point is that the solution to a bad Excel problem was Excel” …more specifically, the Power Pivot add-in for Excel was used to correctly define a single version of the truth rather than the multiple inaccurate versions of conflicting, static data. Resulting data models were published to SharePoint libraries with scheduled data refresh. With a small, well-directed team using the right tools, they delivered scorecards and KPIs that brought significant process improvements and increased profitability to the company.

The Changing Tide of Business-Centric Analytics

Perhaps based on past experience, many BI projects are moving into the hands of the business and away from IT-control.  After all, no one understands how data originates and how it should be used better than the business users.  IT-enforced development standards are often seen as overly-restrictive, possibly because they were established with more rigid types of application development projects.  Developers need to write code based on written technical requirements, which may be difficult for business users to define.  By nature, BI is about discovery and iterative design.  It is difficult to document exactly what you need when you don’t know what’s available or what you might find when you get started.  Likewise, it’s impossible to build a blue-sky solution without concrete objectives.  This a tug-of-war between business and IT can cause either party to go to extremes.  A knee-jerk reaction to build a solution completely without IT involvement could prompt a reaction like “fine, you built it so you can support it!”

Without IT controls, history has proven that business users typically don’t exercise disciplines that prevent “spreadmarts”, poor system design and data quality issues.  Spreadsheets are copied from one machine to another which, in-turn, become sources for even more spreadsheets and cottage solutions.

There are legitimate challenges on both sides of the “business vs IT” argument and both have important roles to play to ensure long-term solutions.

Having data solutions controlled and managed by different business units necessitates a cooperative relationship between business and IT.  A balanced solution addresses these challenges but the right solution is going to be unique to the business culture and specifically-suited to address the needs. Overall, the best approach will include effective communication and cooperation between business stakeholders and dedicated technical staff.

Maintaining Balance with Regular Review Cycles

A proven proactive approach is to assign an IT analyst (this could be a manager, developer or requirements analyst) to meet with the business stakeholder at the beginning of the project and discuss how IT can support the business. They may decide that the business needs little or no help to support the current effort. They should discuss how data will be obtained or provided, who owns the development effort and solution, the long-term support plan and provisions to transfer ownership of the project, should the need arise.

After the project is finished, the IT & business owners should meet again to review the solution for correct design and discuss the need for ongoing review if the scope of the project should increase.

I’ve seen this type of approach work successfully in organizations where a process was defined to help set and maintain expectations. Project reviews were held at a regular cadence with design guidelines and acceptance criteria for every data model, report and dashboard. Not every small BI project needs to conform to rigid standards. Some are small-scale or throw-away projects that serve their purpose and are then retired. But, it’s the small projects that should be retired and aren’t that can be troublesome.

One very effective approach is to categorize each asset (e.g. report, dashboard or semantic model) using criteria similar to the following:

• Designed and created by a business user as a short-term solution. Not reviewed by IT or conformed to IT design standards. Results may not be trustworthy outside the scope of the initial use case.
• Designed and created by the business as a durable solution. Reviewed by IT and validated to conform to IT design standards. Results are trustworthy within the scope of intended use case.
• Designed or maintained by IT. Validated to conform to IT design standards. Results are trustworthy.

In a hybrid approach, each business role has an IT counterpart to help design projects, provide support and transition assistance if needed.

As a result of regular project reviews and a cooperative relationship between business units and IT, having visibility to the health and status of these projects provides confidence and the ability to avoid issues that are common for user-created data solutions. It’s a delicate balance with a lot of work to be done. In all of this, the same problems emerge from large projects; we need a committed preferable, stakeholder. How did they solve this problem at Providence Health?

Maylene: “I see BI work best in our organization like this: We have a small team with an influential and political stakeholder. At first the business comprises most of the team. They work with a subset of data provided by IT and discuss rules and requirements. They come up with a low tech solution. They use it and find preliminary results. Once they are happy and are less strapped for time, the business analyst from that small teams comes along side IT to build a lasting solution with automation, proper security, durability, and enterprise use.  Working together they maximize the iterative development-to-deploy cycle and end on a solution that meets real needs. “

Some departmental or group BI projects won’t need IT involvement – and with time more projects will fall into this category. With training and experience, certain business users can develop right-sized solutions with little or no help from IT. Some business-created BI projects will require support and on-going assistance; and some business-owned solutions will be transitioned to IT-owned solutions as data volumes and complexity grow. If properly managed, these transitions can be anticipated and executed smoothly.

Conclusion

In summary, there are some key elements that will increase the odds for success.  Assess the status of the following topics as a litmus test, not only at the beginning and end, but at regular intervals during the project progression. Consider these questions to check the health of the project:

Scope and Purpose
From the business perspective, are the functional requirements well-defined? Is it clear what elements the solution will contain?
From the designer’s perspective, are the technical requirements defined?  Do you understand how the solution will be architected?  Do all the solution elements continue to adhere to the design/standards?

Direction
Who is the project’s business sponsor and stakeholders?  Do we understand their expectations?
Who is the solution architect who makes design and trade-off decisions while the project work is underway?

Ownership
Who is the data steward to ensure data conformity and accuracy?
Who will maintain the solution?  Where will the solution assets (data models, dashboards, reports) reside?
Who will maintain the solution if the scope or requirements change?

It is true that every project is different because requirements are different and organizational culture is unique. However, mistakes are often repeated because lessons aren’t learned and history is repeated. Nearly every project requires the same key roles and, of course, adjustments can be made to meet your needs. Every project requires both business and technical leadership. Be sure that you have well-defined sponsorship and a business stakeholder who is invested in the success of the project.

Just as every good story has a beginning, middle and end; every successful Business Intelligence solution has a vision, clearly-defined objectives and a set of features that address the business requirements. Like the cathedral, a successful project is architected to deliver capabilities based on a blueprint, and every team member understands their role with a shared vision. In the end, you deliver a beautiful solution that matches the needs of the business, solves a problem and provides important value.

 

About SolidQ

As a Microsoft Gold Competency Partner with 12 competencies (5 gold and 7 silver), SolidQ employs many of the world’s top technical experts, including over 30 recipients of the Microsoft Most Valuable Professional (MVP) award or Regional Director recognition. These professionals provide a mix of consulting and training services to accelerate deployments, reduce issues and expand the knowledge of our clients. A key advisor and content provider to Microsoft, SolidQ experts have published more than 30 books and delivered countless presentations at leading technical conferences around the world. Since 2002, SolidQ has delivered practical education and training for the Microsoft Data Ecosystem by combining real-world consultants with academia and IT professionals to balance theory with real-world practices.

Information about SolidQ is available at SolidQ.com