Monday, September 25, 2017

Oracle NVL equivalent in MS SQL Server - COALESCE

Oracle to Microsoft SQL Server
As I am new to the Microsoft SQL Server, I have decided to do a series on Oracle to SQL Server comparison. So here is one new keyword which I learned the other day.

Scenario: I had this scenario where I am supposed to show an email address of an insurance dependent, if this dependent email id is not updated then we should fetch the email id of the parent. If in Oracle I would have gone for the NVL function or a CASE statement to achieve the same. If it just comparing maybe two to three columns then a CASE statement would suffice. What if we had more than 10 columns in a way that we have to show data if one of these 10 columns are having data. 

Solution: In this scenario I could go for the COALESCE function in Microsoft SQL Server as the it doesn't provide the NVL function and anyways the COALESCE function performs better than NVL in Oracle and makes your code simpler. 

eg: Imagine we have a CUSTOMER table with 4 email address columns, EMAIL_1, to EMAIL_10.  we have to return the first available data among these columns checking serially. If in Oracle we could have gone for the below syntax,


SELECT NVL(EMAIL_1,NVL(EMAIL_2,(NVL(EMAIL_3, NVL(EMAIL_4,'Not available,),,'Not available,),'Not available,),'Not available,) EmailId
FROM CUSTOMER

As you can see the code is messy and may take time to understand. But SQL Server provides you a function for such a scenario called the COALESCE function, which does the same for you. The above result in COALESCE would look like below, 

SELECT COALESCE(EMAIL_1,EMAIL_2,EMAIL_3,EMAIL_4) EmailId FROM CUSTOMER.

Use of the function makes way for simpler and clean code. The COALESCE function converts your code to a CASE function and executes the same. The difference with ISNULL function is that the ISNULL function is executed only once where as COALESCE is executed until a non NULL argument is reached. 

Hope this came of help to you. Happy coding. 

Oracle Target Data Warehouse Schema gets Locked out Intermittently. [SOLVED]

Issue: The Oracle Target Data warehouse schema, with dimensions and facts that OBIEE  or analytics engine access gets locked out intermittently. 

Cause: Oracle account lockouts can happen due to multiple reasons in a Data warehouse environment, 


  1. Multiple failed login attempts from a client trying to access the database. 
  2. Password expiry date has already passed. 
  3. One or more of clients accessing the system has an old password, which it uses to connect to the system.
Solution: The best solution here would be to check all jdbc/odbc/OCI connections where you have saved the schema username and password. This could be anything from connection settings in the analytics engine, parameter files, database links etc. The chances are high that at one of these connections we have provided an incorrect or old username/password combination.  This blog suggests to check the listener log on the database server hosting the schema. 

If you are using OBIEE, check the RPD connection, JDBC connection you have created to the target schema on the BI Publisher engine etc. 

It is an extremely poor solution to increase FAILED_LOGIN_ATTEMPTS parameter at the profile level as this increases the risk of a brute force attack on your database or schema. 



Friday, August 25, 2017

MINUS operation in Microsoft SQL Server using EXCEPT

Oracle Database vs Microsoft SQL Server
Oracle Database vs Microsoft SQL Server

Coming from an Oracle Database background, I had a tough time getting used to some keywords in SQL Server. While most of the keywords are ANSI compliant, there are some keywords that are unique in SQL Server database. This post is to discuss one such exception with the MINUS set operator keyword which was available in Oracle, but not in SQL Server.

Issue: MINUS keyword not working in Microsoft SQL Server. I had this particular scenario, where in I was rewriting an existing query and trying to performance tune it. To make sure the same records are returned when I make changes to queries, I use the MINUS keyword in Oracle to subtract the new query from the existing one and if no rows are returned I use the new query going forward. 

I was trying to run a query similar to below,

SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
MINUS
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME

Ideally the query should give me an output with no rows as I am trying to subtract same query from itself. Instead of returning the desired output, SQL server will show me two result sets individually executed without returning an error. 

Cause: This is because SQL Server doesn't recognize the keyword MINUS.

Solution: The solution is to use the SQL Server equivalent of MINUS set operation which is the EXCEPT keyword. Below is the restructured query for the same using the EXCEPT keyword.

SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME
EXCEPT
SELECT COLUMN_A, COLUMN_B FROM TABLE_NAME

The except keyword basically functions to return unique records in query on top(or left) that doesn't have an identical record in the query on the right side(bottom).

Things to take care when using the EXCEPT keyword are, 
  • All the data sets(queries) should have identical number of arguments.
  • Once the number of arguments are same between different queries, the next check is on the datatype of the arguments. The datatype order in different query sets should be same.  

Saturday, March 18, 2017

OBIEE Analysis Date Format as 0/0/0 [Solved]

Issue: We recently came across an error where client raised a defect that date comes up as 0/0/0 for some of the records.

Analysis: First we checked the particular records in the EDW target schema. We could see that the records are having date as null in the physical tables.

Cause: The root cause of this defect is that in the physical layer of the RPD for this particular column the nullable flag is not ticked. Since the nullable flag is not ticked, OBIEE expects a value here and substitutes 0/0/0 12:00:00 AM for null values.

The nullable flag property in OBIEE RPD Physical column object.
This could also happen if you manually change the data type from datetime to date. OBIEE imports date as datetime by default.

Resolution: Change the data type to datetime and tick the nullable flag. Once done, save and upload RPD to the server. Now clear your cache, reload files and metadata and check again. If that still doesn't change anything then do a restart of your presentation service.

Tuesday, March 7, 2017

OBIEE security LDAP realm provider JAAS Control Flag options

LDAP Configuration Control Flag Option
When you create a new LDAP, Active directory(ADSI) provider to realm in Weblogic Console, you will be able to see an option called 'Control Flag' with the below four set of choices under provider, 
  1. Optional
  2. Required
  3. Requisite
  4. Sufficient
This post we can discuss on the four types of flags and how authentication is carried when each one of them is set to true. 




Optional - In optional setting, either of the two providers should have the username/password used to login. The authentication should return success in either one of them. Authentication provider is not always called. If the first authentication provider returns true then authentication ends there. 

Required - Required is the default option configured in weblogic, when you have no other providers other than weblogic provider. In this type of control flag, the user must pass authentication in all providers. If there are 5 providers with control flag, 

Requsite - User must pass authentication test with the particular provider. Other providers are also tested. 

Sufficient- When you create a new provider to existing LDAP, user required to pass at atleast one provider. If one is a successful authentication, then authentication is completed.  

Note: For existence of Custom LDAP and obiee realm, SUFFICIENT' should be opted for.