MINUS operation in Microsoft SQL Server using EXCEPT

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.  

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.

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. 

Importance of creating separate schema for Work in ODI

We had come across an article a long while back on the significance of using a separate schema for work instead of Target schema in ODIExperts blog. We later learned from this as we could clearly see the advantage of doing the same. 

We use OBIEE as our front end tool and we could see that all the work tables of ODI for each target table would be present in the target schema. The more tables in the target schema, the more time it takes for OBIEE to process the import metadata step. Imagine you have 10 tables in your DWH target schema, if you don't define a separate work schema for each table then for one table, W_STUDENT_D you will have the following work tables also, 

C$_W_STUDENT_D, 

I$_W_STUDENT_D,

E$_W_STUDENT_D

This not only makes OBIEE import slow also it takes up table space on the target schema making database management hard. If maintained separately then you can purge all tables in the work schema at once. The worry of accidentally truncating any of your target tables are also eliminated this way. 

Another important step as they discussed is to not use the 'Stage area different from target option', which is done at the interface level. The same can be done at the physical schema in topology level, 

ODI Work Schema.

 

Using ODI Procedures to load data from Source to Target

We came across a scenario where we used ODI procedure to load data from source to target. The scenario was that we had to implement a very complex logic using sub queries in an ODI Interface. given the short timeline, we went ahead with the approach of loading the same using query in an ODI Procedure. We will discuss the same below,

An ODI Procedure has two parts, Command on Source and Command on Target,

• ODI Command on Source is where you write the source SQL query that you use to fetch data from source. 
• ODI Command on Target is where you write the insert script to load data into the target. 
• Load data from one source to another source (eg: Oracle to Teradata, MSSQL to Oracle, etc)
• The source side query can be a plain query. The number of columns in select though should match with the number columns in the insert script and their datatypes also should match. 

 eg: SELECT ROLL_NO,STUD_NAME FROM STUDENT

• The insert script should contain bind variables to select values from the source query, the insert script for the above source query would be, 

  INSERT INTO W_STUDENTS_D VALUES (:ROLL_NO,:STUD_NAME)

• Extremely inefficient compared to interfaces.
• Never preferred over interfaces. Interfaces are always faster as ODI follows all standards while generating queries. Preferred for loading of tables with less volumes of data.'
• Row by row processing happens and hence it takes longer time for data load. 

Typically the errors you get are either SQL syntax errors, Data type mismatch, column size errors, column order mismatch errors.