Why Snowflake?

Snowflake Schema

Snowflaking is a form of dimensional modeling in which a dimension is normalized by moving attributes with low cardinality (few distinct values) into separate dimension tables that relate to the main dimension table by using foreign keys.

Snowflakingcan be done in case of:

• Sparsely populated attributes, where most dimension member records have a NULL value for the attribute, are moved to a sub-dimension.
• 
  
• Low cardinality attributes that are queried independently.  For example, a product dimension may contain thousands of products, but only a handful of product types.  Moving the product type attribute to its own dimension table can improve performance when the product types are queried independently.
• 
  
• Attributes that are part of a hierarchy and are queried independently.  Examples include the year, quarter, and month attributes of a date hierarchy; and the country and state attributes of a geographic hierarchy.

Number of Centuries

Question: You have 2 tables as follows:

create table table1(player varchar2(10), ground_name varchar2(10), num_centuries number);

create table table2(ground_name varchar2(10), country varchar2(10));

Write a SQL to list the player names who have made centuries in every country

Solution:

select p.player

from

(select count(distinct country) cnt from table2) g,

(select player,count(distinct table2.country) cnt from table1,table2 where num_centuries>0

 and table1.ground_name=table2.ground_name

group by player) p

where g.cnt=p.cnt

Asymptotic analysis of Algorithms

The complexity of an algorithm function is usually described as a function of the number of inputs it receives.

e.g searching(linear search) a particular element in an array of n integers can be described as 

f(n) = 4 + 2n

However, in terms of the performance, we are generally interested in the terms which grow faster as n increases than the terms which grow slower as n increases. So we can drop constant '4' from the above function definition. Also the constant factor '2' can be dropped. Thus the function becomes

f(n) = n

This method of removing all factors and of "keeping the largest growing term" as described above is what we call asymptotic behavior. So the asymptotic behavior of f(n) = 2n + 4 is described by the function f(n) = n. In mathematical terms, it means that we are interested in the limit of function f as n tends to infinity (Mathematically, an aysmptote of a curve is line such that the distance between the curve and the line approaches zero as they tend to infinity. Asymptotic analysis is a method of describing limiting behavior)

So, if a program does not contain any loop can be describes as f(n)=1. This is because the number of instructions are fixed. If a program contains 1 loop (through 1 to n), it will be describes as f(n)=n.

Similarly a program with 2 loops(a loop with in a loop) will be f(n)=n*n

A program with 3 loops(a loop within a loop within a loop) will be f(n) = n*n*n

Big-Oh Notation: Its the notation which expresses the worst case complexity of an algorithm i.e the behaviour of an algorithm will never exceed a certain bound.

In the previous example, the complexity of an algorithm having 2 loops can be written as O(n*n). This means that this program's run time is never worse than n*n. It can be better but can never be worse. This gives a good idea about how fast this program runs.

Exploding Rows........

SQL Puzzle:


You have a table with 2 columns: literal and num with data as shown below:


Literal     NUM
 a                 4
 b                 3
 c                 2


Write a query that returns the output as:


Literal
----------
   a
   a
   a
   a
   b
   b
   b
   c
   c


The NUM represents the number of times the literal has to be repeated in the output




Solution:


The query will be as follows:


select a.literal
from
(select literal, num from explode) a, (select rownum rn from user_objects) b
where b.rn<=a.num

Looping of Workflows in Informatica

Scenario:

You have 3 Oracle source schemas: test_schema1, test_schema2 and test_schema3. Each schema contains 3 tables EMP, DEPT and MANUFACTURERS. The structure of tables is exactly same in each schema. You have a target schema test_schema_target which contains 3 tables: EMP_TARGET,DEPT_TARGET and MANUFACTURERS_TARGET. The stucture of tables is exactly similar as those to source schemas.

Now you have to provide an Informatica solution wherein the the tables in target schema are loaded from corresponding tables in source schema dynamically. i.e there should be only 1 set of mappings for each loading and this set should be called 3 times(since there are 3 source schemas). Everytime the schema name should should dynamically change.

Solution:

Assumption: The user in target schema has the access to tables in all 3 source schemas.

Create a control table as follows:

Build a mapping (m_par) which will select 1 row from the control table as follows:

The port v_Schema_Name will be set as SETVARIABLE($$schema_name,SCHEMA_NAME)

where $$schema_name is a mapping parameter. The source qualifier query is as follows:


SELECT
CONTROL_TABLE.SCHEMA_NAME,
CONTROL_TABLE.FLAG
FROM
 CONTROL_TABLE
WHERE
 CONTROL_TABLE.FLAG='N'
and rownum=1


The idea here is, to assign the value of schema name returned from above query to the mapping parameter $$schema_name. This mapping parameter will then be assigned to a workflow variable. This workflow variable then will be assigned to the mapping parameters of EMP, DEPT and MANUFACTURERS mappings. The flag will be updated for each successful mapping and the workflow will be called again using a CMD task. This whole process is explained below:

Now, build a mapping for each target: EMP_TARGET, DEPT_TARGET and MANUFACTURERS_TARGET.

The mapping screen shot for EMP is shown below:

The source qualifier query will be as follows:


SELECT
$$schema_name_e.EMP.EMPNO, $$schema_name_e.EMP.ENAME,
$$schema_name_e.EMP.JOB,
$$schema_name_e.EMP.MGR, $$schema_name_e.EMP.HIREDATE, $$schema_name_e.EMP.SAL,
$$schema_name_e.EMP.COMM, $$schema_name_e.EMP.DEPTNO
FROM
$$schema_name_e.EMP

Here $$schema_name_e is a mapping parameter defined in this mapping

There will be a Post-SQL on EMP_TARGET as follows:

update CONTROL_TABLE set FLAG='Y' where CONTROL_TABLE.SCHEMA_NAME='$$schema_name_e' and CONTROL_TABLE.TABLE_NAME='EMP'

Similar mapping will be created for DEPT and MANUFACTURERS also. these mappings will also have mapping parameters as $$schema_name_d and $$schema_name_m and will have the similar Source qualifier and Post-SQL in target queries.

The workflow (wf_child)  screen shot is shown below:

Here s_par is the session pointing to Control table mapping (m_par).
s_emp is pointing to EMP mapping.
s_dept is pointing to DEPT mapping.
s_manufacturers is pointing to MANUFACTURERS mapping.

In the properties of the workflow; create a wf variable as shown:

Now within the workflow, doble click on session s_par and go to Components tab:

Click on "Post session on Success Variable assignment" and assign the value $$Schema_name to $$schema_name1.

This effectively assigns the schema name extracted from control table to the workflow variable.

Create a decision task (dec_count) as $s_par.SrcSuccessRows>0

So, if there is any row pending in control_table, this workflow will proceed.

After dec_count place the sessions s_emp, s_dept and s_manufacturers in parallell. Double click on s_emp and go to components.

Set the "Pre-session variable assignment" as follows:

This effectively assigns the value of Workflow variable(derive in the previous step) to the mapping parameter
$$schema_name_e before the actual run of this session. As a result the source qualifier query will execute with this parameter value.

Repeat this exactly for s_manufacturers and s_dept to calculate the values of $$schema_name_m and $$schema_name_d.

There will be a final command task in this workflow cmd2 with the command as

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Parent

This calls another workflow wf_Parent(screen shot below) which in turn calls wf_Child.

The command task s_call contains a call to child workflow:

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Child

For both the workflows, set the property "Configure concurrent execution" as "Enabled"

The control table will keep on updating the value of FLAG column as the workflow runs for particular schema and table. After all schemas and tables are covered, the FLAG column will be Y for all rows and the loop will stop:

In this way, the Informatica workflow will run in a loop loading tables in different schemas.

Hope this helps.........

Changing your password in OBIEE 11G

In OBIEE 11g, the users can not change their password unless they have administrative privileges. To provide this functinality there is a workaround of using an API of enterprise manager. To check this API:

Go to enterprise manager link and navigate to System MBean browser > Runtime MBeans > Security > Domain > myRealmDefaultAuthenticator as shown in below screen-shot

In order to use this API, you have to:
1. Write a Java Class calling MBean methods to connect to MBean server
2. Write a method in that Java Class and call this API into that method
3. Convert this Java Class into a web service exposing the above built-method
4. Deploy the web-service to the admin server.
5. Make an Action Link on your OBIEE dashboard calling the above built web-service in that action link.

Jdeveloper can be used to built Java Class. The Java Class code is given below:


package obieeweb;



import java.lang.management.ManagementFactory;
import java.net.InetAddress;
import java.util.Arrays;
import java.util.HashMap;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.management.MBeanServer;
import javax.management.ObjectName;
import javax.management.remote.JMXConnectorServer;
import javax.management.remote.JMXConnectorServerFactory;
import javax.management.remote.JMXServiceURL;


public class OBIEEWebServices {
          public String OBIEEWebService(String userId, String oldPassword, String newPassword, String confirmPassword) throws Exception {
     
        MBeanServer mbs = ManagementFactory.getPlatformMBeanServer();
        HashMap env = new HashMap();
     
        final String hostname = InetAddress.getLocalHost().getHostName();
        final int port =  your_port_number;
     
        JMXServiceURL serviceUrl = new JMXServiceURL("service:jmx:rmi://"+hostname+":"+port+"/jndi/rmi://"+hostname+":"+port+"/jmxrmi");
     
        JMXConnectorServer jmxConnector = JMXConnectorServerFactory.newJMXConnectorServer(serviceUrl,env, mbs);

        ObjectName securityMBeanName = new ObjectName("Security:Name=myrealmDefaultAuthenticator");
        Object[] params = new Object[] { userId, oldPassword, newPassword};
        String[] signature = new String[] {"java.lang.String", "java.lang.String", "java.lang.String"};
        if (newPassword.equals(confirmPassword)) {
        try
          {
          mbs.invoke(securityMBeanName, "changeUserPassword", params,signature);
          }
        catch(Exception e)
          {
           return e+"|Error changing Password.";
          }
          return "Password changed successfully.";
        }
        else
        {
            return "Passwords don't match. Try again.";
        }
     }


    public static void main() throws Exception {
        OBIEEWebServices ow = new OBIEEWebServices();
        ow.OBIEEWebService( userId, oldPassword, newPassword, confirmPassword);
}
}

Steps to build a web service, in Jdeveloper:

1. Right click on your Java Class and select Create Web Service

2. Select the deployment platform as per your requirements and click Next:

3. Give the service name and port and click next:

4. Choose the binding method and click next:

5. This screen should give the method that you have written in your Java Class to invoke the API:

Click Next on the other screens and finish building your Web Service.

Once WebService is built, you need to deploy it to Application server:

1. Right Click on Project and select Deploy:

2. Choose Application server. Click on the + button to add a new server:

3. Give the connection name :

4. Give username and password:

5. Give the configuration details:

6. Click test Connections and deploy.

Once the web service is deployed , you should be able to check it in Admin server under deployments.

Now, we have to create a dashboard and add a new action link:

Create a dashboard and drag a new action link. select Invoke Web Service:

Give the web Service URL and Select the webservice. It will open the arguments screen. The number of arguements will be the same as the method you have used in your Java Class to invoke the API:

Note that in first arguement for User Id, we have selected a Session variable USER and made it hidden so that its not prompted and its value is automatically assigned to the User which has signed in.

The dashboard looks like:

Object Oriented Programming: Some Basic Concepts

All computer programs consist of 2 parts: code and data. So, a program can be conceptually organized around its code or around its data. So, there are 2 approaches of writing computer programs: Programs that are written around "what is happening" and programs that are written around "who is being affected". The first approach is known as process-oriented approach. Procedural languages like C follow this model. The second approach is known as 'object-oriented' approach. Languages like C++ and Java follow this approach.

Object -Oriented approach helps in managing complexity better as it is more similar in nature to as how humans manage complexity. Human beings manage complexity through abstraction. e.g people do not think of a car as a set of thousands of individual parts or as a set of thousands of different processes that undergo in running a car. Instead they look at it as a well-defined object with its own unique behaviour. People can easily ignore the complex details of  how the engine, transmission and breaking systems work, while they drive a car. So a car can be visualized as a single object containing of several subsystems(with each of its unique behaviour) like steering, breakes, sound-system etc. In a similar fashion, a program can be written wherein data can be transformed by abstraction into its component objects and each of these objects describes its own unique behaviour.

Every object has state and behaviour. e.g Dogs have state (name, color, breed, hungry) and behavior (barking, fetching, wagging tail). Bicycles also have state (current gear, current pedal cadence, current speed) and behavior (changing gear, changing pedal cadence, applying brakes). In similar fashion, software objects  too consist of state and related behavior. An object stores its state in fields (variables in some programming languages) and exposes its behavior through methods (functions in some programming languages). Methods operate on an object's internal state and serve as the primary mechanism for object-to-object communication.

Encapsulation:

Encapsulation is a mechanism that binds together code and data it manipulates and keeps both safe from outside interference and misuse. Class is an example of encapsulation.

Inheritance:

Inheritance is the process by which one object acquires the properties of another object. So a class can have a subclass. The subclass will inherit the state and behaviour of its parent class plus it can have its own state and behaviour.

Polymorphism:

Polymorphism is a concept that allows one interface to be used for a general class of actions. e.g if in a program you need 3 differnt kind of stacks: 1 used for integers, one for characters and one for floating point numbers, then you can use 3 methods with the same name.