BI Tools You Already Own

BI Tools You Already Own

Written By: Perry Thebeau, Brad Vorbeck, and Michael Ramatowski

from July 8, 2014


What Kind of Team Is Necessary for an ERP Implementation?

When a business begins considering an enterprise resource planning software

February 18, 2015

Your business is speaking to you in the language of data and opportunity. Can you understand it?

Fluency in this language of business intelligence (BI) is a key difference between good companies and great companies. Since every company knows that strategic advantage can be generated from data analysis, why wouldn’t a company pursue true BI as a primary component of its overall strategy?

Some companies face numerous challenges to implementing BI, including culture adoption, usability, and master data issues. But for many, they simply don’t know where to start.

Kicking off your BI strategy often comes down to a simple piece of advice: Understand the information and tools you already have, use them to the fullest potential, and then expand as necessary. The functionality within Microsoft Dynamics and Microsoft Dynamics-connected tools can help your business uncover shockingly powerful insights that lead to real strategic improvements and positive results. The key for this depends on a combination of understanding your business and its data, and using the tools that are right for your needs.

This article focuses on two solutions that provide powerful BI capabilities without requiring extensive BI expertise. You likely already own both, and they are highly interrelated with Microsoft Dynamics GP. These solutions are Excel Report Integration and Analysis Cubes for Excel (ACE).

Understand and Optimize What You Already Have

Excel Report Integration

With more than 200 built-in Microsoft Excel reports that connect directly to the Microsoft Dynamics GP database source, Excel Report Integration alone offers a solid foundation for reporting. It’s a surprisingly powerful starting point, and users can connect Excel to their Microsoft Dynamics GP solution at no additional charge. To generate reports, Excel pulls data from the Microsoft Dynamics GP database to populate an Excel workbook using an Office Data Connection (ODC). The workbook, now populated with cleanly formatted, relevant data, transforms it into a report.

There are numerous advantages to using Excel Reports, especially for non-expert BI users. Ease-of-use is a primary benefit, and it goes a long way for improving BI adoption in a company. If reports are hard to generate or understand, users will resist implementing them into the everyday routines of the company. You want BI to become a part of your company’s operational culture. For this to happen, the process should be facilitated through an easy-to-use starting point.

Like ease-of-use, familiarity of the application itself is a huge leap forward on the learning curve that other solutions may present. By using an application you already know, less training and education are required, and users can save a lot of money. Perhaps the best part of familiarity with Excel, however, is that users can enhance their reports with interactivity, calculations, and visual options they already know how to use.

One of the unique advantages of Excel-integrated reporting is the ability to run live reports. These reports don’t need to be generated each time you need them. They use real-time, refreshable data in Excel to maintain up-to-date reports that can be viewed at any time. For reports that use information that is easily outdated, this feature is a huge time-saver.

Excel Reports offer a great starting point and are part of the Microsoft Dynamics-integrated reporting solution, but they are only the tip of the iceberg. Let’s evolve their capabilities with another tool you already have included in your Microsoft Dynamics GP licensing: Analysis Cubes for Excel, or ACE.

Analysis Cubes for Excel (ACE)

Excel Reports offer two-dimensional views of the relationships between your various segments of data. Analysis Cubes for Excel allows you to add more dimensions to your analysis and manipulate your data in ways that are more specific to your needs. This allows users to derive more significant insights from their data by adding details to their queries. Cross-dimensional analysis provides users with points where their segmented data intersects. Each intersection represents a measure that can be used to uncover insights and metrics for very specific aspects of the business.

To create these multi-dimensional cubes, ACE uses an online analytical processing (OLAP) database that is specifically designed to help analyze data. To populate the OLAP database, integration services extracts data from the Microsoft Dynamics GP transactional company database and stores it in a data warehouse database. Analysis Services aggregates the data into totals and subtotals, then stores this consolidated data into arrangements called cubes. Because the data warehouse and the OLAP cubes are isolated from Microsoft Dynamics GP, and the data remains static between updates, the data can be retrieved more quickly.

Using Excel and ACE Together

While ACE can be applied to other BI sources, the two solutions (Excel Report Integration and ACE) are planned by Microsoft to be utilized together in one solution scenario. But how?

ACE is already included in the Microsoft Dynamics GP Starter Pack, and most Microsoft Dynamics GP users already have Microsoft Excel. Both solutions connect seamlessly with Microsoft Dynamics GP.

Common business application examples of BI with ACE are:

  • Asset management and optimization (fleet management)
  • Retail forecasting, supply chain, and product placement
  • Gaming industry consumer behavior analysis
  • Healthcare industry disease detection/prevention
  • Business scorecard implementation (for operational control)
  • Executive dashboard implementation (for quick strategic analysis)
  • Performance management applications (to address performance or sustain it)

Overview of Other Microsoft Dynamics GP BI Tools

ACE and Excel are two of the most readily-available tools and provide great all-around functionality. But for users who want to evolve their BI and take it even further, there are numerous BI tools to help translate data into insight, and each one has its strengths and weaknesses in terms of functionality.

Before you can decide which BI tools are right for you, you must first formulate your BI strategy and its requirements. What business events and processes will be analyzed for decision support information? How much visualization, such as charts and dashboards, will be needed for your users? How deeply will you need to query your data? How frequently will you export and distribute your BI reports? Answering questions like these will help you understand your goals and preferences for your BI solution.

This chart shows that from an overall functional perspective, Excel and ACE arguably provide the richest self-service and usable BI functionality across the board, making them appealing for the broadest range of Microsoft Dynamics GP users. As previously mentioned, since ACE and Excel can quickly generate ad-hoc and flexible multi-dimensional business insights, the two solutions, when used together, are highly-rated and form a multi-featured BI solution. We recommend this as your starting point for fundamental, yet powerful, BI.

BI Adapted with New Microsoft “Power”

BI often becomes decision modeling. It happens through the preparation of flexible reporting solutions and decision applications that can take advantage of data to answer your business questions. That, in essence, is the definition of “Power BI” from Microsoft and a value proposition for your evolving use of ACE. Power BI consists of numerous integrated analysis tools that provide ways to visualize your data and translate it into verbal insight that’s easy to understand.

Analytics with visualization tells a data story leading to a business action, using your business dimensions and/or geographic data with metrics and facts about your business transactions and events. Seeing the answer to complex relationships presented to you visually and contextually with data visualization is extremely valuable for any Microsoft Dynamics GP user. Analytics with visualization is integral to business persuasion as well as fact-based data storytelling strategies.

The End in Mind

For BI to work in an organization, it must be not only sustainable, but also usable. Fortunately, Microsoft Dynamics GP provides various BI capabilities, including ACE and various flavors of “right time, right format” Excel-adapted reporting that is completely usable for non-experts. Microsoft has invested heavily in solutions such as the Power BI stack that can be sourced from the Microsoft Dynamics transactional database.

Using BI for decision-making is a significant step in taking your company to the next level of its success. It fosters confidence and, more importantly, it’s a foundation for recurring good decision-making.

If you’re not using the data already being generated by your business, you’re actually ignoring one of your most valuable assets. Understanding and capitalization of this vast opportunity are the new language of business. Your fluency in BI strategy will pay dividends if you take steps to formalize a strategic plan and recognize its potential using Microsoft Dynamics and readily-available BI tools you likely already own.