Software Testing

Delete Blank Columns From The Excel

2022-06-18T10:03:00.002+05:30

Following VBA Code will delete blank columns by ignoring 1st row (i.e header) in the excel

Steps to follow

1. Open New Excel Workbook

2. Press Alt+F11

3 Click Insert-->Modue

4. Copy and paste the below code in the Blank module inserted from above step 3

5. Click on the Save button or press Ctrl+S

6. Save the excel workbook in ".xlsm" OR "Macro-Enabled Workbook" format.

How to Use this Macro-Enabled WorkBook

1. Open the Macro-Enabled Workbook (eg: "delete_column.xlsm")

2. Copy-paste the data (including header) which contains blank columns.

Note: If your data doesn't have a header row then replace the following line of code

If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) =0 Then

3. Navigate to View in the Menu Bar

4. Select drop down from the Macros Icon--> View Macros

5. Ensure macro name "DeleteROPBlanks" is selected and macros in selected as (eg: "delete_column.xlsm")

6. Hit Run button

7. You can observe the blank columns are deleted.

Sub DeleteROPBlanks()

' DeleteROPBlanks Macro

Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then

Set Rng = Selection

Else

Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))

End If

ColCnt = 0

For Col = Rng.Columns.Count To 2 Step -1

If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) < 2 Then

Rng.Columns(Col).EntireColumn.Delete

ColCnt = ColCnt + 1

End If

Next Col

Exits:

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

Steps to Deploy the OFSAA application in WEBLOGIC server:

2019-11-20T16:55:00.001+05:30

Steps to Deploy the OFSAA application in WEBLOGIC server:
=========================================================

1) Generate the "context_name.ear" from the FIC_WEB area by executing the ./ant.sh
2) Create a folder by the name "applications" within the domain directory
3) Create a folder by the name "context_name.ear" within the newly created "applications" folder
4) Copy the context_name.ear file from the FIC_WEB folder to the newly create the "context_name.ear" folder created within the applciations folder
5) Explode the .ear file copied from the FIC_WEB area (jar -xvf context_name.ear), There are 2 files and 1 directory created after the .ear file is exploded as mentioned below
 a) context_name.war ---> file
 b) context_name.ear ---> file
 c) META_INF      ---> folder
6) Delete the above files (.ear and .war files) 
7) Create a new folder by the name "context_name.war"
8) Copy the context_name.war file from the FIC_WEB area into the newly created "context_name.war" folder
9) Explode the context_name.war file (jar -xvf context_name.war)
10) Folder and files which are same as in the FIC_WEB area are created on exploding

log into the weblogic console
=============================

1) create data sources for atomic and config schemas
2) Make sure the REVELEUS server is up before the deployment is started in the WEBLOGIC
3) Before deploying, increase the memory for the webserver. Navigate to domain/bin and modify the below mentioned properties in the file setDomainEnv.sh 
  WLS_MEM_ARGS_64BIT="-Xms512m -Xmx512m"
  export WLS_MEM_ARGS_64BIT

  WLS_MEM_ARGS_32BIT="-Xms512m -Xmx512m"
  export WLS_MEM_ARGS_32BIT 
4) From the Deployments int he console, deploy the context_name.ear
5) once the deployment is complete, restart both APP and WEB servers and login to OFSAA

Steps for OFSAA ALM cash flow generation

2019-10-23T13:09:00.002+05:30

Please find the steps below for the OFSAA ALM cashflow generation.

--------------------------------------------------------------------------------------------------------------
0. OBJECT REGISTRATION
1. Create Product Hierarchy in front end.--- DIM_PRODUCTS_B
2. Create COA hierarchy in the front end. -- dim_common_coa_b
3. Map product members to COAs
4. Create one Legal Entity in the Front end.
5. Execute "Reverse Population" -- DT name : REVERSE_POPULATION
6. Create one Active Time bucket.
7. Populate DIM_DATE : Execute function "" in the back end with start date and end date.
8. Populate FSI_D_LOAN contracts
9. Create one product characteristic in ALM
10. Create one ALM Static Deterministic process for Loan data.
11. Execute from ALM Process.
12. Use the below queries for cashflow data.

select /* + PARALLEL(16)*/
a.as_of_date,
a.account_number,
a.amrt_type_cd,
a.int_type,
a.origination_date,
a.last_reprice_date,
a.next_reprice_date,
a.maturity_date,
a.adjustable_type_cd,
a.reprice_freq,
a.reprice_freq_mult,
a.org_term,
a.org_term_mult,
a.amrt_term,
a.amrt_term_mult,
a.last_payment_date,
a.next_payment_date,
a.pmt_freq,
a.pmt_freq_mult,
a.remain_no_pmts_c,
a.cur_par_bal,
a.after_payment_balance,
a.cur_net_rate,
a.cur_payment,
a.org_payment_amt
from fsi_d_loan_contracts a
where a.as_of_date = '29-JAN-2016';

cash flow query

select /* + PARALLEL(16)*/
id_number, tl.financial_elem_name, a.*
from fsi_o_process_cash_flows a, dim_financial_elements_tl tl
where a.financial_elem_id = tl.financial_elem_id;

select
AMRT_TERM_MULT,
ORG_TERM_MULT,
PMT_FREQ_MULT,
ISO_CURRENCY_CD,
ACCOUNT_NUMBER,
ORG_TERM,
AMRT_TERM,
AMRT_TYPE_CD,
--LAST_TRANSACTION_DATE,
ISSUE_DATE,
NEXT_PAYMENT_DATE,
MATURITY_DATE,
AS_OF_DATE,
ID_NUMBER,
PRODUCT_ID,
IDENTITY_CODE,
CUR_PAR_BAL,
CUR_BOOK_BAL,
LEGAL_ENTITY_ID,
BEHAVIOUR_TYPE_CD,
PMT_FREQ,
ORG_BOOK_BAL,
NET_MARGIN_CD,

ADJUSTABLE_TYPE_CD,

--RESIDUAL_AMT_OF_GUARANTEE,
COMMON_COA_ID,
ORG_UNIT_ID,
GL_ACCOUNT_ID,
CUSTOMER_ID
from fsi_d_ledger_stat_instrument where as_of_date = '30-Sep-2016';

----------------Functional Currency ----------------
select * from FSI_DB_INFO;
update FSI_DB_INFO set functional_currency_cd = 'INR';

2018-12-23T23:58:00.001+05:30

1) Why are we able to see all other schemas along with the schema which we provided in the connection pool settings (odbc / OCI )

A: We will be able to see all other schemas along with the schema credentials which we provide, only when provided schema has a read / write permissions to the schemas available in that instance.

2) How will RPD get the number of records count even when we open RPD in offline and all the services are down.

A: Through ODBC / OCI

3) Is Between operator supported by OBIEE when you create joins in RPD ?

A: No

4) We have only 1 connection pool with "SCOTT" user but want to import tables from "HR" schema and view data. (Assuming SCOTT has read only permission to HR schema).

A: We need to select the check box in connection pool settings "Require Fully Qualified Table Names"

5) Can you delete a parent table if you have created an alias table

A: No, Alias table has to be deleted first before deleting parent table

6) Can you delete a column from parent table if you have created an alias column

A: Yes.

7) Can you delete a column from Alias table

A: No

8) Can you delete an Alias table ?

A: Yes

9) Advantages of Alias Tables

A:
i) Alias can be given to assign business names (Better Readability for Business Users)
ii) Cannot Edit / Delete a column in the alias table
iii) Create a column / any changes in physical table will dynamically appear in the alias table.
iv) Self - Join is not supported in RPD, to accomplish this requirement we need to use Alias.
v) Circular Joins are not supported by OBIEE

Count the actual months and days between two dates

2018-12-23T23:56:00.001+05:30

select trunc(months_between(date2,date1)) months,

date2 - add_months(date1,trunc(months_between(date2,date1))) days

from (select to_date('25-Aug-2013','dd-Mon-yyyy') date1,to_date('23-Oct-2013','dd-Mon-yyyy') date2

from dual

)

MONTHS	DAYS
1	28

Kiran UCMAS Abacus 2017 April

2017-06-17T22:54:00.001+05:30

Search with file extension in Windows

2014-12-05T12:14:00.002+05:30

Search with file extension in Windows

Enter that from whatever directory you want to start.

FOR /R will do the rest for every single file extension (*.xls) that’s in the descendants of the launching directory.

for /r %d in (*.xls) do echo %d >> c:\temp\Output.txt

TimeSaver....!!!!

Oracle DB Login Issues between Windows and Linux

2013-02-19T17:51:00.000+05:30

Issue: Unable to connect to Oracle DB in OEL (Linux) from windows

1) Go to $cd $ORACLE_HOME/dbs
2) $mv /<ORACLE_HOME>/product/10.2.0/server/dbs/orapwtestdb /<ORACLE_HOME>/product/10.2.0/server/dbs/orapwtestdb_bkp

3) $orapwd file=orapw$ORACLE_SID password=<enter your password> force=y entries=10

4) $ls -ltr orapw*

5) $sqlplus / as sysdba
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
... Now trying logging into database from windows...

Errors while bringing up Oracle database in OEL (Linux)

2013-02-19T17:23:00.001+05:30

Procedure followed to bring up the Oracle DB:

1) Login with the su - oracle user
2) sqlplus / as sysdba
3) Check the lsnrctl status if it is not up start by using lsnrctl start

4) Execute "startup" command after login to sqlplus / as sysdba
5) If you encounter with this error:
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

Check if ORACLE_HOME_LISTNER path is set in the .profile
Execute the .profile
Execute tnsping <ORASID>

6) Login sqlplus / as sysdba
7) Execute "startup"

Encountered with following error:

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora'

8) Login with sqlplus sys/Password as sysdba
9) Check for the existence of spfile under this path "/home/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora"
10) Execute " create pfile from spfile='/home/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora' "
11) Once hc_<ORASID>.dat and init<ORASID>.ora files are created
12) Login sqlplus / as sysdba
13) Execute "startup"

Normalisation Definitions

2013-01-22T04:22:00.001+05:30

Summary of Normalization

1st Normal form removes repitition by creating one-to-many relationships

2nd Normal form not creates one-to-many relationships but many-to-one relationships, thus effectively seperating static from dynamic information. 2nd Normal form removes items from tables independent of primary key.

3rd Normal form is used to resolve many-to-many relationships into unique values 3rd NF allows uniqueness of information by creation of additional many-to-many join resolution tables

OBIEE Interview Questions

2012-12-29T04:38:00.002+05:30

1. Query for top 5 emps from 10 dept tables

2. Purpose of Alias and duplicate tables, how do use them, explain them with a scenario

3. What is Usage Tracking and how do we implement that

4. Implicit Fact Tables

5. You have 4 Dimensions how will you create a Pivot Table View

6. Pre-requisites for OBIEE Installation

7. How do you implement Snowflake Schema in your RPD

8. What is the toughest issue which you face during your Answers development ? ANS: While preparing a simple report with 2 dims & 1 Fct, data is not populated in a column, reason is there is a (chr32) in one of the dimension values and we have used the same as filter in the report

9. How do you improve performance of OBIEE

10. A user should not able to view a particular table / column while preparing answers

11. In a snow flake schema a Dimension Table D1 is mapped to D2 and inturn Dimension D2 is mapped to Fact F1, in this case how can you show your report with D1 & F1 data

12. How & Why did you implemented Aggregate tables

13. Have you used Aggregate Pesistance wizard, why & how ?

14. How do you improve performance during you ETL process ? How do you debug when there is performance issue.

15. Level Based Measures

16. Difference between Physical Aggregate table which we create manually in DB & Aggregate table created through Aggregate Persistance Wizard

17. Can you develop a RPD without any Hierarchy ?

2012-12-29T04:20:00.000+05:30

1) How do you debug COREDUMP, in weblogic
2) How do you identify duplicate values in a table
3) Explain Reveleus Services, Architecture, Modules and functionality
4) How do you apply patches on Reveleus ?
5) What are the commands you use in UNIX while installing Reveleus
6) What are the Oracle SQL functions do you use daily
7) If you find an error on WEBLOGIC, whether you can trace the same from REVELEUS logs ?
8) How do you identify the root cause for core dump and how do you debug, what are the commands
9) PL/SQL & SQL related questions
10) UNIX Commands
11) Clustering concepts in WEBLOGIC
12)

Multiple RPD's & Multiple Presentation Services in a Single OBIEE 11G & Weblogic Installation

2012-05-02T17:36:00.000+05:30

Purpose:

To host Multiple RPD's & Multiple Presentation Services in a Single OBIEE & Weblogic Installation

Following steps can be executed even on OBIEE 11.1.1.6.0

Step 1:

Trigger rcu.bat and create required schemas, trigger RCU from the following location

D:\obiee116\V29675-01\rcuHome\BIN

Provide these schema names while executing Step 3

Step 2:

All the OBIEE servers should be up and running

Step 3:
Trigger config.bat from the path below

D:\obiee116\Oracle_BI1\bin\config.bat

Post configuration you can see these new folders in your OBIEE installed folders

Please feel free to post your comments on queries related to Multiple RPD & Presentation service deployments

Next set of Interview Q & A

2011-12-09T12:02:00.001+05:30

1) How do you handle teams emotions.
2) How do you avoid leakages when there is lot me context switching.
3) Automation approach.
4) How do you manage when there is more work compared to your came width.
5) Conflict management.

Procedure to disable all user constraints on Oracle

2011-12-07T08:43:00.001+05:30

BEGIN

FOR c IN

(SELECT c.owner, c.table_name, c.constraint_name

FROM user_constraints c, user_tables t

WHERE c.table_name = t.table_name

AND c.status = 'ENABLED'

ORDER BY c.constraint_type DESC)

LOOP

dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);

END LOOP;

END;

2011-12-04T22:32:00.001+05:30

1) How do you decide the defect prone area ?
2) What do you do when you have resources with low competency ?
3) Example of risk and how to mitigate it ?
4) What do you do to increase teams productivity ?
5) What is effort estimation process in your company ?

EXP utility on 11gR2 will not work on empty tables with zero rows

2011-11-24T11:44:00.002+05:30

1)Identify tables with 0 rows
2)Issue following command on those tables
ALTER TABLE <<%empty_table%>> ALLOCATE EXTENT;
3) Now EXP utility will work

Identify empty tables in oracle

2011-11-24T11:43:00.000+05:30

SELECT * FROM User_Tables s WHERE s.num_rows=0

To find PORT availability on unix systems

2011-11-22T12:08:00.002+05:30

To find PORT availability on unix systems
netstat -an | grep <<%PORTNUMBER%>>

Command to set 'NLS_LENGTH_SEMANTICS' after Installation & Creating 11gr2 db

2011-10-27T19:03:00.000+05:30

update PROPS$ SET VALUE$='CHAR' where NAME='NLS_LENGTH_SEMANTICS';

Find A File Command

2011-09-20T11:04:00.000+05:30

Find all perl (*.pl) files in current directory:
> find . -name '*.pl'

Remove Control M characters from a file in UNIX

2011-08-04T08:27:00.003+05:30

Open file in putty and execute the following command

:%s,^VM,,g <------------ to remove ctrl m characters

To find Solaris version

2011-08-03T17:37:00.000+05:30

1) cat /etc/*release
2) uname -a

To Verify ML versions

2011-08-03T17:36:00.000+05:30

Following commands will give the ML versions in AIX

1) instfix -i | grep ML
2) oslevel -r

Find command in UNIX

2011-08-03T17:27:00.002+05:30

Find text in the current folder and redirect output to OP1.txt

find . -name "*" -exec grep "FIND THIS TEXT" {} \;>> OP1.txt

Find text in the current folder and print the output on screen

grep "FIND THIS TEXT" `find .` |cut -d":" -f1 | grep -v properties | sort -u

find the text and print the file name along with the path on screen

find xxxxxPATH_TO_SEARCHxxxxxx -type f -exec grep -l 'FIND THIS TEXT' {} \;