PL/SQL Purge Recycle Bin

Recently came across several objects that needed to be purged from our Oracle DW. It seems after a drop table the object still remains in the RecycleBin.

Example to query for the objects:

select 'purge table "' || object_name || '" ;' , original_name, type, can_undrop as "UND", can_purge as "PUR", droptime   from recyclebin

Example of purge:
purge table "BIN$3EgaHwhNbdPgQy8CKArrew==$0" ;



Using Oracle's recycle bin

SSAS MDX Query for Current Year, Current Month and Current Day

Recently I had a need to retrieve current year via an MDX query. Below are some very useful VBAMDX functions that will help with looking at current periods. 

MDX for retrieving current Year;

WITH MEMBER [Measures].[Current Year] AS

    VBAMDX.Format(VBAMDX.Now(),"yyyy")

SELECT

  {[Measures].[Current Year]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH MEMBER [Measures].[Current Month] AS

    VBAMDX.Format(VBAMDX.Now(),"MM")

SELECT

  {[Measures].[Current Month]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Date;

WITH MEMBER [Measures].[Current Date] AS

    VBAMDX.Format(VBAMDX.Now(),"dd")

SELECT

  {[Measures].[Current Date]} ON COLUMNS

FROM [Adventure Works];

Sometimes user wants to retrieve data for current year, current month, and current day. You can retrieve data by using VBA function and STRTOMEMBER function.

Check following MDX samples by using Adventure Works sample.

MDX for retrieving current Year;

WITH

  MEMBER [Measures].[Current Year Value] AS

    (

      StrToMember

      (

            "[Date].[Calendar].[Calendar Year].&["

          + VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Year Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH

  MEMBER [Measures].[Current Month Value] AS

    (

      StrToMember

        (

            "[Date].[Calendar].[Month].&[" +

             VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]

            &[" + VBAMDX.Format(VBAMDX.Now(),"MM") + "]"

        )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Month Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Day;

WITH

  MEMBER [Measures].[Today's Value] AS

    (

      StrToMember

      (

           "[Date].[Calendar].[Date].&[" +

           VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")

           + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Today's Value] ON COLUMNS

FROM [Adventure Works];