POWER BI - DAX - Add Offset with Date

Add or minus offset (DAY,HOUR,MIN,SEC ) with datetime then we can use below one.

Use new Column with below formula

[DateTime] + #duration(0,10,0,0)

##duration(DAY,HOUR,MIN,SEC) 

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

Add or minus offset (DAY,HOUR,MIN,SEC ) with datetime then we can use below one.

Use new Column with below function.

DATEDIF(date1,date2,interval)

Interval : DAY/MONTH/YEAR.

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

To get the current date use TODAY()  or NOW()

POWER BI - DAX - Create Calendar Table

 Use the below Code.

New Table -> 

Calender = 

ADDCOLUMNS (

    CALENDAR ("01-Jan-2013", "31-Dec-2030"),

    "DATE_WID", FORMAT([Date],"YYYYMMDD"),

    "Creation Date", FORMAT([Date],"DD-MMM-YYYY"),

    "Year", FORMAT([Date],"YYYY"),    

    "Month", FORMAT([Date],"M"),

    "MonthName",FORMAT([Date],"MMM"),

    "Quarter", "Q" & FORMAT ( [Date], "Q" ))

OBIEE: Date Difference Excluding Weekends

We need to calculate the difference between two dates excluding weekends. Here we have considered Friday and Saturday as weekends.

We can create a function in database which can be used to do the same job.

 CREATE OR REPLACE Function WORKINGDAY( DATE1 IN DATE,DATE2 IN DATE )
   RETURN number
IS
   workingday number;
 
BEGIN

  select (CASE WHEN (COUNT(ROW_WID)-1)<0 then COUNT(ROW_WID) else COUNT(ROW_WID)-1 end) INTO workingday
 from w_day_d where   CALENDAR_DATE between DATE1 and DATE2
 AND  DAY_NAME NOT IN ('Friday','Saturday');

RETURN workingday;
END;

We can call this PL/SQL function in OBIEE report.

EVALUATE('WORKINGDAY(%1,%2)' as double, DATE1,DATE2)


Stay well and enjoy !!

OBIEE Out of Disk Space

Error Details

Error Codes: AAD5E5X3:OPR4ONWY:U9IM8TAC
Odbc driver returned an error (SQLFetchScroll).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46118] Out of disk space. (HY000)

We can overcome this issue by clearing up some cache files. 

1) WebLogic Server Log Files

Check your Admin and Managed server log directories and remove any old log files that you no longer exist:

[Middleware Home]/user_projects/domains/bifoundation_domain/servers/bi_server1/logs
[Middleware Home]/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

2) Oracle BI System Log Files

Check all the sub-folders underneath the following directory and remove any old .log files:

[Middleware Home]/instances/bi_instance/diagnostics/logs

3) Oracle BI "tmp" Folder for Cache Entries

If an Oracle BI process dies unexpectedly for any reason it mean that tmp and cache files do not get cleared up.   So check all the folders and sub-folders in the following location and remove any old files:

[Middleware Home]/instances/bi_instance/tmp

4) Core Dump Files

Some processes will create core dump files/folders to be created if they ever die unexpectedly.     So it would be worth removing any "core_" files and folders which exist in the following locaiton:

[Middleware Home]/instances/bi_instance

5) Oracle BI Patch Files

After you have applied a patch to Oracle BI 11g, you can remove the extracted patch folders once they have been applied.   The patch files would have probably been extracted into sub-folders within the following locations:

[Middlware Home]/Oracle_BI1                   e.g. /app/oracle/middleware/Oracle_BI1/7843472

[Middlware Home]/Oracle_common

If issue still not resolved then we can change the workdirectory to the new mount point in NQSconfig.ini and restart the services.

Oracle Apps DB: Active Sessions

SELECT DISTINCT fu.user_name user_name              
               ,to_char(ic.first_connect, 'DD-Mon-YYYY HH12: MI: SS AM') "First conn"
                ,to_char(ic.last_connect, 'DD-Mon-YYYY HH12: MI: SS AM')"Last conn"
                ,decode((ic.disabled_flag), 'N', 'ACTIVE', 'Y', 'INACTIVE') status
                ,fvl.responsibility_name "Resp"
                ,ic.function_type
                ,ic.time_out
                ,fu.user_id
                ,ic.org_id
                ,fr.menu_id
  FROM fnd_user                   fu
      ,fnd_responsibility         fr
      ,icx_sessions               ic
      ,apps.fnd_responsibility_vl fvl
 WHERE fu.user_id = ic.user_id
   AND fr.responsibility_key = fvl.responsibility_key
   AND fr.responsibility_id = ic.responsibility_id
   AND ic.disabled_flag = 'N'
   AND ic.responsibility_id IS NOT NULL
   AND ic.last_connect > SYSDATE - (ic.time_out / 60) / 96;

NOTE: Copied Code. 

Oracle DB : TableSpace Used Details

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name
--and rownum < 5
ORDER BY 1;

NOTE: Copied Code. 

Oracle DB: CPU Usage By USER

Displays CPU usage for each User. Useful to understand database load by user.


SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

NOTE: Copied Code.