Business Intelligence / Microsoft / Data Visualization
How Microsoft is Changing the Traditional Corporate Analytics Model
For many years, Microsoft Excel has remained one of the most widely used tools for working with corporate information.
Even enterprises that use industrial ERP, CRM, and analytical data warehouses often continue to prepare a significant portion of management materials in the form of spreadsheets.
The reasons are obvious: Excel is familiar to most employees, supports a wide range of calculations, and allows for quick report creation without specialized development.
However, as the volume of information and the number of sources increase, the limitations of this approach become increasingly noticeable. Users are forced to manually merge tables, transfer information between files, and maintain complex formulas. Multiple copies of the same report may contain different data, and changes in calculation methodology often remain poorly documented.
Microsoft is gradually developing a set of tools that allow combining the familiar Excel approach with the capabilities of corporate analytical platforms.
An important stage in this development was Power BI – a suite of technologies for connecting to information sources, preparing data, creating models, and interactive visualization.
As of June 2015, Microsoft already offers Power BI for Office 365, and the new generation of the Power BI cloud service and the Power BI Desktop tool are in preview.
In December 2014, Microsoft introduced preview versions of the new Power BI and Power BI Designer. During the first half of 2015, these products were actively improved.
For enterprises, this means the emergence of new Self-Service BI capabilities that do not require the mandatory creation of each analytical report by a specialized development team.
From Excel to a Comprehensive Analytical Platform
The development of Power BI is closely linked to technologies that were previously used as add-ins or components of Microsoft Excel.
These include Power Query, Power Pivot, Power View, and Power Map.
Each of these tools addresses a specific category of tasks.
Power Query: Data Acquisition and Transformation
Power Query allows you to connect to external sources, retrieve data, and perform transformations without needing to manually program each operation.
Users can import information from databases, spreadsheets, text files, and other available sources.
After connecting, you can change field types, remove unnecessary columns, filter records, merge tables, and perform other preparation operations.
For example, a financial analyst receives sales files from several branches every month.
Instead of manually copying information, they can set up a sequence of transformations that will generate a consistent dataset.
An important advantage is the ability to re-execute such operations. However, Power Query should not indiscriminately replace corporate ETL processes, especially when it comes to critical reporting with a large number of sources and complex business rules.
Power Pivot: Modeling and Calculation
Power Pivot allows you to create analytical models based on several interconnected tables.
One of the key components is the in-memory columnar storage technology known as VertiPaq.
It provides efficient data compression and fast execution of many analytical operations.
The DAX language, Data Analysis Expressions, is used for calculations.
Unlike standard Excel formulas, DAX is designed to work with analytical models, table relationships, and calculation contexts.
For example, a user can define a total revenue measure:
Total Sales := SUM(Sales[Amount])
After that, the measure can be used in various analytical views: by years, regions, customers, or product categories.
Importantly, its formula does not need to be recreated for each report.
Power View: Interactive Visualization
Power View allows the creation of visual analytical representations based on prepared models.
Users can create tables, charts, and other reporting elements.
The interaction with one element can affect the display of related metrics.
For example, selecting a specific region allows you to explore relevant sales by product categories.
This approach makes analytics more interactive compared to standard static reports.
Power BI Designer: A New Tool for Analysts
One of the most interesting directions in Microsoft BI development is Power BI Designer.
Unlike traditional Excel add-ins, this is a standalone desktop application for connecting to data sources, preparing data, and creating interactive reports.
Its previous version is available for users to test and explore.
Microsoft combines data acquisition, transformation, modeling, and visual analysis functions in a single environment.
Functionality Updates in 2015
In March, Microsoft introduced an update to Power BI Designer Preview, which included a connection to Google Analytics, automatic detection of relationships between tables, and improvements to data loading mechanisms.
On May 19, Microsoft released another update, expanding the modeling functions.
Among the changes are calculated columns, data categorization, sorting by another column, an improved DAX editor, and additional data transformation capabilities.
This is an important development for business analysts.
Functions that previously required working with multiple tools are gradually being integrated into a single application. However, Power BI Designer remains a preview product, so its capabilities, performance, and terms of use should be evaluated considering this status.
For critical enterprise processes, it is advisable for the company to conduct a pilot implementation and verify compatibility with the existing infrastructure.
Power BI Architecture: From Source to Analytical Report
For correct use of Power BI, it is necessary to distinguish several functional levels.
Information Sources
Initial data can be stored in Microsoft SQL Server, Oracle Database, Excel files, text documents, and other supported systems.
For enterprises using ERP and CRM, it is advisable to organize access through prepared views or analytical repositories.
Direct connection of each user to a productive ERP can create uncontrolled load and complicate information security.
Data Preparation
Power Query and related Power BI Designer functions allow for transformations.
At this stage, field types are defined, unnecessary attributes are removed, relationships are formed, and necessary normalizations are performed.
However, it is important to understand the boundaries of responsibility. If the company has approved rules for cleaning counterparties, it is advisable to implement them at the corporate level, rather than creating independent versions in each analytical file.
Data Model
After preparation, an analytical model is formed.
To do this, it is necessary to define the main tables, keys, relationships, and metrics.
For example, a sales model may contain a transaction table and related dimensions of time, products, customers, and regions.
Such a structure allows for the analysis of one metric in different contexts.
Visualization
Based on the model, the user creates graphs, charts, and tables.
It is important that the visualization matches the nature of the indicator.
- To analyze sales dynamics, it is advisable to use a time-series chart.
- To compare regions, use a bar chart.
- To study the structure, use an appropriate proportional representation.
An incorrect choice of chart can complicate the interpretation of even reliable data.
Practical Example: Sales Analysis in Power BI Designer
Consider a hypothetical trading company that uses Microsoft SQL Server to store sales information.
Analytical data comes from the corporate DWH.
Management wants to get an interactive report that allows comparing revenue by regions, product groups, and time periods.
Step 1. Connecting the Source
The analyst opens Power BI Designer and creates a connection to the prepared data source.
To minimize load, it is advisable to use special SQL views or analytical tables that contain only the necessary fields.
Step 2. Verifying Information
After receiving the data, column types, correctness of dates, numerical values, and names are checked.
For example, the sales amount should be a numerical indicator, and the transaction date should be of the appropriate date type.
Information preparation also includes checking for the presence of necessary relationships between tables.
Step 3. Building the Model
The analyst creates relationships between sales, products, and regions.
DAX formulas are defined for key performance indicators.
For example, you can create separate metrics for sales amount and order quantity, and then calculate the average sales volume per order.
It is necessary to consider the rules for processing canceled operations and returns.
Step 4. Report Creation
The analytical canvas displays trend charts, comparative diagrams, and summary metrics.
The user can change filters and explore individual segments.
Step 5. Results Verification
Before submitting the report to management, it is necessary to compare the main indicators with the official corporate reporting.
If the total sales amount does not match, you need to check the model, the relationships between tables, and the aggregation rules.
It is at this stage that the importance of centralized metric management becomes apparent.
Power BI Cloud Model and Data Placement Issues
Microsoft is developing Power BI not only as a desktop tool but also as a cloud service for using analytical materials.
This creates opportunities to access reports through a browser and for users to collaborate.
However, for Ukrainian enterprises, it is necessary to assess what specific information is transmitted to the external environment.
Corporate Data Transmission
If an analytical model contains personal customer data, confidential financial indicators, or other restricted information, the legal and organizational conditions for its placement must be determined.
The enterprise must know what data is transmitted, who can use it, and how access is terminated after an employee’s authority changes.
For certain categories of information, it may be advisable to use anonymized or aggregated indicators.
Local Corporate Analytics
For enterprises with increased information control requirements, local platforms such as Microsoft SQL Server Reporting Services and Analysis Services remain relevant.
They allow for maintaining centralized analytics within one’s own infrastructure.
Power BI Designer can be considered an additional tool for research and visualization creation, but the choice of how to publish and refresh information should align with the platform’s available capabilities.
It should not be assumed that all local reporting and cloud service functions are interchangeable.
Data Model Performance and Limitations
One of the advantages of Microsoft technologies is the use of columnar storage and in-memory computations.
This allows for efficient execution of many analytical operations on large datasets.
However, performance depends on the model’s structure.
For example, an excessive number of text attributes with many unique values can reduce compression efficiency.
Incorrectly defined relationships between tables can lead to incorrect summaries.
Complex DAX expressions may require additional computational resources.
Therefore, designing an analytical model remains a task that requires technical competence.
Self-Service BI simplifies user operations but does not eliminate data modeling principles.
Power BI and Enterprise ETL Processes
It is important to correctly define the place of Power Query and Power BI Designer in the overall information architecture.
For small analytical tasks, the user can independently retrieve data from multiple sources and perform simple transformations.
However, for a large enterprise, critical reporting usually requires centralized ETL procedures.
For example, sales information from several ERPs must undergo checks for currencies, tax rules, product catalogs, and organizational structure.
If these transformations are performed separately in dozens of analytical files, there is a risk of discrepancies.
Therefore, a multi-level model is advisable.
At the first level, industrial ETL processes ensure information consolidation.
At the second level, the corporate DWH contains verified data and consistent metrics.
On the third, Power BI provides users with interactive analysis capabilities.
This approach allows combining the flexibility of self-service analytics with the requirements of corporate information governance.
Comparison with Other BI Platforms
Power BI is evolving in competition with Tableau, QlikView, Qlik Sense, and traditional enterprise BI solutions.
Each platform has its own characteristics.
| Platform | Primary Approach | Features as of June 2015 |
|---|---|---|
| Microsoft Power BI | Integration with Excel, modeling, cloud visualization | New service and Power BI Designer are in preview access |
| Tableau 9.0 | Visual data exploration | Interactive analytics, LOD calculations, improved data preparation |
| QlikView | Associative analytical model | Mature interactive analysis tools |
| Qlik Sense | Self-creation of analytical representations | Development of Self-Service BI based on the associative model |
| SQL Server Reporting Services | Centralized reporting | Standardized reports within the Microsoft infrastructure |
The choice depends on the nature of information sources, available licenses, personnel competencies, and data placement requirements. For enterprises already using Microsoft SQL Server, Analysis Services, and Excel, Power BI is of particular interest due to technological affinity.
However, this does not mean an automatic advantage over other platforms for all scenarios.
Organizational Aspects of Implementation
The implementation of Power BI should not be viewed solely as the acquisition of a new visualization tool. The enterprise needs to define the procedure for preparing information, rules for creating analytical models, and responsibility for metrics.
User training is a separate issue. Excel knowledge facilitates getting started, but is insufficient for correctly using DAX, understanding relationships between tables, and building complex analytical models.
For corporate implementation, it is advisable to form a small group of trained analysts who will create standard models and assist other users. It is also necessary to establish rules for publishing reports.
A personal experimental report should not automatically gain the status of an official corporate document.
Preparing data for Power BI
The effectiveness of implementing an analytical platform depends on the quality of the information base. Process and document management systems can supply records of approvals and other workflow steps for corporate reporting.
At the same time, regardless of the chosen platform, the enterprise must independently define the rules for calculating business indicators and the responsibility for their reliability.
Practical Guidelines for Implementation
Microsoft Power BI demonstrates the development of corporate analytics towards more accessible and flexible tools for working with information. The combination of Power Query, Power Pivot, DAX technologies, and interactive visualization allows for a gradual reduction in the dependence of business users on developers when performing daily analytical tasks.
Power BI Designer is an important step towards unifying data preparation, modeling, and report creation in a single environment. However, for enterprises considering the use of the new Power BI in June 2015, it is necessary to consider the preview status and test functionality on practical scenarios.
The greatest effect of the technology can be achieved with a quality corporate DWH, consistent reference data, and clear rules for managing metrics.
The future value of Power BI for enterprises lies not only in data visualization but in the ability to make corporate analytics more accessible without losing control over its accuracy.
Author