Part 1 of the BI Blog, “Dynamics AX Reporting Out of the Box,” focused on what is available to Dynamics AX users natively (Click Here to read part 1). Part 2 will focus on a brief chronology of the elements making up the Microsoft Business Intelligence solution, some of the incremental features and the strategic value of adding Power BI. Power BI complements and extends what’s already available for Dynamics AX users. For example, products like Management Reporter and SQL Server Reporting Services will continue to be used as will List Pages and Analysis Services. Part 3 will look at Business Intelligence when data are mashed up, refreshed, and how to access on premise data directly from Power BI (recall, Power BI is a cloud based solution) and how AX7 will incorporate Power BI data views directly in AX workspaces to allow users to analyze the data directly from within AX.
Building Around Excel…
In 2012, Microsoft introduced a Tabular Database model in SQL Server Analysis Services. There are pros and cons to using either a Tabular or Multi-dimensional model for Business Intelligence and Microsoft supports both. However, if starting from scratch and considering time-to-value, the Tabular Database model appears to have significant merit. Multi-dimensional cubes oriented BI projections often take month/quarters/years to complete due to the complexity and unique skills required, while using a Tabular Database model is generally a degree of magnitude faster. This topic is introduced to ensure that the modeling step is at least briefly discussed. Having great and easy end user tools for BI cannot succeed without having data that is relatively easily: modeled, accessible, changeable, and with good performance. On the last point, performance, Tabular Databases are loaded into memory which is good from a performance standpoint but also raises questions regarding server sizing and any data size limitations. See more information on this topic here which also introduces use of Power Pivot: https://msdn.microsoft.com/en-us/library/hh212940.aspx
In 2013, Microsoft’s Excel added new self-service reporting functionality delivered in Power Query, Power Pivot, Power View and Power Map. These “Power” applications helped in the Extracting/Transformation/Load (ETL), Modeling, Analysis and Visualizations to deliver dashboards, reports, and KPIs in an on-premise environment.
Merging the Cloud with Power BI
More recently, with the introduction of Office 365, Azure, and other cloud-based solutions, Power BI follows by bringing these same “Power” tools to the cloud and extends the delivery to mobile device by supporting HTML5. And, there are natural collaborative capabilities that are leveraged (Power BI Sites, e.g.).
A primary goal of any Business Intelligence (BI) solution is to bring actionable insight to data. Power BI enables easier access to: more data sources, transformation services, visualization, unique interaction and device choices to make better, faster, and easier decisions. Without reservation, Power BI improves decision making by enriching self-service business intelligence.
Microsoft uses the following two buckets to help define the tools within Power BI.
Insight:
Discovery - using PowerQuery (Search, Access and Transformation)
Analyze - using Power Pivot (Data modeling and in-memory analytics for performance)
Visualize - using PowerView & PowerMap
Collaborate:
Share - Power BI Sites (Share data views and workbooks and allows refresh schedules for both on-premise and cloud data sources)
Find - Natural Language Query (I disagree that this is collaborative - more end user experience and should probably be under “Analyze” in the Insight Category above)
Mobile - HTML5 (makes output pretty close to device agnostic)
Why Power BI? To start…
Self-Service – this is a combination of unique visualizations and also natural language query which improves the way in which users interact to find insightful data (and, to easily publish that for others)
Reuse – it leverages existing Dynamics AX queries, and through OData, allows that data to be reused and/or edited
Other Data – it allows mashups and external data (this will be covered in Part 3 of the blog)
Process to Export Data from Dynamics AX to Power BI
Dynamics AX has many pre-built queries which can be reused and/or used to combine with other data sources by Power BI tools. AX is just another data source where tools like SQL Server Analysis Services are still used. The primary interface between AX and Power BI is OData, a data migration service. OData allows a specific query from Dynamics AX for use in external applications. Which queries are available through OData is managed from within AX in Document Data Sources.
After the query is exposed in Power BI, another tool, PowerQuery, is used for the Extraction, Transformation and Load (Publish) of what will be used by Power BI users. Normally, the transformation cleans up the data by removing unnecessary columns, renaming content or by adding measurements (e.g., quarters for financial reporting). This is an important step but requires collaboration to align with user requirements. More on that later…
The point is: while the existing pre-built queries generally won’t satisfy the AX data requirements out of the box, it will provide a good starting point to refine and extend.
Building Visualizations
Accessing the transformed data (workbooks) involves PowerView. Excel enables this by starting with a blank PowerView sheet with the available columns available to simply drag and drop or select. The "Designer" allows for editing the table and visualizations.
Visualization – Power BI examples:
1. Funnel – a Sales funnel is a good example since the values normally decline as you move through the funnel. Normally, each stage allows for drill down as well.
2. Gauge – provides a nice visual representation with stop lighting to quickly identify the health of whatever is being reported
3. Combo Chart (e.g., chart and line)
4. Tree Map - enables filters/categorization and attributes/coloring to display voluminous data points – empowering accelerated insight
5. Fill Map - uses shading, tinting, patterns or 3D images to display how a value differs in proportion across a geography or region
Visualizations are important since individuals digest information differently. Finding new ways to visually present data helps to more easily draw inference, relationships and insights to make better decisions or to act.
Publishing – New Security Model to be aware of...
Publishing, one of the collaborative aspects of Business Intelligence, involves Power BI Sites. From a security standpoint, any access permissions invoked within Dynamics AX is now not applicable. Access now managed by Sharepoint security at the workbook level. It is vital for the designer to understand the Sharepoint security hierarchy and to publish workbooks/visualizations accordingly since this publisher likely has a wider net of data access than the recipients.
A PowerBI site on your Office 365 tenant must be created first. After the PowerBI site is available, it’s a simple Add and Upload process to publish the workbook to the site. To enable Natural Language Query, this option must be enabled as a part of the publishing step.
Speaking of Natural Language Query (NLQ)
As a starting point, see how Power BI was introduced by Amir Netz during the Worldwide Partner Conference 2013 (the part that exposes NLQ begins around 3:30 into this video)
For those unfamiliar with NLQ, instead of writing SQL select statements, browsing through workbooks, exact-match searching (or, using non-intuitive symbols) of workbook names, English-like phrases can be entered. A simple example could be, “Sales by region, year to date.” Recall the discussion earlier on transformations? This is where understanding the data requirements along with the way in which users will search become invaluable. Aligning transformed column names is just one example of why not only understanding what data is being accessed, but also recognizing the company or role-based jargon to assist in the search and outputted data is paramount.
PowerQ&A is the tool enabling this cool technology. Within the BI site, there’s an “Ask with PowerBI Q&A.” This provides a blank search bar that immediately begins to interpret the English-like search query when entered. It’s displayed under the actual query in italics. Not only it brings up the table, but also the filtered results. And, it allows the results to be easily changed on the fly by adjusting visualization, filters and columns.
PowerQ&A is a rules-based system where phrases and synonyms are used to interpret and execute (the query and workbook selection) from the inputted search term. It is always being updated to be more and more intuitive and to include more interpreted phrases and synonyms. Despite this, companies have their own expressions and nuances. To account for this, PowerQ&A can use PowerPivot to create and edit these interpretations/associations. Alternatively, PowerQ&A optimization in Office 365 is another method of building upon (add, change, delete) the library of phrases and synonyms.
NLQ can be a game changer for some organizations. Not only it allows a user to locate data, but specific data (i.e., not just the overall table of data but also adds filtering in the query itself). Like everything presented in Business Intelligence, it requires setup and continual modification (e.g., semantics), but in the end provides an incredible and unique, end user self-service solution.
Summary
It has been an interesting journey to watch Microsoft develop the data infrastructure (e.g., tabular database modeling for business intelligence), services (Azure IaaS/PaaS) and applications (“Power” tools and Power BI) to enable self-service Business Intelligence which is full featured and achievable in a reasonably quick timeframe. This enables Microsoft customers to ascribe to the Ready-Fire-Aim approach that is often necessary in the rapidly changing world and individual industries resulting in much faster time to value. In my opinion, this is the most important aspect of the new tools and strategy being introduced by Microsoft in the ever important Business Intelligence sector and in Information Technology in general.
Forrester Group agrees. In the latest Forrester Wave Report, "Agile Business Intelligence Platforms, Q3 2015,” Microsoft is now considered to have the Top Current Offering.
This concludes the second part of the Blog. Part three will begin to look at including and mashing up external data, refreshing data, real-time access to on-premise data and how Power BI will begin to add more value to Dynamics AX clients.
