3 ways you can use Power BI to access ERP data

One of the many strengths of Power BI is the ability to access enterprise resource planning (ERP) data and liberate this data within the organization for analysis. Unfortunately, this creates challenges within organizations with highly functional ERPs and massive investments in tools like SAP, Dynamics, and Oracle. 

Within Power BI, there’s a host of different access paradigms that allow for access to this data and a few options for organizational controls. Let’s look at the different paradigms possible for ERP access and the options for organization-wide distribution of reporting that follow these options for data accessibility. 

Option 1: Direct query mode

Power BI has a host of connectors that can be modified or directly access your ERP system, including connectors to SAP BW, HANA, Dynamics NAV, Oracle, IBM, and others. Many organizations choose to enable direct query mode within Power BI to access data within their ERP.  But this becomes challenging, as ERP data is often not structured for easy analysis within Power BI. 

Virtually no organizations are using direct query mode for this reason, except in cases where the ERP produces schematized data that’s semantically intelligible. HANA does this and also supports in-memory caching, but most other tools do not, and so that is why most customers elect to use Import Mode for ERP data access. 

Option 2: Import mode

After initially experimenting with Direct Query Mode, most organizations will choose to either go down the path of import mode or create an incremental semantic layer on top of their data. 

Most of the time, the creation of this sort of connector requires incremental transformations from Power BI, as well as the addition of calculations. As a result of this common paradigm, Power BI released the preview feature of Dataset Certification so that organizations using Import Mode can more easily and readily engage with their datasets at scale, share queries, and have “blessed datasets” to enable analytics. 

The downside of this approach is a lack of enterprise controls. Although the Dataset Certification, DataFlows, and semantic modeling features of Power BI are excellent and very useful, most organizations don’t use them or haven’t used them because of their newness. All of these features are designed for distribution. So, a common result of this approach is a large group of ungoverned analysts, creating chaos without having organizational blessing on analytical reports. Many organizations will have the “Gary report” or “Cindy view” for their data as a result of this approach. 

Option 3: Separate semantic layer

Larger organizations start to adopt a nuanced approach as their maturity of Power BI usage increases. These organizations utilize their modeling tool (Azure Analysis Services, Power BI Premium, Power BI in Import mode) to define a more complex model decomposing ERP data, including detailed calculations for date-range roll-ups and other key performance indicators. 

This increased level of modeling often requires more computing power than can be provided out of the box by Power BI by default import mode. 

The downside of this approach is increased management costs from an IT perspective. While the other options make use of cheaper features, this approach requires a large up-front, ongoing implementation and maintenance effort. Also, complexity for data dictionary considerations is required since the aggregations and calculations commonly created in this view are increasingly different from those offered by default by an ERP. 

Best practices & conclusion

Typically, the best practice adopted by most organizations is to offer “blessed reports” that have audited & canonical connections to Power BI, based either on a separate semantic layer or Power BI in Import Mode. 

As a best practice, user-interface queues indicated these reports indicating IT or Finance audits are used. For example, logo applications, styling choices and consistency, layout, and filter uniformity indicate IT-blessed, audited, and managed reports. 

Conversely, it is also important to foster an analyst community so that users will be encouraged to manage their businesses more closely with data.  Without a vision towards promoting a data-driven culture, any BI initiative is doomed to failure and iniquity as users continue operating in the dark & making gut decisions rather than verifying hunches with data. 

Combining culture, a technical implementation approach, and the soft approach (top-down, bottom-up, or hybrid) referenced in the 2018 Power BI whitepaper Planning an Enterprise Deployment leads to the most successful implementations of Power BI. 

Good luck out there with your Power BI implementations! 

Learn More:

In order to learn more about SAP connection in Power BI, be sure to check out the Microsoft documentation, or the Power BI SAP Connection whitepaper here, on the Power BI whitepapers site.

This blog was originally published on 3/17/2020 and has since been updated.