OBIEE Data Lineage Solution

.

Tracking OBIEE reporting data

BI implementations may use hundreds of data warehouse tables and thousands of reports, and one of the most common requirements in a BI project is the ability to track back the data used in reports. The possibility to quickly identify the set of columns and tables used in a given report or dashboard, or to check which reports may be affected by a change of physical column in the data warehouse, is also crucial for development. This calls for a data lineage solution which is accessible to users and developers,  and allows checking such data on an ad hoc basis.

Various vendors offer data lineage solutions, but these can be expensive and vendor-specific. With our simple solution, we combine Catalog Manager and Administration Tool export sources to create an easily accessible solution for tracking data lineage in OBIEE.

By implementing the OBIEE data lineage solution, we can check the following:

  1. Which physical tables and columns are used in a given report or dashboard
  2. Which reports use given physical columns or tables; this is especially important when modifying an existing table, as any change in the table’s structure must take existing reports into consideration
  3. Which are the most commonly used columns in reports in a given subject area; identifying the most commonly used columns in a report can hint at creating indexes to improve the overall performance of the OBIEE implementation even further.

Solution

The ClearPeaks OBIEE Data Lineage Solution gathers all the required data lineage information in one place and uses it as a source for OBIEE reports within Data Lineage Subject Area. Two sources are combined to achieve this:

  1. List of OBIEE reports and presentation columns used

Catalog Manager provides an option to export the list of reports and columns used. The export can be done either with the Catalog Manager UI tool or through the command line utility. We use the latter option, as it allows automation of the whole process later.

  1. Mappings of presentation columns back to the physical columns

Such mappings can be obtained manually by creating Repository Documentation from Administration Tool’s Repository Documentation utility. The output will be a repository documentation in the form of comma separated values file (.csv). This will contain column mapping from presentation through logical to physical layers, including column formula expressions. Another way to obtain column mappings is by extracting the OBIEE repository in .xml file format through the command line utility. Our solution uses the second option, as the repository file will be used in our automated script.

OBIEE Data Lineage Solution

Once we have obtained both files, we need to populate the data lineage tables.

The data can be transformed and manually inserted into the tables, but in our solution we use a script (which can run on the OBIEE server) that parses the data and inserts it into the tables.

Data Lineage Subject Area

Once we have populated the data lineage tables and their data model has been mapped in Administration Tool, we can create and run reports in OBIEE using Data Lineage Subject Area and filter the results according to our requirements.

OBIEE Data Lineage Solution

Let us look at a few of the use cases for the Data Lineage Subject Area:

Use case 1. Which data warehouse tables and columns are used in a given report?

We would like to know which data warehouse tables and columns are used in a particular report or dashboard. We can create a report with a list of the columns used by a given OBIEE report and their data lineage:

OBIEE Data Lineage Solution


Use case 2.
Which reports use given physical tables?

We want to know how many and which reports or dashboards are using given physical tables or particular columns; this could be very useful when assessing the potential impact of column formula or table structure changes on reporting. Using Data Lineage Subject Area we can fetch up the list of OBIEE reports used by a given physical table:

OBIEE Data Lineage Solution


Use case 3.
Which reports use given subject areas?

We need to know which reports and dashboards are accessing data from given subject areas. This may be particularly useful when revising users’ access permissions.

OBIEE Data Lineage Solution


Future Improvements

OBIEE Data Lineage Subject Area can also serve as a backbone for further developments, providing complex and complete solutions for managing existing OBIEE project implementations. Here are some examples and potential benefits of merging additional information into the solution:

Usage Tracking data – allows analysis of the physical tables used in the most accessed reports, the tables and columns in reports not used by anyone, removing non-used reports or underlying tables.

Data warehouse database metadata – such as table size, indexes on columns. This allows for performance analysis of the most heavily used tables and columns by report usage.

ETL Data Lineage – additional layer of data lineage tracking – allowing the tracking of data back to the very source – can be achieved by adding ETL transformation data obtained from ETL systems. For example, it is possible to track all the ETL transformations on a given presentation column down to the source system.

Catalog metadata – it is possible to extract additional information regarding catalog objects such as user permissions, report owners, last modified date etc., to further enhance the solution usability.

Adding all the above components creates a strong core for the OBIEE management dashboard, allowing continuous improvement through:

* tracking data from the report to the source database column
* constant analysis of the most used tables in order to improve performance
* checking which users have permissions for given OBIEE reports and dashboards

All of the above is accessible from the OBIEE front-end, providing a convenient and quick way to facilitate many daily business-as-usual tasks in BI deployments.

Conclusion

The ClearPeaks OBIEE Data Lineage Solution can be easily deployed in any project using Oracle Business Intelligence Enterprise Edition. The solution can be run from the command line tools, which makes it possible to create automated jobs to extract and update data on a regular basis.

If you think your OBIEE project could benefit from our data lineage solution, please contact us for more information via our web contact form or by leaving your comments below!

Blog Article Author: Rafal Ostaszewski

Tableau quick integration in Salesforce

.

Salesforce is one of the leaders in the CRM sector and it is used in thousands of companies around the world. It is really useful software for managing the data of a sales department and it performs even better if you can combine it with Tableau. If you integrate Tableau into Salesforce you will be able to have the departmental reporting on the same page, saving time and making better analysis of the data.

Tableau offers you a solution with the Salesforce Canvas Adapter, but depending of the level of integration needed you can implement the following very easily. With the method explained here you could save time developing the integration and therefore save money too.

This method takes advantage of the flexibility offered by Tableau with the URL parameters and the facility to embed Tableau in an iframe. Also, Salesforce allows us to create some flexible objects with Apex programming language. Now we are going to explain the procedure for integrating Tableau with Salesforce in four quick and easy steps. Take into account that these actions need to be done in the dev version of Salesforce (sandbox) and then deploy it to production.


Tableau Salesforce 1
Step 1 – Tableau dashboard

Create a dashboard in Tableau and publish it to the server. This Dashboard has to be filtered with an available field in Salesforce. For instance, the ID for a certain account can be the perfect candidate if you are sure that exists in the EDW and in Salesforce. Once published, the URL provided by Tableau Server will be useful in the next step.

Step 2 – Visualforce page

Create a Visualforce Page in Salesforce:

This part can seem scary if you are not used to Salesforce development, but following this step by step guide can help you without needing previous knowledge in this area. All that is necessary to do this is to access the developer console in the sandbox environment.

Tableau Salesforce 2

Once inside the developer console is time to add the apex code below. The URL copied in the previous step needs to replace the red one. The filtering field in Tableau must replace the blue variable and the ID field of Salesforce replaces the green one. Name it and save it as a visualforce page.


<apex:page standardController="Account">

<apex:iframe src="http://tableauserver/views/workbook/dashboard?:embed=yes&CRMID={!account.AccountID__c}&:toolbar=no"

height="1110px" width="1160px" scrolling="true"/>

</apex:page>


Now we are going to analyse the relevant parts of this code:

  1. <apex:iframe – apex code to create an iframe where the Tableau dashboard will appear.
  2. http://tableauserver/views/workbook/dashboard - URL of the published dashboard in Tableau server.
  3. ?:embed=yes – parameter to embed the dashboard into the iframe.
  4. &CRMID={!account.AccountID__c} – part of the URL to filter the view. CRMID is the field used in Tableau and has to be set as a filter in the dashboard. !account.AccountID__c is the ID of the accounts used in Salesforce. Both fields have to be common.
  5. &:toolbar=no – Parameter to hide the toolbar.
  6. height="1110px" width="1160px" – height and width of the iframe. It is advisable to be the same as the Tableau dashboard (unless using automatic size).
  7. scrolling="true" – enables the option of having a scroll in case it does not entirely fit.

The console will look like this:

Tableau Salesforce 3

(!) The common fields for which Tableau has to filter have to be defined in {!account.AccountID__c} section of the code. To detect the proper name of the Salesforce field it is advisable to go to the fields section just to ensure that you are picking the correct name and not the alias that is used in the view.

Tableau Salesforce 4

Step 3 – Create Salesforce section

To create a section to place the Tableau dashboard you will have to edit the page layout. To enter the editor mode press the “Edit Layout” link.

Tableau Salesforce 5

Afterwards, drag and drop the section object to the desired emplacement and name it.

Tableau Salesforce 6

Step 4 – Add Visualforce page

The next step consist in drag and drop the Visualforce page that we have created in step 2 to the “Tableau Reporting” section added in step 3.

Tableau Salesforce 7

Once added, it is possible to edit the dimensions of the Visualforce page pressing the settings button.

Tableau Salesforce 8

Finally, you will have to save the layout and the Tableau report will appear in your account view filtered by the account that you are visualizing. Each time that you change the account in Salesforce the Tableau report will also change. The final result will look something similar to this:

Tableau Salesforce 9

 

 

Enjoy your integration!

 

Implementing KPI Trending in OBIEE

.

Business Managers are frequently provided BI reports designed to support their decision making process. These analyses typically include highly visual graphs showing how the company performs over time against a set of predefined indicators, also known as KPIs.

However, to build a report under OBIEE showing the historic trend of different KPIs is not as simple as it may seem.

Take for example...

KPI trending for Procurement

Suppose that a procurement manager needs to track the evolution of the following indicators:

·         Number of Winning RFQ Responses

·         Number of Tenders Under Process

Before going ahead with the solution, a few details about the RFQ business process need to be explained:

·        The first stage of The Request for Quotation process starts when the buying company opens a tender for acquiring a product/service.

·         In the second stage, suppliers can start bidding for that specific product/service by sending their RFQ responses.

·        Once the buyer decides which is the best option, the winning RFQ response is approved and the buying company places an order containing the terms agreed upon.

The following report would cover what the procurement manager requires:

 

Problem description

The main problem lies in the fact that the time dimension used in the trending analysis needs to be related to the dates that define the different KPIs:

1. The number of winning RFQ responses has to be shown according to the Tender Award Date.

2. For the Number of Tenders under Process, we’ll have to count the number of RFQs that are included in the date range defined between Tender Enter Date and Tender Close Date.

At this stage it is important to identify the specified aggregation for the two KPIs. For clarification purposes, let’s take a look at the company results in January 2012:

Winning RFQ responses

Setting a sum so the aggregation rule will bring the expected results:

  • Tenders Under Process

In this case, the last value of the period will lead to the results we need:

In the next few lines, we will explain how to conform these two dates and be able to report on a single time dimension analysis.

 Solution

1. Create a new time dimension, i.e. Dim_Date_PRC_Trending

2. Join it to the fact table within the physical layer and set a 1:1 relation.

3. Link the two tables in the business layer

4. Create two measures. One based on count distinct and the other based on the last period value:

  • Number of RFQs (sum) -> RFQ # (sum)

  • Number of RFQs (last period) -> RFQ # (last period)

5. Create the KPIs as the new logical columns in the related fact table

6. Set the formula of the two columns as it follows. This is where the join condition with the trending date will be specified:

Number of Winning RFQ responses:

Number of Tenders Under Process:

Setting Custom Data Format in OBIEE Answers

.

Sometimes it is necessary to display numeric values in specified format or replace the null values in table or pivot table with zeros or custom text. OBIEE Answers and Dashboards give us a possibility to customize the data masks for presentation. This could be useful when we want to change the display of data for the purpose of a given report. Using Custom Data Format feature we can change the masks for numeric values, change the display of null values or show dates in custom format.  In this article I will provide some examples on how to deal with custom data formatting for numeric values, dates and null values.

Continue reading this post >

APEX 4.2 goes live!

.

Last week, after more than a year since the release of v4.1, Oracle released version 4.2 of APEX. In this article I would like to show its most significant modifications. Moreover, you will see not only ‘this feature, that feature’ list, but also some examples of those new functionalities.

So, the most important modifications are:

-> Introduction of HTML5  charts/graphs, themes and components

-> Return of package applications

-> A lot of new functionalities for mobile devices:

  1. mobile interfaces
  2. new components added for mobile applications (of course already mentioned above: HTML5 & responsive themes also should be mentioned in this section )
  3. new ‘Dynamic actions’ like ‘swipe’, ‘tap’
  4. HTML5-mobile-oriented items like slider, on/off switch or calendar

-> Responsive themes

-> Refreshed APEX’s interface

With uncertain future of flash on mobile devices (or maybe word ‘certain’ is much more convenient: after this announcement by Adobe we finally realized that Steve Jobs was 100% right in that matter) there is no surprise that everybody is moving toward HTML5 and, well, looks like days of using flash in that kind of applications are numbered.

So now you just need to update your APEX to 4.2 and then we can proceed to take a closer look at those new, fancy features!

Continue reading this post >

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav