Quick Start User Guidev1.0 05.01.2014

www.drost.name

support@drost.name

0031-6-53967124

Table of Contents

Introduction.................................................................................................................................................................................................................................. 3

Login............................................................................................................................................................................................................................................. 4

Home............................................................................................................................................................................................................................................. 6

Import........................................................................................................................................................................................................................................... 7

New Compare............................................................................................................................................................................................................................ 22

Restart Compare........................................................................................................................................................................................................................ 35

Analysis....................................................................................................................................................................................................................................... 39

Processing................................................................................................................................................................................................................................... 46

Trash............................................................................................................................................................................................................................................ 48

Authorisation............................................................................................................................................................................................................................. 50

Administration........................................................................................................................................................................................................................... 56

SQL Browser............................................................................................................................................................................................................................... 60

Glossary...................................................................................................................................................................................................................................... 66

IntroductionThe DREAM solution automatizes regression (RT), non-regression testing (NRT) and test-output-control by using a data model based approach. Almost everywhere, where the basic principle of input-process-output (IPO) is applied, DREAM can be used in order to search for bugs, issues and incidents. Thereby, DREAM can handle the structures of relational databases (RDBMS) and more complex dimensional data warehouses (DWH) for Business Intelligende (BI). DREAM works dynamically and adapts itself to changing data structures. The growing demand for regression testing in agile methods like continuous integration (CI) gives DREAM a key role in high quality and fast software development processes. Thereby, DREAM protects your mission-critical data and functionality. In strategies for accelerating software testing, the DREAM is an important element.

LoginUsers can log in to DREAM via a web browser. The front-end application is a web based application which is deployed on an Oracle enterprise server or VM (Virtual Machine) in the cloud. The back-end application is deployed in an Oracle database and optionally an Unix server.

Log in with your user name and password (see Illustration 1) The password is case sensitive. Click the Login button in order to proceed.

Illustration 1

HomeThe Home tab is the initial tab where the application starts.

After the Login dialogue the user starts from the Home tab (see Illustration 2).

Illustration 2

ImportThe Import function is used in order to copy data that has to be compared in the DREAM system. For every DREAM application the system knows where the source data can be found. Normally, it can be found in an Oracle schema belonging to a test or development environment. DREAM cannot copy tables without privileges of the owner of the data.

Illustration 3

The first step is to choose the DREAM application for which data has to be imported (see Illustration 3). The application list box displays the application to which a user has been granted access by the DREAM administrator. After selecting the application, use the Check Application Status dialogue in order to control if the application is not locked or claimed by another user (see Authorisation).

Illustration 4

The module imports one or more tables from a source location such as a test or development environment. The set of tables to be imported is called DREAM baseline, which is actually, a snapshot of data from the output of a business application. Every baseline has to be identified by a release-nr, test cycle-nr and day-nr. This identification in Illustration 4 is an example that means that the to be imported baseline is the result of release 13’s first test cycle 1 on run day 1. The description is useful in order to remember special details later on. The list box at the bottom displays existing DREAM baselines. Click the Proceed button to go on with the next dialogue.

Illustration 5

The source table naming is often very technical and sometimes confusing. This dialogue (see Illustration 5) makes the mapping between the source naming and the destination naming. The destination naming starts with the DREAM application name and contains identifying parts such as the release-nr, cycle-nr and day-nr. The mapping between source table naming and destination table naming has to be bijective in order to assign every source table name exactly one destination table name and every destination table name exactly one source table name. Once the mapping has been chosen, it will be stored as meta information in the library and will reused and extended when importing future baselines.

On the left side are indicators which indicate integrity issues in de table naming mapping. The indicators are calculated when activating the Check Consistency dialogue. There are three kind of indicators which should be zero before the physical import can take place:

[IND no match YN] A flag means that the chosen destination name does not exists in the previous baseline

[IND unique YN] A glag means that the destination name is not unique

[IND too long YN] A flag means that the destination name is too long

In case of an initial baseline the Format Naming dialogue should be used in order to make up the naming. If a baseline already exists, the Reuse Naming From Library dialogue should be used in order to reuse naming conventions.

When importing a baseline for the first time, it is advisable to choose a standard naming for the destination tables in the DREAM system. The destination table naming should be self-explanatory regarding table content, should not be longer than 30 characters and should be unique within an DREAM application. In order to achieve a standardised destination naming the Format Naming dialogue can be used.

Illustration 6

The Format Naming dialogue supports users in order to make up the destination table naming from the source table naming by two basic cut operations. In the example (see Illustration 6) the destination table name will consist of two parts of the source table naming. The first part is the8th position and the second part is from position 12th . The second part of the string is optional.

Illustration 7

Once the formatting operation has been performed (see Illustration 7), the resulting destination table naming should be checked in order to findproblems as described above.

If a baseline already exists , it is most efficient in order to reuse the naming of the previous baseline from the library. This guarantees that there are no naming conflicts because the existing baseline table naming has been checked in previous sessions. Click the button to start the Reuse Naming From Library dialogue.

Illustration 8

First, the baseline has to be chosen from which the table naming has to be loaded (see Illustration 8). Although every baseline can be chosen, normally users choose the previous baseline.

Illustration 9

There are two ways in order to load table naming definitions from previous baseline (see Illustration 9). First, you can take over peer table naming which assigns every source table from the current baseline the corresponding destination table from the baseline in the library. Secondly, one can take all table naming in order to detect those tables that have been omitted in the current baseline. Accidentally forgotten import tables will thereby be detected.

Illustration 10

After finishing the Reuse Naming From Library dialogue, the loaded destination table naming has been loaded (see Illustration 10).

Illustration 11

The destination table naming can manually be adjusted. In Illustration 11, the naming of table D_CUSTOMER has been extended with an underscore and digit 2. Now, the Check Consistency dialogue, is activated in order to check the table naming integrity.

Illustration 12

The check of the table naming integrity of the current baseline is to be executed against a previous baseline, the so-called reference baseline (seeIllustration 12). Initially, when there is no baseline available, the naming integrity check is done merely on the current baseline.

Illustration 13

In Illustration 13 the no match indication is flagged which means that the chosen name is not in sync with the reference baseline. This should beadjusted in order to make the DREAM system work correctly.

Illustration 14

In Illustration 14, the check is successful. The table naming mapping can be saved by clicking the button Save Naming In Library. The import module is finalised by clicking the button Physical Table Import (step 2 of 2). The import of the tables may take some seconds to minutes.

Illustration 15

The tab Processing (see Illustration 15) shows the status of all processes. The import process consists of two processes which have been successful.

New CompareThe New Compare function is used in order to compare DREAM baselines and to find differences between pairs of tables. Differences between tables can occur deliberately or accidently. After changes, one often does expect differences between baselines. At the same time one does expect that other parts have been left unchanged. The New Compare function does realise this complex search process by generating under water thousands of lines of SQL source code. This approach requires no programming skills from the user but is based on basic data modelling knowledge.

The New Compare process starts with the selection of an application (see Illustration 16). Please check if the application is not claimed or lockedby another user. The Check Application dialogue is explained in the chapter Authorisation. Every compare should be given a description in order to explain the purpose of the action. The principle of comparing comprises the match of two DREAM baselines. First, users choose the so-called left baseline. The left baseline will be compared with a younger baseline, the so-called right baseline. After the selection of a left baseline, the DREAM application offers a list of younger DREAM baselines from which users chooses one.

Illustration 16

Below, one can look up the history of previous compares (see Illustration 17). Click the Proceed button the continue the New Compare dialogue.

Illustration 17

The dialogue continuous with the selection of tables that should be compared (see Illustration 18). Often users want to focus on specific tables, although a whole baseline can comprise from one to a few tens of tables. The order of processing is a means to get most important tables processed first.

Illustration 18

The next step is to elaborate the data model (see Illustration 19). This can be done from scratch or by reusing a data model from the library, so far as a DREAM baseline already exists. First, select a table and click Proceed With Elaboration Of Data Model button in order to look at the initial state of the data model of the two underlying baselines.

Illustration 19

Initially the data model is undefined (see Illustration 20). On the left side, the columns per table can be found. On the right side the indications column left IND and column right IND, which are flagged in the case where columns do only exist in the right or left table. These are differences in the data structure that should be acknowledged. As the left side table is the base for the compare, additional columns in the right

Illustration 20

table are ignored. However, if columns are omitted on the right side, the user has to accept the absence of those columns because this may imply the loss of data in a regression situation. In order to make the tables ready to be compared the following fields have to be set:

[Functional key] One of the columns that uniquely identifies a row in a table, dimension or fact

[Technical key] The surrogate key used for the identification of rows in dimensions

[foreign key] The foreign key of a fact that references the identifying surrogate key in a dimension

[Foreign skey table] Dimensions that the foreign key references

[Exclude column] Columns that have to be excluded from the compare; columns with a column left IND indication have to be excluded explicitly

[Non-standard tech skey] Indication for column containing dimensional surrogate key without having appropriate column naming

In order to load the data model definitions from an existing library please go back and click the button Reuse Constraints From Library (seeIllustration 19).

The data model definitions reference a left and a right baseline (see Illustration 21). The user can reuse the data model definition of every pair ofbaselines and its associated data model. Normally, one takes the most recent data model definition of the same pair of baselines or a previous pair of baselines.

Illustration 21

After loading the data model definition with the Reuse Data Model dialogue, the data model definition fields of the current pair of baselines have been populated (see Illustration 22). Now, go back and click the button Analyse Data Model against data (see Illustration 19) in order to start the process of analysing.

Illustration 22

The data, data structure and data definition is being analysed (see Illustration 23). Finally, there are three types messages: Errors, warnings and information messages.

• Errors that have to be eliminated before the execution of the compare

◦ no functional key defined (data structure)

◦ technical key is double defined (data structure)

◦ overlap of functional and technical key (data structure)

Illustration 23

◦ foreign key has no underlying surrogate key (data structure)

◦ different data types of columns (data structure)

◦ data functional key is not unique (data)

◦ data technical key is not unique (data)

◦ foreign key value has no reference value in technical key (data)

• Warnings of problems that can be handled by the system automatically

◦ column only on left side (data structure)

◦ functional key contains NULL values (data)

◦ technical key contains NULL values (data)

• Informative messages

◦ column only on right side (data structure)

In case of errors the user has to elaborate the data model definition again. Data issues like NULL values in keys are partly solved by the DREAM system and they do remain traceable in the results. Other cases of bad data you should be solved with a SQL browser in order to clean up the data. DREAM supports the user to do so, by providing a cascading data model on the DREAM compare tables. The cascading constraintset makes it easy for users to clean up test sets, removing bad data without inflicting data integrity damages. Alternatively, in case of bad data quality the delivering system could be requested to deliver set without DQ issues.

When there are no more errors the user saves the data model in order to store the data model definition in the library.

Finally, the user proceeds with generating the compare result tables (see Illustration 24). There is no need to wait for the process to be finished. Users can go on with other actions, the process will be executed asynchronously in the background.

Illustration 24

The status of every process can be looked up on the process tab (see Illustration 25). Every process has a unique ticket (run id), that allows multiple users to use the DREAM system simultaneously.

Illustration 25

Restart CompareThe Restart Compare function is the fastest way to do a compare again for a pair of compare table baselines. You can reproduce results of an earlier compare without stepping through the Data Model dialogue. Or one decides to do the compare again after cleaning up test data in the DREAM compare tables. This goes as long as DREAM compare tables, which are temporary copies of DREAM base tables being enriched with constraints, are available in the DREAM system.

In the Restart Compare dialogue, the user starts with the selection of a previous compare, consisting of two baselines (see Illustration 26).

Illustration 26

Below, one can lookup the history of compares (see Illustration 27). Then click the Proceed button.

Illustration 27

Then the user can make a selection of tables that have to be compared (see Illustration 28). In case of fact or aggregates of BI/ DWH database it is advisable to choose all tables of the corresponding star at once.

Illustration 28

The execution of the processing is asynchronous. There is no need to wait. The final status of the restart compare processes can be looked up on the process tab (see Illustration 29).

Illustration 29

AnalysisThe Analysis function allows users to look up compare results without a SQL browser. After finishing the New Compare and Restart processes the DREAM result tables and DREAM compare tables can be made available to users with the Analysis dialogue. Alternatively, one can use a SQL Browser (see SQL Browser).

First, choose a DREAM application and ensure that the application is not locked (see Illustration 30).

Illustration 30

Then choose the baselines that you want to make available (see Illustration 31) and click the Generation button (see Illustration 30). This a synchronized process. Please wait for the process to be finished. The next step is to go to the View Selection dialogue.

Illustration 31

In this dialogue, different views can be chosen (see Illustration 32) for looking up status information, detailed differences and aggregated differences.

Illustration 32

The status information view informs the user about the ticket (run id), date, time and the underlying baselines of the compare (see Illustration 33). This information should be checked first when controlling results, in order to be sure that the right version of result set is available.

Illustration 33

The detailed differences between tables can be divided into three categories.

1. Records that do exist in the left side table but do not exist in the right side table.

2. Records that do exist in the right side table but do not exist in the left side table.

Illustration 34

3. Records that do exist in both tables with differences in one or more columns.

In Illustration 34 the three types of differences are denoted by the terms: ONLY_IN_RIGHT_TABLE, ONLY_IN_LEFT_TABLE and IN_BOTH_TABLES. Every record can be identified by its functional key or substituted functional key.

In Illustration 35 there is a detailed difference in a column reported: Myller versus Miller.

Illustration 35

In order to get an overview about the differences between baselines, a dashboard is available (see Illustration 36). The dashboard let users quickly analyse the overall situation of differences in order to come to know which tables and columns have differences.

Illustration 36

ProcessingThe processing dialogue informs users and administrators about the status of processes. The DREAM system is a multi-user system which allows different users to work simultaneously. Many processes are running asynchrounoulsy in the background. Every process has an unique ticket that allows users to trace their processes.

The processing dialogue (see Illustration 37) shows a table where users can find their processes by the unique ticket (run id). The most relevant processes are the CMP_IMP_PART2, CMP_NEW_PART2 and CMP_RE_PART2 processes because they inform the users about the status of more time consuming processes such as import, new compare and restart compare. Those processes can run in the background. When processes do not succeed, retry the processes and after all, inform the administrator.

Illustration 37

Trash

The trash function allows users to remove a whole baseline with all meta information at once. It is used to clean up the DREAM system from data that is no longer needed (see Illustration 38). When DREAM is used for regression testing, only the history of recent baselines will be needed. The system should regularly be freed from unnecessary disk usage.

Illustration 38

Select an application and a baseline from the application that has to be removed (see Illustration 39). Click the remove button in order to execute the removal physically.

Illustration 39

AuthorisationThe authorisation function is for administrators and allows them to grant and revoke access right on DREAM applications to and from DREAMusers. Part of this functions is the Check Application Status dialogue which allows DREAM users to unlock, claim and free DREAM applications.

Only administrators are allowed to use the Authorisation dialogue (see Illustration 40). An administrator specific password gives them access to the dialogue. Click the login button to proceed.

Illustration 40

There does exist an option in order to change the administrator password (see Illustration 41). Otherwise proceed with the preparation of the authorisation process.

Illustration 41

The Authorisation dialogue shows the current situation of authorisations (see Illustration 42) . The user can grant and revoke authorisations and finally executing them by clicking the Execute button.

Illustration 42

In order to give a DREAM user access rights on a DREAM application, select an user and an application and click the Grant button (seeIllustration 43).

Illustration 43

In order to revoke DREAM user access rights from a DREAM application, select an user and an applications and click the Revoke button (seeIllustration 44).

Illustration 44

The Application Status dialogue (see Illustration 45) is available on every tab. Here, users can unlock applications from technical locks and they can claim or free applications in a teamwork manner where different users have access to the same application.

Illustration 45

AdministrationThe administration function is used in order to make, remove, change and configure DREAM applications.

Only administrators are allowed to use the Administration dialogue (see Illustration 46). An administrator specific password gives them access to the dialogue. Click the login button to proceed.

Illustration 46

In the Administration dialogue (see Illustration 47) the user has an overview about DREAM applications. Here the user can make, remove, configure applications and look up the application history.

Illustration 47

Making a DREAM application can simply be done by using another DREAM application as template (see Illustration 48). The so-called DREAMmother application MMA is a pre-configured DREAM application which is present in every DREAM system. Only administrators can change the configuration of the mother application MMA.

Illustration 48

Every DREAM application has a configuration record that is created when making the DREAM application (see Illustration 49). Only administrators can change the parameters of an application.

Illustration 49

SQL BrowserUsers can access DREAM by a SQL Browser like SQL Developer. Every DREAM user has access to the database schema where the base tables, compare tables and result tables are stored. DREAM uses the user-role authorisation mechanism of Oracle and thereby, DREAM is 100% compatible with Oracle and Oracle tooling.

SQL Developer allows users to access DREAM locally and in the Cloud (see Illustration 50).

Illustration 50

The DREAM schema is accessible by the Other User folder (see Illustration 51) .

Illustration 51

The base tables (see Illustration 52) are the original tables and they remain unchanged for traceability reasons.

Illustration 52

The compare tables are temporary tables that will be used when restarting the compare (see Illustration 53). These tables are equipped with a cascading constraint structure which allows users to clean up test data without touching the data integrity. Alternatively, the compare tables are available in the web browser application (see Analysis).

Illustration 53

The result tables can be looked up for every DREAM application (see Illustration 54). Alternatively, the result tables are available in the web browser application (see Analysis).

Illustration 54

Glossary[DREAM application ] Environment in the DREAM for storing baselines of tables to which only authorised DREAM users have access.[Business application] Regular IPO (Input-Process-Output) application which is part of the business process.[DREAM mother application] Initial and called MMA application, containing the most general parameters.[DREAM system] Equal to the term DREAM[DREAM user] An Oracle user with access to one or more DREAM applications[Administrator] DREAM users with the privileges for doing authorisation and administration tasks.[Baseline] A baseline is a set of tables including corresponding meta information about table naming and data structures. [Reference baseline] A reference baseline is a baseline that is used as reference for the current baseline.[Library] The DREAM library stores the DREAM baselines' meta information like table mapping naming and data model [Source data] Tables in different Oracle schemas that DREAM copies to a DREAM application by importing[Source data location] The location where the source data can be found. Normally, this is an Oracle schema. [Base table] Those tables that contain data as originally imported into DREAM[Compare table] Compare tables are copies of base tables, enriched with cascading constraint allowing cleaning up test data.[Substituted functional key] The substituted functional key is the situation where the surrogate key is substituted by the functional key[Cascading Data Model] A cascading data model allows to clean up parent and child records directly.[Result table] There are several types of result tables: status information, detailed results, counting, grouping and dashboard[DQ] Data quality