Oracle Bi Testing Strategy

The primary focus of DW/BI testing is to ensure competent and perfect database structures, ETL processes, front-end access and that BI reports generation processes completely support the client requirements.

What is OBIEE?
Oracle Business Intelligence Enterprise Edition (OBIEE) is a comprehensive business intelligence platform that delivers a full range of analytic and reporting capabilities. Designed for scalability, reliability, and performance, Oracle Business Intelligence Enterprise Edition delivers contextual, relevant and actionable insight to everyone in an organization, resulting in improved decision-making, better-informed actions, and more efficient business processes. In our project, we are doing OBIEE Report testing:

• Understanding Sub Queries
• Understanding Table Manipulation
• XML in SQL Server

Prerequisites:

Before go through the below documents we must have basic knowledge of OBIEE and its components
Like …
Oracle Bi Server
Oracle Business Intelligence Dashboards
Oracle Business Intelligence Answers/Analysis
Oracle Business Intelligence Delivers
Oracle Business Intelligence Briefing books
Oracle Business intelligence Publisher

1. Sample report and Navigation steps

• Oracle Interactive Dashboard page is displayed as designed for project
• Click on Customer Subject area (Note : Generally set of reports may or may not  come under specific subject area)

2. Checking the Source record count and Target record count

• Checking the source record count and Target record count for matching
• While writing the queries we can validate the record count

Once the Report gets executed we need to validate query in database by finding the matching record count.
3. Checking the Naming Conventions: During the Project setup and later during maintenance these conventions can help for communication about several possible entities: ETL, DWH and Repository. Using naming convention is only effective if they are used by everyone at the same level of details:

• Fact Table (W_xxx_F)
• Dimension Table (W_xxx_F)
• Dimension Hierarchy Table(W_XXX_DH)
• Aggregated Fact Table (w_xxx_AG

4. Authentication: Is the process used to verify the identity of a user who tries to access the system. This is implemented by BI Server using either the Internal Authentication or External Methods

Authorization: Is the process used to verify that a user has been granted sufficient privileges to perform the requested action on the special object.

5. Report Data Validation: In BI testing world, generally we call it as End to End testing hence we need to perform report data validation against data warehouse(DB) and source system data (i.e. Test data which are created specific to report from Business team or Front office team)
Whenever the report is opened, one session log will be generated with Query which is used to identify the physical data source to which Oracle BI Server is connecting. Use this SQL to analyze the tables and fields while validation the data of the report.

6. Steps to follow to navigate to the Session log Query:

Step 1: Click Settings > Administration to open the Oracle BI Presentation Services Administration   window
Step 2: In the Session Management window, under Cursor Cache, click the View Log link for the last entry

7. Source to target validation : Here source is represented by metadata repository and Target represents OBIEE Reports & Dashboards, BI Publishers.

8. Presentation Layer Object Validation: This is the layer of the logical business model that is accessible for the client through the structure query Language better known as the logical SQL. The presentation is the appropriate layer to set user permissions and to validate user permissions to reports.

9. Categorizing the metrics: It is important to classify the metrics from multiple perspectives such as, their frequency of use, potential performance impacts, and complexity of calculations involved.  Such a classification helps drive priority of testing.

10. Dashboard charts and filters criteria: User interface testing should encompass tests with multiple options in the available filter criteria.  OBIEE gives enough drilldown features to verify the underlying data on the clickable components of the charts.  Test cases written should be detailed enough to verify data aggregated at various layers.

11. Filter Validation

• Validate the entire filters which are available on report. Example refer below report and its filter
• Example: For Performance Measure filter- Validate filter contents against report requirement and database

Filter types:

Local filters:  Filtering the records in the report level.
Global filters: Filtering the records based on user selection in Dashboard.

Dashboard Validation: When a user selects certain request that need to display the exact results in the dashboard.

12. Data level security: Data level security validation means user will be able to see only particular data for the given permission

Example: Both the Eastern and Western region Sales Managers will be seeing the same reports but the Data visible to them in the reports will be Eastern and Western region Sales data respectively.

Object Level security: Need to validate whether the particular user is able to access the particular dashboard or folder etc.

Example: For example, users in a particular department can view only the subject areas that belong to their department.

13. Bursting the reports: Bursting the reports means distributing the reports based on the regions. Eg: if there are 4 regional reports, validate to burst the reports (based on East, West, South, North regions).

14. Buzz Matrix validation: Need to validate the alerts in the Dashboard.

Example: We are running stock market and CEO is very much interested to know today’s business weather, has it reached a certain level that which he expects compared to the last week. If the level has reached to a certain level in Dashboard Buzz (Alert), it should raise an alert saying that it has reached the level in such a way the buzz matrix validates.

15. Testing in Levels: In a typical OBIEE project, it is advisable to test in multiple areas rather than attempting to test everything at once.

a) The first set of tests can verify the accuracy of the column to column transport of the data between the source and target.  This verification is typically done using SQL statements on the source and target databases.

b) The next step is to verify the accuracy of the repository (the .RPD file.) These tests will include testing with appropriate dimensional filters on the metrics and the formula used to compute those metrics.  Testers can build two sets of comparable queries within the repository interface.

c) The next step in testing will be to verify the dashboard / reports against comparable queries on repository metrics.  In these tests, testers verify dashboard charts / reports against corresponding results from queries they execute on metrics of the repository.

d) Finally, the functional interface tests will cover tests to verify the lookups, performance, ease of use, look and feel etc.

The first three types of tests are performed by testers who can create simple SQL statements.
Structure and organization of test cases – the choices on test cases naming convention and structure can help organize the test artifacts better and aid a great deal in implementing the overall testing strategy.
For example: if the test cases are grouped based on the nature of the tests, like,  source to target verification, RPD metrics tests, functional, security, performance and usability, it would be easier to pick and choose the tests based on the testing context and tester capabilities.

16. User acceptance criteria: Users typically have an existing legacy mechanism to verify if what is displayed in the new solution makes sense.  Testers should dig into this and understand how the end users built the project acceptance criteria.  Testers should challenge the assumptions made by the business community in deriving the acceptance criteria.  This activity helps get an end user perspective built into the testing efforts from early on.

17. Validating Master Detail Report: Master Details linking of views allows you to establish a relationship between two or more views such that one view, called the master view, will drive data changes in one or more other views, called detail views.

18. Time series functions validation: Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods.
Time series functions enable comparisons between current sales and sales a year ago, a month ago, and so on.

a. Ago: With ago function  we can compare period to period
b. To date: Time series functions enable comparisons between current sales and sales a year ago, a month ago, and so on.
c. Period rolling: The PERIODROLLING function does not have a time series grain; instead, you specify a start and end period in the function.

19. Oracle bi-publisher validation: Oracle BI Publisher known as XML Publisher offers efficient scalable reporting solution available for complex, distributed environments. It provides a central architecture for generation and delivering information to employees’, customer and business partners both security and in the right format.

Thus this Document gives an overview of OBIEE Testing and commonly used in BI Components while doing validation.

Thanks For Reading Our Blogs. Please Know More About:: Oracle BI Testing

Strategies For Testing Data Warehouse Applications

Introduction:

There is an exponentially increasing cost associated with finding software defects later in the development lifecycle. In data warehousing, this is compounded because of the additional business costs of using incorrect data to make critical business decisions. Given the importance of early detection of software defects, let’s first review some general goals of testing an ETL application:

Below content describes the various common strategies used to test the Data warehouse system:
Data completeness: 

Ensures that all expected data is loaded in to target table.

1. Compare records counts between source and target..check for any rejected records.
2. Check Data should not be truncated in the column of target table.
3. Check unique values has to load in to the target. No duplicate records should be existing.
4. Check boundary value analysis (ex: only >=2008 year data has to load into the target)

Data Quality:

1.Number check: if in the source format of numbering the columns are as xx_30 but if the target is only 30 then it has to load not pre_fix(xx_) .. we need to validate.

2.  Date Check: They have to follow Date format and it should be same across all the records. Standard format : yyyy-mm-dd etc..

3. Precision Check: Precision value should display as expected in the target table.

Example: In source 19.123456 but in the target it should display as 19.123 or round of 20.

4.  Data Check: Based on business logic, few record which does not meet certain criteria should be filtered out.
Example: only record whose date_sid >=2008 and GLAccount != ‘CM001’ should only load in the
target table.

5. Null Check: Few columns should display “Null” based on business requirement
Example: Termination Date column should display null unless & until if his “Active status”
Column is “T” or “Deceased”.

Note: Data cleanness will be decided during design phase only.

Data cleanness:

Unnecessary columns should be deleted before loading into the staging area.

1.  Example: If a column have name but it is taking extra space , we have to “trim” space so before loading in the staging area with the help of expression transformation space will be trimed.

2. Example: Suppose telephone number and STD code in different columns and requirement says it should be in one column then with the help of expression transformation we will concatenate the values in one column.

Data Transformation: All the business logic implemented by using ETL-Transformation should reflect.

Integration testing:

Ensures that the ETL process functions well with other upstream and downstream processes.

Example:
1.  Downstream:Suppose if you are changing precision in one of the transformation “column”, let us assume a “EMPNO” is column having data type with size 16, this data type precision should be same for all transformation where ever this “EMPNO” column is used.

2.  Upstream: If the source is SAP/ BW and we are extracting data there will be ABAP code which will act as interface between SAP/ BW and map where there source is SAP /BW and to modify existing mapping we have to re-generate the ABAP code in the ETL tool (informatica)., if we don’t do it, wrong data will be extracted since ABAP code is not updated.

User-acceptance testing:

Ensures the solution meets users’ current expectations and anticipates their future expectations.
Example: Make sure none of the code should be hardcoded.

Regression testing:

Ensures existing functionality remains intact each time a new release of code is completed.

Conclusion:

Taking these considerations into account during the design and testing portions of building a data warehouse will ensure that a quality product is produced and prevent costly mistakes from being discovered in production.

Read More About:  Data Warehouse Applications

Automation Tool Selection Recommendation

• Overview
• Information Gathering
• Tools and Vendors
• Evaluation Criteria
• Tools Evaluation
• Matrix
• Conclusion
• Overview

“Automated Testing” means automating the manual testing process currently in use. This requires that a formalized “manual testing process” currently exists in the company or organization. Minimally, such a process includes:

–        Detailed test cases, including predictable “expected results”, which have been developed from Business Functional Specifications and Design documentation.

–        A standalone Test Environment, including a Test Database that is restorable to a known constant, such that the test cases are able to be repeated each time there are modifications made to the application.

Information Gathering

Following are sample questions asked to tester who have been using some the testing tools:

How long have you been using this tool and are you basically happy with it?

How many copies/licenses do you have and what hardware and software platforms are you using?

How did you evaluate and decide on this tool and which other tools did you consider before purchasing this tool?

How does the tool perform and are there any bottlenecks?

What is your impression of the vendor (commercial professionalism, on-going level of support, documentation and training)?

Tools and Vendors

• Robot – Rational Software
• WinRunner 7 – Mercury
• QA Run 4.7 – Compuware
• Visual Test – Rational Software
• Silk Test – Segue
• QA Wizard – Seapine Software

Tools Overview

Robot – Rational Software

–        IBM Rational Robot v2003 automates regression, functional and configuration testing for e-commerce, client/server and ERP Applications. It’s used to test applications constructed in a wide variety of IDEs and languages, and ships with IBM Rational TestManager. Rational TestManager provides desktop management of all testing activities for all types of testing.

WinRunner 7 – Mercury

–        Mercury WinRunner is a powerful tool for enterprise wide functional and regression testing.

–        WinRunner captures, verifies, and replays user interactions automatically to identify defects and ensure that business processes work flawlessly upon deployment and remain reliable.

–        WinRunner allows you to reduce testing time by automating repetitive tasks and optimize testing efforts by covering diverse environments with a single testing tool.

QA Run 4.7 – Compuware

–        With QA Run, programmers get the automation capabilities they need to quickly and productively create and execute test scripts, verify tests and analyze test results.

–        Uses an object-oriented approach to automate test script generation, which can significantly increase the accuracy of testing in the time you have available.

Visual Test 6.5 – Rational Software

–        Based on the BASIC language and used to simulate user actions on a User Interface.

–        Is a powerful language providing support for pointers, remote procedure calls, working with advanced data types such as linked lists, open-ended hash tables, callback functions, and much more.

–        Is a host of utilities for querying an application to determine how to access it with Visual Test, screen capture/comparison, script executor, and scenario recorder.

Silk Test – Segue

–        Is an automated tool for testing the functionality of enterprise applications in any environment.

–        Designed for ease of use, Silk Test includes a host of productivity-boosting features that let both novice and expert users create functional tests quickly, execute them automatically and analyze results accurately.

–        In addition to validating the full functionality of an application prior to its initial release, users can easily evaluate the impact of new enhancements on existing functionality by simply reusing existing test casts.

QA Wizard – Seapine Software

–        Completely automates the functional regression testing of your applications and Web sites.

–        It’s an intelligent object-based solution that provides data-driven testing support for multiple data sources.

–        Uses scripting language that includes all of the features of a modern structured language, including flow control, subroutines, constants, conditionals, variables, assignment statements, functions, and more.

Evaluation Criteria

Record and Playback         Object Mapping
Web Testing Object              Identity Tool
Environment Support        Extensible Language
Cost                                            Integration
Ease of Use                             Image Testing
Database Tests                     Test/Error Recovery
Data Functions                    Object Tests
Support

3 = Basic  2 = Good  1 = Excellent

Tool Selection Recommendation

Tool evaluation and selection is a project in its own right.

It can take between 2 and 6 weeks. It will need team members, a budget, goals and timescales.
There will also be people issues i.e. “politics”.

Start by looking at your current situation
– Identify your problems
– Explore alternative solutions
– Realistic expectations from tool solutions
– Are you ready for tools?

Make a business case for the tool

–What are your current and future manual testing costs?
–What are initial and future automated testing costs?
–What return will you get on investment and when?

Identify candidate tools

– Identify constraints (economic, environmental, commercial, quality, political)
– Classify tool features into mandatory & desirable
– Evaluate features by asking questions to tool vendors
– Investigate tool experience by asking questions to other tool users Plan and schedule in-house demonstration by vendors
– Make the decision

Choose a test tool that best fits the testing requirements of your organization or company.

An “Automated Testing Handbook” is available from the Software Testing Institute (www.ondaweb.com/sti), which covers all of the major considerations involved in choosing the right test tool for your purposes.

Thanks For Reading This Blog. You Know More About: Automation Tool

Performance Counters And Their Values For Performance Analysis

Performance Counters:

Performance counters are used to monitor system components such as processors, memory, network and the I/O devices. Performance counters are organized and grouped into performance counter categories. For instance the processor category contains all counters related to the operation of the processor such as the processor time, idle time, interrupt time and henceforth.  If performance counters are used in the application, they can publish performance-related data to compare them against acceptable criteria.

The number of counter parameters to be considered by the load tester/designers greatly varies based on the type and size of the application to be tested. Some of the Performance Counters and their Threshold values for Hexaware Performance Analysis are as follows:

Memory Counters:

Memory: Available Mbytes –This describes the amount of physical RAM available to processes running on the system.

Threshold to watch for:

Available Mbytes consistent value of less than 20 to 25 percent of installed RAM is an indication of insufficient memory. Values below 100 MB may indicate memory pressure.

Note: This counter displays the last observed value only. It is not an average.

Memory – Pages /sec-Indicates the rate at which pages are read from or written to disk to resolve hard page faults.

Threshold to watch for:

Memory-Pages /sec higher than 5 indicates a possible bottleneck

Process: Private Bytes: _Total -Indicates the current allocation of memory that cannot be shared with other processes. This counter can be used to identify memory leaks in.NET applications

Process: Working Set: _Total - This is the amount of physical memory being used by all processes combined. If the value for this counter is significantly below the value for Process: Private Bytes: _Total, it indicates that processes are paging too heavily. A difference of more than 10% is probably significant.

Processor Counters:

% Processor Time_Total Instance - Percentage of elapsed time a CPU is busy executing a non idle thread (An indicator or processor activity).

Threshold to watch for:

Processor % Time of sustained at or over 85% may indicate that processor performance (for that load) is the limiting factor.

% Privilege Time-Percent of threads running in privileged mode (file or network I/O, or allocate memory)

Threshold to watch for:

Processor % Privilege Time consistently over 75 percent indicates a bottleneck.

Processor Queue Length - Number of tasks ready to run than the processors can get to.

Threshold to watch for:

Processor Queue Length greater than 2 indicates a bottleneck.

Note: High values many not necessarily be bad for % Processor Time. However, if the other processor-related counters are increasing linearly such as % Privileged Time or Processor Queue Length, high CPU utilization may be worth investigating.

• Less than 60% consumed = Healthy
• 51% – 90% consumed = Monitor or Caution
• 91% – 100% consumed = Critical or Out of Spec

System\Context Switches /sec. Occurs when higher priority threads preempts lower priority threads that are currently running, and can indicate when too many threads are competing for processor time. If much processor utilization is not seen and very low levels of context switching are seen, it could indicate that threads are blocked

Threshold to watch for:

As a general rule, context switching rates of less than 5,000 per second per processor are not worth worrying about. If context switching rates exceed 15,000 per second per processor, then there is a constraint.

Disk Counters:

Physical Disk (instance)\Disk Transfers/sec

To monitor disk activity, we can use this counter. When the measurement goes above 25 disk I/O’s per second then we got poor response time for the disk (which may well translate to a potential bottleneck. To further uncover the root cause we use the next mentioned counter.

Physical Disk (instance)\% Idle Time

This counter measures the percent time that the hard disk is idle during the measurement interval, and if we see this counter falling below 20% then we will likely get read/write requests queuing up for the disk which is unable to service these requests in a timely fashion. In this case it’s time to upgrade the hardware to use faster disks or scale out the application to better handle the load.

Avg. Disk sec/Transfer - The number of seconds it takes to complete one disk I/O.

Avg. Disk sec/Read - The average time, in seconds, of a read of data from the disk.

Avg. Disk sec/Write - The average time, in seconds, of a write of data to the disk.

Less than 10 ms	very good
Between 10 – 20 ms	okay
Between 20 – 50 ms	slow, needs attention
Greater than 50 ms	serious I/O bottleneck

Note:  These three counters in the above list should consistently have values of approximately .020 (20 ms) or lower and should never exceed.050 (50 ms).

Source: Microsoft

Network Counters:

Network Interface: Output Queue Length - This is the number of packets in queue waiting to be sent. A bottleneck needs to be resolved if there is a sustained average of more than two packets in a queue.

Threshold to watch for:

If greater than 3 for 15 minutes or more, NIC (Network Interface Card) is bottleneck.

Network Segment: %Network Utilization - % of network bandwidth in use on this segment.

Threshold to watch for:

For Ethernet networks, if value is consistently about 50%-70%, this segment is becoming a bottleneck.

Conclusion : These values may not exactly depict the threshold limits but provides a consideration to be valued upon for Performance Analysis.

Very Very Thanks For The Viewrs Of This Blog. Hope All Enjoy It. Know More  About

: Performance Counters And Their Values

LoadRunner Runtime Settings – Multithreading Options

Performance testers are confronted with this classic dilemma when they decide to execute their script in LoadRunner. Whether to run the Vuser as a thread or as a process?

1.1  Difference between a thread and a process 

A Process

• Let us consider a process as an independent entity or unit that has an exclusive virtual address space for itself.

• A process can interact with another process only through IPC (inter process communication). More than one process could run at any given time but no two processes can share the same memory address space.

E.g. when we open an application say notepad from our Windows OS, we see that a notepad.exe process is displayed in our task manager under processes tab. If we open another such notepad a new notepad.exe process is displayed. This process has its own set of virtual address space.

A Thread

• Threads are contained inside a process. More than one thread can exist within the same process and can share the memory space between them.

• The advantage here is that multiple threads can share the same memory space. I.e. when a thread is in idle state another thread can utilize the resource thereby faster execution rate is achieved.

• A memory space can be accessed by another thread if one thread remains idle for a long time.

• Threads can also access common data structures if required.

1.2  Multithreading

While defining the runtime settings in LoadRunner, we have to choose between running the Vuser as a thread or a process. “The Controller uses a driver program (such as mdrv.exe or r3Vuser.exe) to run your Vusers. If you run each Vuser as a process, then the same driver program is launched (and loaded) into the memory again and again for every instance of the Vuser.” – LoadRunner User Guide. The driver program mentioned is nothing but a process that runs when we generate a Vuser load.

1.3  Run Vuser as a process – Disable Multithreading

• If we choose the first option and run ‘n’ number of Vusers as a process, we will be able to see ‘n’ number of mmdrv.exe processes running in the Load generator machine. Each of this process would be consuming their own memory space.

• When this option is selected, each of the Vuser process establishes at least one connection with the web/app server.

1.4  Run Vuser as a thread – Enable Multithreading

• But we can choose to run the Vuser as a thread if we want to go easy on the resources. This way more number of Vusers can be generated with the same amount of available load generator memory.

• When this option is selected, each of the Vuser thread can share the open connections between them (connection pooling). Opening and maintaining a connection for each Vuser process, is resource consuming. In connection pooling, the amount of time a user must wait to establish a connection to the database is also reduced.This is surely an advantage right? Wrong. The argument is that this is not an accurate replication of the user load - A single connection for each Vuser should be created like in a real time scenario and to achieve this we have to run Vuser as a process. There are other factors such as thread safety to be considered. When we run a large amount of Vusers as a single multi threaded process, the Vusers run as threads which share the same memory location. Thus one thread may impact, interfere or modify data elements of another thread posing serious thread safety concerns. Before selecting either of the options we need to determine the load generator capacity such as available system resources, memory space and also the thread safety of the protocols used.

Please Visit At: LoadRunner Runtime Settings For Know More.