Evolution of BI & the Future

Business Intelligence has come a long way from MIS reports to prebuilt dashboards to self-service to Chatbots. And along with this the end users have also evolved from mere consumers to explorers to data hungry power users.  

We can divide it into 3 periods,

1. Initial Era (1990 - 2005) - This was the beginning of the BI. There were static MIS reports and the end users were consumers who were heavily dependent on the IT team. 
2. Middle Era (2005 - 2020) - This was the middle ages, where BI was synonymous to interactive Dashboards with slice and dice capabilities. The end users have also evolved from consumers to explorers, who used features like slicers, drill down / through to envision different scenarios of the data.  
3. Modern Era (2020 onwards) - In the recent times there have been massive changes., The data hungry workforce wants more than dashboards, they want empowerment 💪, to create their own insights, or in other words self-service capability. With that comes SaaS models and cloud hosted platforms, which give flexibility of scale up and scale out with minimal effort. 

As per leading Marker Researchers, 

• Clients no longer concentrate on which BI platform provides better data visualization, dashboards, or OLAP (“slice and dice”) functionality; rather they are prioritizing platforms that are: 
• Native to their preferred hyperscaler platform
• Seamlessly integrate with business applications
• Built on API-first architecture
• In an ideal BI environment, 80% of all BI requirements should be carried out by the business users themselves 

But what lies ahead? What is beyond Self-Service? 

With introduction of AI, ML and other technology evolutions, the power users will no longer have create their own reports by themselves. The BI tools will be smart enough to understand their requirement and built reports for them. May be we can call it Autonomous Self-Service (like Autonomous cars 🚗) ..... 😇

In fact, it is no longer a thing of future. Let's take the following examples,

• Oracle Synopsis - It is the Mobile Analytics App that lets you quickly open and interact with spreadsheets and business data in a visual and intuitive way, while you're on the go.

It is a standalone mobile based BI reporting tool which takes excel as data source, available in both Android and IOS. Oracle Synopsis is very intuitive in nature and the end user do not need any technical training to use it or create and share reports. This app is ideal for the Travelling Salesmen, Medical Representatives, PMOs who are always on the go and have the need to share reports (mainly excel based) 24 x 7.

• Oracle Lens - It is also a mobile based tool, which can scan any scan any tabular data using mobile camera and create insights instantaneously. 

• ThoughtSpot Search - It helps Self-Service analysis using NLP. In the search bar the users can type the analysis / insight they are interested in, and the reports get generated. Therefore with minimal knowledge self-service can be performed.

• Power BI Insights - It creates auto insights from the data with minimal intervention. 

And the list goes on and on. 

That leads us to the next question about our future, what is going to happen to us the BI Consultants / Developers? Are we also get redundant and obsolete like our little Rexy 🦖(can't help it my son is a big fan of Rexy ... 😋) ?

To my opinion, we are not going to get extinct at least for now. However, with the advent of every evolution and modernization the old roles get replaced by new more advanced roles. I think the role of dashboard developer is going to get reduced as self-service becomes easier, smart and automated. But the backend structure and governance will become more important, the following activities would become more and more important. 

• Advisory - Helping clients and end users choose the right tools as they are going to get spoilt by choices. The set of tools must fulfil the requirement and complement the organization's long and short term technology roadmap.
• Designing BI Architecture - As BI tools becoming more advanced and complex and there are significant number of integration opportunities both upstream and downstream. Therefore designing a robust, scalable architecture becomes more important to support all the smart things on top.  
• Designing Data Mart - Somethings don't change, a solid scalable data model (Normalized, Denormalized, Star, Snowflake) is the foundation of the BI solution. And thanks to Mr. Ralph Kimball and Mr. Bill Inmon, there is enough of confusion already on which approach to choose, a top down or a bottom up.  
• Defining Metadata (Metrix, Dimensions, Hierarchies) - Once the ground work is done, the calculations and the logics need to be defined, so that the automation can use them create the answers / analysis users seek. 
• Governance - With empowerment comes governance. As more and more user are empowered the create their own analysis, that would lead to more and more confusion about the version of truth. To maintain single version of truth and standard across the organization, a governance process is very important. There has to be a Heimdall protecting the Bifröst. 

Is it possible to show 2 lines in the same OBIEE graph, one for selected and other for all

A few days back one of my friends came up with a problem in OBIEE, is it possible to show a line graph having 2 lines in the same graph. One line representing Revenue by a selected Sales Person and another line as the average Revenue by the remaining Sales Persons.

This might be achieved through different ways, but what I preferred was to using a Presentation Variable.

1. I have created a union report, the 1st criteria contains, Month and Avg Revenue. 'All Sales Person' is a hardcoded dummy column.

Avg Revenue is coming directly from a fact, and it is a simple Average on Revenue.

2. The 2nd criteria contains Month and Avg Revenue with Filter in the formula.

I am using the formula = FILTER("Base Facts"."Avg Revenue" USING ("Sales Person"."E1  Sales Rep Name" IN ('@{var_SP}')))

Basically it will filter the Avg Revenue for a Sales Person, and the value of the Sales Person will be passed through the Presenation Variable var_SP.

And in the dummy column I put '@{var_SP}'. This will help me show the name of the selected Sales Person in the line graph.

3. Next I have created a Prompt to filter the Year and Sales Person (to pass the value for var_SP)

4. I have put them all together in the Dashboard. When I first run the report without any Sales Person selected, it shows me only the Avg Revenue for all Sales Persons.

But the moment I select a specific Sales Person, an additional line appears.

Now it is time to validate, if my report is giving right data or not. And for that purpose I have created 2 simple seperate reports with Month, Avg Revenue and Month, Sales Person (Aurelio Miranda only) and Avg Revenue.

The result is matching with the chart, and that validates the Line chart report.

Accessing Oracle Database Cloud Service using Putty

Enabling Log in Oracle BI Publisher

Unlike OBIEE, there is no straightforward way in Oracle BI Publisher to view the log and the SQL generated for a report. However, there is a workaround to achieve the same, and in this post, I am going demonstrate that.

1. Open a text file and paste the code as below

LogLevel=STATEMENT
LogDir=D:\Tilak_Work\OBI11G\BIP_Debug

I have set the LogDir path according to my system, this needs to customized based on the environment.

For information, there are 7 levels of log information in BIP.

• UNEXPECTED
• ERROR
• EXCEPTION
• EVENT
• PROCEDURE
• STATEMENT
• OFF

2. Save this file as xdodebug.cfg and place at <OBIEE Home>\Oracle_BI1\jdk\jre\lib

3. Restart the services. and now when I run some BIP reports and go to the log directory ('D:\Tilak_Work\OBI11G\BIP_Debug' in my case), I see multiple log files are generated. 

4. Among these files, if I check xdo.log file I can find the backend SQL for the BIP reports.

Configuring Oracle Database Cloud Service and Accessing using SQL Developer

I have used Oracle DBCS (Database Cloud Service) as part of BICS (Business Intelligence Cloud Service) before, which comes as preconfigured. But to use DBCS as part of Oracle Analytics Cloud, we need to configure it first. Now I have got my trial OAC account, so I have 3 objectives, 

1. Create and configuring Oracle Database Cloud Service instance
2. Access the DBCS using SQL Developer in my local
3. Accessing the DBCS EM online

First I have to create a DBCS instance, and to do that I go to Oracle Cloud My Services - Dashboard, and click on 'Create Instance'.

And click on the Database option in the popup.

Next I have to select the preferances / configurations I want for my DBCS. I am taking Oracle DB 12c Release 1 - Enterprise Endition, which is compatible with Analytics service also.

In the Details page I set the DB name and the Administrator / SYS password. I have not chosen any Backup and Recovery option, as it would mean additional cost, and I don't need it for this demo purpose. 

There is an option for SSH Public Key. You can edit it. I would prefer to create fresh Public - Private Key pair using Putty Key Generator. This would be beneficial for connecting to Oracle DBCS from Putty.  

Once done I confirm and create my DBCS instance.

It will take some time for Oracle to create and configure the DBCS instance, and the status will show 'Creating service ..', and if I hover my mouse on that it shows me the details.

Once the status is completed, I have achived my 1st objective. But now what about 2nd and 3rd. The problem is now if I try connect to the DBCS from my local SQL Developer using the informations such as Public IP, Port etc, it is not connecting and I am unable to open the DBCS EM in the browser.

Actually I have spent quite some amount of time on this bottleneck, and what I have learnt is,
there are 2 major ways to connect to the DBCS using SQL Developer, viz

1. Using SSH when the Listener Port is Blocked
2. Without using SSH when the Listener Port is Unblocked

For now I am focusing only on #2. I will try to cover #1 alongwith connecting from Putty in a later post.

Now I need to use the Oracle Database Cloud Service console to enable one of the automatically created access rules.

I click the Navigation menu icon navigation menu in the top corner of the My Services Dashboard and then click Database. The Oracle Database Cloud Service console opens.

From the hamburger menu icon menu for the database deployment, select Access Rules and the Access Rules page is displayed. It contains the following access rules set to a disabled status.

• ora_p2_dbconsole, which controls access to port 1158, the port used by Enterprise Manager 11g Database Control.
• ora_p2_dbexpress, which controls access to port 5500, the port used by Enterprise Manager Database Express 12c.
• ora_p2_dblistener, which controls access to the port used by SQL*Net.
• ora_p2_http, which controls access to port 80, the port used for HTTP connections.
• ora_p2_httpssl, which controls access to port 443, the port used for HTTPS connections, including Oracle REST Data Services, Oracle Application Express, and Oracle DBaaS Monitor.

I enable ora_p2_dbconsole and ora_p2_dblistener, which are required for the EM and the SQL Developer respectively.

And now I can use the same details as below to connect to DBCS using SQL Developer in my local.

And similarly I am able to open the EM also using internet browser.

Which confirms the closure of my 2nd and 3rd objectives.

Load data using Data Flow in Oracle DV

This time I explore the inbuilt ETL feature called Data Flow in Oracle DV ( DVD in my case).

To start with first go to Data Sources and Create Data Flow.

It will prompt for Data Source. We can select a Data Source or alternatively we add a source by either clicking on the '+' icon on top to add data or 'Add Data' option on the lefthand side menu.

Now let me, explore the left-hand side menu. These are the basic ETL activities those we can perform.

I start with selecting Sample Order Lines excel as a source.

My objective is to load the Customer data into Customer dimension.

Therefore first I need to select the Customer columns, and I use 'Select Columns'.

And I select 'Customer ID', 'Customer Name', 'Customer Segment'.

Next, I use 'Rename Columns' to match the target column names.

And now I define the target table.

I save and execute the Data Flow.

Now I open the table in DB and check the loaded data. It has loaded 9000 rows.

However, there is a problem. There are duplicate records. And as I have stated earlier my objective was to load data for Customer dimension. I need to get distinct rows only.

To do that, I have added one more column 'CUST_COUNT' which is nothing but 'Customer ID' only.

And I add aggregation 'Count' on CUST_COUNT.

I do not do any further change, and as I do not have any target column as CUST_COUNT, it is not going get loaded in the target. But by having an aggregation I am using group by other columns, and that in turn is eliminating the duplicate rows.

Now I truncate the target table and rerun the Data Flow, and this time the result is as desired.

There are multiple other options and we can use expressions also, which we might cover in a later post. 

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.