How aggregation works

Analytical / BI solutions provide a bird's-eye view of the enterprise data. The key focus here is summary data rather than detailed. For the later you would already have transactional or operational systems in place. Aggregation plays a major role in the design of BI solutions. What data you aggregate, at what level and  where( depending on the chosen technology or BI tool) is important to present the accurate results in the Dashboard.

In OBIEE, you can define aggregations in the

1. Analytics page
2. RPD

You would define the aggregation in the Analytics page for a one of report or computational need.

You would be better off defining it in the repository for common needs across subject areas.

When you define it in  the RPD, you would do it in the BMM layer with

1. derived from existing logical columns
2. derived from physical mappings

The Important thing to understand is that when you define aggregation using the existing logical columns, the calculations are done later than the aggregation itself. Take a look at the below example where we are calculating the discount rate. Here the values are first aggregated (Sum) and then the calculation is carried out.

In the second case, for the measure DiscountRate_Physical we derive it from physical mappings. In this case, the calculation is carried out for every record first and then the aggregation (Avg) takes place.

we define DiscountRate_Physical as "xe".""."BISAMPLE"."F1 Revenue"."DISCNT_VALUE" / "xe".""."BISAMPLE"."F1 Revenue"."REVENUE"  * 100

I have used the Oracle provided BISAMPLE schema with the XE database for this example.

we can see in the below analysis the difference in values

If you have write a query to manually  fetch the results against the data warehouse, this is how it would look like.

-- derived from existing logical columns .
select d.per_name_year, (sum(discnt_value)/sum(revenue)*100) "%Discnt"
from samp_revenue_f f, samp_time_day_d d
where
f.bill_day_dt = d.calendar_date
group by d.per_name_year;

-- derived from physical mappings
select d.per_name_year, (avg(discnt_value/revenue)*100) "%Discnt"
from samp_revenue_f f, samp_time_day_d d
where
f.bill_day_dt = d.calendar_date
group by d.per_name_year;

To sum it it up, the aggregated result will vary depending on how you have designed to carry it out. Usually you define all the default aggregations and where it should be defined in your Dimensional model design document. Discuss it with your team and the Analyst at client side locations to arrive at a consensus on your approach.

Logging into oracle from SQL Plus without TNS entry

I got this from Ask Tom
very userful for quick logins / checks

>sqlplus "myuser/mypasswd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.0.20)(PORT=1521))
(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=orcl11g)))"

Be careful with extra spaces and extra parentheses. In Windows use double quotes "

This can also be done in a shorter way like this:

>sqlplus "myuser/mypasswd@192.168.0.20/myserviceName"

Getting Hardware Info in Linux

There are many ways to view the hardware configuration of the Linux system. Bulk of these ways derive information from the /proc directory
examples below

cat /proc/cpuinfo
cat /proc/meminfo

But if you want a detailed description of the hardware components, then this is the better way. It also gives the voltage of the CPU, type of RAM, serial number of the mother board, etc. check it yourself.

dmidecode

Getting over max processes exceeded error

Sometimes due to the default setting or limit on process, the server might hang up. To get over this problem, increase the number of processes to a more appropriate value.
login as sys in SQL Plus and type the following code

-- this will give you the list of all the processes along with their numbers
SQL> show parameter processes
-- this will give you the current setting for the number of processes
SQL> select count(*) from v$process;
-- change it to something higher
SQL> alter system set processes=100 scope=spfile; 

Don't forget to shutdown the database and restart it.

Similarly we can change the job queue processes too

SQL> show parameter job_queue_process 
SQL> ALTER SYSTEM SET job_queue_processes=20;

Customized Data Backup in Oracle

To take a data backup, there are many options available such as using RMAN, taking a dump using export and export pump utilities. But sometimes you need to backup specific data from specific tables at certain intervals. Here is an example of how to do this. I have done this on XE. This post also gives an idea of using XE in commercial environments by managing the data size restrictions of oracle XE. In this example, we backup data which is more than 45 days old from a particular table.
First we create a directory object that refers to the location where the dump files will be stored. Also, grant appropriate access rights to the directory object.


Next, we define the procedure which does the data backup.


now that we have the procedure ready, we will create and schedule a job that will call this procedure at an interval defined by us.
create the job as shown in the following code.

This job will run everyday at 2 am. It will backup data which is 45 days old from the table MY_TABLE01. You can access the DMP files at the location mentioned in the directory object. Just make sure you have read/write access to the directory on the linux filesystem.
If you want to backup data from multiple tables, then either repeat the backup code in the procedure for another table using a new job handle, if there is no common relation between the data or else use the same handle code to list additional tables provided the same filter query can be applied to all the tables.

Hiding calendar control in APEX

If we do this,

It disables the text compoenent of  the calendar control, but the calendar icon still remains visible.
To hide the control completely, i.e the text part as well as the calendar icon, use the DOM and the html_HideElement function. see below the tweaked function courtesy OTN.
Lets say for example, we have to show or hide the calendar control based on the selected values from an LOV. i.e. for some values the calendar is to be shown and for some other values the calendar is to be hidden. this can be achieved as shown below


call the function onChange of LOV values.