Tableau LOD Calculations Explained with Ranking / Window functions

I started my learning journey with Tableau few months ago. 

So far , it is pretty good. However , I found LOD ( level of detail) expressions are interestingly complex 

at the first encounter.  Reading thru few examples and watching few videos to get a better understanding of these concepts. 

Being I come from database background and SQL knowledge , I am hoping , I can provide my understanding of LOD thru ranking/ analytical 

functions. 

Please see my blog posts on ranking / analytical functions  here 

https://mfzahirdba.blogspot.com/2009/10/analytics-20.html

https://mfzahirdba.blogspot.com/2009/11/analytics-20-part-ii.html

https://mfzahirdba.blogspot.com/2018/10/windowing-clause-why-it-matters.html

https://mfzahirdba.blogspot.com/2013/08/oracles-analytical-functions-vs-sql.html

https://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html

There are three LOD keywords ( FIXED , INCLUDE , EXCLUDE) in Tableau. 

FIXED LOD calculations does not depend on the visuals ( aka viz) , where as visulation impacts 

how INCLUDE and EXCLUDE are calculated. 

In my example , I have created a table in SQL server and populated few records. 

To follow along the example , feel free to use the script below. 

Create table CourseEnrollment 

( StudentName varchar(50) , 

  Department varchar(50) , 

  Subject varchar(50) , 

  EnrollmentDate Date 

 ) ;

 GO 

Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Maths' , 'Pre Calculus' , '1988-03-01') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Maths' , 'Multivariate Calculus' , '2010-05-16') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Statistics' , 'Statistics 101' , '2020-07-25') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' ,  'Maths' , 'Pre Calculus' , '1991-03-01') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' ,  'Physics' , 'Phy101' , '2020-07-25') ;

I have connected Tableau to my underlying table . 

Here , I want to know the very first enrollment of the student no matter what department / course he enrolled into . Also , this value does not change , when the viz is changed. 

In the "sheet" tab , I create a calculated field for the FIXED LOD  , by clicking on "Create Calculated Field " as below. 

Similarly , create LOD calculations for INCLUDE and EXCLUDE. 

Let us bring in these LOD calculations into the sheet . 

The value for First Enrollment Date - Fixed is the minimum enrollment date   per student , no matter what the dimensions are available in the viz.  FIXED by Student Name .

The value for First Enrollment Date - INCLUDE is the minimum enrollment date  per student and all other available dimension in the viz . In our case , Department . So , in essence , first enrollment date per student / department. 

The value for First Enrollment Date - EXCLUDE is the minimum enrollment date  per student and all other available dimension in the viz , except student name as specified in the LOD  . In our case , Department . So , in essence , first enrollment date per department. 

These results can be derived from SQL using analytical / window functions as 

When we remove the department  / Student , the FIXED LOD remains the same ; there is a change in Include and Exclude. 

Hope , this simple example provides the LOD in the eyes of SQL developer. 

I offer support for database maintenance , performance tuning , database development. Please contact for any database related activities. 

Renaming PDB

 Renaming the PDBS is very easy in the CDB / PDB architure. 

With 18c onwards , we can have 3 pdbs with the regular oracle license. 

Please refer to the documentation for further details. 

In my example , I have 3 pdbs. We will be renaming one of the pdbs ( from PDB1 to  pdb_lx). 

Here are my list of PBS

SQL> select name  from v$pdbs;

NAME

--------------------------------

PDB$SEED

PDB1

PDB2

PDB3

Let us confirm if we are in the correct container. 

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_id

CON_ID

------------------------------

3

SQL> show con_name

CON_NAME

------------------------------

PDB1

PDB needs to be restricted state for it to be renamed. 

So , we will close and reopen in the restricted mode. 

SQL> alter pluggable database rename global_name to pdb_lx;

alter pluggable database rename global_name to pdb_lx

*

ERROR at line 1:

ORA-65045: pluggable database not in a restricted mode

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open restricted;

Pluggable database altered.

SQL> alter pluggable database rename global_name to pdb_lx;

Pluggable database altered.

Just to confirm , we will select the old name for the PDB. 

As expected , this will fail. 

SQL> alter session set container=PDB1;

ERROR:

ORA-65011: Pluggable database PDB1 does not exist.

Even though the renaming of the PDB is successful , the underlying file structure still has pdb1. 

$ ls -lrt /ora01/oradata/CDBLX/pdb1

total 640088

-rw-r-----. 1 oracle oinstall  37756928 Apr 19 11:32 temp01.dbf

-rw-r-----. 1 oracle oinstall   5251072 Apr 19 11:39 users01.dbf

-rw-r-----. 1 oracle oinstall 104865792 Apr 19 11:39 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Apr 19 11:39 system01.dbf

-rw-r-----. 1 oracle oinstall 262152192 Apr 19 11:39 sysaux01.dbf

We will create directory and move the datafiles ONLINE ( hey , we are in 19c) . 

mkdir -p / /ora01/oradata/CDBLX/pdb_lx

$ ls -lrt /ora01/oradata/CDBLX/pdb_lx/

total 0

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 19 11:47:46 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> alter session set container=pdb_lx;

Session altered.

SQL>  alter database move datafile '/ora01/oradata/CDBLX/pdb1/undotbs01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/undotbs01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/sysaux01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/sysaux01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/system01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/system01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/users01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/users01.dbf';

Database altered.

We will drop the temp tablespace datafile from the old directory and recreate it in the new directory.

SQL> alter database tempfile '/ora01/oradata/CDBLX/pdb1/temp01.dbf' drop including datafiles;

Database altered.

SQL> alter tablespace TEMP add tempfile '/ora01/oradata/CDBLX/pdb_lx/temp01.dbf' size 1G reuse;

Tablespace altered.

Let us confirm the contents of the  directory structure

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb1/

total 0

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb_lx

total 641056

-rw-r-----. 1 oracle oinstall  104865792 Apr 19 11:48 undotbs01.dbf

-rw-r-----. 1 oracle oinstall  262152192 Apr 19 11:49 sysaux01.dbf

-rw-r-----. 1 oracle oinstall  283123712 Apr 19 11:49 system01.dbf

-rw-r-----. 1 oracle oinstall    5251072 Apr 19 11:49 users01.dbf

-rw-r-----. 1 oracle oinstall 1073750016 Apr 19 11:49 temp01.dbf

Let us say close the PDB and open it in the normal mode. 

SQL> alter pluggable database pdb_lx close;

Pluggable database altered.

SQL> alter pluggable database pdb_lx open;

Pluggable database altered.

We are back in business with the new name for the PDB. 

For the databases in non-CDB architecture , we will be using DBNEWID (nid) utility.