Data Access - Getting to your data easily - the options

ODBC, ADO, RDO, OLEDB, OData, HDBC – its an acronym jungle out there when it comes to navigating your way though database connectivity which of course is the first step in building any reports or data visualisations.

So if you are an SAP Business One user how do you get connected to your data and easily extract the vital information that’s in there to make better business decisions?

SQL or HANA: A Critical Consideration

The first consideration of course is what underlying database are you using – Microsoft SQL Server or SAP HANA.

It may sound counterintuitive, but it is actually easier to get connected to your data if you are running on Microsoft SQL Server – this is partially because SQL Server has been around for such a long time and is standards based.

SAP HANA, despite its power, is still relatively new and unfortunately (and this is just my opinion) SAP have really focused on encouraging you to only use SAP products when you want to connect to your data.

Of course, part of the advantage of SAP Business One when its running on SAP HANA is the prebuilt analytical views and simplified data aggregates that come as part of the core delivered product also known as Pervasive Analytics which you can extend if you want to break out the SAP HANA Studio and start constructing new views or you have an SAP Business One Professional User license and want to use the built in tools.

This means that to create or consume these views you’ll need to be fully licensed SAP Business One user which in an organisation with a larger number of users can start to get quite expensive.

Ease of Access to Data: The Contenders

So, let’s look at the main contenders in the Analytics space and what capabilities to direct connect to your data they give you.

Please note that I am ignoring the export to Excel options as these days no-one wants to be playing that game and it leads to potential data mismatches.

• Microsoft Power BI                         

SQL and HANA via ODBC (Data sync to Azure Cloud via On Premises Gateway Tools)

• SAP Lumira                                         

HANA (On Premises)

• SAP Analytics Cloud                        

HANA (data sync to SAP Cloud Platform)

• QlikView                                             

SQL and HANA via ODBC (On Premises)

• Phocas                                                 

SQL and HANA via ODBC (data sync to Phocas Cloud via Sync Tool)

• BI360                                                    

SQL and HANA via ODBC (onPremises or Azure Cloud)

• Excel with Power Query               

SQL and HANA via ODBC (OnPremises)

The three winning options here in terms of ease of connectivity for my money are Phocas, PowerBI and BI360 because they offer that native connectivity to both SQL and HANA however I tend to lean towards Phocas and BI 360 for the native data warehouse option that they offer.

So that’s our primary dimension of data connectivity addressed and there’s no clear winner here but a couple of definite top contenders in the pack.

The Importance of Semantics

One final important point to note and it’s an important one – even though you can connect to the data don’t underestimate the importance of a semantic layer or a set of pre-built data views of critical business data.

Wikipedia: A semantic layer is a business representation of corporate data that helps end users access data autonomously using common business terms. A semantic layer maps complex data into familiar business terms such as product, customer, or revenue to offer a unified, consolidated view of data across the organization.

This will save you hours of work trying to understand table structures and building the right kind of queries to get what you need but we’ll talk more about that in our next article on design tools.

Source: Richard Duffy | CEO and Founder at SMB Cloud Solutions - Brewing and Distillery Technology Solutions