Dashboard Prompt constraining the results

In one of our dashboards, we had to use a dashboard prompt to allow users to pick a value and display the report accordingly.

The prompt in question is - Year Name and is part of our Time Dimension. The Fact table is properly connected to the Time Dimension.

When I dropped the TimeDim.YearName in to the Dashboard prompt, I was expecting the results to be constrained for those records in the Fact Table. Let me explain,

The Time Dimension has data ranging from 1990 through to 2099.

The Fact table has data starting from 2001. If i write a SQL selecting TimeDim.YearName, joining Fact to Time Dimension it will only show distinct years from 2001.

But in OBIEE, when I execute the prompt it was listing all the available Years (distinct) in Time Dimension ie 1990 through to 2099. This is not what I was expecting.

After mulling over different things, I finally got it working but I am not sure that is the way. What I done is -

In the RPD, in the BMM, I explicitly specified both TimeDim and Fact table as Sources, and explicitly created the Join condition. Once I have done this, the results are as I was expecting.

Please feel free to comment if I had missed anything obvious.

Adding Foreign Key Constraints using OMB+

To add a new foreign key to an existing table using OMB+, the syntax is

For Eg: Sales_Fact has a column Order_Date_Key and a new FK needs to be created. This Order_Date_Key is populated with the PK of Time_Dim. To implement this, the OMB+ syntaxt is -

OMBALTER TABLE 'SALES_FACT' ADD FOREIGN_KEY 'FK_SALFACT_TIME_DIM' \
SET REF COLUMMNS ('ORDER_DATE_KEY') \
SET REFERENCE PRIMARY_KEY 'DIMENSION_KEY' OF TABLE 'TIME_DIM'.

OBIEE Answers - Pivot Table / Chart Horizontal Layout - Multiline

At the current client site, we had a requirement to show PIE charts one for each section. As we all know, by default OBIEE presents the sections vertically. If the number of sections is 3 or less this layout would look nice, but what if there are more sections, scrolling down vertically would not appeal to the end users to get a comparative view.

Joe Bertram had posted Javascript to present the sections horizontally and I thought we could use this script to rotate the PIE charts horizontally. We have duely lifted his script and use in ours and the result looked like below.

Which is not appealing as the users have to scroll across, so I thought there must be a way to present this on multiple rows with limited columns per rows. Am no expert on Java scripting, so another cry for help!!

This time another OTN Member John Minkjan replied with a variant of original script by Joe and the final desired output which looked like below.

.
Thanks to Joe & John for their effort.

Snow! Snow!

Last night, we had high snow fall here in England, the view was so wonderful.

OWB 11g Getting Started Book

I have been sent the book, Oracle Warehouse Builder 11g Getting Started by Bob Greisemer (Packt Publishing, ISBN 978-1-847195-74-6), to be reviewed.

I did a quick browse through the book and my initial impression is that this is a good reference to any body who wants to know how to use the OWB as a tool. This book talks about the OWB11gR2 version and has detailed steps, with pictures, how to get started with the tool. What this book does not do is teach you any Data Warehouse concepts or any thing about Dimensional Modelling etc.

In a nut shell, this book provides a good starting point to get to know about the tool.

 

Oracle Application Express – End User Change Password Procedure

We have several applications built using Oracle Application Express, and are accessed by several end users. The Authentication mechanism that we have adopted is – “Database Account” at the global level and within each application, the access mode is set to “Restricted Access” and the users are listed within the application who needs access to it. So every time a new user needs an access, or when the user forgets their password we had to reset the password and allow the user to change the password from the application.

The procedure that I have adopted is split into 3 stages as detailed below.


STAGE 1 - Database Activities

• For the end user to change their password they need “ALTER USER” privilege. We have created roles to control various privileges to the Application Express users and then granted the “ALTER USER” privilege to the role, the role is then assigned to the Application Express End Users.
• Create a PLSQL procedure that performs the password validation & changing passwords for the End users.
  The PLSQL procedure is defined as below –

CREATE OR REPLACE PACKAGE BODY BCFHPTL.APEX_UTILS_PKG
AS
PROCEDURE CHANGE_DB_PASSWORD_APEX (in_user IN VARCHAR2,
in_cur_pwd IN VARCHAR2,
in_new_pwd IN VARCHAR2)
AS
passwords_mismatch exception;
l_proc_name VARCHAR2 (200)
:= 'APEX_UTILS_PKG.CHANGE_DB_PASSWORD_APEX' ;
l_cur_db_pwd VARCHAR2 (200) DEFAULT NULL ;
l_new_db_pwd VARCHAR2 (200) DEFAULT NULL ;

BEGIN
--dbms_output.put_line ('1 'in_user'-'in_new_pwd'-'in_cur_pwd);
IF (in_user IS NOT NULL AND in_cur_pwd IS NOT NULL AND in_new_pwd IS NOT NULL )
THEN
-- dbms_output.put_line ('2 'in_user'-'in_new_pwd'-'in_cur_pwd);
-- Get the current password value from database
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_cur_db_pwd
USING in_user;
--dbms_output.put_line('Current db password is 'l_cur_db_pwd);
-- Change password as specified by user for "Existing Password"
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_cur_pwd
' ACCOUNT UNLOCK ';

-- Retrieve the password value for Existing password
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_new_db_pwd
USING in_user;
--dbms_output.put_line('Old password specified value is 'l_new_db_pwd);
IF l_cur_db_pwd != l_new_db_pwd
THEN
RAISE passwords_mismatch;
ELSE
-- dbms_output.put_line (in_user'-'in_new_pwd);
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_new_pwd
' ACCOUNT UNLOCK ';
END IF;
ELSE
RAISE_APPLICATION_ERROR (
-20001,
'Error in ' l_proc_name ' - ' 'Username, Old Password, New Password and Confirm Password Must be Present'
);
END IF;
EXCEPTION
WHEN passwords_mismatch then
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY VALUES '''
l_cur_db_pwd
''' ACCOUNT UNLOCK ';
RAISE_APPLICATION_ERROR (
-20005,
'Error in ' l_proc_name 'Old Password specified does not match the value in Database'
);
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (
-20006,
'Error in ' l_proc_name SUBSTR (SQLERRM, 1, 132)
);
END CHANGE_DB_PASSWORD_APEX;
END APEX_UTILS_PKG;
/

STAGE 2 - Oracle Application Exporess
Create a global application, which accepts the username, current password ( which will be a default password from DBA’s) , new password , confirm new password. This application then executes a PL/SQL block to change the password.
First created an application which looks like this –

Create a new application with just a login page in it.
Inside the application, the following are defined in the Login Page.
ITEMS

• P101_USERNAME - capture UserName


• P101_X_OLD_PWD - capture Old Password declared as “Password”


• P101_X_NEW_PWD - capture New Password declared as “Password”


• P101_X_CNF_PWD - Capture Confirm New Password.

BUTTONS
Two buttons are added.

• CANCEL - Close this application and redirects to the application from which the “Change Password” application has been called from.


• SUBMIT - Submits the application and alters the User Password.

The details are as shown below.


VALIDATIONS
A validation process has been created to verify whether the values specified in the fields P101_X_NEW_PWD and P101_X_CNF_PWD are same, if not throws an error message.
The PLSQL block contains the following code to validate the inputs by user-

BEGIN
IF :p101_x_new_password = :p101_x_confirm_password
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;If the above routine throws an error then the following message will be displayed –Both New and confirm Password fields must match. The details of the validation process are as below –



PROCESSES
An “after Submit” process has been created which calls a database procedure to change the passwords.
This process has the following PLSQL block –

The implementation of the validation process is as shown below -

TCL Script to Deploy OWB Mappings from file

This is another TCL script, which enables the developers to deploy OWB Mappings. The main function of this script is to deploy the mappings, with the list of the mappings to be deployed specified in a file.

This has been tested for OWB11g version.

puts " ******************* READ ME ********************"
puts ""
puts ""
puts " The proc owb_deploy_map_4m_file is Created"
puts " To execute the proc call the above proc name at OMB+ command prompt "
puts " DISCONNECT FROM THE DESIGN REPOSITORY BEFORE CALLING THE PROC"
puts ""
puts ""
puts " IF THIS IS THE FIRST TIME THE OBJECTS ARE BEING DEPLOYED"
puts " INTO THIS RUNTIME ENVIRONMENT"
puts " !!!!!!!!MAKE SURE THE LOCATIONS ARE REGISTERED!!!!!!!"
puts ""
puts ""
puts "**************************************************************"
puts ""
puts " This procedure reads the mappings from the file"
puts " Name & location of the file : C:/tmp/mappings_2_deploy.txt "
puts " "
puts "**************************************************************"
puts ""
puts ""
puts "Usage:"
puts "=============================================================="
puts ""
puts " owb_deploy_map_4m_file owbowner Password host port service ModuleName"
puts "=============================================================="
puts ""
puts ""
puts "**************************************************************"
#
proc owb_deploy_map_4m_file { desuser despwd host port srvc module } {
set fname [ open "c:/tmp/deploy_maps_4m_file.log" w]
puts $fname "----------------------------------------------------------"
puts $fname "Connecting to the Design Repository "
puts $fname "----------------------------------------------------------"
OMBCONNECT $desuser/$despwd@$host:$port:$srvc USE WORKSPACE 'ZZZZZ'
set projList [ OMBLIST PROJECTS '*']
foreach projName $projList {
OMBCC '$projName'
set projcon [OMBDCC]
puts $fname "current project context is $projcon"
puts $fname "CONNECTING TO RUNTIME REPOSITORY"
puts $fname "----------------------------------------------------------"
OMBCONNECT CONTROL_CENTER
#
set ModList [OMBLIST ORACLE_MODULES '$module' ]
set i 1
#
foreach ModName $ModList {
puts $fname " Working on : $ModName"
puts "$ModName"
OMBCC '$ModName'
set curcon [OMBDCC]
set mapList [OMBLIST MAPPINGS]
set j 1
#
#foreach mapName $mapList
set fhandle [open "C:/tmp/mappings_2_deploy.txt" r]
puts $fhandle
while {-1 != [gets $fhandle line]} {
set mapName $line
puts "Deploying ..........$mapName"
puts $fname " Creating Delpoyment Action Plans for :$mapName"
#
# CREATE THE DEPLOYMENT ACTIONS PLAN FOR EACH MAPPING
#

OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName' ADD ACTION '$mapName.CREATE' SET PROPERTIES(OPERATION) VALUES ('REPLACE') SET REFERENCE MAPPING '$mapName'
puts $fname " Executing the Deployment Action for MAPPINGS"
#
# DEPLOY MAPPINGS
#

OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBCOMMIT
#
# DROP THE DEPLOYMENT ACTIONS PLANS
#

OMBDROP DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBCOMMIT
incr j
}
close $fhandle
OMBCC '..'
set curcon [OMBDCC]
puts $fname "context at the end is --- $curcon"
incr i

}
OMBCOMMIT
OMBCC '/'

}
close $fname
OMBCOMMIT
OMBDISC
#
#**********************************************************************************************
# END OF PROC owb_deploy_map
#**********************************************************************************************
}