Parse File Names Using Regular Expressions

Recently I needed to parse a full path to a bunch of files to get just the name of the file. To do this I had to use a regular expression and some string manipulation. In doing this I found the use of regular expressions in Oracle Analytics to be very powerful and I thought I would share.

In my examples I focus on project calculations but you can also do this in data preparation or data flows.

I am no regular expression expert so full credit goes to Matthew Jakubiak the architect for our product who pointed me in the right direction.

The key step was to create a new calculated column that modified the file path I had and turned the very last ‘/’ into a series of characters I could use later in string manipulation. The file paths in my source data were formatted like this:

/Library/User Pictures/Flowers/Red Rose.tif

The expression I used was:

regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**')

This would change the path to have the text ‘**FOUND**’ in place of the last ‘/’:

/Library/User Pictures/Flowers**FOUND**Red Rose.tif

With this in place I could use out of the box string functions like RIGHT, LEFT, and LOCATE to parse the new modified path and get the file name and path separately.

For the file name I used the LOCATE function to find my new unique string and then the RIGHT function to parse out just the name of the file. Notice I have to remove 8 characters because the LOCATE function will return the location of the start of the string.

RIGHT((File Path),(LENGTH((regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**')))-LOCATE('**FOUND**',(regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**'))) - 8))

Same idea to break out he path except I used the LEFT function.

LEFT(File Path,Locate('**FOUND**',(regexreplace(File Path, '(\/)(?!.*\1)', '**FOUND**'))))

This is just one example of using regular expressions to manipulate stings but hopefully you can think of other use cases for this powerful function. If you would like to read more about Regular Expressions in Oracle Analytics you can visit this blog on as similar topic. https://blogs.oracle.com/analytics/enhance-your-data-preparation-using-oracle-analytics


Oracle Analytics on Chrome 80 (now 84)

Google had put the brakes on this update back in March but now it is back, and rolling out as we do this update. All the same details below still apply just a few months later.


In February Google will roll out an update to Chrome (80) that will implement a secure-by-default model for cookies, enabled by a new cookie classification system.  Details can be found here – https://blog.chromium.org/2019/10/developers-get-ready-for-new.html  

Although general use of Oracle Analytics (any version) will not be impacted by this change we know that some specific embedding use cases may be impacted. 

An official support notice on this topic can be found here, but here are some details on how to prepare for this.

  • Browser Administrators can implement special policies to temporarily revert Chrome Browser to legacy behavior.  This is ideal for desktops that are IT managed.
  • Browser Users can manage this flag by navigating to chrome://flags/ in the Chrome browser and change the flag SameSite by Default Cookies to Disabled.  This works best in an environment were users manage their own desktop and updates.

Administrators can test can custom embedded use cases with this above flag set to enabled to verify if there will be an issue or not.

Remove Duplicates in a Data Set

Have you ever needed to remove duplicate rows in a data set? Well me neither, but an unnamed PM asked me to do it one night long ago and I am finally getting to publishing how.

The Setup

I have some survey data (totally fake) and in the data there are duplicate rows with one column having different data. Notice Viv has 3 of the same rows listed with various favorite foods. Not good when we what to know for sure what the people’s favorite food is!

How can she like candy, soda, and sugar??

Method 1 – Project Calculation

In this method I will add calculations right in my project. First calculation I add is:


This will do a running count of the rows based on the column PERSON_NUMBER. I want one row per person so this is why I selected that column.

Counting the row for Viv.

Now that I am counting the rows I want to know the total count and I want that available at the row level.


This will yield a total count for that person and display it in every row.

Viv has 3 rows.

Now I have to pick what row to keep in this case I have 2 examples first or last and yes is it arbitrary. For last row you can use this function:


For first you simply do the following:


From here you can leverage visualization or canvas filters to only show the rows you want.

I am an 80s kid.

Method 2 – Data Flows

Using data flows has the advantage of not having to apply filters or have extra columns for users to wonder about as the resulting data set will only have the rows and columns needed for analysis.

Over all the calculations are just about the same and I have set this up to produce two data sets; one for first row and the other for last row.

Prior to my split I add columns for Total Count and Running Count using the same exact functions as above.

These columns are used in both my first and last functions so I want them in place prior to the split. Also I plan to exclude those columns in my output so the data set used in my projects are clean.

This time for picking the first row all I needed to do was to filter on my Running Count column when it is = 1. Same concept as the calculation above.

For keeping the last row a calculation is needed to determine the this as there can be a different number of duplicate rows for a given person. This function just subtracts Total Count from Running Count and when 0 we have the last row.

Then for this branch I use the same filer concept and above and filter to show only those where the above calculation is = 0.

The rest of the Data Flow just cleans things up like hiding my calculation column along with any renames needed.


Overall this is not as straightforward as some single click option, but it gets the job done and the concepts here can be customized to meet a bunch of unique needs. I am sure this can also be done in a better way but I was up too late to try when I created this project. Hope it helps!

Use Emojis in Analytics 😍

Did you know that emoji can be used in Oracle Analytics projects? Well now you do and there are some cool 😎 use cases for this as long as you are willing to be creative.

Point to Stuff

☝️Showing users exactly what to click or tap on is often important in delivering analytics. Pointing hands in text areas or titles is a great way to tell people what they are looking at or what to do.

Use in a Text Box
Use in a Chart Title

Data Conditions

Emojis are a great way to highlight data conditions. Classic traffic lighting is a great example. This one is a bit harder to pull off but leveraging project calculations in a table or pivot brings it all together.

Project Calculation that Results in Emojis
Emojis in a Table

This sort of formatting has been used in analytics for years but now with the help of emojis and a bit of creativity it can be said with real emotion and style 😎.

Add to Values

Leveraging calculations and features in prep emojis can be used in various ways. Grouping is a great way to create values summarizing data but named with style using emojis.

Group Feature in Prep
Bar Chart with Creative Group Names

How it is All Done

It turns out that all of this is super easy because emojis, like it or not, have become a standard. Here is a great page that has a list of all the standard ones along with some emoji analytics of their own. You can actually adopt and emoji!


For use in an Oracle Analytics project the best bet is to just copy and paste from this page or a page like this. On Mac and Windows you can also open an emoji panel while typing.

  • Windows – In the text area, open the emoji panel using the Windows key + ; (semicolon) or Windows key +. (period).
  • Mac – In the text area, open the emoji panel using Control-Command-Space.

I hope this helps to enable the creation of useful, fun, and stylish visualization experiences.


Tips and Tricks and When to Use Oracle Analytics Desktop

Oracle Analytics allows customers to get insights on data with a couple key deployment options; cloud (OAC) and on-premises (OAS). This should cover most needs, but there are a couple of specific reasons for a desktop version.

First, if you just want to try things out you can download the desktop version. One great way check out the product is to try the free Udemy course – Modern Data Visualization with Oracle Analytics Cloud. The course can be done with OAC but the course will direct you to desktop in case you are just kicking the tires.

Second, if you need to leverage our SDK and build a custom visualization like the ones we publish to the Oracle Analytic Library. Here are some links in case you want to get started down a path of custom plugin development.



Also here is part one of a four part blog dedicated to building visualization on MacOS. Keep in mind these steps can be easily applied to Windows and know a Windows version of this blog is on the to do ✅ list.


Tips and Tricks

So now that you know when to use Oracle Analytics Desktop here are some tips to help you along.

Run in SDK Mode

For SDK Development you will need to know how to run in SDK mode. Here are the basic command line arguments for Windows and Mac.

"C:\Program Files\Oracle Analytics Desktop\dvdesktop.exe" --args -sdk -jettyPort=8800

open /Applications/dvdesktop.app --args -sdk -jettyPort=8800

-sdk will launch using the standard port unless -jettyPort is used.

Clean Uninstall

By default the uninstalls will just remove the application so that when you re-install the content is still in place. With that, there are times you want to do a full uninstall and remove everything you ever created on Oracle Analytics Desktop. Below are some scripts for Windows and Mac that do just that.

Keep in mind this will remove everything, never to be recovered!!


# Uninstall Data Visualization for Desktop on Mac
echo "Uninstalling Oracle Analytics for Desktop on Mac..."

sudo rm -rf "/Applications/Oracle Data Visualization for Desktop.app"
sudo rm -rf "/Applications/Oracle Data Visualization Desktop.app"
sudo rm -rf "/Applications/Oracle Analytics for Desktop.app"
sudo rm -rf "/Applications/Oracle Analytics Desktop.app"
sudo rm -rf "/Applications/DVDesktop.app"
sudo rm -rf "/Applications/Oracle Data Visualization Desktop Configure Python.app"
sudo rm -rf "/Applications/Oracle Analytics Desktop Configure Python.app"

# Uninstall All Data and Projects
echo "Uninstalling All Data and Projects..."

rm -rf ~/Library/Application\ Support/DataVisualization
rm -rf ~/Library/Application\ Support/DVDesktop
rm -rf $TMPDIR/DVDesktop

echo "Finished Uninstallation process."

ECHO This will delete the following directories:
ECHO %localappdata%\DVDesktop"
ECHO %localappdata%\temp\DVDesktop
ECHO %localappdata%\DataVisualization
ECHO This will clean up the Oracle Analytics Desktop installation and it will remove all artifacts you have created including projects and data sets.
ECHO Basically it will all be gone, so continue with care!!

SET /P startCleanup=Do you want to continue (y=yes):

IF '%startCleanup%'=='y' (

ECHO Attempting to delete the directroy %localappdata%\DVDesktop

IF EXIST "%localappdata%\DVDesktop" (
	@RD /S /Q "%localappdata%\DVDesktop"
	ECHO deleted the directroy %localappdata%\DVDesktop
) ELSE (
	ECHO Could not find the directroy %localappdata%\DVDesktop

ECHO Attempting to delete the directroy %localappdata%\temp\DVDesktop

IF EXIST "%localappdata%\temp\DVDesktop" (
	@RD /S /Q "%localappdata%\temp\DVDesktop"
	ECHO deleted the directroy %localappdata%\temp\DVDesktop
) ELSE (
	ECHO Could not find the directroy %localappdata%\temp\DVDesktop

ECHO Attempting to delete the directroy %localappdata%\DataVisualization

IF EXIST "%localappdata%\DataVisualization" (
	@RD /S /Q "%localappdata%\DataVisualization
	ECHO deleted the directroy %localappdata%\DataVisualization
) ELSE (
	ECHO Could not find the directroy %localappdata%\DataVisualization

) ELSE (
ECHO OK nothing cleaned up

Quit OA Desktop on Mac

Sometimes, and deepening on what MacOS you have installed it can be tricky to quit the application and all its running processes (working on fixing this). If you know what to look for this can be done using Activity Monitor but here is a script to make this a bit easier.


# Uninstall Data Visualization for Desktop on Mac
echo "Killing Oracle Analytics for Desktop on Mac..."

pid=$(ps -fe | grep 'dvdesktop' | awk '{print $2 }')
if [[ -n $pid ]]; then
    sudo kill $pid
    echo "Does not exist"

echo "All Done!"

I keep all of these scripts up to date here https://github.com/mmilella/DV-Scripts where you can just download the latest as I am sure this blog will get out of date.

Show the Latest Data

Sometimes you need to just show the latest data. This is often the case for metrics like inventory or some percentages where adding things up just does not make sense. Most analytic tools like Oracle Analytics allows authors to pick from various aggregation methods as a solution, but when building a project you don’t always want this to change for all cases. Users could also leverage date filters and this can be done at various levels in the project, but as new data changes report maintenance goes up!

With all this in mind I have recently introduces the concept of Relative Time into my data sets using a custom column and a function. I am doing this as part of a Data Flow but this concept can be leveraged elsewhere in the product.

Let’s break things down a bit and lay out the use case:

  • This is a self service use case. I am loading a new data file almost every day with data updated to the most recent. This can also be down with a database or other source that gets automatic updates.
  • At times the data updates get a bit behind and they may not have the latest data. Using today – 1 day or anything like that is not predictable.
  • I only care about the most recent days data in my analysis. This could be most recent week, hour, or even minute, but in my case it is day.

So what I have done is created a column that calculates the difference between today (Current Date) and the date column (ObservationDate) in my data set. I ordered the function so I would get number of days away represented in negatives so the closest day is the highest number, in this case -1. Doing it the opposite way would have been my approach for solving the problem of ‘oldest date’. You will see what I mean in the next step.


Next apply a rank function to the previous function to have each row in my data set report it’s rank. As you can see below that ranks things and because there are a lot of ties in the data set the same rank is repeated many times. Also note that with -1 being my largest number it is going to be ranked #1.


So now I know that my most recent time period is 3/27/2020 and I can clean things up so my naming convention is clear for my users consuming the report.


With this in place, using filters on my visualization or other levels, I can add the column value ‘Most Recent Day’ for the metrics where I just want the latest data! Once this is done I never have to update my data, I just load new data and the visualizations in my project always show the most recent data.

Also this is a great trick for making your data searchable and ready for voice for Oracle Analytics Day by Day use cases. More to come on this topic so stay tuned.

Be ‘Responsive’ to a Point

By default Oracle Analytics data visualization projects are responsive by default and this is great so authors generally don’t have to worry about device, device size, or screen resolution. Right?

Above is an example of some to the beautiful screenshot I see on social media showing off an authors work, but this sort of layout can break down pretty quickly. Here is what happens when the browser is resized.

As you can see the visuals all respond and now are really hard to read. It can get worse if this is brought up on a projector with poor resolution support or opened on a tablet in portrait orientation.

Introduce Scrolling

So why not let the user scroll and account for the various aspect ratios that might be encounter. Using canvas properties this can easily be done by keeping auto fit on and setting the height or width to a custom size.

Just by giving the canvas the freedom to scroll vertically the visuals show up better in the same size window as the example above. As an author I want to do better so keeping mind my target user, their devices, and default screen resolutions (often you do not know) I have arranged things with the following in mind:

  • Put key information on top and keep like information close.
  • Usually more than three performance tiles in one row is too much.
  • Pie, donut, and radar charts are normally good for small spaces.
  • Horizontal charts are best wide while most vertical charts are just fine tall and narrow.

No magic rule just some good, basic guidelines, and you get something that looks and scrolls like this.

This will work on most small screens and even on tablets in portrait mode! Keep in mind data visualization projects are adaptive but only on the phone where we have not choice but to neatly stack everything. For all other devices the author should take some care when laying things out but as you can see it does not require a lot of work.

Hope it helps and here is the exported .dva file if you want to give this a try.

Ready for Launch

With little to do in my spare time I am now able to finally do this blog consolidation that has been at the bottom of my todo list for many months. The plumbing part is all complete and here is what you need to know.

More content is on the way! This clean up needed to be done so I could clear the path for fresh content on all things data and analytics. Stay tuned for posts on topics like, tips and tricks, mobile analytics, visualizations, stuff in the labs, and the occasional rant. I have moved content from datavizlabs.com and bimobilelabs.com with a bit of clean up on some really old posts. Turns out mobile and tech in general can become obsolete fairly fast 🤪. If you need the old post you can still visit these sites (for now) at https://datavizlabs.tumblr.com and https://bimobilelabs.tumblr.com

I am not planning to move any of my historical Essbase or Smart View content as I have handed all that over to the Essbase team and they will need to keep this one going. I still love Essbase but just don’t have the capacity to be good and that many things and so I must focus.

If you are missing any post or one of the post I migrated has broken links or needs some fixing up please DM us @vizunderground or comment on the post.

✅Blog Consolidation
✅New Domain
✅Old Domain Redirects
✅Basic Site Structure
🟩Fresh New Content – Coming Soon!

A Fresh Start

For a few years I have been trying to find time to launch a new and organized place to me to share some of my knowledge on data. I have been at this game for more than 20 years and my blogging and ‘underground’ days started decades back when I was writing about and posting utilities for Essbase.

Maybe that logo rings a bell, it was from 2003 and I actually forgot the tag line and may have to bring it back. Back then we were doing more coding than writing but I will need to save the full history for another post. For now I need to focus and in the coming weeks I will be combining post from a few different blogs and consolidating a few twitter accounts.

Stay tuned…

Setting the PORTALPATH for OAC 5.5 and Above

For years our Oracle Business Intelligence customers have been using an RPD session variable to redirect end users to a specific dashboard when they hit the URL for their analytic platform.


This has served us well but fast forward to Oracle Analytics and this approach needed to change.  Starting with OAC 5.5 this is set using System Setting on our console page.

Once in system settings you simply do the following:

  • Search for or scroll to the setting called ‘Portal Path’
  • Update it with a valid location (path) to a dashboard.  ex. /shared/_portal/MyStartPage
  • A restart of OBI Presentation Server is required and the UI will prompt the user to do this.

All set!  Keep in mind that any logged in users will be impacted by this restart so it is best to make this change during off hours.  Also, user must have Access to Home ‘Granted’ in the Manage Privilege section of the Administration page for this to work properly.  Lastly once set the setting in the RPD will simply be ignored so you can remove it if you wish.

Here is a support note with all the same details: