Tableau Tricks – Using Shapes and Bar Charts to get Instant Insights

.

 

Introduction

As Business Intelligence professionals, developing visually-intuitive dashboards for optimal data insights is one of our daily task. This is the first blog article of a series of articles where we will share useful tricks for developing key analytics tools with Tableau in order answer your questions or test your hypothesis.

In this blog article, we will explain how to combine shapes and bar charts to compare KPI performances over two periods of time. The main benefit of this approach is that the analysis is highly intuitive, so we get the data insights straight away.

 

Use Case

Imagine that you are the Head of Sales for the Mediterranean territory of a manufacturing company and you would like to know the difference between 2017 sales and 2016 sales by country (by percentage). As you can see, with this approach you can visually compare the sales by year for each country in a clear and easy way.

Below are all the steps that we need to follow in order to generate this analysis:

1 First, we need to create three calculated fields: “Sales 2017”, “Sales 2016” and “Sales Diff %”

Figure 1: Formula to calculate sales in 2016

Figure 2: Formula to calculate sales in 2017

Figure 3: Formula to calculate % difference between sales in 2017 and 2016. We multiply it by 100 because afterwards this value will be converted to string

 

2 Secondly, we have to create the bar chart, moving the “Country” dimension to Columns and the  “Sales 2016” and “Sales 2017” measures to Rows.

Figure 4: Tableau bar chart with separate measures

 

3 Then, you have to apply some steps to change the look and feel of the bar chart. Here, we moved “Measure Names” to the Color tile, applied “Dual Axis” to one of the bar charts and resized the size of the bars from the Size tiles.

Figure 5: Tableau bar chart with “Dual Axis” applied, and with the bar size narrower for “Sales 2017” measure

 

4 After customizing the look and feel, we need to create two new calculated fields, “Icon UP” and “Icon Down”. These fields will indicate if the sales in 2017 are increasing (Icon UP) or decreasing in relation with 2016.

Figure 6: Formula with the text when de “Sales Diff %” is positive

 

Figure 7: Formula with the text when de “Sales Diff %” is negative

 

5 Finally, we have to move these two calculated fields into the Label tiles to display the yearly sales variation. Note that you will also need to change the Color label to make this analysis more intuitive.

 

In the “Sales 2016” mark,  we put “Icon Down” into Label tile.

Figure 8: In the “Sales 2016” mark, we put “Icon Down” field into Label tile

 

In “Sales 2017” mark,  we put “Icon Up” into Label tile.

Figure 9: In the “Sales 2017” mark, we put “Icon Up” field into Label tile

And this is the final result:

Figure 9

Figure 10: Bar chart to compare sales between 2017 and 2016 with text that shows the % difference

 

 

Conclusions

We can use some icons as text instead of using shapes, so that we take advantage of embedment into Labels or Tooltips.

These kind of analysis are very engaging when we perform data analysis or when we are currently in the data discovery processes because they allow us to get insights in a very quick and visual way.

As a Tableau partner, we are certified with the last Tableau Certifications, so do not hesitate to contact us if you have any data challenge in mind.

 

OBIEE Security Dashboard

.

 

Introduction

As a reporting tool, OBIEE handles an organization’s critical data in the form of Reports and Dashboards. Considering the criticality of data, it is crucial to have robust security measures in place as part of the OBIEE implementation framework.

The roles and responsibilities of business users change frequently due to changes in their respective job roles or in their business’s organizational hierarchy. As part of maintaining data security, it is mandatory to review the entire access map for users across all catalogues and dashboards on a regular basis to ensure that there is no data leakage by any unauthorized user. This blog explains the idea of having a separate dashboard to accomplish this objective.

The following are benefits of having a separate security dashboard.

Provides a 360-degree overview of user access to OBIEE catalogues and dashboards
Fetches complete access details of any particular business user
Fetches the list of catalogs, users and AD groups (If applicable) mapped to any application role

 

1. Business Value

From the business point of view, it is crucial to have the user access control maintained meticulously. To achieve this, the auditors and business focal points for any subject area should review the entire access map of users across all catalogues and dashboard in their respective subject area on a regular basis to ensure there is no data leakage.

Below are some of the requirements from business focal points or auditors, in order to review security access in BI on a regular basis.

List of users having access to any corporate dashboards like HR, Finance, Purchasing, Procurement etc
BI catalogs or data access for a given business user
List of users, catalogs or AD groups mapped to an application role

OBIEE does not have any inbuilt solutions to fetch the above information on a regular basis. In order to retrieve this information, we will have to check the permissions granted to the required BI catalogs. A user can be granted access to BI catalogs through various means, such as by directly mapping to the catalog (not a good practice), by mapping the user directly to an application role that in turn is granted permission to access the catalog, or by adding the user to the AD group, which is mapped to an application role. There are even scenarios where an application role is mapped to another application role. In such cases, it is necessary to loop through application roles and extract the users granted access to the catalogs. In short, it becomes a tedious and time-consuming manual task when there are multiple catalogs involved.

This blog provides a high-level overview on consolidating this information in the form of a dashboard to help make security information easily accessible by both auditors and business focal points at any point in time.

 

2. Solution Overview

The following are the three levels where we can implement security in OBIEE:

Object level - Deals with access restrictions to various OBIEE catalogues/objects for different application roles/ users
Data level (Row level) - At this level, users have access to reports, but they see different sets of data within these reports based on their authorization to view data
User Level Security – At this level, the goal of the authentication configuration is to get confirmation on the identity of the user based on the credentials provided

To build a security dashboard we have to consolidate the security information from aforementioned levels into a data model for reporting. As part of data integration process, we have consolidated the information from Application Roles, Oracle BI Presentation catalogues and the active directory into a consolidated security master table. The final fact table for the data model fetches the data from the security master table. Below is the high level architecture of this solution.

Figure 1

Figure 1: Architecture of a BI Security Dashboard Project

As shown below in the dashboard, the ‘Dashboards’ tab comprises the first section of the security dashboard. This section provides access details from the dashboard(s) page perspective. Dashboards are grouped into different categories, enabling users to select the required dashboard category.

Figure 2

Figure 2: Dashboard Page of the Security Dashboard

The ‘Analysis’ tab provides access details related to standard reports created in BI. All standard reports, which are developed, based on business requirements and accessed by users for their job purpose, are kept in a separate folder – ‘Standard Reports’ in our environment. Under Standard reports folder, there are several sub-folders created for categorizing reports developed for respective subject areas. By clicking on any standard report, we can easily get the list of application roles mapped to it, along with the users mapped in those Application Roles.

Figure 3

Figure 3: Analysis Page of the Security Dashboard

The ‘Users’ tab provides access-related details for a particular user in BI. The dashboard prompt allows the selecting of a specific user whose BI access needs to be analyzed. On selection, basic user details are displayed, like their name, organization, location, employment ttype, job title etc.

Figure 4

Figure 5

Figure 4: Page of the Security Dashboard

 

Conclusion

The Security Dashboard project is one of the best solutions for providing a comprehensive overview of user access in OBIEE. Its implementation promises to ease the task of maintaining data security in any organization.

If you liked our solution stay tuned, in one of our next articles, we will show how to extract this data from Active directory. Meanwhile you can always contact us if you want to know more about the Security Dashboard project.

 

OBIEE Data API

.

 

Introduction

OBIEE Data API is a REST API using Node.JS to consume OBIEE 11g web services. The REST API identifies the call from external application using OAuth. The application helps to build a non-direct connection using rest calls to the data source rather than using a direct JDBC/ODBC connection.

We came across a situation where we needed to expose the data from OBIEE seamlessly to our mobile application in an agile way without tightly coupling the data layers with the mobile presentation layer. Using the OBIEE Data API will ensure the application server only connects to the data source via REST calls and then communicates to the users, as shown in the diagram below.

Figure 1: Data flow architecture

Figure 1: Data flow architecture

 

1. Motivation

Below are the key factors that drove the development of the OBIEE Data API:

How to get data from multiple silos for the enterprise mobile application. 

The Enterprise mobile application required data from multiple data sources, including OBIEE reports and Oracle and SQL databases of internal applications. We wanted to limit the configuration of new data connections to a single source to simplify the data flow to the mobile application.

All the complexity surrounding data source definitions, database drivers, and multi-silo network access should be the responsibility solely of the data API and should not be tied to the data reading application

How to conceal the direct connection details to data sources.  

As per the security audit recommendations we received, we needed to minimize the exposure of the direct connection to the databases from multiple applications and to conceal the actual connection details.

 

2. How does the Data API work?

When a predefined data endpoint is called from the external (mobile) application, the API checks if the called endpoint exists in the metadata dictionary of defined Data API entities.

If it finds a suitable match, then the API establishes a connection to the respective data source and requests to execute the logical SQL statement defined in the entity. The response received from the OBIEE server is rearranged into a predefined JSON format and sends back the final response to the external requestor.

Let’s see how the Data entity is defined and configured in the API.

Defining Entities

Data API entities are the defined SQL statements to be executed against a known data source connection. They include what parameters are to be used and how the flat JSON response is reshaped, as per the user requirements.

We can create data source connections to connect the OBIEE server as shown below.

Figure 2: Define data source connection

Figure 2: Define data source connection

 

The below screen describes how we define a new entity or data endpoint.

Every data entity has a unique name, a data source connection, a query to be executed, a list of parameters it can receive and process, and the configuration necessary to transform the raw data to the required JSON output.

Figure 3: Define data entity

Figure 3: Define data entity

 

How to connect to the OBIEE server using the API to retrieve data.

Data API operations predominately first attempt to establish a connection to the data retrieving system and then initiate the execution of a query to retrieve raw data.

From the OBIEE web services, we are using the SAWSessionService logon method to establish the connection. The response received from the logon method contains the session ID or token required to call further methods to fetch data from OBIEE instance. Once the connection is established, the executeSQLQuery method of XMLViewService is called by passing the logical query saved in the entity, along with the session ID needed to retrieve the raw data. The data formatter module of the API will reformat the received XML data to the required JSON format. A sample output of the data is shown below.

Figure 4: Sample Output

Figure 4: Sample Output

 

3. Is the Data endpoint secure?

Yes, Security mechanism within the API will ensure that the API call is from a trustworthy source. This is achieved through a token authentication method. Prior to the data endpoint call, the authenticate method has to be called to obtain a token. This token is sent as a header message along with the data end point call to the REST API server.

 

Conclusion: Applicability (Mobile Application, Web Applications...)

Commonly, enterprise data is hosted on a large number of systems. It is frequently hard to get access to them and quickly build new solutions, which need data from multiple silos. If you implement a RESTFUL API solution, which has access to multiple silo’s, you can expose the data in multiple silo’s to a large number of applications and their servers in a more agile way. Implementing REST API to expose enterprise data from multiple silo’s is a great way of setting your data free to any type of applications including mobile, web, embedded components, etc.

The OBIEE Data API initiative has now been enhanced to retrieve data from Oracle and SQL server databases using the oracledb and mssql npm packages, respectively. The REST API application services can be further extended to connect to mongodb, cloud web services, essbase cube, etc.

Figure 5: Solution road map

Figure 5: Solution road map

 

Click here if you would you like to know more about how to use the OBIEE Data API.

ClearPeaks´ Visualisation Plugin

.

 

Introduction

If you’ve worked with any kind of data, you know how harrowing reading rows and rows of numbers can be. It isn’t easy to wade through all of those numbers and easily figure out what they mean. That’s the case where visualisation comes to the rescue. Visualisation de-mystifies the data and helps decision-makers derive actionable insights from it. The whole idea of this blog is to delve into the blossoming world of data visualisation. It is hard to ignore the weight that more advanced and more interactive visualisations carry in today’s data-centric world. It is common to get requests to add visualisations or other improvements to the dashboard in order to provide a flashier look or design. Typically, these kinds of requests are handled by embedding HTML and JavaScript to provide data driven, custom visualisations. At ClearPeaks, we have developed a set of Custom Visualisations that allow configurable visualisations to work natively in modern browser technologies.
 

1. Motivation

Following are the main Key factors, which drive the need for Custom Visualization Plugins:

Add new Capabilities to existing chart types for modern reporting tool.
Add brand new Visualization types.

Let us discuss each one of these points in detail.

Add new Capabilities to existing Chart types.
How many times have you received a request that you just could not meet with native chart types because there was not enough ability to customize the chart? For example, imagine that you want to build a line/bar combo chart with a third distinct Y-axis. Oh sure, you can map three metrics, but if the metrics do not have the same unit of measurement or the same scale, then you are screwed. To handle these kind of scenarios, we have to use these Pre-build custom Plugins.

Add brand new Visualisation types.
There is huge scope for improvement in terms of Interactive Visualizations with traditional charts Library and there are so many new visualization Patterns we can build using JavaScript like Donuts, Meters, Pokers or Animated Donuts etc, which is hard to visualize with Traditional Reporting tools.

 

2. How does it work?

ClearPeaks’ plugin is solely based on native browser technologies and doesn't require client side plugins like Flash or Java. The plugin is built with jQuery and SVG. With the ClearPeaks plugin referenced in your webpage, we are ready to use a wide range of highly customizable charts. In case of the Oracle BI implementations, where their servers are isolated from the Internet and protected in internal LAN segments, referencing these resources from Oracle BI means having to first deploy them in the WebLogic Server as static resources.
ClearPeaks’ plugin allows us to easily customize the design of a chart, like its size, colour and fonts. Also, it is possible to include or exclude parts of the charts.
 

3. Data Visualization Graphs

In this section, we present some of the visualization graphs developed by our team:

Gauge Chart: Gauge provides a rich amount of configurable items, which can set options we pass through the plugin invocation.
Figure 1 - Gauge Chart
Donut Chart: A Donut Chart is a circle chart that shows the percentage of an activity.
Figure 2 - Donut Chart
Pyramid: A Pyramid chart displays a single series of data in progressively decreasing or increasing proportions, organized in segments, where each segment represents the value for the particular item from the series.
Figure 3 - Pyramid
TimeLine: Timeline allows to visualise the starting and ending of an activity.
Figure 4 - Timeline

Visualisation also includes the below charts.

Horizontal Bar Chart:
Figure 5 - Horizontal Bar Chart
Vertical Bar Chart:
Figure 6 - Vertical Bar Chart
Bubble Chart:
Figure 7 - Bubble Chart
Percentage Bar:
Figure 8 - Percentage Bar

 

Conclusion

ClearPeaks’ custom visualisation plugin provides some of the best and most unique visualisations available. The charts are highly customizable in terms of size, colour and design. Better yet, the visualisations can be implemented in OBIEE or any custom html reports or dashboards.

Click here if you would you like to know more about this innovative solution!

Displaying Geographic Data in OBIEE

.

 

Introduction - What is geographic data?

The main goal of Business intelligence is to transform raw data into meaningful and useful information for the purpose of enabling more effective operational insights, as well as more tactical decision-making. The nature and character of this raw data can be very heterogeneous, ranging from structured data of orders originating from transactional databases to unstructured data coming from clients´ Twitter feeds. Today, I want to focus on one specific data type: geographic data.

The term ‘geographic’ neither refers to the way data is stored, nor its source. Instead, it denotes a functional characteristic, meaning data can be somehow positioned on the Earth. More precisely, geographic data can be defined as data with an implicit or explicit association with a location relative to the Earth, either a point, a line or a polygon.

In the following images you can see a clear example of how important is to show geographic data properly. While it is very difficult to see a clear pattern on the bar chart, the map displays a much clearer picture. Indeed, it turns out we are visualising the latitude of each region of Spain. The conclusion is that, as I like to say, showing geographic data without a map means losing information.

What is geographic data? Comparison bar chart - map

Figure 1: Comparison bar chart - map

As you might know, most organisations have some geographic data among their data sets. It can be points with a client’s location or an event’s situation, lines representing streets or railways, or polygons with the shape of countries or some other customised regions. Geographic data is usually present, and hence, it has to be properly displayed in order to get useful insights and information from it.
 

1. Geographic Visualisations Components

When creating geographic visualisations, that is, maps with data on the top, three pieces or components are needed:

Background map: which is the map displayed on the bottom. It can be either an online map (Google Maps, Bing, TomTom, etc.), or an on-premise map (e.g. HERE or OpenStreetMaps) which was previously designed and stored. As you can see on the images below, the visual experience of an online map is hardly achievable with an on-premise one.
Examples on-premise maps

Figure 2: Examples background maps

Data layers: which are composed by a shape (a point, a line or a polygon) and data objects (measures and attributes). The critical issue is which shapes are identified by the visualisation tool and which are not. Geocodes comprised of latitude/longitude coordinates are usually identifiable as points, but not literal addresses. For polygons, only main administrative areas are usually recognised, while custom areas will have to be manually introduced.
High-end geographic information system (high-end GIS): which is the software that matches the background map with the data layers, renders the map and includes some extra spatial functionalities. Some examples of high-end GIS are Oracle MapViewer (for OBIEE), Tableau (already built-in) or libraries such as Google Maps JavaScript API, Leaflet or Kendo.

 

2. Creating Geographic Visualisations in OBIEE12c

When working with OBIEE12c, we have three main options to implement some nice geographic visualisations:

Oracle MapViewer with online or on-premise maps:Developing OBIEE built-in maps using the Map View analysis type, which is specially designed to display several map visualisations such as Color Fill Map, Bubble Map or Pie Graph Map. The background map can be either some online map like Oracle eLocation if you have Internet connectivity or a customised offline one. The MapViewer toolkit for OBIEE and the Oracle Spatial option for Oracle database are necessary. The pros are that no third party is involved in the solution, nor is any code needed. On the other side, the amount of visualisations is limited and requires the configuration of layers and background maps.
On-premise library with online or on-premise maps: Another solution is developing maps using a library on-premise, such as Leaflet or Kendo, together with a customised on-premise map, for a 100% offline solution, or with an online map. Remember, OBIEE allows you to run JavaScript code using Narrative View analyses. In this case, the pros are no extra Oracle tools needed, higher visualisation features and options, and no Internet required. However, the development and maintenance cost increases significantly.
Google Maps JavaScript API: The last main solution is developing maps using the Google Maps JS API (or some other geographic API). Again, we use Narrative View analyses to run the JavaScript code on OBIEE, but in this case you also need to enable cross-site scripting from the API to OBIEE server. The pros are increased user experience and better visualisation features, while some drawbacks are dependency on third parties and higher development cost.

In short, each solution has its pros and cons. For this reason, doing an analysis of the users requirements, the system limitations and the maintenance and development cost is a must before starting with the development.
 

3. Developing Geographic Visualisations with Google Maps JS API

In this section, we explore the possibilities of developing geographic visualisations in OBIEE12c using Google Maps JavaScript API. Specifically, we show three different dashboards, each with one map and several extra functionalities developed with HTML, CSS and JavaScript, such as the title and some navigation buttons.
The first one is a heat map colouring the area of towns by some measure. It also includes several background map styles, a legend with the minimum and maximum value of the measure, and a tooltip with some specific attributes and measures. Moreover, the traditional Google buttons can be configured. In this case the Street View button is hidden and only the zooming buttons are shown.

Developing Geographic Visualisations with Google Maps JS API

Figure 3: Developing Geographic Visualisations with Google Maps JS API - Dashboard 1

The next dashboard shows a heat map with a similar look and feel to the previous one. This one also incorporates a label with the number of points requested, which is very important to control due to performance issues. It is a perfect way to identify geographical patterns on the localisation of events.

Image 4

Figure 4: Developing Geographic Visualisations with Google Maps JS API - Dashbaord 2

The last one shows a markers map which uses specific icons to represent different values of a category. Also, a legend with the descriptions of the icons used can be shown or hidden by clicking on the information button. This is a great manner of introducing another dimension on a geographical analysis.

Developing Geographic Visualisations with Google Maps JS API

Figure 5: Developing Geographic Visualisations with Google Maps JS API - Dashbaord 3

Obviously, the complexity of the code depends on the characteristics of the map itself such as the type of map, the utilisation of custom geometrics or markers, the amount of auxiliary elements such as legends and tooltips, or the level of customisation of the background map. However, when working with OBIEE, there is another complexity element to take into account: The insertion of this code into the narrative view structure.
 

Conclusions

Although this article gives a more general overview of several topics related to the big world of geographic data, we can still get some conclusions from what has been said.
First and most importantly, if you have geographic data, remember to draw a map! Moreover, we have talked about the main components you need to create a geographical analysis, and the technical options to implement it in OBIEE. Finally we have shown some nice dashboards using Google Maps API.
Click here if you would you like to know more about displaying geographic data in OBIEE and the services we offer!

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav