Summarize Tableau Server permissions in one single view

.

Introduction Tableau Server permissions

Our customers are usually interested in knowing who has access to the different elements (sites, workbooks, views…) in the Tableau Server. Only the administrator knows this information and he will need to go element by element checking the Tableau Server permissions.

Figure 1: Current Tableau Server Permissions View

Figure 1: Current Tableau Server Permissions view

Furthermore, this information cannot be exported to any format, so in case we want to create a report, we will have to copy all the names manually. This work can become tedious, especially when the number of workbooks and users grows.

Our solution consists in a Permissions Dashboard that will allow us to have all the Tableau permissions organized by Site, Project, View and User. As all Tableau dashboards, it can also be exported to different formats.

1. Understanding the Tableau Server database

By connecting to PostgreSQL Tableau Server database, we will be able to get to all the necessary information to monitor the Tableau Server (i.e. user actions, permissions, logins, performance, etc).
In order to understand our solution, we recommend you to check what the PostgreSQL Tableau Server database offers:

➜ Create Custom Administrative Views: https://onlinehelp.tableau.com/current/server/en-us/adminview_postgres.htm
➜ Data Dictionary: https://onlinehelp.tableau.com/current/server/en-us/data_dictionary.html
➜ Enable external access to "Tableau" and "readonly" users: https://onlinehelp.tableau.com/current/server/en-us/adminview_postgres_access.htm

 

2. Connecting the Tableau Desktop to Tableau Server Database

As the Tableau Server is a PostgreSQL database, you will need to set a new PostgreSQL connection.
Click here to find out how to connect to the Tableau Server Database!

In order to get permissions information, we will need to query the following tables:

next_gen_permissions capabilities licensing_roles
users workbooks domains
system_users sites group_users
views projects groups

I recommend you to use a Custom SQL statement as follows:

SELECT DISTINCT * FROM (
    /** User views **/
    SELECT  s.name as Site,
            p.name as Project,
            w.name as Workbook,
            v.name as View,
            su.friendly_name as User
    FROM    next_gen_permissions ngp, 
            users u, 
            system_users su, 
            views v, 
            capabilities c, 
            workbooks w, 
            sites s, 
            projects p
    WHERE   ngp.grantee_id = u.id
            and u.system_user_id = su.id
            and ngp.authorizable_id = v.id
            and ngp.capability_id = c.id
            and v.workbook_id = w.id
            and v.site_id = s.id
            and p.id = w.project_id
            and ngp.grantee_type = 'User'
            and ngp.authorizable_type = 'View'
            and c.name = 'read'

    UNION ALL

    /** Group views **/
    SELECT  s.name as Site,
            p.name as Project,
            w.name as Workbook,
            v.name as View,
            uig.friendly_name as user
    FROM    next_gen_permissions ngp, 
            views v, 
            capabilities c, 
            workbooks w, 
            sites s, 
            projects p,
            (SELECT system_users.friendly_name, 
                    groups.id as group_id
            FROM    system_users, users, licensing_roles, domains, group_users, groups
            WHERE   users.system_user_id = system_users.id
                    and users.licensing_role_id = licensing_roles.id 
                    and	system_users.domain_id = domains.id 
                    and	group_users.user_id = users.id
                    and	group_users.group_id = groups.id) uig       
    WHERE   ngp.grantee_id = uig.group_id
            and ngp.authorizable_id = v.id
            and ngp.capability_id = c.id
            and v.workbook_id = w.id
            and v.site_id = s.id
            and p.id = w.project_id
            and ngp.grantee_type = 'Group'
            and ngp.authorizable_type = 'View'
            and c.name = 'read'
) as Workbook_Permissions

Figure 2: ER Diagram of the involved tables

Figure 2: ER Diagram of the involved tables


3. Building the Dashboard

Once we have the Data Source set, building the dashboard is straight forward. Find below a few tips that will improve the usability.

3.1 Manually: Adding an option to the mapping

Create a hierarchy that will allow us to drill down from site level to view level:     

Figure 3: Site-Project-Workbook-View Hierarchy

Figure 3: Site-Project-Workbook-View Hierarchy

Place the hierarchy in the columns shelf and the User in Rows shelf as follows:

Figure 4: Columns and Rows shelfs

Figure 4: Columns & Rows shelfs

This hierarchy will allow us to have the access summarized at site level and also detailed at View level without navigations.

3.2 Filters 

The filters will be defined according to our dimensions: Site, Project, Workbook and User.

Figure 5: Filters applied

Figure 5: Filters applied

3.3 Numbers of views

As the granularity of our dashboard is view, therefore the Number of records metric will show us the total views of each user at any level of the hierarchy. The value is shown in the tool-tip and also next to the blue shape.

Figure 6: View at Site and Workbook levels

Figure 6: View at Site & Workbook levels

Conclusion

To summarize, after customizing it with our logo and our colours, this is how the permissions dashboard looks like:

 

Figure 7: Customized permissions view

Figure 7: Customized permissions view

We can see for every element in the dashboard, who has access (blue shape) and who has not (red cross).

We have set a live connection, so all the data is always updated, keeping the permissions under control in one single view. Furthermore, this information can be shared by publishing the workbook in Tableau Server or by using the export option.

In conclusion, this dashboard is a useful solution to keep all the permissions under control in one single view. This will help us to ensure every user has the correct access increasing the security and maintainability of our Tableau Server.

Click here if you would like to receive more information about the topic!

 

Oracle Data Visualization Desktop – April 2016 Release

.

1. Introduction - Oracle Data Visualization Desktop

With the release of Oracle Business Intelligence Enterprise Edition 12c (OBIEE 12c), Oracle announced a new Data Visualization tool, aimed at ad hoc personal data discovery tasks. Oracle is putting a great deal of effort into developing this new tool, that is available as:

* component of Oracle BI Cloud Service (BICS)
* standalone cloud service named Data Visualization Cloud Services (DVCS)
* component of OBIEE 12c on premise
* a standalone desktop tool named Data Visualization Desktop (DVD)

Oracle Data Visualization Desktop

At the end of April 2016 Oracle released the first publicly available version of Oracle Data Visualization Desktop (DVD), under version 12.2.1.0.0 (and timestamp 20160422085526). In this blog post we will present the main characteristics of this tool (DVD); but most aspects are common to the other three above modalities.


2. Data Sources and Data Management

There are 3 types of data sources that can be used in DVD:

  1. Excel sheets, which allow complete offline analysis
  2. Databases like Oracle, SQL Server, MySQL, Teradata, Redshift, MongoDB, Spark, etc.
  3. OBIEE, where the user can connect to an existing Analysis or use a Logical QueryOracle Data Visualization Desktop

Once a source has been added to a project, DVD offers different options to manage the data:

* Modify data types (string, number, date, etc.)
* Alternate between Attributes and Measures
* Select the Aggregation rule of Measures
* Create Calculations using a wide variety of functions

Oracle Data Visualization Desktop

Multiple sources of different types can be added to a DVD project, joined automatically using fields with matching names; the joins can always be edited using the Source Diagram.

Oracle Data Visualization Desktop


3. Visualizations

One of the most important characteristics of DVD is the high number of visualizations available out-of-the-box. There are 22 data visualizations in total, plus the possibility of including Text Boxes and Images. All the available visualizations are shown in the image below:

Oracle Data Visualization Desktop

The visualizations are very easily created by dragging and dropping Data Elements (data columns in DVD) to the different Drop Targets (that is, the corresponding visual dimensions of the visualization).

Oracle Data Visualization Desktop

The visualizations can be highly customized in DVD. The user can edit the titles and axis, modify the colour schemes, sort the data, change the data format, etc.

In addition, the Map visualization allows you to create custom maps using the GeoJSON format. The underlying map engine is able to render the new maps and join them to the corresponding data element.

Oracle Data Visualization Desktop

Multiple visualizations can be combined in the Canvas, thus allowing the creation of complete dashboards to analyse the data. In addition, through the Data Brushing feature, the data selected in any visualization is highlighted in the others.

Oracle Data Visualization Desktop


4. Data Discovery and Advanced Analytics

As a Data Discovery tool, DVD includes multiple features to facilitate the data analysis process. One simple tool used for data discovery is the filters: the user can decide to filter Attributes based on values or Measures based on ranges.

Oracle Data Visualization Desktop

Together with the filters, Reference Lines and Trend Lines are available in DVD straight out-of-the-box. As well as these features, more Advanced Analytics tools are available in combination with R. For this reason, DVD includes an Oracle R Distribution (version 3.1.1) installer executable after the installation of DVD. When R and the required libraries are installed, we will be able to use Clustering, Outlier Detection and Forecasting, as well as custom R scripts.

In the example below we use Clusters to identify how the number of apartments by neighbourhood affects the price. In addition, we have a Reference Line to analyse the average apartment price for different room types. Finally, using Trend Lines, we can see that the relationship between minimum number of nights and price has been increasing over the last few years.

Oracle Data Visualization Desktop

Thanks to the data discovery and advanced analytics capabilities of DVD, we can easily identify hidden information and patterns in our data. In order to keep track of the data discovery flow, we can use the Story Navigator, which allows different insights to be saved. These insights are very useful when we want to share a project, letting other users understand our findings fast.

Oracle Data Visualization Desktop


5. Managing Projects

It is very easy to share Projects in DVD. The first thing to do is to save them locally; the different Projects are shown in the Home page. From the Home page we can select the option to export the Project, which will create a DVA (.dva) file. It is possible to store the source data in this file and protect it with a password.

Oracle Data Visualization Desktop

At the other end, we can similarly use the Import option to add the Project to our main folder in the Home page.


6. Oracle Data Visualization Training

We provide a wide range of training services designed to propel course delegates to a project-ready state, ensuring they have both the necessary BI theory as well as the hands-on practical skills needed to engage in a BI project with confidence.
Here in ClearPeaks we are experts on Oracle Data Visualization and we can offer you this expertise in our specialized training sessions.

Get in touch with us and see what we can do for you!

Blog Article Author: Iñigo Hernáez

A picture, or 1000 words?

.

If you are reading this article, it is probable that you, too, are working in the rapidly evolving world of corporate performance management, business intelligence, data science, data management, or are closely associated to it.  You will be acutely aware of the unprecedented hype and interest surrounding Big Data and will be asking yourselves how your organisation can profit from the complex mash-up of structured, semi-structured and unstructured data.

You will have heard that data is the new oil, the new high value corporate resource which, when discovered, explored, processed and refined, will be the fuel to drive your business ahead of your competitors for years to come.  But keeping abreast with the volume, variety and velocity of Big Data is only of value if you have tools and techniques to convert it into a useable asset, and that is where today’s data discovery and visualisation platforms play their part.

As a regular reader of The Guardian, a UK-based national newspaper, I follow their “Data” section, which helps me keep abreast of how data is used in solving problems across broad spectrums of life, industry and politics. But what is especially interesting to me in this section is how data is presented through high impact visual graphics to create compelling and enlightening stories for their readers.

The guardian

As little as 10 years ago, data visualisation was, in most part, restricted to national media stations, advertising gurus and graphical designer types, who would work with data analysts to convert spreadsheet-style input data into more appealing infographics for mass consumption through TV and national press.

And now, just a decade on, our appetite for highly visual information has dramatically diversified into the mass market of corporate consumers – from executive ‘C’ level managers down the ranks to entry level analysts.  The era of business storytelling and infographics is rapidly edging out the classic tabular report, pivot table and pie chart culture of the 80s and 90s.  Business intelligence tools are now expected to predict behaviour, track patterns and trends, highlight relativity and performance, all in one single interactive dashboard!

From a data perspective, I am sure you will share our sentiment that we are facing a revolution.

No longer is data the fenced territory of IT departments and database administrator types, but actually that data becomes high octane in nature when put into the hands of the business people who can really act on it, take critical decisions on it and who really understand it.  IT clearly have a key role in provisioning and governing business data, ensuring consistency, query performance , platform stability, security etc., but leaving the discovery and analysis in the eager and empowered hands of the business users who can make it work for the good of the business.

Working with industry leading information visualisation and discovery products, namely Tableau and Oracle Endeca, we at ClearPeaks are perfectly positioned to advise you - as an end user or BI/IT Manager - in this fast-paced, rapidly evolving world of big data, data blending and storytelling through powerful visualisation.  We can empower your business influencers and decision makers with rich functionality to detect patterns, trends and business metrics which otherwise would have lain undiscovered in the depths of your corporate data lakes.  We will coach and facilitate your business users to play and discover, to work creatively in order to present data in engaging ways for their audience, and most importantly,  help them excavate and reap tangible business value from their data.

If sight is our most efficient human sense (capable of interpreting visual information at network speeds), then business information presented in visually impacting formats must surely be a winning formula for ensuring corporate performance is monitored, managed and acted upon for optimal business benefit.

If you would like to get the benefit out of exploring your data, contact us and we will show you how to do it.

Oracle MAD Key Features

.

In 2013, mobile devices passed PCs to be most common Web access tools. Through 2014, JavaScript performance will push HTML5 and the browser as a mainstream application developer environment and by 2015, over 80% of handsets in mature markets will be smart phones. On the other hand connection speeds are increasing, local storage is losing importance and cloud computing is getting more importance every day.mad1

Under these facts, don’t you think it’s the right time to start thinking on how to move your BI platform to the mobile world?

In this blog we’ll review some of the relevant features of the Oracle Business Intelligence Mobile App Designer (MAD), a component from Oracle BI Foundation Suite developed to deal with mobile devices.

Don’t panic! Despite the name we will not get MAD using it. As you will see it is a very simple tool and provides excellent results even for non-expert users.

In the first part of this blog post we’ll review some visualization techniques introduced in Oracle BI MAD: navigation list, tile & accordion layouts (which are the most striking ones) and a new type of filters. If you are used to Oracle BI EE reporting, you might find them quite interesting.

In the second part of the blog post we’ll focus on some other interesting features: plugins, subscriptions and links.

1. Visualization Features

As a dashboard designer you’ll have to consider some specific aspects related to mobile devices: The screen is smaller than a regular monitor, and graphics and data must fit into the screen. On the other side, navigation is different than using a mouse. Oracle MAD was developed considering that. Based on HTML5, it includes enhanced visualization of charts, with transitions and animations that allow us to show data in a different way than a classical BI tool, as we will see below.

1.1  Navigation list

Navigation pages enable you to define a navigable set of filters that you can use to filter the data visualized on the page. The navigation area of the page contains a hierarchy of dimensions that you tap through to drive the display on the detail side of the page; you can see successively more refined sets of data, or navigate up and down the hierarchical filters to see the subset of data that interests you. Additionally, you can also give a conditional format into the navigation pane, so it is easier to drill into the hierarchy of interest.

The navigation pane is available through the new page editor menu.

In the next sample we’ll set a navigation list to analyze the revenue by period, drilling from Year to Month, just with a few steps:

Select New Page to add a navigation pane
mad2

In the navigation List we select the top level in the time hierarchy (YEAR) and the aggregation type Summation for the revenue metric.

mad3

Next, we add the Quarter and Month in the navigation hierarchy to be able to drill to the lower level.

Then we can set a conditional formatting for the revenue metric, based on the target revenue metric by clicking first the revenue text box, and then Edit spotlight button, to set up the thresholds

mad5

In the spotlight editor, we set up the conditions based on user defined thresholds, and the target revenue measure.

mad6

Once we have configured the navigation list we can proceed to add some charts into the layout, and we will be able to check the data at different levels of the hierarchy just tapping on the desired level in the navigation pane.

1.2  Tile and Accordion Layout

Tile pages provide a scrollable grid of cells, each containing identical components that are repeated for a specific field in your data. You can use tile pages to provide an instant view of a key measure across a specific dimension. This view not only provides a complete picture within each cell, but also enables an at-a-glance comparison across the data set. You can further enhance the visual impact of the values represented in each cell by creating stoplight formatting to highlight cells whose results fall outside the range of specific threshold values.

In the following image you can check the look and feel of the tile layout.

Accordion Layout allows to view data in a set of elements that we can expand vertically; it also allows to set a metric for each element in the dimension and conditional format in a similar way to the navigation pages.

To create it, just select new tile or accordion layout and give your custom format.

mad10

1.3  Filters

MAD allow to add filters to the layout, these filters are simply added to the layout in the insert tab in the menu bar, and will display values which we can tap to filter the data within the page.

mad11

Just click on filter and add it to the layout, once added, drag and drop an object from the data source into it.

mad12

mad13

Notice the top bar in the image above, where you can see years and quarters, acts as a filter to the all values in the layout.

Charts can be used as filters as well by tapping data into one chart. For example, we may want to filter a bar chart just to have the values that we have tapped in another chart.

As you will see in the following images, once you tap a graph, a text box appears showing that data has been filtered to show just the values for the object you tapped.

mad14

2. Other Features

2.1  Plugins    

A great feature of Oracle MAD is that allows us to code Java Script plugins enabling to customize the BI App at your own desire. Some plugins are built-in with the installation, such as geomaps. But you can also create your own custom plugins. To make a plugin available you just need to copy the code (plugin_name.js) into the BI publisher plugins directory.  It will appear in the Plugins toolbar. Once the plugin is available you just have to drag and drop it into the layout.

Let’s see some of the plugins you can add to your mobile app:

mad15

Another interesting plugin is the heat map, which shows rectangular areas and the value for a metric. We just need to add two levels of hierarchy and a metric value. It is also zoomed in when tapped the rectangular area, to see the details.In the screenshot above we drop the geomap plugin into the layout, together with the country dimension into the region box and a metric (in this case paid amount) into the value box.

mad16

Other less sophisticated, but effective as well is the following YouTube plugin that allows us to include a video in our mobile app.

mad17

2.2  Links

The Link button it’s a really cool feature included in Oracle BI Mad. As you might be guessing you can include a link in your report to a web page, but also you can send an e-mail, make a call or send a text message.

On your mobile App just click on the link button

mad18

And then select the type of link you want to set

mad19

As you can see for a Phone App there are 4 kind of links: web page, email, Phone and SMS. To create the link you can enter a data field value (from the list of fields on the left screen), a static value or both.

2.3  Displaying the app

Oracle BI MAD has an built-in emulator that allows us to see how the mobile app will look like. You can test the app at any time going to the emulator and selecting on PC. To test it into a physical device just click preview and click on Mobile. The page will show a QR that we can scan with our device to run the app, or the URL of the app we can connect to.

mad20

Once you display your app into the physical device, you may realize that layouts may not display identically as they were into the emulator. In order to avoid that, you just have to use the in build templates or use frame objects to set the display properly.

2.4  Subscriptions

The distribution of the mobile apps to the users is easily managed with the Apps library. As a developer you can publish a new app in the library once is it finished and ready to be distributed.

As a consumer, in the mobile device, you can choose the apps you want to subscribe. By doing that, when the mobile App is updated, you are notified a new version is available. The apps you are subscribed to, appear in the My Apps Library folder.

madx

Conclusions

Oracle BI MAD allows you to create mobile BI apps quickly offering very good results in a short period of time.  It’s based on HTML5 technology, so it works on most mobiles devices, including Android, iOS, Blackberry and Windows phone operating systems, either phones or tablets.

The designer offers you multiple options to get enhanced visualizations of your data with an easy navigation, tap driven. And there is no need to install any app on the devices, as the app is web browser based. The subscription is controlled by the Oracle Business Intelligence App Store.

If you want to get more information about Oracle BI MAD contact us or leave a comment below this post!

 

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav