Understanding the Challenges of Business Intelligence

© 2014 Edgewater Fullscope 1 Understanding the Challenges of Business Intelligence: What You Should Know Before Deploying a BI Solution TABLE OF CONTENTS I. Introduction ................................................................................................................ 1 II. What’s Involved in BI and Why It Matters ....................................................... 2 III. Implementation Considerations ....................................................................... 4 IV. The Microsoft Dynamics AX BI Landscape .................................................... 6 V. Reasons to Consider Enterprise BI ..................................................................... 11 VI. Eight Things to Do before Implementing BI ................................................. 12 About the Author ......................................................................................................... 14 About Edgewater Fullscope ..................................................................................... 14 © 2014 Edgewater Fullscope 1 I. INTRODUCTION For businesses of all sizes, competing in an increasingly dynamic marketplace means having the abil- ity to understand and respond to change. To attain, sustain and further their competitive standing in this environment, companies need to be agile and responsive in the face of constantly changing market conditions. To do this, they need intelligence—business intelligence (BI)—to make better de- cisions based on reliable and timely information. At its broadest level, business intelligence can be said to reside anywhere information is grouped together; but for purposes of this discussion, business intelligence is an umbrella term that refers to a set of methodologies, processes and technologies that transform raw transactional data into mean- ingful and useful information easily accessed and explored by business users. Since business has evolved into a digitally-driven activity with supporting technologies evolving at a breakneck speed, the definition, impact and challenges of business intelligence have changed ac- cordingly. For most companies, information is locked up in enterprise resource planning (ERP) systems (e.g., Mi- crosoft Dynamics AX) that are developed to support transactions and with scalability in mind. These activities sometimes run counter to the goals of BI. And although in its broadest sense, an ERP system is a direct source to extract business intelligence information, if the possibilities of BI beyond ERP and other transactional systems are considered, it’s apparent the two can co-exist and do so harmoniously. Companies that have invested in Microsoft Dynamics AX ERP want to leverage the BI tools and tech- nologies delivered within the product. With that in mind, this white paper will define BI within Micro- soft’s framework, explain what is being delivered out of the box, address challenges to success and offer recommendations for a successful BI deployment. © 2014 Edgewater Fullscope 2 II. WHAT’S INVOLVED IN BI AND WHY IT MATTERS BI is a natural extension of ERP, and ERP is the transactional backbone and essential foundation of BI. Integrated ERP systems (e.g., Microsoft Dynamics AX) require significant data input, and organi- zations want to capitalize on that data in every way possible. Even though an integrated ERP system provides business value in and of itself, companies are understandably looking to maximize that value —they want information to help them manage their business, both tactically and strategically. A look at the BI technology stack is useful to reveal the complexity in what business users often per- ceive as a simple, application-based tool or information dashboard. The Business Intelligence Technology Stack The BI stack can be considered as a seven-layered pyramid with infrastructure at the base and de- livery at the apex. All components in the structure must work in concert to realize BI for the orga- nization, and the skills required to implement BI can be unique to each layer within the stack. That is, a company may need several individuals with domain expertise in each area to work in concert to address the requirements of BI. This is the very reason Microsoft has delivered pre-packaged BI components within its Dynamics AX application—the need to simplify and demonstrate how these components work together to deliver value. Understand, though, that change in these pre-packaged components is often necessary to meet current and future BI needs, and no BI solution will be static. Each component layer demands that hard questions be asked to determine which tools and tech- nologies will best support the vision and the capabilities the company has for BI. This is an inherently intricate undertaking. © 2014 Edgewater Fullscope 3 An organization’s informational needs will evolve over time. As it matures in its BI capability, more relevant information will be delivered to decision makers. Often, the result is that these insights spawn new questions and deeper interrogation of the data. So the selected BI technologies, tools and frame- work must be practical and something that the organization can support for the long term. It is im- portant that the organization is not dependent on an outside source for every need or change they will want to make. The organization should be empowered on a day-to-day basis to further explore its most valuable asset (i.e., its data) to gain the most value in BI. © 2014 Edgewater Fullscope 4 III. IMPLEMENTATION CONSIDERATIONS Frequently there are misaligned expectations around the “what,” “when” and “how” of implementing BI. And while “big data” and other bleeding-edge trends have emerged as hot BI topics in recent years, the fact is that most organizations are still focused on BI as a tool to better leverage their ERP investments. When a company is purchasing Microsoft Dynamics AX, the executives making the in- vestment decision may see the BI dashboards and envision the improvements possible with all this valuable information to help manage their business. Many times, the dashboards are the very reason they move forward with an ERP investment. There is another reason why BI is important to think about in terms of an organization’s ERP imple- mentation. Information within transactional ERP systems is highly normalized to make the transac- tional process more efficient and deliver the scalability necessary to support the volume of transac- tions involved in enterprise operations. By doing this, it makes it more difficult to extract information for BI purposes, because the data is spread out over so many tables within the ERP system. A frame- work driven from this transactional database results in reporting complexities and inefficiencies, often requiring the engagement of sophisticated users or programmers and leaving the typical business user dependent on others to meet their most basic BI needs. Since the BI layer is being delivered as a pre-packaged solution with Dynamics AX, it seems like a nat- ural and easy extension. What is not so clear is that implementing BI is not easy—a reality borne out by the fact that many users are simply not getting what they need from BI, nor are they meeting their own expectations of what they thought Dynamics AX would deliver in this area. This misaligned expectation is typical across most ERP implementations, regardless of the chosen sys- tem. Companies simply underestimate what it takes to be successful with BI, and anyone who claims there is an easy solution is simply not being honest about everything it entails. Consider these factors: • BI is much more dynamic than ERP. For example, with an ERP implementation, there are only so many ways to define how to create and manage purchase orders. With BI, an organization’s informational needs will con- stantly evolve. Comprehensively defining an organization’s informational requirements as a prelude to a BI project is near impossible. Beyond the reporting provided today—or perhaps a perceived wish list or a higher-level framework of a BI vision—how does the organization re- alistically know what it wants when it doesn’t know what it can get? This evolution underscores the importance of implementing a framework and tools that will empower the organization as it evolves over the long term. © 2014 Edgewater Fullscope 5 • A successful BI implementation requires skills across several disciplines or domains. The array of skills required to successfully deliver BI is daunting, especially for a mid-sized company with limited resources. Consider the BI Pyramid and the standard technologies re- quired for each layer of the technology grid. Additionally, a unique business talent is required to lead and provide direction—someone who understands the informational needs of the organization combined with a general understanding of the underlying technology, and of course someone who can dedicate time and focus to the initiative. Communication and co- ordination across the multiple disciplines, plus maintaining overall focus, can be a challenge. These are the projects that can potentially go on and on without delivering intended value. • It is very difficult to implement BI in parallel with ERP. While designing BI in parallel with an ERP implementation may be conceptually appealing, it isn’t practical. Consider this: an ERP implementation transforms core business processes throughout an organization, so it is important that it is done correctly. When a company flips the switch, it must be able to ship orders, invoice customers, properly track inventory, and so on. Companies may see this as a no-brainer, but it is not. The focus must be on core business processes, the basic blocking and tackling of ERP. Many companies struggle to meet the most basic demands of an ERP implementation—throw in BI, and they just do not have the people, bandwidth and the necessary skill sets to execute the project. More often than not, companies should wait until business processes are stable and the orga- nization as a whole is prepared to focus on BI as a strategic initiative. There are variations to this approach: • When an existing BI framework is already in place and the effort simply involves mapping a new ERP to it. • When the company leverages BI to avoid upgrading or migrating historical data during the ERP implementation. In this case, the BI project would be more successful as a prelude to the ERP implementation, versus being done in parallel, with the mapping initially coming from the legacy environment. In the midst of an ERP implementation, the end-state ERP is evolving, so the BI data mapping becomes a moving target. Mapping both legacy and end- state ERP to a BI solution at the same time will tax both projects in a significant way. © 2014 Edgewater Fullscope 6 • The BI software vendor landscape is confusing. The idea that BI is a one-stop shop is a misconception. In fact, Microsoft’s BI layer is delivered with Dynamics AX, yet there are many Independent Software Vendors (ISV) who sell BI add- on solutions. This can be confusing, and as each vendor takes advantage of standard core Microsoft capabilities to evolve its own solution, the line between standard Microsoft and ISV becomes cloudy. Selecting the right tools to meet an organization’s unique needs is an intri- cate task. • There is no single answer to BI. In fact, a multi-threaded approach to BI is emerging in the marketplace. No single BI tool is ap- propriate for every user’s needs for all questions that must be answered or explored in a day; consequently, one often finds a combination of complementary BI offerings being deployed at the enterprise level. In addition to an enterprise framework that delivers a top down view of the business, there are other useful tools to be leveraged independently, or even as part of the Enterprise framework: • Microsoft Excel. Excel is the number one BI tool used in enterprises today. It’s a great end-user analytical tool: it’s easy to understand, has high adoption rates, and business users know how to use it. It’s no surprise that Excel remains a key part of Microsoft’s BI strategy. • Financial reporting tools such as Management Reporter. These are tools that are geared for financial statutory reporting. Although many times they are not needed and can be supplanted by the Enterprise BI framework, some companies deploy them as part of their over-arching BI strategy. • Embedded Microsoft Dynamics AX Client Tools. Microsoft has delivered valuable BI tools embedded within the Dynamics AX client. Advanced query tools (AQT) are used to create and execute queries against the Dynamics AX database and offer the user the ability to filter, arrange and sort the data the way the business needs to see it. This is most commonly seen through the Advanced Filter and Data Grids throughout Dynamics AX. After building these business-centric queries, users are able to save them as Cues (Web parts) and publish them to the Enterprise Portal and Role Centers. List pages with PowerView is another Dynamics AX tool that can be used to create highly interactive, ad-hoc reports quickly and easily by users. And finally, the Auto-Report feature within Dynamics AX provides users with an ac- celerated approach to developing the reports they need. © 2014 Edgewater Fullscope 7 IV. THE MICROSOFT DYNAMICS AX BI LANDSCAPE For Dynamics AX customers who want to leverage as much of their Microsoft investment as possible, using components of the Microsoft BI stack is an understandable goal. There are five distinct compo- nents of Microsoft’s BI capability that should be understood: • SQL Server Integration Services (SSIS) SSIS is the data and discovery component, also referred to as ETL (Extract, Transform and Load). SSIS provides the tools necessary to automate the processes for cleansing data, con- solidating data from multiple sources, and transforming the data into a structure well suited for analysis (i.e., within a data OLAP cube). Users can schedule the periodic execution of these processes using SQL Server Agent. Microsoft has built-in tools within Dynamics AX that serve as its ETL, with the inclusion of Perspectives and other BI components that help extract, transform and load the data into the data warehouse. A Dynamics AX Perspective represents a collection of related application tables and views. There are several scenari- os for using Perspectives in Microsoft Dynamics AX. One of those scenarios is to identify whether the tables and views contain fact data or dimension data and to define how the data will be surfaced to a cube. Among other things a Perspective enables a user to specify if a value is to be used as a measure or an attribute and how it will be rendered in the pre- sentation layer. When leveraging Perspectives, SSIS is not mandatory for building the cube, but it can certainly add incremental value. • SQL Server Analysis Services (SSAS) SSAS is the actual data OLAP cube, or the multidimensional database structure. Changes to the SSAS cube are made through a Microsoft tool called BIDS (Business Intelligence Devel- opment Studio), which is a part of Microsoft Visual Studio and requires knowledge of MDX programming language. SSAS supports sophisticated, high-performing, interactive queries via an OLAP database and incorporates integrated BI metadata. Because of the complexi- ties involved in building an OLAP cube from scratch with the standard tools, Microsoft offers 20 default (i.e., standard) cubes as part of the latest version of Dynamics AX, as illustrated on the next page. © 2014 Edgewater Fullscope 8 • SQL Relational Database Microsoft Dynamics AX relies on an installation of Microsoft SQL Server. In addition to the standard, highly normalized OLTP (Online Transactional Processing) relational database that houses the Dynamics AX application and transactions, organizations can opt to create a de-normalized SQL Data Mart or aggregated data sourced from Dynamics AX contained within a standard relational SQL database. In this case, the Data Mart can be used as a staging database for building an OLAP cube, or can be leveraged directly for reporting. It’s important to note that using the cube approach to BI is not a requirement—many organizations use the simpler data framework of a relational database solely or in tandem with the more sophisti- cated OLAP approach. • SQL Server Reporting Services (SSRS) SSRS is a reporting platform that includes tools to develop reports, to secure and manage published reports using a centralized administrative infrastructure, and to support user access to reports. It is tightly integrated with the Dynamics AX Client, AOT and SharePoint. Further, it hosts published reports and provides the report builder application and framework for BI. • SharePoint SharePoint is a web-based user portal used to surface BI reporting components. For Dynamics AX, Microsoft delivers Role Centers, which are merely SharePoint-based, user-defined portals unique and specific to a role (e.g., CFO, AP Clerk). The default Role Centers each have relevant Dynamics AX links and alerts, as well as reporting, KPIs and other BI components. Of course, the underlying data source for some of these Role Center reports and KPIs are the OLAP cubes as defined above. The default Role Centers should be considered examples; customers will want to modify them to meet their specific needs. © 2014 Edgewater Fullscope 9 Let’s consider the path that an end user might have to go through to get the right information on Role Center page within Dynamics AX, assuming it is not there. If a user (e.g., an accountant) wants a chart for analysis to show up on his/her Dynamics AX Role Cen- ter and it is not already available, it may require reaching all the way back into Dynamics AX and step by step, pulling the data out of Dynamics AX, integrating that data within the cube, validating the data in the cube, developing the chart (or report) and then surfacing that chart on the Role Center. If that sounds like an easy task, consider the following skill sets that are needed to accomplish this task: • The business user who hopefully understands what he or she wants to see on the Role Center and can clearly define the requirements. • An AX business analyst who understands where the data resides in Dynamics AX • An AX developer who may be required to surface the data properly • An Data Architect who is familiar with SSIS and SSAS, specifically skilled in MDX programming language (probably not the AX developer above) © 2014 Edgewater Fullscope 10 • Another resource who can write SSRS reports (maybe an AX developer can pinch hit here— reporting in AX2012 has moved to SSRS and many AX developers are picking up this skill) • An infrastructure resource who maintains and works in the SharePoint world to properly surface the information. (most likely not the AX developer or the Data Architect) Also, if the business user needs to tweak his requirements, it is possible to have to go back through the same cycle again. It is no wonder that business users tend to resort to the “just download the data into an Excel spread- sheet and I’ll create my own chart” mentality. The analysis may not be as granular or incisive, but they get it quickly in a form they are used to using. And that says it all: Excel becomes the tool of choice for business users who want to explore their data without the perceived and sometimes real limitations, complexities and latencies that can be associated with delivering these BI components through a more formal and structured BI framework. It is this very principle that drives software providers, including Microsoft, to embrace Excel as a de- livery component of their BI solution—mass adoption and a low learning curve. But this is only one small part of BI, and there are several limitations and risks with leveraging Excel as one all-encom- passing BI tool. © 2014 Edgewater Fullscope 11 V. THE REASONS TO CONSIDER ENTERPRISE BI Despite the attraction of Excel, there are compelling reasons for organizations to push forward with an Enterprise BI initiative and data warehousing specifically: • Enabling consolidation of data from multiple sources and applying business logic to get to a common format with consistent meaning • Leveraging the data warehouse to summarize data quickly with little or no impact on the pro- duction environment. (Analytical queries on a corporate database can be an expensive opera- tion in terms of database resources) • Storing data more efficiently. Historical data is often archived rather than maintained indefinitely in an ERP database. To review trends over time, the solution might need to be a repository for data that doesn’t exist in ERP. • Providing data access to all that need it. It’s common that personnel who need the data do not have regular access to the corporate ERP database. • Improving enterprise upgrade ability: moving reporting off of ERP can help streamline future ERP upgrades. • Supporting the bottom line. Every decision made each day as people perform their jobs, wheth- er solving a problem or planning for the future, translates directly or indirectly into a cost or a profit for an organization. The latter point is worth elaboration. Recent Microsoft research shows that BI capability penetrates only about 15 to 20 percent of the total employee base in a typical organization. This is counterintui- tive to common sense. Is critical business information less valuable to 85 percent of the organization? Do 85 percent of employees make decisions that don’t have an impact on the business? © 2014 Edgewater Fullscope 12 VI. EIGHT THINGS TO KNOW OR DO BEFORE IMPLEMENTING BI At times, this paper has made a point of putting a sharp spotlight on the challenges of BI implementa- tion. But don’t mistake the desire for clarity as criticism. Inevitably, BI is on the rise in an age of digital business, and for good reasons. At the outset of the decade, Gartner reported the global business intelligence and analytical software market rose above the $10 billion mark, with Microsoft showing double-digit gains. On-going trends affecting the market include: • Increasing social and collaborative BI • Mobile BI • Greater demand for Location Intelligence • Simpler data visualization • Continuing growth of SaaS-based BI • The rise of social media analytics • Self-service BI • Better real-time analytics • Increased small to mid-sized business adoption of BI For those seriously contemplating a BI implementation, take the following eight considerations or actions: 1. Don’t believe that there is a silver bullet—it doesn’t exist! 2. Plan to invest in building skills internally. 3. Seek a business leader with a vested interest in enterprise information and a commitment to in- vest time to lead the project. © 2014 Edgewater Fullscope 13 4. Review out-of-the-box tools and design delivered within Microsoft Dynamics AX • This functionality is designed for the masses, not for your specific organization’s enterprise needs. • Consider implementing one or two elements initially to begin understanding the tools and capabilities. 5. Find some quick wins and build on them. BI will be most successful with a series of roll outs, one building on the next. 6. Consider what the ISVs are bringing to the table and determine how this adds value to your orga- nization. Seek counsel from an independent partner to help (Hint: the biggest booth at the trade show does not equate to the best product for your organization). 7. When selecting a BI vendor: • Talk to as many customers as you can find, not just those the vendor recommends. (Lever- age an ERP partner to help find them.) • Remember that the organization is as important as the tool. 8. Don’t forget about governance and operations in these areas: • Administration and management • Platform and infrastructure • Security Successful BI deployments require organizational commitment, support, resources, and budget, as well as considerable technical skills. But in today’s competitive markets, the term business intelligence continues to promise the core value it carried long before it attained its current high-tech connota- tion: competitive advantage. Securing that has always been an intelligent choice. © 2014 Edgewater Fullscope 14 ABOUT THE AUTHOR Gina Pabalan brings over 23 years of experience working with clients to implement enterprise appli- cations and business intelligence solutions. While working for PricewaterhouseCoopers Consulting and IBM Global Services, she worked with many large global clients, primarily manufacturers, in the delivery of enterprise applications. At Microsoft, Gina managed a team dedicated to serving Micro- soft Dynamics customers. Now at Edgewater Fullscope, Gina runs the Customer Support & Optimi- zation services team, which is dedicated to serving the needs of existing Dynamics AX customers and helping them take their Dynamics AX implementation to the next level. Gina holds a BA in accounting and an MBA, both from the University of South Florida. She is also a former CPA. ABOUT EDGEWATER FULLSCOPE Edgewater Fullscope is an award-winning Microsoft partner and one of the largest resellers of Mic- rosoft Dynamics AX ERP and Dynamics CRM software and services in the United States and Canada. Its customers include discrete, mixed mode and process manufacturers. Fullscope offers traditional or cloud deployment options, Microsoft SharePoint and Business Intelligence services, and is an early adoption partner for Parature and Yammer. For more information, visit www.fullscope.com. This document is for informational purposes only. Trademarks are properties of their respective companies and duly noted. 2500 Northwinds Pkwy #600 Alpharetta, GA 30009 www.fullscope.com