Level based Metrix - Content Level in OBIEE vs Fixed in Tableau

This is a continuation of my earlier post Level based Metrix - Content Level in OBIEE vs Exclude in Tableau , and I am going to focus on Fixed in Tableau.

To start with, I am creating a new calculation 'Company wise Revenue', which is the Sum of Revenue at the Company level of the Office hierarchy in the RPD BMM.

I add this column in an analysis in OBIEE. 

I am keeping all the 3 columns 

1. Revenue - Details level

2. Revenue (Sum for All Office) - Total level

3. Company wise Revenue - Company level

(For the hierachy refer to Level based Metrix - Content Level in OBIEE vs Exclude in Tableau )

We can compare these 3 columns side by side and can onserve how the 'Company wise Revenue' is summing up at Company level.

Next I calculate the same in Tableau using 'Fixed' expression, at Company level of the Office hierarchy in Tableau (for the hierachy refer to Level based Metrix - Content Level in OBIEE vs Exclude in Tableau ). 

And I test that column along with 'Revenue' and 'Revenue Exclude'.

The output is same as we have seen in OBIEE.

Can we open file created in Tableau Desktop in Tableau Public

I am relatively new to Tableau. I have created some TWBX file using tableau, and now I have a question, what happens once my license expires? by any means can I still open/work on those files?

And that brings me to this post.

I am using one of the TWBX files which I have created earlier, and I can open and use it using Tableau Desktop.

However, if I try to open the same file in Tableau Public, it is not allowing me.

Now I select 'Save to Tableau Public' from Server → Tableau Public.

And log into to my Tableau Public profile.

And publish this workbook.

Next I go to my Tableau Public profile and download the uploaded file in TWBX format.

Now I open Tableau Public client, I have 2 options to open the file.

1. Open - I can open the TWBX file I have downloaded.
2. Open from Tableau Public - I can directly open the file from my profile.

The file works fine in Tableau Public client.

However, if I do any change and want to save it, the only option is in Tableau Public.

Level based Metrix - Content Level in OBIEE vs Exclude in Tableau

In this post, my objective is to showcase Level based Metrix in using Content Level OBIEE vs Exclude in Tableau. However, I am not going to get into which one is better or worse. This more of exploring options in OBIEE and Tableau to reach to get the same result.

Level Based Matrix - We create a hierarchy of dimensions in reporting solutions and built Calculations / Metrics associated with a specific level. Examples of such calculations are Monthly Sales, Quarterly expense, Country wise revenue etc.

I am using 1 fact and 1 dimension. First, let me do it in OBIEE. I have created the joins in the physical layer.

I have brought those 2 tables in the BMM.

And created logical dimension/hierarchy for Dim - Office.

In the Fact - Revenue, I have kept the content level for Dim - Office at the details level like usual. 

I have 2 Revenue columns, 'Revenue', and 'Revenue (Sum for All Offices)'. Revenue is a straightforward logical column with aggregation as SUM, and Level not defined separately.

However, Revenue (Sum for All Offices) has level defined at the total level of Office. 

I test both the columns and the result is as desired.

Now it is time to replicate the same in Tableau. I have added those 2 tables and joined them.

Next, I have created Office hierarchy, and duplicated 'Revenue' as 'Revenue Exclude'. 

And I have used 'Exclude' expression, and have excluded all the levels of Office hierarchy.  

I test it 'Revenue', and 'Revenue Exclude' in a report. The output is same as we have seen in OBIEE.

There are 2 more expressions 'Include' and 'Fixed' in Tableau, which are also used for the same purpose. I will try to explore them on the next blog.

Using Driving table in OBIEE Complex Join

In OBIEE we can join tables present in different Schema and Connection Pool. However there is a possibility of performance issue in such case. Using Driving Table can be solution for that. 

In this blog I am going to demonstrate how we can use Driving Table, and what is the impact of it.

To start with I am using the fact SAMP_REVENUE_F from BISAMPLE schema. 

I am going to join it with SAMP_OFFICE_D in the same schema

And EMP_D which is present in a different schema. 

The concept of Driving table is useful specifically if we are joining 2 tables which are in different schema and one of them has less number of rows.

I am using 2 dimensions one present in the same schema and another in different schema to showcase the difference in join.

Next I have imported all 3 into the physical layer and joined them.

Next I create a BMM and put them into it and create logical fact and dimension tables. 

The Logical / Complex join between Fact - Revenue and Dim - Office is like a usual Logical / Complex join in BMM.

To showcase the difference in query with / without Driving table first I am doing the Complex join between Fact - Revenue and Dim - Employee is like a usual Logical / Complex join in BMM. 

Let me create a simple report to test it out.

In the session log we can see 2 different SQL queries have been fired for 2 different schema which have no connection in between.

Now let me define the Driving Table in the complex join. As in this case EMP_D has less number of rows, I am using it as the Driving Table.

And in the back end SQL query we can find a additional in statement using parameters.

And this makes the difference in performance.

Get Query Log for in Oracle BI Publisher

Is it possible to show a line graph having 2 lines in the same graph. One profit line for a selected store and other profit line as the average of the remaining 9 store. 

Facing ORA-01033 / ORA-01157 while trying to run RCU for OBIEE installation

The OBIEE 11g instance in my local has got corrupted and I was trying to reinstall it. In the process, I was required to recreate the DEV_BIPLATFORM and DEV_MDS schema (DEV4_BIPLATFORM and DEV4_MDS in my case).

Before running the RCU for creating new schemas, I had gone to the DB and have dropped the schemas. Point to be noted I have not dropped them using RCU rather I have manually dropped them using SQL Developer.

As there is a shortage of space in my local, I thought of deleting the DBF files also.

And I have done Shift + Delete these 2 files.

Now when I try to log in the RCU using my SYS account, I am facing error 'ORACLE initialization or shutdown in progress'.

I have tried to connect to the SYS account using SQL Developer and facing the same error 'ORA-01033: ORACLE initialization or shutdown in progress'.

As I am unable to log in to the DB SYS account, I try to log in to SQLPLUS using the command prompt as '/'. And this time I am successful.

Now I try to alter the DB open (alter database open), and that revealed that Oracle can't identify/ lock data file 14 and 15.

A bit of googling revealed that it is due to deleting those 2 DBF files from the Oracle folder. The DB is unable to start completely as it is still looking for those 2 files.

Oracle docs note this on the ORA-01157 error:

ORA-01157: cannot identify/lock data file string - see DBWR trace file

Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use.

The database will prohibit access to this file but other files will be unaffected. However, the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

Action: Have operating system makefile available to the database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

Obviously, I have made a mistake by deleting those 2 files manually, and now I am unable to use my DB. Now, what could be the resolution ?

There could be different ways out, including restoring the files. But I do not have the files and I want a quick and easy solution.

I run the command 'alter database datafile 14 OFFLINE DROP' (and for datafile 15 also).  And it alters the DB.

Next, I try to run 'alter database open' once again, and I this time I was successful. And these steps do the magic and resolves my problem with RCU, and puts me back on track for OBIEE installation.

Custom Style in OBIEE 11g / 12c

Let me take you through the steps for defining custom Skins and Styles in OBIEE. It is similar in 11g and 12c.

To start with, there is an sample ear file ORACLE_HOME/bi/bifoundation/jee/bicustom-template.ear

I copy and paste this ear file and rename it as bicustom.ear

I extract this ear file into a folder and then extract the war file inside the folder into another folder, let me name it bicustom_war.

Inside the bicustom_war/res folder there are 2 folders, and 1 xml file
1. s_Custom - folder for style
2. sk_Custom - folder for skin

3. filemap.xml - it contains the hierarchy for skins an styles

For the purpose of this demonstration, let me go into the  s_Custom/master. I am going to replace the existing custom.css and oracle_logo.png with new files for the customization purpose.

Once I am done with my changes, it is time to create the war file. And for that the prerequisite is to set the path for JDK.

Next step is to create the ear file. The command is same as of the war file.

Once the bicoustom.ear file is created I place it at the ORACLE_HOME/bi/bifoundation/jee folder. 

Now I need to deploy the ear file in the Weblogic Console. I go to Deployments

Click Lock & Edit, and click Install in the Deployments.

I navigate to ORACLE_HOME/bi/bifoundation/jee folder where I have kept the bicustome.ear file, and I select the ear file.

Next I select 'Install this deployment as an application'.

I select 'I will make the deployment accessible from the following location', and set the location as the ORACLE_HOME/bi/bifoundation/jee folder

I click Finish and the deployment is done.

Now I Activate the changes

Next I go to the Deployments again and select bicustom checkbox and start the new application.

If the deployment has happened successfully, then we can see a new option in Style drop down as 'Custom'.

I have created a sample report and put them into 2 dashboards. One with Style default 'Skyros'

And the other one with Style 'Custom'.

As we can see it has a different look and feel. There are different other styling options also in the folders which can be customized.

The details customizing steps can be further referred at Oracle Site.