Tuesday, October 2, 2018

Oracle Data Visualization and the Attribute function

The Attribute function is an important function of Oracle Data Visualization, useful when we attempt to create something interesting based on measure values. It was covered by Philippe Lions in the post "Leverage the 'Attribute' Function to Boost Data Insights", but I want to dig a bit deeper and show a simple use case.

First example why we need it:

 I want to create 2 groups one with profit over 500 and the other with 500 and bellow. So I create the following calculation :






In our case I used City and few measures (Row Count and Profit), as a result I have each city assigned to group:




In many cases I jut want the totals, for each group. So I'll remove the city and get a strange result:



What happened?  Our 'Case 1' is treated as a measure and aggregated!

In Answers we had an option to mark "Treat as an attribute column":
but in Answers we have many layers, this part is in criteria, and we don't really have it in data visualization that uses more direct and simple approach.  

The Attribute function should solve this problem.  

The explicit syntax for this function is:  
ATTRIBUTE(<expression> BY <attribute list> WHERE <predicate>)  

The BY clause may be empty, in which case grand total grain is assumed.


I will return to our example. Instead of case when Profit>500 then 'Group A' else 'Group B' end
I will use case when Attribute(Profit by City)>500 then 'Group A' else 'Group B' end
And replace the Case 1 with Case 2:

Great.


Lets talk a bit about the BY part:

The Profit or any other measure is summarized according to the BY clause. In  our case we wanted to summarize it by City. If we want it on any other level we should define it.

For example in my data the primary key of the data is Order ID and Order Line ID. We can use them in the BY clause:
In this case the profit is summarized by the lowest level of the data grain and return different results:

But this is exactly the case when we can omit the BY clause and get exactly the same result:











The wrong way to use Attribute function:

From what I have seen, the attribute function should be used as close to the measure as possible. For some reason, people (me including) tend to do it the other way, which seems to be the wrong way.

In our example using Attribute function outside the Case function:





Will get you an error with some advise

this can be resolved by having an administrator refresh the server metadata.
SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1;


You don't have to, just keep the Attribute function close to the measure as described above.




If, for some reason, you insist on this approach, the workaround is to use numeric values as result.




Or you can always use some cast function on top of it.





It will work:

Did I mention it's an ugly and not recommended approach?






Monday, July 23, 2018

Adding Your own cities to Oracle DV - for the clueless

The aim of this post is to add your own cities to the Oracle geo-Json files in Oracle Data Visualization (DV). There are lots of smart options to create the geo files, that were already covered (extracting from Oracle database map, converting from maps Shapefile, based on image ...) this post describes how clueless person can do it with Excel.
This post is relevant for most types of Oracle Data Visualization (including desktop, OAC - cloud, on-prem). It was "quick and dirty" work and naturally can be greatly improved.

Introduction


There are 3 layer when you work with maps and DV.
  1. The background map (such as Google Maps, Oracle Maps...) 
  2. Your Data that contains information such as country or city names.
  3. A layer that translates your data to geographical info such as specific location (lat&long point) or borders (a set of lat&long points). This layer is created in Oracle DV using geo-Json files.
We can see the existing maps/geo-json files in the Console:



You can see them as physical files as well. The DV desktop files are located in: C:\Users\YOUR_USER_NAME\AppData\Local\Temp\DVDesktop\config\fmwconfig\biconfig\catalogs\basemaps   (replace YOUR_USER_NAME with your windows user name)

In the file world_cities.json, a single city in this file looks like this: {"type":"Feature","_id":"293394","geometry":{"type":"Point","coordinates":[34.76807,32.08194]},"properties":{"City Name":"Tel Aviv-Yafo","ISO":"ISR","Country Name":"Israel","State Name":"Tel Aviv","Scalerank":"2","Country Code_State Province Name_City Name":"ISR_Tel Aviv_Tel Aviv-Yafo","Country Name_State Province Name_City Name":"Israel_Tel Aviv_Tel Aviv-Yafo","Country Name_City Name":"Israel_Tel Aviv-Yafo","Country Code_City Name":"ISR_Tel Aviv-Yafo"}},

In this case we translate the City Name (Tel Aviv-Yafo) to coordinates :[34.76807,32.08194]

The system finds your data and scans existing map files for it, then translates it to geo-location. As a result you can have the same point with multiple name(Tel Aviv-Yafo, Tel Aviv, Tel Aviv Yafo, TA-Israel....) including translation to your local languages...The system would work fine! The one thing that is best not to do is to have 2 rows with the same City Name.

So I wanted to take the existing world_cities.json and add all the locations in Israel, in English, Hebrew and Arabic. The original file has few cities in English names.

How do I find possible location? Google it! There are plenty of people that published free relevant data to your country, or you can right click a map such as google map and copy manually the location coordinates or get it from the URL (for example https://www.google.com/maps/@32.08194,34.76807,17z), just note in google the order is [lat, long] in our geo-json file it's [long,lat].
It's always a good idea to check first at Oracle Analytics store - Maps & Spatial. Maybe someone already did the hard work.



Solution

To make thing extra easy I created an Excel file with a set of calculations. All I care about is filling the first 6 columns: running number, City Name, State Name, Country Code, country name and Long,Lat. the result is column G that is result you want to add to world_cities.json. 
The excel file is located here.
Just delete the values in columns B, C, D, E, F
Copy line 2 (columns A - AG) to the bellow lines
Add your own data as described above:
B - City Name
C - State Name
D - Country Code
E - Country Name
F - Long,Lat


Copy column G result and Paste it as Values.
Add the Values to world_cities.json, before the last line (our data has a comma at the end of the line, it can't be the last data line).

Here is am example file world_cities-HEB.json with 76 major cities in Israel added in English, Hebrew and Arabic.
Here is an example of world_cities_Hebrew_All_Israel.json with over 1000 places in Israel added, only in Hebrew.

Of course if you don't need all the wold cities and need only the local cities for your case you can create your own smaller json file without the world cities, based on world_cities.json file structure (in that case you can greatly simplify the attributes list, if you want).

Last step is to upload the files as custom map layers.

For the DV Desktop or OAC (Oracle Analytic Cloud) we can upload the file using the Console:

Press "Add Custom Layer (click, or drag and drop GeoJSON file)"
Select the json file.
You are required to select Layer Keys. Select the "City Name" at least:


Now you can use this in your maps:

Most likely the layer would be selected automatically. You can manually select your preferred layer option in properties any way.



You can find the same in OAC. Check the Console for Maps or use the URL similar to:
http://SEVER:PORT/dv/ui?consoletab=manageMapDataTab   



Monday, April 23, 2018

obiee 12.2.1.4.0 is available

OBIEE 12.2.1.4.0 was released this weekend. 
The download page is here.

The installation includes:

The unexpected component is the patches for the weblogic server that should be installed before installing the BI. Oracle advises to install them when upgrading from previous 12c.

The JDK and Weblogic components seem to be the same as for 12.2.1.3.

At the moment the BI Client seems to be rather big download file. When I opened it I discovered another copy of the BI server software, beyond the expected client software.  So if you are installing OBIEE Windows server...


The new features are mainly major upgrade of the Data Visualization component, described here.

Data Visualization new features:



 Publisher new features:

 Specifying the Barcode Types in RTF Templates.You can use the qrcode or PDF417 functions to specify the barcode type in the RTF templates. See 2D Barcode Functions.
  • Creating Accessible PDF Documents.You can create structured accessible PDF documents that include the document title and PDF tags. See Generating Accessible PDF Output.
  • Skipping Pages and Page Numbers in Reports.You can create a RTF template to skip pages and page numbers in the first page, even pages, or odd pages in reports. See Skipping Pages and Page Numbers.
  • Enable Diagnostic Logs for Online Reports.You can enable diagnostic logs for online reports. See Enabling Diagnostics for Online Reports.
  • Purge Scheduled Job History Data.You can purge the history of old scheduled jobs. See Purging Job History.
  • Set Memory Guard Property for Bursting Data Size.You can set the server.BURSTING_REPORT_MAX_DATA_SIZE memory guard property to limit the size of bursting data. See Memory Guard Properties.
  • Generate Command-Line Utilities.You can run the GenerateBIPUtility script to generate the utilities for configuring memory guard properties (BIPCONFIGSERVICE.ZIP) and for managing catalog (BIPCATALOGUTIL.ZIP). See Using Command-Line Utilities.



 OBIEE new features:

  • This release includes the Manage Themes functionality, which allows the entire interface, dashboard styles, or both to be customized with a logo, colors, link appearance, and button shape. See Modifying the User Interface With Themes.










Tuesday, March 13, 2018

OBIEE 12c using database authentication provider - addendum

In older versions of OBIEE, DB based authentication were very popular using Initialization Blocks.
Oracle does not support this sort of authentication lately.
If you insist on Database as Authentication Provider, you can check chapter 3 of BI security Guide, "Configuring a Database as the Authentication Provider". Or the second half of this RittmanMead blog here.


2 things to remember:

1. When running the libovdadapterconfig script at the end, set the dataSourceJNDIName value as the JNDI Name and not the "regular" Name. Next, give the weblogic password, when requested for AdminServer password.



2. In case you made a mistake while running the libovdadapterconfig script, running it again informs you the adapter was already created. The guideline to fixing it is at the bottom of note 2226809.1 at Oracle Support:


Note: If for any reason the adapter would need to be recreated, follow steps detailed in the Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition -> Correcting Database Adapter Errors by Deleting and Recreating the Adapter
Note: If the delete adapter command is run as per the documentation, it gives the impression that the adapter was not deleted by returning this message:

'Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root MBean.' with no further message.

 
For example:

C:\app\Middleware\Oracle_Home_122120\oracle_common\common\bin>wlst.cmd
Initializing WebLogic Scripting Tool (WLST) ...
Welcome to WebLogic Server Administration Scripting Shell
Type help() for help on available commands
wls:/offline> connect ('weblogic','Welcome1','t3://hostname.domain:9500')
Connecting to t3://hostname.domain:9500 with userid weblogic ...
Successfully connected to Admin Server "AdminServer" that belongs to domain "bi".
Warning: An insecure protocol was used to connect to the server.
To ensure on-the-wire security, the SSL port or Admin port should be used instead.
wls:/bi/serverConfig/> deleteAdapter(adapterName='MySQLGroupProvider')
Location changed to domainRuntime tree. This is a read-only tree
with DomainMBean as the root MBean.
For more help, use help('domainRuntime')
wls:/bi/domainRuntime/> exit()

However, the adapter is effectively deleted and can be confirmed in the Weblogic Administration Console.




In my case the server is localhost: