SQL Developer Hidden Features

Opening objects in separate views without closing them:

Option 1: Set this in:
Tools > Preferences > Database > ObjectViewer > Automatically Freeze Object Viewer Windows

Option 2: If you like to pin only certain query results and not all the time, here is an alternative:

SQL to Hive Cheat Sheet

Retrieving Information

Function	MySQL	Hive
Retrieving Information (General)	SELECT from_columns FROM table WHERE conditions;	SELECT from_columns FROM table WHERE conditions;
Retrieving All Values	SELECT * FROM table;	SELECT * FROM table;
Retrieving Some Values	SELECT * FROM table WHERE rec_name = "value";	SELECT * FROM table WHERE rec_name = "value";
Retrieving With Multiple Criteria	SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";	SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Retrieving Specific Columns	SELECT column_name FROM table;	SELECT column_name FROM table;
Retrieving Unique Output	SELECT DISTINCT column_name FROM table;	SELECT DISTINCT column_name FROM table;
Sorting	SELECT col1, col2 FROM table ORDER BY col2;	SELECT col1, col2 FROM table ORDER BY col2;
Sorting Reverse	SELECT col1, col2 FROM table ORDER BY col2 DESC;	SELECT col1, col2 FROM table ORDER BY col2 DESC;
Counting Rows	SELECT COUNT(*) FROM table;	SELECT COUNT(*) FROM table;
Grouping With Counting	SELECT owner, COUNT(*) FROM table GROUP BY owner;	SELECT owner, COUNT(*) FROM table GROUP BY owner;
Maximum Value	SELECT MAX(col_name) AS label FROM table;	SELECT MAX(col_name) AS label FROM table;
Selecting from multiple tables (Join same table using alias w/”AS”)	SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;	SELECT pet.name, comment FROM pet JOIN event ON (pet.name = event.name)

Metadata

Function	MySQL	Hive
Selecting a database	USE database;	USE database;
Listing databases	SHOW DATABASES;	SHOW DATABASES;
Listing tables in a database	SHOW TABLES;	SHOW TABLES;
Describing the format of a table	DESCRIBE table;	DESCRIBE (FORMATTED|EXTENDED) table;
Creating a database	CREATE DATABASE db_name;	CREATE DATABASE db_name;
Dropping a database	DROP DATABASE db_name;	DROP DATABASE db_name (CASCADE);

Current SQL Compatibility

Command Line

Function	Hive
Run Query	hive -e 'select a.col from tab1 a'
Run Query Silent Mode	hive -S -e 'select a.col from tab1 a'
Set Hive Config Variables	hive -e 'select a.col from tab1 a' -hiveconf hive.root.logger=DEBUG,console
Use Initialization Script	hive -i initialize.sql
Run Non-Interactive Script Source	hive -f script.sql

SQL: Joins Explained - Visually

SQL: Oracle Naming Conventions

When designing a database it's a good idea to follow some sort of naming convention. This will involve a little thought in the early design stages but will save significant time when maintaining the finished system.

It's less important which exact conventions you choose to follow - but this page has a few suggestions.
The benefits of using a naming convention are more to do with human factors than any system limitations - but this does not make them any less important.
Table Names

Table names are plural, field name is singular

Table names should not contain spaces, words should be split_up_with_underscores.
The table name is limited to 30 bytes which should equal a 30 character name (try a DESC ALL_TABLES and note the size of the Table_Name column)

If the table name contains serveral words, only the last one should be plural:
APPLICATIONS
APPLICATION_FUNCTIONS
APPLICATION_FUNCTION_ROLES

There are pros and cons to adding a prefix or suffix to identify tables-

Pros: If most access will be made via VIEWS then prefixing all the tables with T_ and all the views with V_ keeps things organised neatly, you will never accidentally query the wrong one.

Cons: Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the APPLICATIONS table would be called APPLICATIONS_TAB. If as time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, you will then have to create a View or Synonym that points at the original tables and is confusingly called APPLICATIONS_TAB.

Of course if all your code is written against Views in the first place then you will skirt right around this issue.

Field Names

Ideally each field name should be unique within the database schema. This makes it easy to search through a large set of code (or documentation) and find all occurences of the field name.

The convention is to prefix the fieldname with a 2 or 3 character contraction of the table name e.g.

PATIENT_OPTIONS would have a field called po_patient_option
PATIENT_RELATIVES would have a field called pr_relative_name
APPOINTMENTS would have a field called ap_date

In a large schema you will often find two tables having similar names which could result in the same prefix. You can avoid this by thinking carefully about the name you give each table - and documenting the prefixes to be used.
One advantage of this prefix is that you are very unlikely to choose a reserved word by accident.

For very complex systems (thousands of tables) consider alternatives e.g. a prefix/suffix to identify the Application module.
Keeping names short: Oracle places no limit on the number of columns in a GROUP BY clause or the number of sort specifications in an ORDER BY clause. However, the sum of the sizes of all such expressions is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.

Primary Key Fields - indicate by appending _pk

e.g.
PATIENTS would have a primary key called pa_patient_id_pk

REGIONS would have a primary key called re_region_id_pk
And so on…the name of the primary key field being a singular version of the table name. Other tables containing this as a foreign key would omit the _PK

so
CLINIC_ATTENDANCE might then have a foreign key called ca_patient_id
or alternatively: ca_patient_id_fk

Tables with Compound PK's, use _ck in place of _pk

Notice that where several tables use the same PK as part of a compound foreign key then the only unique part of the FK fieldname will be the table prefix.

View Names

View names are plural, field name is singular

View names should not contain spaces, words should be split_up_with_underscores.
While it is common to prefix (or suffix) all views with V_ or VW_, a strong argument can be made that neither are really needed.
One of the easiest ways to boost the performance of an application is to provide, at an early stage in the design, a carefully tuned set of views, then write all the application code against those Views.
Giving your Views friendly easy names will promote their use by developers and end-users, this in turn will mean fewer badly written queries and more use of 'shared SQL' which will improve the cache hit ratio.
For very large systems, it can make sense to prefix tables/views with the application module name, so a database holding data for both Widget Production and Human Resources data might prefix everything with either HR_ or WP_

Index names

Name the Primary Key index as idx_<TableName>_pk

e.g.
PATIENTS would have a primary key index called idx_patients_pk

Name a Unique Index as idx_<TableName>_uk

e.g.
PATIENTS would have a unique index called idx_patients_uk

Where more indexes are added to the same table, simply append a numeric:

idx_<TableName>_##
Where ## is a simple number

e.g.
PATIENTS would have additional indexes called idx_patients_01, idx_patients_02,…

Note - Conventions that attempt to use the column name as part of the index name become unmanageable as soon as you have multiple columns appearing in multiple indexes.

Constraints

Primary and Unique constraints will be explicitly named.

Name the Primary Key Constraint as pk_<TableName>

e.g.
PATIENTS would have a primary key index called pk_patients

Name a Foreign Key Constraint as fk_<TableName>

e.g.
PATIENTS would have a Foreign Key constraint called fk_patients

Note - in general each constraint should have a similar name to the index used to support the constraint.

Other Fields

Without getting carried away, you can also apply a suffix to non key fields where this is helpful in describing the type of data being stored.
e.g.
A column used to store boolean (Yes/No) values can be given a _yn suffix: retired_yn, superuser _yn, driver_yn

In lookup tables an easy way to identify the main text field is to name it as a singular version of the tablename
e.g.
asset_types.at_asset_type_id_pk (Primary Key) asset_types.at_asset_type (Text field) asset_types.at_network_yn (boolean)

SQL

Type all SQL statements in lowercase, being consistent with capitalisation improves the caching of SQL statements. A common variant is to put only SQL keywords in capitals.
SELECT em_employee_id_pk, em_employee, ab_start_date FROM employees em, absences ab WHERE absences.ab_employee_id=employees.em_employee_id_pk;You already have a unique prefix worked out for every table, so use the same thing when an ALIAS is required - this makes the SQL much easier to read.

Always list tables in the FROM / WHERE clause in desired join order - even with CBO you are giving the Query Optimiser less work to do.

PL/SQL

Prefix scalar variable names with v_
Prefix global variables (including host or bind variables) with g_
Prefix constants with c_
Prefix procedure or function call parameters (including sql*plus substitution parameters) with p_

Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)

Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)

Suffix cursors with _cursor
Prefix exceptions with e_

If a pl/sql variable is identical to the name of a column in the table Oracle will interpret the name as a column name.

Packages
Prefix package names with PKG_

Write one package for each table - named PKG_TABLENAME, put all other code that logically belongs to the schema, but not to any particular table in a single Schema package PKG_SCHEMANAME.
If, as is likely, more complex grants are required for different groups of users then create an additional package for each workgroup - these should contain no code just wrappers that call procedures/functions in the other packages.
This gives a level of separation between the basic code and the user security/grants and makes it easier to change one without breaking the other.

A pl/sql function name like PAYROLL.TAX_RATE the word PAYROLL could refer to either a schema or a package name.

Edit Replace

If you apply a naming convention and then decide to rename something it may be possible to use Edit-Replace to update the associated code. But consider these two fieldnames:

area_codes.ac_code   region_area_codes.rac_code

The columns may be unique but one is a substring of the other!

Instance

Oracle database instance names are limited to eight characters. The first two or three characters of the name should reflect the Application, with the remainder indicating the nature of the instance.
e.g.
Live instance SSLive Test instance SSTest Train instance SSTrain Data Warehouse SSdw Staging Area SSsa

Data Files

Name Data files so that they identify the instance and the tablespace.

Each filename should end with a two digit numeric value starting with 01, that is incremented by 1 for each new datafile added to the tablespace.
Use the extension ".dbf"
e.g.
SSLive_temp01.dbf SSLive_rbs01.dbf SSLive_clinical01.dbf SSLive_clinical02.dbf

Tablespaces

Avoid naming tablespaces according to time periods.
(Oracle never forgets a tablespace and SMON will scan the list of tablespaces in TS$ every 5 minutes) For a partitioned datawarehouse, try to adopt a strategy of recycling the tablespace names.

Redo Logs

The redo log is a separate file (not in the tablespace)
Name Redo Logs so that they identify the instance, group and member number of the log. Use the extension ".log"
e.g.
SSLive_redo_01.log

For more detail on the physical placement of files see Oracle Optimal Flexible Architecture (OFA)

Documentation

Lastly - write and maintain a data dictionary for all data elements - rather than just dumping the Oracle data dictionary into a text document or an Entity relationship diagram - you should also be defining the business meaning of each data item.

Summary

RDBMS naming conventions can become the subject of endless debate - here are a few last things to consider:

Does your naming convention make names longer or shorter?

PURCHASE_ORDER_DATE versus PO_DATE

Will you have novice users writing SQL against the database?
If so will they understand the meaning of things like PO_DATE

Is the naming convention documented somewhere that everyone can find? If you don’t plan to change it very often, drop the text right into a table SELECT * from Naming_Conventions;

Source

OBIEE: New Calculated Item in Pivot Table

This post explains how to calculate "New Calculated Item" option in Pivot table:

Step 1:

Step 2:

Step 3:

Step 4: We can change function to "Avg/Count/Last/First ..." In above example I have taken Function "Average" format the Title with different color.

SQL: Top 10 SQL Query Tuning or SQL Optimization

SQL Tuning / SQL Optimization Techniques:

1) SQL query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:
SELECT * FROM student_details; 

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.

For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
   a) Usually IN has the slowest performance.
   b) IN is efficient when most of the filter criteria is in the sub-query.
   c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:
Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.
   a) Use single case for all SQL verbs
   b) Begin all SQL verbs on a new line
   c) Separate all words with a single space
   d) Right or left aligning verbs within the initial SQL verb

Source

SQL Server Date Formats

Standard Date Formats Date Format Standard SQL Statement Sample Output Mon DD YYYY 1 HH:MIAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1 MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98 MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998 YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01 YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01 DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72 DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972 DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05 DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005 DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98 DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998 DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1 DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1 Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1 Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1 HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53 Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default + milliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1 MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06 MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006 YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23 YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23 YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124 YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124 DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1 HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013 YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24 YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical (with milliseconds) SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489 YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250 DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1 DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function. Extended Date Formats Date Format SQL Statement Sample Output YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] 99-01-24 YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] 1999-01-24 MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 08/99 MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005 YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08 YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12 Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1 Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1 Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1 DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1 Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1 DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1 DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1 MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] 12/92 MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006 YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] 92/12 YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05 MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506 MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006 DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702 DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002 Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] Sep-02 1 Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] Sep-2002 1 DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1 DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-2005 1 1 To make the month name in upper case, simply use the UPPER string function.

Source

OBIEE 11g: Bridge Tables and Many to Many Relationships in OBIEE 11g

by John Cook

Bridge tables - entire books have been devoted to this concept, countless blogs write about it, and organizations offer entire classes dedicated to demystifying this idea. Ralph Kimball, creator of Kimball Dimensional Modeling and founder of the Kimball Group has written quite a few great articles discussing the theory of bridge tables.

Yet when researching for comprehensive guides on how to actually implement a bridge table in OBIEE 11g, the documentation available is either:

• Out of date
• Contains implementation steps for OBIEE 10g which has since been deprecated
• Does not contain adequate detail 
• e.g. missing key steps

This guide is going to outline the basic use case of a many to many relationship, how OBIEE 11g resolves this dilemma and how to successfully implement a bridge table model within the 11g platform.

First thing's first - what is a bridge table and why do we need it?

At its core, bridge table solve the many to many relationship we encounter in many datasets. Many to many relationships in itself are not "bad", but when attempting to conform a data set to a star schema - many to many relationships just do not work. Star schemas assume a one to many (1:N) cardinality from the dimension to the fact. This means "one attribute of a dimension row can be found in many rows of the fact table".

For Example:

• One job (job dimension) can be performed by many people
• You would see the same JOB_WID repeating in the fact table
• One employee (employee dimension) can have many jobs
• You would see the same EMPLOYEE_WID  repeating in the fact table
• One call at a call center(ticket dimension) can have many ticket types
• You would see the same CALL_WID repeating in the fact table
• One patient (patient dimension) can have many diagnosis
• You would see the same PATIENT_WID repeating in the fact table

This 1:N cardinality is represented in OBIEE as (using call center/employee example) :

"Cardinality of '1' applied to the dimension and cardinality of 'N' applied to the fact'

But what happens when in the above examples, the cardinality is actually N:N? 

For Example:

• Many employees can have multiple jobs and each job can be performed by multiple employees
• Many patients can have multiple diagnosis and each diagnosis can be 'assigned' to many patients
• Many calls can have multiple call ticket types and each ticket type can belong to multiple calls

This many to many relationship is initially (and incorrectly) represented in OBIEE 11g as:

'Cardinality of '1' is applied to the two dimension tables and cardinality of 'N' is applied to the fact'

Any BI Architect should recognize the above model - it's a traditional star schema! If you stop here and decided to ignore the issue with your dataset and 'hope' OBIEE aggregates the model correctly, you're about to be disappointed.

Why star schemas dont work for N:N cardinality

Consider the following scenario: You're a call center manager and you want to capture the number of calls with positive feedback per employee. You also want to capture the type of calls an employee answers in any given day.

Upon analysis of the requirements you conclude that "each call received by an employee can have many call types and each call type can be answered by multiple employees".

For example:

• I answer a take a call that is classified as a 'new call', 'urgent', and 'out of state transfer' (three different call types) - this is the "each call received by an employee can have many call types".
• A colleague also received a phone call that is classified as 'out of state transfer' - this is the 'each call type can be answered by multiple employees"

Now let's put this data in a traditional star schema fact table as modeled below:

ID	EMPLOYEE_WID	CALL_TYPE_WID	NUMBER_OF_GOOD_CALLS
1	1	1	300
2	1	2	300
3	1	3	300
4	2	2	500
5	2	3	500
6	3	1	200

You can see in the above data set that:

• EMPLOYEE 1:
• Has 3 different call types
• Has 300 positive reviews (NUMBER_OF_GOOD_CALLS) 
• This metric is at the EMPLOYEE level and not the call type level!
• EMPLOYEE 2:
• Has 2 different call types
• Has 500 positive reviews (NUMBER_OF_GOOD_CALLS)
• This metric is at the EMPLOYEE level and not the call type level
• EMPLOYEE 3:
• Has 1 different call type
• Has 200 positive reviews (NUMBER_OF_GOOD_CALLS)

Now you receive a requirement to create a KPI that displays the Number of Good Calls as a stand alone widget.

PROBLEM 1 - Aggregation :

The number of good calls you received based on the above fact table is not 2100 - it's 300 + 500 + 200 = 1000

• Employee 1 received 300 good calls
• Employee 2 received 500 good calls
• Employee 3 received 200 good calls

but due to the many to many cardinality of the data, the star schema duplicates the measures because each employee can take calls of many call types and each call type can be assigned to many employees!

PROBLEM 2 - Grand Totaling:

What if you don't care about aggregates? What if you just want a report that contains the employee, call type and a summation/grand total?

Notice how NUMBER_OF_GOOD_CALLS is repeated across multiple call types and the grand total is still incorrect. It's being duplicated due to the many to many relationship that exists between call type and employee. Furthermore, it paints an incorrect picture that NUMBER_OF_GOOD_CALLS is some how related to CALL_TYPE

How do we resolve this many to many cardinality with a bridge table?

When all is said and done, the incorrectly built star schema:

should be modified to:

Let's break this down:

The bridge table:

This the purpose of the bridge table is to resolve the many to many relationship between the call type and employee. It will contain, at a minimum, the following four columns:

1. The primary key of the table
2. The EMPLOYEE_WID
3. The CALLTYPE_WID
4. The weight factor

The weight factor is what's going to resolve the issue of double counting. 

• If an employee has 3 call types, there would be 3 rows and the weight factor of each row would be .33
• If an employee has 10 call types, there would be 10 rows and the weight factor of each row would be .1

In our bridge table data set, we're going to use the same 3 EMPLOYEE_WIDs and create the following:

ID	CALL_TYPE_WID	EMPLOYEE_WID	WEIGHT
11	1	1	0.33
12	2	1	0.33
13	3	1	0.33
23	2	2	0.5
24	3	2	0.5
31	1	3	1

You can see from this example that we've taken the N:N dataset in the fact table and moved it into this bridge.

The dimension that is joined to both the fact and bridge

This is a generic dimension that contains the unique EMPLOYEE IDs in your organization's dataset.

For example:

ID	EMPLOYEE_ID
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

The dimension that is joined to only the bridge table

This dimension contains all of the possible call types. Note how this table is not physically joined to the fact. This is because this specific dimension (CALL_TYPE) is what's causing the N:N cardinality

For example:

ID	DESC
1	Call Type 1
2	Call Type 2
3	Call Type 3
4	Call Type 4
5	Call Type 5
6	Call Type 6
7	Call Type 7
8	Call Type 8
9	Call Type 9
10	Call Type 10

The Fact Table

We've moved the N:N cardinality from the original fact table to the bridge table so the new fact table now contains exactly one row per employee and does not have the CALL_TYPE_WID.

ID	EMPLOYEE_WID	NUMBER_OF_GOOD_CALLS
1	1	300
2	2	500
3	3	200

How do we implement this model in OBIEE 11g?

Step 1: Import Tables into Physical Layer

This is always the first step performed when creating a model regardless of its type. In the above example i'm importing four tables:

Step 2: Create the Physical Data Model

Based on our data set above the join conditions would be implemented as follows:

• 1:N relationship from employee dimension to fact table
• 1:N relationship from employee dimension to bridge
• 1:N relationship from call type dimension to bridge

Notice how employee_demo_d is the only dimension that is joined to the fact. w_call_type_d is not joined to the fact because that is the dimension that is causing the many to many relationship issue.

Step 3:  Create the Logical Data Model
The creation of the BMM is where we deviate from our standard build steps of a traditional star schema:

1. All associated dimension tables referencing the bridge table will be stored in a single BMM table
2. The single BMM table will have two logical table source

Step 3.1 : Drag the fact table and dimension table that is connected to the fact table into the BMM. 

In our example, we are dragging w_calls_f and w_employee_demo_d into the BMM:

Step 3.2: Create a 2nd LTS in the existing dimension table

1. Right click W_EMPLOYEE_DEMO_D -> New Object -> New Logical Table Source
2. Name it 'Bridge'
3. Add W_BRIDGE_D and W_CALLTYPE_DEMO_D (the two dimensions not directly joined to the fact table) under the 'Map to these tables' section

1. Next add the remaining dimension columns from W_CALLTYPE_DEMO_D and W_BRIDGE_DEMO_D to the Dimension table in the BMM

Step 3.3: Create a level-based dimension hierarchy for the dimension BMM

1. This step should be completed whether or not the schema is a star or bridge

Step 3.4: Confirm the BMM model has a 1:N relationship from the dimension to fact

Step 3.5: Set aggregation rule of NUMBER_OF_GOOD_CALLS to sum 

All measures in the BMM must have a mathematical operation applied to the column

Step 3.5: Set the Content level of the dimension table to 'detail' in within the LTS of the fact table

Again, this is something that should always take place regardless of the type of model

Step 4: Create the Presentation Layer

This part is straight forward, just drag the folders from the BMM into the new subject area:

The moment of truth

So why did we go through this elaborate exercise again? To fix the aggregation issues we were having with NUMBER_OF_GOOD_CALLS due to the N:N cardinality of the data set. Let's create that 'standalone KPI' Number of Good Calls:

Notice how the metric correctly sums to 1000. Let's check the back end physical query to confirm:

Notice how it's hitting the fact table and not the bridge or the call type dimension. 

But what about the weight factor?

Let's go back to the scenario where we want to compare across dimensions joined via the bridge table (EMPLOYEE and CALL_TYPE):

• When creating a report that uses a measure from the fact table, a dimension value from the the employee table, and a dimension value from the table that causes the N:N cardinality - you need to use the weight factor to make sure your measure isn't getting double or triple counted:

Notice column is using the the NUMBER_OF_GOOD_CALLS multiplied by the WEIGHT factor in column 2 Each row in column 1 correctly represents the NUMBER_OF_GOOD_CALLS in the fact table despite having the repeated values of multiple call types Note the aggregation of grand total sums to 997. This is because the weight factor is rounded to the 2nd decimal for EMPLOYEE_WID = 1 (.33%) In order for grand totaling to work correctly with bridge table measures that use weight facts you must set the aggregation rule of the column (in this case column 1) to sum within Answers: So what did we accomplish in this guide? A basic understanding of many to many (N:N) cardinality A basic understanding of why the star schema won't work for N:N cardinality How to resolve the cardinality issue with a bridge table How to implement a bridge table in OBIEE 11g Source