PowerBI, Tableau, Excel connection Wedia Analytics

Wedia Analytics offer a simple, standard oData connector.

oData is a standard for sharing Analytics data that is widely supported by Analytics software editors, such as Microsoft Excel, Microsoft PowerBI, Tableau…

Accessing the oData source from an Analytics tool

Wedia exposes the “raw” data that are used to build the analytics dashboard in an API endoint :

https:// {your Wedia dam address } /api/wedia/analytics/odata

You can try it in our demo system : https://club-wed.wedia-group.com/api/wedia/analytics/odata/

Securing who can access the analytics data.

These data may contain private information, and you can set up who can access them.

The access is protected by a valid DAM user name and password.

You may set up wich roles and users have access to this endpoint, by setting up the parameters of the WXM_Analytics_OData plugin configuration :

Open

Either this is secured by a list of roles Ids (4, 27 in our screenshot), or by user logins (comma separated), or both.

Starting with simple CSV file exports

In a vast majority of cases, an Excel file with the raw data export is sufficient. The oData API offers a simple system to publish raw data as CSV files, and it is a good starting point to get acquainted with the API.

The alternative endpoint that returns a CSV file with the raw data is almost the same, as the standard API endpoint, with “tools” appended as the endpoint name :

https:// {your Wedia dam address } /api/wedia/analytics/odatatools

Three data endpoints are available depending on the data scope that is required for the analysis :

• Data on who and when connected to the DAM :

  • /api/wedia/analytics/odatatools/connexion/csv

• Data on the actions performed in the DAM

  • /api/wedia/analytics/odatatools/dam/csv

• Data about the fulltext searches in the DAM

  • /api/wedia/analytics/odatatools/fulltext/csv

To query these data source, you will add parameters in the endpoint, that will indicate to the system what timeframe, sampling, … you would like to get.

To discover which parameters are available for each endpoint, a documentation is available with the following endpoint :

https:// {your Wedia dam address }/api/wedia/odatatools?urls.primaryName=%2Fapi%2Fwedia%2Fanalytics%2Fodatatools%20-%20plugin.wedia.analytics.odatatools.RestApiTools

This is the sample documentation from our demo system : https://club-wed.wedia-group.com//api/wedia/analytics/odatatools?urls.primaryName=%2Fapi%2Fwedia%2Fanalytics%2Fodatatools%20-%20plugin.wedia.analytics.odatatools.RestApiTools

The documentation lists all the endpoints available :

Open

Clicking on an endpoint will display all available parameters.

This can be tested “live” by using the “Try it out” button, that will show a form where parameters can be filled to generate a proper endpoint :

In testing mode :

Connecting Excel with the oDATA source

The Microsoft Excel “Data” tab offer a way to connect to a oData source :

From there, you can fill the oData endpoint of your application, based on the URL described in the first chapter of this document (Accessing the oData source from an Analytics tool) :

A popup asking for your login / password will appear.

The API exposes data in multiple data sources, that are sorted by scoped, year, and month :

The name of the datasource is constructed following the table below :

From there, you may either directly Load data, or Transform them :

Loading will just create a new workbook with the default setup :

Transform will give you the opportunity to better control the different loading options, exposed on the right tab :

For example :

• Only keep te first 150 first lines

• Remove some columns (id, “session_type”)

• Add conditions on columns value

• etc.

Connecting PowerBI with the oDATA source

The Microsoft PowerBI desktop software can connect to a oDATA source :

From there, you can fill the oData endpoint of your application, based on the URL described in the first chapter of this document (Accessing the oData source from an Analytics tool) :

The login / password to access the data must be filled in the connection screen :

From there, the available data source will be offered :

The API exposes data in multiple data sources, that are sorted by scoped, year, and month :

The name of the datasource is constructed following the table below :

From there, loading button will open the data connection and load the data into PowerBI :