Category: Business Intelligence

Business Intelligence, Business Intelligence Platform

Error downloading data to Excel in OBIEE 12c

If you get the error …

“There was an error processing your download. Please check with your administrator.”

… when Exporting / Downloading data from an analysis in OBIEE, then this post might be helpful.

In OBIEE, you have a few options for exporting / downloading data from an analysis / report as shown below. You can export / download to PDF, Excel 2007+, PowerPoint 2007+, Web Archive (.mht), or in CSV, Tab Delimited, or XML data formats.

OBIEE_Export_Analysis_Data

If you are trying to download data from an analysis and get this error …

OBIEE_Export_to_Excel_Failed

“There was an error processing your download. Please check with your administrator.”
then try the following fix. Note, you might find that you are able to download to CSV, but get the error when you try to download to Excel.

Edit the config.xml file
Located at the directory specified below (for OBIEE 12c):
ORACLE_HOME/user_projects/domains/bi/config/fmwconfig/biconfig/OBIJH

FYI, this is the location of the config.xml file in OBIEE 11g
[MW_HOME]/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1

Locate the section and the parameter within that section. It may look like below, but there could be more or less parameters within the section than shown here.

<XMLP>
<InputStreamLimitInKB>8192</InputStreamLimitInKB>
<ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>
</XMLP>

Change the value of the InputStreamLimitInKB parameter to 0 as shown below…

<XMLP>
<InputStreamLimitInKB>0</InputStreamLimitInKB>
<ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>
</XMLP>

Restart the OBIEE services and try your export again.

If it succeeds, then we know for sure that altering this parameter fixes it.
Setting the above parameter value to zero (0) means that there is no limit. So, you may now go back and modify the value to a number that is suitable for your environment, such as, 8192, 15384, 65536, etc.

Business Intelligence, Business Intelligence Platform

Error on reports after upgrading to OBIEE 12.2.1.1.0 – “nQSError 35029: Unable to evaluate text 0.0 as either true or false”

After upgrading or migrating to OBIEE 12.2.1.1.0, you may encounter this error: “nQSError 35029: Unable to evaluate text 0.0 as either true or false”.
This is a known bug and there is a patch for it.

The Bug # is: 24005980
And the Patch # and description is: 24005980 – RPD CONSISTENCY ERRORS AFTER UPGRADE FROM 12.2.1.0.0 TO 12.2.1.1.0.

Applying this patch resolved the issue for us.

Good luck with your resolution.

Business Intelligence Platform, Data Development, Data Integration, Data Integration (ETL)

InfatoODI – Informatica to ODI conversion tool

We are currently in the process of upgrading Oracle Business Intelligence Applications (OBIA) from version 7.9.6 to OBIA 11g.  Oracle has replaced Informatica as the data integration tool in the platform with it’s own tool, Oracle Data Integrator (ODI). This was a selfish, profit-driven move on Oracle’s part with no consideration for the impact on customers, but it is what it is.

Because of this, as a part of the upgrade to the new OBIA release, we need to convert all our hundreds of Informatica mappings to ODI.  As you can imagine, this is a lot of work.  We are getting help from a company that has developed a specialized conversion tool called InfatoODI, which converts Informatica mappings to ODI interfaces.

We are performing the conversions specifically for an OBIA application, but the tool can be used as a straight conversion tool for Informatica-to-ODI for any type of application.

We are in the beginning stages of the project, but early indications are that the tool will save us time, but I am not sure how significant as yet. I will post updates as we progress through the conversions with my experience and opinion of the tool.

Business Intelligence

OBIEE 11g vs OBIEE 12c – What’s new in OBIEE 12c

In this post I highlight a few of the new features of OBIEE 12c, and in some cases show how they differ from OBIEE 11g.

The OBIEE Home Page looks a bit different, and includes a new “Data Exploration & Discovery” option and functionality.

OBIEE 11g
Whats_new_OBIEE12c_OBIEE_11g_HomePage

OBIEE 12c
Whats_new_OBIEE12c_OBIEE_12c_HomePage

OBIEE 12c has a new visualization feature:  Mouse-over highlights the selected area with animation.

For example, the below image shows what it looks like when you mouse-over the “14.8% purple” slice.

Note: In reports where the selection drills on the entire stacked column (such as in a vertical stacked graph), the drilling will operate the same as before – that is – if the drill was done on the selected area only in 11g, then that will continue to occur; If the report was drilling on the entire stack/column when clicked in 11g, that will continue to occur also (even though the animation only happens on the area that was clicked).

Whats_new_OBIEE12c_new_visualization_feature

The Dashboard “Page Options” icon has changed from the “3 lines with the down arrow” to a “gear” icon.

OBIEE 11g
Whats_new_OBIEE12c_Dashboard_Page_Options_11g

OBIEE 12c
Whats_new_OBIEE12c_Dashboard_Page_Options_12c

OBIEE 12c now provides the ability to Sort in graphs by right-clicking and using the pop-up menu. So, you will now see the Sort option along with the Action Links when you right-click on a graph.

Whats_new_OBIEE12c_Sort_in_Graphs

For report developers:

In OBIEE 12c: It is now possible to modify Column Properties from the Results tab – more efficient.

Whats_new_OBIEE12c_modify_column_properties

In OBIEE 12c: There is a new “Scale for % (x 100)” option in the Column Properties – Data Format tab.

Whats_new_OBIEE12c_new_scale_in_column_properties

In OBIEE 12c: You can create a new Calculated Column in the Results tab by clicking the “ruler” icon.

Whats_new_OBIEE12c_new_calculated_column

After adding the column, use the new “Save Column As” option to save the column. This is great feature that will allow for re-using calculated columns instead of having to re-enter the formula each time.

Whats_new_OBIEE12c_Save_Column_As

OBIEE 12c introduces 2 new visualization options – Tree Map and Heat Index

OBIEE 11g
Whats_new_OBIEE12c_Vizualization_options_11g

OBIEE 12c
Whats_new_OBIEE12c_Vizualization_options_12c

OBIEE 12c introduces a new Global Variable type, that can be a value or an expression.

OBIEE 11g  Whats_new_OBIEE12c_Variable_Types_11g                OBIEE 12c Whats_new_OBIEE12c_Variable_Types_12c

In OBIEE 12c, there is a new “Subject Area Sort Order” option available in Account properties.

OBIEE 11g
Whats_new_OBIEE12c_Account_Properties_11g

OBIEE 12c
Whats_new_OBIEE12c_Account_Properties_12c

OBIEE 12c provides the ability to search a subject area by keyword entered, and to sort folders and columns in a subject area.

OBIEE11g  Whats_new_OBIEE12c_Subject_Areas_11g                OBIEE 12c Whats_new_OBIEE12c_Subject_Areas_12c

In OBIEE 12c, there are a few new analytic functions.  A new Analytics folder contains new functions Cluster, Evaluate Script, Outlier, Regr, and Trendline. The Aggregate folder contains a new function, Bin. And the Time Series Calculations folder contains a new function, Forecast.

Whats_new_OBIEE12c_New_Analytic_Functions_12c_1          Whats_new_OBIEE12c_New_Analytic_Functions_12c_2          Whats_new_OBIEE12c_New_Analytic_Functions_12c_3

Analytics, Business Intelligence, Data Analysis, Data Integration, Data Integration (ETL), Data Science, Data Security, Data Strategy, Data Warehousing, Reporting

Creating a Business Intelligence (BI) & Analytics Strategy and Roadmap

This post provides some of my thoughts on how to go about creating a Business Intelligence (BI) & Analytics Strategy and Roadmap for your client or company.  Please comment with your suggestions from your experience for improving this information.

 

When creating or updating the BI & Analytics Strategy and Roadmap for a company, one of the first things to understand is:

Who are all the critical stakeholders that need to be involved?

Understanding who needs and uses the BI & Analytics systems is critical for starting the process of understanding and documenting the “who needs what, why, and when”.

These are some of the roles that are typically important stakeholders:

  • High-level business executives that are paying for the projects
  • Business directors involved in the usage of the systems
  • IT directors involved in the developing and support of the systems
  • Business Subject Matter Experts (SME’s) & Business Analysts
  • BI/Analytics/Data/System Architects
  • BI/Analytics/Data/System Developers and Administrators

 

Then, you need to ask all these stakeholders, especially those from the business:

What are the drivers for BI & Analytics? And what is the level of importance for each of these drivers?

This will help you to understand and document what business needs are creating the need for new or modified BI & Analytics solutions. You should then go deeper to understand … what are the business objectives and goals that are driving these business needs.  This will help you to understand and document the bigger picture so that a more comprehensive strategy and roadmap can be created.

The questions and discussions surrounding the above will require deep and broad business involvement. Getting the perspective of a wide range of users from all business areas that are using the BI & Analytics Systems is critical.  The business should be involved throughout the process of creating the strategy and roadmap, and all decisions should tie back to support for business objectives and goals. And the trail leading to all these decisions must be documented.

Some examples of business drivers include:

  • Gain more insight into who our best customers are and how best to acquire them.
  • Understand how weather affects our sales/revenue.
  • Determine how we can sell more to our existing customers.
  • Understand what causes employee turnover.
  • Gain insight into how we can improve staffing schedules.

 

And examples of business objectives and goals may include things like:

  • Increase corporate revenues by 10%
  • Grow our base of recurring customers
  • Stabilize corporate revenues over all seasons
  • Create an environment where employees love to work
  • Reduce payroll costs without a reduction in staff, for example, reduce turnover.

 

Then, turn to understanding and documenting the current scenario (if not already known). Identify what systems (including data sources) are in place, who are using them (and why and how), what capabilities do they offer, what are the must-haves, and what are the pain points and positive highlights.

Also, you will need to determine the current workload (and future workload if it can be determined) of the primary team members involved in developing, testing, and implementing BI & Analytics solutions.

This will help you understand a few things:

  • Some of the highest priority needs of the users
  • Gaps in capabilities and data between what is needed and what is currently in place (including an understanding of what is liked and disliked about the current systems)
  • Current user base knowledge and engagement
  • IT knowledge and skills
  • Resource availability – when are people available to work on new initiatives

 

What are the options and limitations?

  • Can existing systems be customized to meet the requirements?
  • Can they be upgraded to a new version that has the needed functionality?
  • Do we need to consider adding a new platform or replacing one or more of the existing systems with a new platform?
  • Can we migrate from/integrate one system to/with another system that we already have up and running?
  • Are any of our current systems losing vendor support or require an upgrade for other reasons? Has the pricing changed for any of our software applications?
  • What options does our budget permit us to explore?
  • What options do our knowledge and skills permit us to explore?

 

Once you have identified these items …

  • Identify and engage stakeholders, and document these roles and the people
  • Identify and document business drivers, objectives and goals
  • Understand and document the current landscape – needs (including must-haves), technology, gaps, users, IT staff, resource availability, and more
  • Identify and document options – based on current landscape, technology, budget, staff resources, etc.

… you can develop a “living” Strategy and Roadmap for BI & Analytics. And when I say “living”, I mean it will not be a static document, but will be fine-tuned over time as new information emerge and as changes arise in business needs, technology, and staff resources.

 

Your Strategy and Roadmap for BI & Analytics should include, but is not limited to:

  • BI & Analytics that will be used to satisfy business drivers, objectives and goals
  • Data acquisition and storage plan for meeting the analytics needs
  • Technology platforms that will be used to process and store data, and deliver the analytics
  • Information about any new technologies that needs to be acquired or implemented, and schedules
  • Roles and Responsibilities for all stakeholders involved in BI & Analytics projects
  • Planned staffing allocations and schedules
  • Planned staffing changes and schedules
  • User training (business users) and Delivery team training (technical implementers & developers for example)
  • List dependencies for each item or set of items
Business Intelligence, Business Intelligence Platform

When Export to Excel (csv) in OBIEE 12c, rows are limited to 65000 (65K)

After upgrading to OBIEE 12c, you may have noticed that your downloads to Excel CSV files are now getting cut off at 65000 rows.  If you are experiencing this issue, this post may help.

In OBIEE 11g, there was a parameter (instanceconfig.xml parameter) called “DefaultRowsDisplayedInDownload” that controlled the number of rows downloadable to a CSV file.   So, if you had that set to a number higher than 65K, then you would have been able to download more than 65K rows in the past.

However, the parameter that now controls the number of rows downloadable to CSV in OBIEE 12c is “DefaultRowsDisplayedInDownloadCSV“.  You will need to set this parameter in the instanceconfig.xml file based on your needs.

This parameter DefaultRowsDisplayedInDownloadCSV is found within the <Table> section which may look something like this:

<Table>
< DefaultRowsDisplayedInDelivery>1000000</DefaultRowsDisplayedInDelivery>
< DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>
< MaxCells>10000000</MaxCells>
< MaxVisiblePages>50</MaxVisiblePages>
< MaxVisibleRows>1000000</MaxVisibleRows>
< MaxVisibleSections>100000</MaxVisibleSections>
< DefaultRowsDisplayed>100</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDownloadCSV>200000</DefaultRowsDisplayedInDownloadCSV>
< /Table>

As always, back up your files before making changes.  Then, change the parameter as needed, and restart OBIEE services.

Business Intelligence, Data Analysis, Data Careers

Your career in 2018 (referencing 2017 Gartner Magic Quadrant for Business Intelligence and Analytics)

How will you grow your career in 2018?

Each year Gartner publishes Magic Quadrants for several technologies, including one for Business Intelligence and Analytics. Let’s take a look at this year’s.

The “2017 Gartner Magic Quadrant for Business Intelligence and Analytics” document was published earlier in the year, but as we are at the end of the year, it is a good time to take stock of the industry and your career in this field.

Below is an image of the results published by Gartner for 2017.  As you can see, from their perspective, Tableau and Microsoft lead the way, with Qlik also in the leader quadrant.

Source: Gartner

Since the same 3 players were also in the leader quadrant in 2016 and 2015 (along with others), we know they are solid.

So, based on this, if you are primarily an Oracle BI (OBI), IBM Cognos, or SAP Business Objects (BO) person (for example), you may consider learning or at least getting exposure to one or more of the 3 platforms in the Leaders quadrant.  There is still plenty of work out there for OBI, Cognos, BO and other platforms, but having additional technical/platform skills in leading platforms never hurts.

And this is just a broad suggestion, because you may find that developing skills in Salesforce (for example) is a better strategic move for you based on your situation.  The bottom-line is, assess your career/skills/goals/etc., and plan on learning something new in 2018.  Don’t stop learning!

Happy New Year to you, and best wishes for 2018!

 

 

 

 

Business Intelligence, Business Intelligence Platform, Reporting

QlikView Sheet Objects Quick Overview

This post provides a quick overview for each of the QlikView Sheet Objects.
Sheet Objects are the various objects that a developer or analyst places on a QlikView document’s sheet to provide the rich functionality of dashboards.

By right-clicking on an open area in a sheet, the below menu will appear.  Select “New Sheet Object” to see the available Sheet Object options – which are – List Box, Statistics Box, Multi Box, Table Box, Chart, Input Box, Current Selections Box, Button, Text Object, Line/Arrow Object, Slider/Calendar Object, Bookmark Object, Search Object, Container, Custom Object, and System Table.
Qlik_SheetObject_ListBox

The sheet objects can also be created from the Design Toolbar selections.
Qlik_SheetObject_DesignMenu

Some of our example images are taken from the Movies Database application that comes with QlikView.

List Box
The “List Box” is one of the most heavily used QlikView sheet objects.  It lists all the distinct values of a particular field. For example, you may have a List Box that displays all the products available within your data set.  The List Box is often used as a menu, where a user selects a value to analyze the data based on that value.
Qlik_SheetObject_ListBox_example2
The above example displays 3 List Boxes – the Director list box, the Actor list box, and the Title list box. When a user selects one or more values from one or more list boxes 9or any other object), the data in all other objects cascade to show only records relevant to that combination of selections.

Statistics Box
The “Statistics Box” is used to show aggregated representations of the data.  There are several statistical aggregation functions that are available, such as Sum, Average, Min, Max, Std Dev, Null Count, and several more.
Qlik_SheetObject_StatisticsBox_example
The above example shows “Statistics” for the Rating field – the number of ratings, the average rating, the min and max ratings, and the average and median ratings.

Multi Box
A Multi Box allows you to display several fields in a single object, with the values for each field available via a drop down. This object can be described as a set of List Boxes compacted together. The example screenshot below shows a Multi Box with the 3 fields, Director/Actor/Title, and by clicking any of the fields, you will get a drop down of all the values for that field – similar to a List Box for that field.
Qlik_SheetObject_MultiBox_example

Clicking on Director will display the list of all Directors, as shown below. Similarly, you can Actor or Title to get those respective drop down lists.
Qlik_SheetObject_MultiBox_dropdown_example

Table Box
The Table Box, as the name implies, displays a table of records for the selected data fields.
Qlik_SheetObject_TableBox_example

Chart
The Chart object is used for creating charts of various types. Some of the chart types available include bar, line, combo, radar, scatter, grid, pie, block, funnel, gauge, and mekko charts, and pivot and straight tables.
Qlik_SheetObject_Chart
The above shows one of the properties dialog tabs for the chart object – which shows some of the various chart types you can create.

Input Box
The Input Box object allows for users to enter a value. (which sets a variable that can be used for further processing)
Qlik_SheetObject_InputBox

Current Selections Box
The Current Selections Box displays all the selections that the user has selected. The Current Selections box is usually placed at the top of a sheet (dashboard page) in a position that allows users to easily see the selections that have been made.
Users are able to clear selections by clicking on the eraser beside the selection in the Current Selections Box, and the data on the sheet will change accordingly.
Qlik_SheetObject_CurrentSelectionsBox
In the example above, the user has selected Rating 5 and Length Range 1 to 1.5 hrs.

Button
The Button object creates a button that can be configured to execute various types of actions. When clicked the configured action will be executed.
Qlik_SheetObject_Button

Text Object
The Text Object is used for adding simple text to the document/sheet. It can be used to display informational messages, directions, descriptions or titles and labels, or any kind of information that needs to be shared with viewers/users of the document.
Qlik_SheetObject_TextObject

Line/Arrow Object
This object is used to create a line with or without arrows, and vertical or horizontal or diagonal. This object can be used for many purposes such showing relationships and flows.
Qlik_SheetObject_LineArrow

Slider/Calendar Object
The Slider/Calendar object is used to create either a slider with multiple values that the user can slide across to select, or a calendar that the user can use to find and select a date.
Qlik_SheetObject_Slider                 Qlik_SheetObject_Calendar
In the above slider, the user has selected Rating 4.  In the calendar object, clicking the grid icon opens a calendar that allows selection.

Bookmark Object
The Bookmark object is used to save the selection state of the current sheet. If a bookmark is created after a specific Actor has been selected, when you select that bookmark, the selection and corresponding data in the sheet will be displayed based on that saved selection state of the bookmark.
Multiple bookmarks can be saved for each sheet. Each having a different set of saved selections.
Qlik_SheetObject_Bookmark

Search Object
The Search Object allows users to search for values in entire data set or within a specific set of fields.
Qlik_SheetObject_Search

Container
The Container is a sheet space saving object that can contain multiple other objects, such as multiple chart objects. This allows you to display multiple views of the data within the same sheet “real estate” and change the view by clicking on the appropriate tab to get to each chart.
Qlik_SheetObject_Container
In the above example, the container contains 3 objects (3 charts), and therefore displays 3 tabs across the top, one for each chart. There are different possible orientations for the tabs – they can be to the left, right, top (as in this example), or bottom.

Custom Object
The custom object is a sheet object specifically intended to carry custom defined OCX replacement controls.
The Custom Object: Object Menu can be accessed from the Object menu, when the custom object is the active object.
Replacement controls are windowless OCX controls programmed by Qlik, yourself or third party vendors, which comply with the specifications for QlikView replacement OCX controls. The OCX control will be drawn in a rectangle on the sheet outlined by the underlying custom object. Communication between the OCX control and the QlikView document is maintained via the internal macro interpreter.

System Table
The System Table object creates a pivot table that shows the data structure of the qlikview document.
Qlik_SheetObject_SystemTable

In future posts, we will be covering each object in depth.

Business Intelligence Platform

Changing the Oracle logo in OBIEE 12c

Most customers want to change the Oracle logo located in the top-left corner of the OBIEE website to their own company’s logo.

obiee12c_oracle_logo

After upgrading from OBIEE 11g to OBIEE 12c, your previous change for this logo will not carry forward, or of course, if you did a new install, you will need to make a change to replace the Oracle logo with your own.  This post explains the simplest way to change the Oracle logo in OBIEE 12c.

Perform a search (recursive search) on file system of your OBIEE 12c server, in all OBIEE directories, for files with “oracle_logo” or “oracle_logo.png”.
Your search will return many directories containing this file.  You will need to change the file in a subset of these directories.

Since you will be likely using the “s_Alta” style in OBIEE 12c, pick out the directories with “s_Alta” in their path.  There should be about 9 directories and they will look like the directories listed below.  If you are not using “s_Alta”, then pick the directories with the appropriate style.

Get your company logo and make it a similar size to the Oracle Logo image.  Rename your company logo file to the name oracle_logo.png.

Go into each of the directories you identified in the above steps, and rename the oracle_logo.png file to oracle_logo.bkup or oracle_logo.orig.
Then, copy your company logo (that is now renamed to oracle_logo.png) into all the directories.

The directory paths may look something like these shown below, but will be different.  If you not using BI Mobile App Designer (bimad) or BI Publisher (bipublisher), you can ignore the bottom 6 directories, and just update the 3 analytics directories.

./user_projects/domains/bi/servers/obips1/tmp/earmanager/analytics/rXWkmiVVOSYZhmHWZK763w/res/s_Alta/master/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/analytics/eiguw6/war/res/s_Alta/master/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/analytics/za01ic/war/res/s_Alta/master/oracle_logo.png
 
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bimad_11.1.1/hkbdzw/war/theme/alta/images/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bimad_11.1.1/hkbdzw/war/theme/alta/mobile/images/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bimad_11.1.1/hkbdzw/war/theme/alta/master/oracle_logo.png
 
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1/to5gma/war/theme/alta/images/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1/to5gma/war/theme/alta/mobile/images/oracle_logo.png
./user_projects/domains/bi/servers/bi_server1/tmp/_WL_user/bipublisher_11.1.1/to5gma/war/theme/alta/master/oracle_logo.png

 

Good luck with your change.  Thanks for reading!

 

Business Intelligence Platform

Installing the OBIEE 12c Client on Windows

Installing the OBIEE 12c Client is straight forward.  Here are the steps.

Visit the Oracle Business Intelligence page on the Oracle website. Go to the Downloads tab.
OBIEE12c_Downloads
Select the version of Oracle Business Intelligence (OBI) 12c that you want to install.

When the version download page appears, select “Accept the License Agreement”, and then under “Oracle Business Intelligence Developer Client Tool (12.x.x.x)”, click on “for Windows x86-64 bit” – (see green arrows below)
AcceptLicenseAgreement_ThenDownload

Download and save the file to your computer.

After the download is complete, go to the downloaded zip file … bi_client_12.x.x.x.x_Windows.X64.zip
OBIEE12C_ExtractZip
Right-click on the file and select Extract All.  Or extract using another method.

After the executable has been extracted, right-click on it and “Run as administrator”.
OBIEE12c_RunAsAdministrator

This will start the installer

OBIEE12c_PreparingTheInstaller   OBIEE12c_Step0of5

Click through the next 5 steps

OBIEE12c_Step2of5

OBIEE12c_Step3of5

OBIEE12c_Step5of5

After the installation is complete, run one of the applications: Start -> Oracle Business Intelligence Client -> Administration
OBIEE12c_Run

The Oracle BI Administration Tool should open and be ready for you to use it.
OBIEE12c_BIAdministrationTool

Thanks for reading. Hope this helps.