Oracle Essbase End-user Tools

.

Turn around your Excel tables with the SmartView plug-in and discover further insights with Tableau

Excel is the most widespread tool across organizations for dealing with data, and whilst it is clear that it puts a big package of features and capabilities in the users’ hands, it is also worth noting that there are more options to enhance its performance and capabilities.

In this Oracle Essbase End-user Tools article, we'll explore how Essbase and its specific add-in for Excel can bring the performance of your staff to another level when a support tool for data-driven decision making or data analysis is required. Not only we will present the Excel add-in for Essbase, but we will also introduce Tableau as a complementary tool to analyze data from Essbase, thanks to its built-in connector.

 

1. Main Motivation

Excel is the world’s favorite data analysis tool, but we all too often see how a BI solution with a particular tool is then exported top Excel so that business users can carry on working in a comfortable environment.

Instead of trying to fight the end-users’ natural instincts, setting Excel against the BI system, a more suitable approach would perhaps be the following: Why shouldn't the BI system use Excel as its final consumption tool?

You might think that a regular Excel sheet is not fancy or powerful enough to meet the requirements of a BI report tool, but suppose that the BI system is going to use an enhanced version of Excel, specifically designed to gain crucial capabilities in two main aspects:

• Lightning-fast access to corporate data

• User interactivity with data. Drill-downs, drill-throughs, and other capabilities to explore and navigate the data

 

2. Essbase Technical Overview

Before starting a more detailed description of how Essbase could enhance your BI stack, let’s review what Essbase actually is from a technical perspective.

Essbase is as an On-Line Analytic Processing (OLAP) technology which consists of a Multidimensional Database Management System (MDBMS) for storing and analyzing data using a multidimensional approach. This key technologic feature gives us the developed Essbase applications, called cubes (or hypercubes).

In contrast with On-Line Transaction Processing (OLTP), OLAP is focused and optimized for processing queries instead of transactions.

It is also interesting to mention the etymology of the product name: Essbase stands for Spread Sheet Database, so it’s easy enough to see that it can be optimized for analyzing Excel-like data and enhancing Microsoft Excel for analytic purposes.

In particular, Essbase shines at financial reporting, scenario analysis (such as actual, budget, forecast, etc.) and, in general, at analyzing highly hierarchical data and complex formula calculations.

 

3. Benefits and enhancements of empowering Excel with Essbase

So far, we have introduced Essbase as a tool that could empower Excel in order to provide an enhanced experience to Business users working with data in spread sheets, and we have also outlined the technology behind it. But what exactly are the specific features and capabilities that this OLAP system can really offer Excel?

Let’s have a look at some of what Essbase can do for Excel users:

• Build your own ad hoc analysis in a user-friendly way.
And remember you will still be using your favorite spreadsheet program!

• Navigate the data.
Once a cube is accessed from Excel, exploring the data through all its dimensions is both fast (we are talking about sub-second query times) and intuitive. After a quick training session on the new features offered by this tool, users will find themselves slicing and dicing the cube according to the specific information they are looking for.

• Drill-down through the hierarchies of your dimensions.
Cube dimensions are designed in a hierarchical fashion in Essbase, and aggregation rules are always defined for its measures. This provides built-in drill-down functionality, allowing the end-user to retrieve data with the best-fitting detail for each analysis.

• Access pre-built reports directly in Excel.
Although ad hoc analyses may be the jewel of the crown, SmartView for Excel also provides functionality to develop reports so that they can be directly accessed by end-users.

• No exporting and importing data.
There is no need to export the requested data from any BI presentation tool for subsequent import to Excel; the data is already in Excel, and you only need to access the cube, look for the required data and build your own custom analysis. Bear in mind that once the data is in your spreadsheet, no one is going to prevent you from applying formulas to get new calculations to support your analysis.

 

4. Hands On: SmartView add-in for Excel

Now everything’s ready for show-time! Let’s proceed to a brief demonstration of how to access to your Essbase applications (cubes) with the SmartView plug-in for Excel.

Once the add-in (kindly provided by Oracle) is installed in the user’s local machine, the SMART VIEW tab will be available right after the default ones.

The very first time, it will be necessary to set up the connection to Essbase. Once this is done, connecting to the server is going to be as easy as:

➜ Open the SMART VIEW table

➜ Click the Panel button

➜ Access Shared Connections

➜ Fill in your user name and password

Figure 1: Applications (cubes) with the SmartView plug-in for Excel

Figure 1: Applications (cubes) with the SmartView plug-in for Excel

Note that all the security settings for data governance are managed through user accounts in the Oracle’s WebLogic domain.

Once logged in, we will be able to select the Oracle® Essbase server, where the developed applications will be shown; expanding the desired application will show the cube deployed.

Clicking the cube (in this example we are selecting TBC_PO) will make different options to access and manage it available.

Figure 2: Excel SmartView Shared Connections

Figure 2: Excel SmartView Shared Connections

In this case, we select Ad hoc analysis, thus loading the cube and making the ESSBASE tab available:

Figure 3: Excel Ad hoc analysis

Figure 3: Excel Ad hoc analysis

Here we can see the aggregation of total profit across all the dimensions. We can move dimensions in and out of the spreadsheet, set filters and drill-down to the level of detail required. In this article, we will perform two different ad hoc analyses:

 

Analysis 1

We want to look at the Sales per region across the year 2000. We will need both Market and Time dimensions:

Figure 4: Analysis 1

Figure 4: Analysis 1

Double-clicking the Market dimension will drill-down to the next level of the Market hierarchy:

Figure 5: Analysis 1 - Market hierarchy and Time

Figure 5: Analysis 1 - Market hierarchy and Time

In a few steps, we obtain the desired dataset (by drilling-down and pivoting the dimensions):

Figure 6: Analysis 1 - Sales by Market and Time

Figure 6: Analysis 1 - Sales by Market and Time

Analysis 2

Now let’s say that we are interested in the Sales by Product type and quarter, across the year 2000.

We will need both Product and Time dimensions. Then, as we did in the other analysis, we will drill-down until we get the desired detail of data.

Let’s have a look at the final dataset:

Figure 7: Analysis 2 - Sales by Product and Time

Figure 7: Analysis 2 - Sales by Product and Time

Now that the desired data has been gathered, it is up to the user whether there are going to be further steps in the analysis or not. In any case, the good news is that the data is already in Excel, so it is 100% ready to be played with.

As you can see, in a few steps we can build our own ad hoc analyses by querying the Essbase cube, which provides us with fast access to corporate data. And there is no need for the end-user to work in any BI-specific client tool; everything is done in the friendly Excel domain.

 

5. Hands On: Tableau with Essbase data sources

Now that we have seen the classic end-user tool for accessing Essbase applications, we are going to introduce an alternative, more oriented to data-discovering and visualizations, in a modern-day BI fashion. There are a number of similar tools covering this market, such as QlikView or Microsoft Power BI.

In this article we will introduce Tableau, which comes with a pre-built connector for Essbase. In particular, we will show how to perform the same analyses as above with SmartView, but taking advantage of the advanced visualizations to give them a special twist.

First of all, we need to make the connection to the cube. In order to do so, we have to:

➜ Click More Servers

➜ Select Oracle Essbase

➜ Fill in the connection properties

Figure 8: Tableau with Essbase data sources connection properties

Figure 8: Tableau with Essbase data sources connection properties

Figure 9: Oracle Essbase: Connection

Figure 9: Oracle Essbase: Connection

Note that a specific driver is required for this connection; if it is not already installed in the client computer, Tableau will provide a link to download it.

Once the connection with the server is set up, we need to select the application and the Database:

Figure 10: Tableau Database

Figure 10: Tableau Database

Clicking Sheet 1 will open the default blank sheet for the selected cube. Note that both Dimensions and Measures come with pre-built hierarchies (those we set up in Essbase):

Figure 11: Tableau Clicking Sheet 1

Figure 11: Tableau Clicking Sheet 1

Now we can proceed with the two analyses:

 

Analysis 1

We want to look at the Sales per region across the year 2000. In this case, we can take advantage of Tableau’s map visualization, and also use an animation to represent the Time dimension:

Oracle Essbase: Sales per region across the year 2000

Figure 12: Oracle Essbase: Analysis 1 - Sales per region across the year 2000

It’s a rather different output from before, isn’t it? Of course, there is no good or bad output; it all comes down to each analysis’s requirements. This one is much more useful in a scenario in which we are attempting to gain some extra insight from our data, while in the other, we might be more interested in the specific amount ($) of Sales for each region and month, or perhaps plan to use it as the input data for further calculations.

 

Analysis 2

We are interested in the Sales by Product type and quarter, across the year 2000. It is worth noting that we have the functionality to drill-down in the Time dimension, so that we can display the information by quarter instead of by year:

Oracle Essbase: Sales by Product type and quarter, across the year 2000

Figure 13: Oracle Essbase: Analysis 2 - Sales by Product type and quarter, across the year 2000

Again, the outputs of an equivalent analysis in Tableau and in SmartView are quite different; nevertheless, we maintain that both tools are complementary, each one better suited to different analytic scenarios.

Finally, I would like to provide a (non-exhaustive) summary list of extra features and limitations:

• Extra features
- Dynamic hierarchies inherited from the database (no need to build them in Tableau)
- Alias file support, in order to display the aliases created in Essbase
- Creating Calculated Members for the Measures, through MDX queries to the cube
- Security settings and functionality preserved from the Essbase configuration (within Weblogic domain)

• Limitations
- Extracts are not allowed, only direct connections to the cube
- Drill-down is not available for Measures
- It is not possible to create Dimension Calculated fields
- It is not possible to create Constraints for Level of Detail Expressions

 

Conclusion

In this article, we have been looking at how Essbase can empower your spreadsheets and take them to a new level. We have also introduced Tableau as a complementary alternative to SmartView for Excel, which may be better suited to certain analytic situations.

So far we have only spoken about end-user applications and how can they be empowered by Essbase, so now let’s have a brief look at some of the generic benefits of using Essbase:

• Centralized and consistent corporate information, with security settings governed by Weblogic server

• Outstanding performance and features for financial analytics

• Built-in writeback capabilities

• Fast, in-memory database. Response query times much lower than RDBMS

It is also worth noting that we have only mentioned a few end-user tools, but these are not the only ones. The different tools can be placed in three main groups:

• Specific tools for Essbase: like SmartView for Office or EAS Console

• Traditional BI tools: like OBIEE, which can have Essbase as one of its data sources

• Modern BI tools more focused on data discovery: like Tableau or QlikView

To summarize, Essbase is a data storage tool that excels in fields where RDBMS can’t match its performance in analytics with highly hierarchical information and complex formula calculations. Besides its core technical features, it performs really well as a database system for spreadsheet data, both because of its design and SmartView client tool. And it’s not only limited to spreadsheets - there are also more data discovery oriented tools, offering built-in connectors.

So, if you are looking for a way to enhance your company’s analytics, especially if you want to boost Excel or if you are in a financial environment, Essbase may just be the back-end tool you are looking for!

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

Cross-Database Join functionality with Tableau 10

.

Cross-Database Join, one of the most anticipated features in Tableau 10

Tableau 10 comes with a great new feature called Cross-Database Join, which allows us to cross data between different sources easily and intuitively.

In previous Tableau versions, you needed the Data-Blending solution to join data from different databases. This feature works well enough in one-to-one relationships, but unwanted asterisks pop up when we want to perform a join in one-to-many relationships. JOIN Data from Different Sources is one of the most voted for ideas in the Tableau community for avoiding this scenario and at last we got this great feature in August with Tableau 10.

In this article we are going to reproduce these painful asterisks by applying Data-Blending and then explain how to use Cross-Database Join functionality to escape such limitations.

 

1. Data-Blending

Imagine that we want to analyse sales by employee and region and that the data comes from different sources:

➜ Oracle table: contains region details

➜ Excel file: contains region details and sales by employee

Notice that in the second data source, there are multiple employees by region, so in order to cross data between both data sources we use the Region Name field whose relationship is one-to-many.

 

Figure 1: Primary Data source

Figure 1: Primary Data source

Figure 2: Secondary Data source

Figure 2: Secondary Data source

As we mentioned before, when we apply Data-Blending in one-to-many relationships we get asterisks in those cases where a specific region contains more than one employee.

Figure 3: Data-Blending

Figure 3: Data-Blending

Until now, there was no quick way to avoid these asterisks, and technical IT knowledge was needed to apply database federation between connections.

 

2. Cross-Database Join

Cross-Database Join is the new feature that Tableau 10 provides to cross data between different sources much faster and without any additional technical knowledge. Let’s explain how to perform a Cross-Database Join, step by step, using the same example and data sources as before.

First, we need to include the Oracle DB table as a primary source and the Excel file as a secondary source. Once both data sources are available, we need to carry out the following steps to apply Cross-Database Join:

1. Place on localhost connection (HR Oracle Schema data source).

2. In table area, double click on REGIONS to use this entity.

3. Perform the same steps to include Employee Sales entity from Employee connection (Excel file). Now you can see that Tableau tries to join both entities automatically.

4. Click on the circles icon; a Join set-up window will appear.

5. Select which kind of join you want to perform.

6. Select the specific fields of each entity that you are going to use to apply the join. In our example, they are “Region Name” field for REGIONS entity and “region Name1” field for Employees Sales.

7. After following the above steps, just click on “Update Now” to display the join results.

Figure 4: Cross-Database Join, step by step with Tableau 10

Figure 4: Cross-Database Join, step by step with Tableau 10

If we reproduce the same analysis as before (sales by employee and by region), Tableau 10 aggregates fields from the second data sources without any issues and asterisks do not appear in the analysis.

Figure 5: Data-Blending Tableau 9.3

Figure 5: Data-Blending Tableau 9.3

Figure 6: Cross-Database Join Tableau 10

Figure 6: Cross-Database Join Tableau 10

It’s easy to see the benefits of this new feature. Cross-Database Join functionality will allow us to cross data between different data sources and types in an easier and more intuitive way (avoiding those painful asterisks when using Data-Blending). It is a very interesting improvement that many Tableau users will welcome to create their daily scorecards.

If you want to know the latest news about Tableau 10 check our previous post and keep updated!

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

Tableau 10 Release!

.

Introduction

The Tableau 10 release is already here and we’d like to highlight the most important new features available in this version. Some of these features will revolutionize how we develop with the tool whilst others will make our lives much easier - just check out the cross database join functionality as an example. It also presents us with some new functionalities for Tableau final users such as the highlighter and, of course, Tableau is still improving the look and feel of the tool and its amazing visualizations, and it is now more flexible for mobile and tablet designs allowing you to consume your reports anywhere.

Tableau 10 Release

 

1. Analytics

We can detect some interesting new features in analytics. One of the most relevant is the cross data source filter - with this capability we can easily set a filter for the data sources feeding our visualizations. We can also build a cluster analysis by just dragging and dropping the functionality to our report, thus allowing the detection of patterns in the data. Finally, with the highlighter function, the dashboard consumer can mark the desired data. Other interesting functions are the custom territories, the new bar charts options available and more advanced analytics.

 

2. Data

In Tableau 10 we can see some big improvements in the way we interact with our data sources. The most important is the cross database join feature, which allows us to make joins between tables coming from different data sources. This will avoid data-blending in many situations and thus multiply analysis possibilities. In this version of Tableau you can also get email alerts and notifications if there is any issue when refreshing a published extract. In terms of data, there are some other new features like a connector to Google sheet and many others, enhanced maps data and more Oracle options in the connection.

 

3. Beauty

Tableau has also invested heavily in developing the look and feel of the tool and the resulting creations of the users. The workbook formatting feature allows you to apply a format to the entire workbook, thus saving time, and global changes are quicker. We can also see enhancements in the predefined fonts and titles.

 

4. Mobile

Here we can see another big improvement: the device designer feature can make a real difference as you can now define how you want to display your dashboards depending on the client’s device. You can design a different disposition of the visualization for mobile, tablet and desktop; and needless to say, there is an Android version of the mobile app.

 

5. Web

Here there are some improvements that offer more flexibility to web users: with web authoring it is possible to create new dashboards and add new data sources in the Tableau Server editor; with the revision history feature, the admin will be able to access the history of the published workbooks and data sources and restore or download an older version if desired. Furthermore, there’s more information on the statistics of your data sources and the publish flow to the server is now more user-friendly.

 

6. Enterprise

In Tableau 10 there is also space for IT perspective improvements. One of the most important is the subscribe others function, allowing you to subscribe other Tableau users to your visualizations so that they will automatically receive the view via email; there are other new features for the admins like the licensing views, new API functionalities for the developers, SAML support and sensible performance improvements.

 

Click here if you would like to receive more information about Tableau 10 or if you need any guidance with the migration process!

 

tableau-partner-logo

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!

 

Informatica Performance – Optimization techniques

.

Informatica performance - Optimization techniques

 

Informatica provides the market´s leading data integration platform. ETL Mappings are designed for data loading into the data warehouse environment to achieve better reporting which in turn helps you to understand business trends better. The major problem faced by anyone working with Informatica ETL is to design a mapping(s) that doesn’t compromise its performance. Often we end up creating a mapping that achieves only the functionality but suffers in terms of performance.

The article explains the steps to identify the performance bottlenecks and the advanced optimization techniques that are available in Informatica to overcome them.

Following topics are discussed in detail:

* Optimizing sources, Targets and Buffer Blocks:
Performance bottlenecks can occur when the Integration Service reads from a source database or writes to a target database. Depending on the source, target data, you may need to increase or decrease the buffer block size. In this article we list the steps you have to take to ensure that the sources and the Targets are fully utilized to optimum level and how to calculate Buffer block size.

* Push down Optimization (PDO):
Push Down Optimization Option enables data transformation processing to be pushed down into any relational database to make the best use of database processing power.

* Session Partitioning & Dynamic Session Partitioning:
The Session Partitioning Option increases the performance of Power Center through parallel data processing. In dynamic partitioning, Integration Service determines the number of partitions to create at run time based on factors such as source database partitions or the number of nodes in a grid.

* Concurrent Workflow Execution:
A workflow configured for Concurrent Execution can run as multiple instances concurrently. In the article we illustrate this functionality by using a workflow that is configured with multiple instances to process data belonging to multiple sites simultaneously.

* Load Balancing:
Load balancing is a mechanism which distributes the workloads across the nodes in the gird. In the article we see how to assigning Service Levels to Workflows to achieve Load Balancing.

Click to read the full article: Informatica performance - Optimization techniques!

 

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav