Power BI: Show Last Data Refresh on Dashboard

 To show last data refresh on Power BI report follow the below steps.

1. Open Report Query Editor Mode.

2. Clink on Get Data -> Blank Query

3. Once you have Blank Query table “Query1” in place under the Queries section, right click on it and rename it to “Last Refreshed Date”

4. Now, open the Advance Editor from the Home ribbon and paste the below M code.

let
Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})
in
Source

5. Click on Close & Apply

6. Next step is to add a calculated measure field to use with the Date Last Refreshed column

          Date Last Refreshed = VALUES (‘Date Last Refreshed'[Date Last Refreshed])

 

 

Happy Learning !!

Power BI: Blank Replace by Zero

 When we need to replace blank values with 0, use below DAX formula for Measure.

Audit Count = IF (

    CALCULATE ( COUNT ( 'Audit Master Data'[Ref.] )) = BLANK (),

    0,

    CALCULATE ( COUNT ( 'Audit Master Data'[Ref.] ) )

)

Happy Learning !! :) 

Power BI : Slicer Impact on One Visual

 Sometime we have a requirement where slicer we created should be impacted only for certain visuals not for all. To achieve this follow below steps.

1. Select the slicer.

2. Go to Format from pane option.

3. Click on "Edit Interactions" 

4. Select the below option for which ever visuals you don't want slicer value to be impacted. 

5. Save the changes and enjoy. 

happy learning !! :)

OAS : Custom Links in the Global Header

 Sometime we have requirement to put any kind of Custom Link along with the OAS Global Header.

In order to do we will have to do two things. First need to create a Custom XML file where details will be mentioned and enable the Customlink in instanseconfig.xml file. 

Note: If you would have created the same in OBIEE , can Custom XML file can be manually moved to OAS.

Custom XML File Content (customlinks.xml)

<?xml version="1.0" encoding="utf-8"?>

<customLinks xmlns="com.siebel.analytics.web/customlinks/v1">

      <link id="l2" name="Data Visualiztion" description="Data Visualiztion" src="http://OAS_URL:PORT/dv/?pageid=home" target="blank" >

         <locations>

               <location name="header" insertBefore="catalog" />

         </locations>

      </link>  

</customLinks> 

We need to keep this file in below location: 

$DOMAIN_HOME/bidata/components/OBIPS/

Now we will have to add a new line in instanseconfig.xml file to enable custom link.

Location: $DOMAIN_HOME/config/fmwconfig/biconfig/OBIPS

Add the below tag in instanseconfig.xml file 

<CustomLinks>
<Enabled>true</Enabled>
</CustomLinks>

Restart the OAS Presentation services. 

Happy Learning !!

OAS : OBIEE 11g to OAS 5.9.0 Upgrade Steps

 Here we will try to capture all required steps to upgrade OBIEE 11.1.1.7.0 to OAS 5.9.0 but in very high level. 

High Level Steps are as Follow. 

                 1. OBIEE 12C Installation

                 2. Migration OBIEE 11g to OBIEE 12C. 

                 3. Post Migration Activity & Tuning. 

                 4. OAS 5.9.0 Installation & Upgrade. 

                 5. Security Configuration

Now lets see the above steps in little bit more details. 

           1. OBIEE 12C Installation

                    a. OBIEE 12C Infrastructure Installation ( Enterprise Manager, WLS, etc.. )

                    b. OBIEE 12C Installation ( OBIEE, BI Platform, BI Publisher, etc. ..)

                    c. RCU - Repository Create Utility ( All BI Schema create & tablespaces)

                    d. OBIEE 12C Configuration ( New Domain Setup)

                    e. Apply Patches (OBI Bundle Patches, WLS patches)

            2. Migration OBIEE 11g to OBIEE 12C.        

                    a. Generating the BI Migration Tool

                    b. Creating the Export Bundle

                    c. Importing with the BI Migration Script

                    

             3. Post Migration Activity & Tuning.     

                    a. Enable Usage Tracking

                    b. Export & Import Users & Groups

                    c. Removing the BISystemUser Policy

                    d. Set Compatibility Framework

                    e. Save Content with HTML

                    f. Increase Java Heap

                    g. Configure Maximum Number of Allowed Records

                    h. BI JavaHost Configuration

                    i. Enabling Clusters

                    j. Enabling Oracle Hardware Acceleration

                    k. Set Compatibility Mode

              4. OAS 5.9.0 Installation & Upgrade.  

                    a. Infrastructure Installation

                    b. Apply the Weblogic Patch

                    c. Install Oracle Analytic Server

                    d. Run the Readiness Check

                    e. Upgrade Schemas

                    f. Backing up Mapviewer Config File

                    g. Reconfigure BI Domain

                    h. Restoring Mapviewer Config File

                    i. Upgrade the Configuration

                 5. Security Configuration

                            OBIEE 12C or OAS has removed Catalog concept. 

Happy Learning !!

OAS : Dashboard Slowness Issue

Sometime we face slowness issue while trying to open any dashboard. Reason behind this When the dashboard list link is clicked, a lot of Catalog object permissions need to be checked. When there are multiple dashboards, the number of operations checking permissions get multiplied - these result in multiple calls back and forth to BI Server, the database, etc., causing slowdown.

Solution : 

Solution is to enable Catalog Cache as follows.

1. Edit <DOMAIN_HOME>/bi/config/fmwconfig/biconfig/OBIPS/instanceconfig.xml

2. Enable catalog cache by adding the below tags under Cache section.

<Cache>
...
<CatalogAttributes>
<Enabled>true</Enabled>
</CatalogAttributes>
</Cache>

3. Restart OBIPS(obips1) to reflect the change.

Note:

The same issue resolved in another case for a different root cause by adding the below tag in instanceconfig.xml

<Dashboard>

<CacheBannerList>true</CacheBannerList>

</Dashboard>

This setting would give performance improvement on dashboard menu listing within a user session i.e. first time in a new session, user may see slowness.

But after that within the same session, it would be instantaneous irrespective of how long the session ends.

But there is an implication of it: Any newly created dashboard would not get automatically listed in the dashboard menu until user logs off and logs in again.

Happy Learning !!

OAS : Files/Logs/Scripts Location

 OBIEE 12C or OAS file location is different than OBIEE 11G. 

Below are the location for some critical files/logs/scripts.

NQSConfig.INI: 

$/Middleware/Domain_Home/bi/config/fmwconfig/biconfig/OBIS

instanceconfig.xml

$/Middleware/Domain_Home/bi/config/fmwconfig/biconfig/OBIPS

Live RPD:

$/Middleware/Domain_Home/bi/bidata/service_instances/ssi/metadata/datamodel/customizations

Catalog: 

$/Middleware/Domain_Home/bi/bidata/service_instances/ssi/metadata/content/catalog

Admin Server Log file:

$/Middleware/Domain_Home/bi/servers/AdminServer/logs

BI Server Log File:

$/Middleware/Domain_Home/bi/servers/bi_server1/logs

BI Tools (datamodel.sh, start.sh, stop.sh, status.sh, etc.. )

$/Middleware/Domain_Home/bi/bitools/bin

happy Learning !!

OBIEE : Catalog Manager Report Run Through Command line

 Below is the syntax to run catalog manager report through command line. 

./runcat.sh -cmd report -offline CATALOG_PATH -outputFile EXPORT_REPORT_PATH/File.csv

-delimiter ";" -type "Analysis" -fields "Col1:Col2:Col3"

Example:

./runcat.sh -cmd report -offline /u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/OracleBIApps -outputFile /u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager/export_object_names01.csv -delimiter ";" -type "Analysis" -fields "Name:Created:Modified:Path:Subject Area:Table:Column" 

Happy Learning !! 

OAS : Repository (RPD) Upload/Download

 OBIEE 12C or OAS has removed the option to upload RPD through EM page. 

We will have to upload or download RPD through command prompt only. 

Go to the below path:

$Middleware/Domain_Home/bi/bitools/bin

Run the below command to Download RPD : 

Syntax: 

./datamodel.sh downloadrpd -O RPD_PATH/oas.rpd -U weblogic -P WeblogicPassword -SI ssi

Example:

./datamodel.sh downloadrpd -O /Stage/OBIEE/uat/RPD/oas.rpd -U weblogic -P weblogic123 -SI ssi

It will ask for RPD password. 

Run the below code if you don't want to mention WebLogic password in command script. 

./datamodel.sh downloadrpd -O /Stage/OBIEE/uat/RPD/oas.rpd -U weblogic  -SI ssi

It will ask for WebLogic & RPD password. 

Run the below command to Upload RPD : 

Syntax: 

./datamodel.sh uploadrpd -I RPD_PATH/after_migration_new.rpd -SI ssi -U weblogic -P WeblogicPassword

Example:

./datamodel.sh uploadrpd -I /Stage/OBIEE/uat/RPD/after_migration_new.rpd -SI ssi -U weblogic -P weblogic123

As like download , upload also can be run without WebLogic Password. 

Happy Learning !!

OAS : Use of Tooltip

 

Hover your mouse on graph it will show the all values along with all dimensions. Like below.

Let’s say if we want to show values as tooltip only then we need to add those columns in Tooltip section.

Now let’s say, we only want tooltip values to be shown when we hover mouse on graph.

Then Select Tooltip -> Tooltip Grammar Only

Keep learning & Stay healthy !!

POWER BI - Newly Created Column Add

 Sometime we needs to add new columns in Power BI whatever we have created the same in Database. 

In order to do so follow the below steps. 

1. Open Power BI report in Desktop.

2. Click on Home -> Edit Quires.

3. In Query Editor click on Home -> Refresh Preview 

                We will have tow options ( Refresh Preview & Refresh All). IF you wants to refresh only the selected table then clink on Refresh Preview and if you wants to refresh all tables in Data model then click on Refresh All. This step will bring all new columns in respective tables.

4. Then save and close. 

Enjoy !!

POWER BI - DAX - FIND()

 When we are trying to search for a specific character or latter from a Word then we can use FIND () function.

Lets say we need to find '/' from COLUMN value, if it find the character then it would return 1 else Null. 

FIND("/",COLUMN,1,BLANK())

Happy learning !!

POWER BI - DAX - Group By [ SUMMARIZE() ]

  Sometime we wants to use some aggregate function like MIN, MAX, COUNT, etc.. but that should be grouped by some dimension fields. In Power BI we just cant create a new column we MIN or MAX function. that will give us MIN and MAX value from the entire data set. So in order to achieve our requirement we  need to use SUMMARIZE ().

Create a new Table use the below formula .

Groupedby_Table = SUMMARIZE (Dim1,Dim2,"Column Name 1 - Min", MIN(X),"Column Name 2 - Max", MAX(X))

Follow below example :

Employee_INOUT = SUMMARIZE(

'Access JournalLogView',

'Access Personnel'[Emp No],'Access Personnel'[Employee Name],'Access JournalLogView'[Entry/Exit Date].[Date],'Access Personnel'[Ref. Ind],

"Entry Time" , MIN('Access JournalLogView'[Entry/Exit Time]),

"Exit Time" , MAX('Access JournalLogView'[Entry/Exit Time]))

POWER BI - DAX - New Column for Flag

 when you wants to create a new column which will be used as flag.

Create a new column and use the below formula 

Flag = IF ( X1=X2 , "Yes","No")

Example ..

FLAG = IF(WC_ASSAI_TRANSMITTAL_F[LATEST_REV]=WC_ASSAI_TRANSMITTAL_F[REVISION_CODE], "Y","N")

POWER BI - DAX - New Measure ( Distinct Count with Multiple Filters )

 When you ants to create a new measure like count or distinct count with some multiple filters.

CALCULATE ( COUNT ( X , FILTER(Exp1, Exp2, ...)))

CALCULATE ( DISTINCTCOUNT ( X , FILTER(Exp1, Exp2, ...)))

Check the below example..

CALCULATE (DISTINCTCOUNT (WC_ASSAI_TRANSMITTAL_F[DOCS_SEQ_NR]),FILTER(WC_ASSAI_TRANSMITTAL_F,WC_ASSAI_TRANSMITTAL_F[FLAG]="Y" && WC_ASSAI_TRANSMITTAL_F[RETURN_DATE] = BLANK() && WC_ASSAI_TRANSMITTAL_F[RESPONSIBLE_ENGINEER_IND]="Y" && WC_ASSAI_TRANSMITTAL_F[DUE_DATE]< TODAY() ))

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.