Project/Program : BI Developer Test

Title: Laborator 02
Name: TOADER Alexandru
Initial creator :
Entity : TGI/OLN/NSSO/BSM

By: FILIP Andrei / CÎRSTEA Ana

Last saved :
When: 22/05/2019 09:33:00

Difficulty:
Medium

Maximum Score:
120 points
Table of Contents

1 Introduction ................................................................................................................ 3
1.1 Overview ....................................................................................................................................... 3
1.2 CONTEXT ....................................................................................................................................... 3
1.3 Prerequisites ................................................................................................................................. 3
2 Laborator 02 – DataBase............................................................................................. 4
2.1 Software installation 10 points ..................................................................................................... 4
2.2 Connecting to PostgreSQL Database 5 points.............................................................................. 4
2.3 Input data 35 points ..................................................................................................................... 4
2.3.1 Tables creation 15 points ............................................................................................. 4
2.3.2 Add data in tables 20 points ......................................................................................... 5
2.4 Output data 40 points .................................................................................................................. 6
2.4.1 Output amount per PARENT_NAME between two dates 10 points .............................. 6
2.4.2 Output COUNTS per PARENT_NAME 10 points ............................................................. 6
2.4.3 Output AMOUNT segmented 10 points ........................................................................ 7
2.4.4 Create TABLE “TRANSACTIONS_DETAILS” as select from TRANSACTIONS 10 points ..... 7
3 Laborator 02 POWER BI.............................................................................................. 8
3.1 Software Installation and Data Connection 10 points ................................................................. 8
3.2 Data Analysis 20 Points ................................................................................................................ 8
1 INTRODUCTION

1.1 OVERVIEW

The document focuses on creation of a PostgreSQL environment, adding sample data and generation
of output. This will provide to the tester information about the candidate ability to search for how
to, install and configure a database and generate data samples as input and output.

Based on the data from the created database the candidate should be able to present in Power BI
Desktop at least one relevant KPI from that dataset .

The candidate should reply with a document that contains all points marked as “To present” .

After the laboratory is completed there will be a live session (if needed) where the candidate will
output requirements marked as “To present”.

1.2 CONTEXT

The candidate can use any information available to fulfill the tasks. The scope of the laboratory is to
act as being a normal day to day activity.

The candidate will receive score per each step followed.

1.3 PREREQUISITES

PostgreSQL 9.5.16 installation (https://www.enterprisedb.com/downloads/postgres-postgresql-

downloads )
PG Admin (https://www.pgadmin.org/download/pgadmin-4-windows/)
Power BI Desktop (https://www.microsoft.com/en-us/download/details.aspx?id=45331)

Observation: Please install version 9.5.16 of PostgreSQL, and any PG Admin and Power BI Desktop
would suffice. The candidate should be aware that Power BI is only an Windows software
2 LABORATOR 02 – DATABASE
The candidate will follow the steps from this chapter and he/she will output in a live session (if
needed) requirements marked as “To present” after completing them.

2.1 SOFTWARE INSTALLATION 10 POINTS

Install on a windows or linux machine the following software

 PostgreSQL 9.5.16 – Free Edition

 PG Admin

Observation: any environment is acceptable, windows desktop or virtual machine

To present: 10 points

 What information was used to install the software (web links, books …) 5 points
 Print screen for PostgreSQL installation path used during installation 3 points
 Print screen for Global database name used during installation (can use default or any name
during the installation ) 2 points

Observation: During the live session can be requested to stop and start the PostgreSQL instances .

2.2 CONNECTING TO POSTGRESQL DATABASE 5 POINTS

Launch the PG Admin application and connect it to the database installed on the above
step.

To present :

 The connection details in PG Admin 5 points

2.3 INPUT DATA 35 POINTS

2.3.1 TABLES CREATION 15 POINTS

2.3.1.1 CREATE TABLE 5 POINTS

Create Table TRANSACTIONS with the columns below
"DATE_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"USER_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"ERROR_CODE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"COUNT" NUMBER(25) ,
"AMOUNT" NUMBER(25)

To present :

 The query used to create table or the table structure if it was created from SQL Developer
menu 5 points
2.3.1.2 CREATE TABLE 10 POINTS
Create Table HIERARCHY with the columns below
"USER_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"NAME" VARCHAR2(25 BYTE) ,
"PHONE" VARCHAR2(25 BYTE),
"PARENT_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"STATUS " VARCHAR2(1 BYTE) NOT NULL ENABLE,
“ID” NUMBER

With primary key (“ID”) and unique index (“USER_ID”,”STATUS”)

Requirements :

 Create sequence HIERARCHY_ID_SEQ with below parameters

 Create trigger HIERARCHY_ID on column ID , table HIERARCHY and using the sequence
HIERARCHY_ID_SEQ
 Sequence and Trigger needs to be created from SQL but the command can be run in PG
Admin .
To present :

 Follow the requirements 5 points

 The query used to create table or the table structure if it was created from PG Admin menu
5 points

2.3.2 ADD DATA IN TABLES 20 POINTS

2.3.2.1 POPULATE TABLE TRANSACTIONS 10 POINTS

Add minimum 40 rows of data in the table transactions

Requirements :
 USER_ID needs to be present in the table HIERARCHY .
 The table need to contain at least 10 rows with DATE_TIME between ’09-APR-19 10:00:00’
and ’09-APR-19 18:00:00’
To present :

 Follow the requirements 4 points

 Show how data was added in the table 3 points
 Show data in the table order by DATE_TIME descendent using SQL query 3 points
2.3.2.2 POPULATE TABLE HIERARCHY 10 POINTS
Add minimum 10 rows of data in the table hierarchy .

Requirements :
 There should be minimum 3 distinct PARENT_NAME values .
 There should be minimum 4 NAMEs containing ALEX

To present :

 Follow the requirements 4 points

 Show how data was added in the table 3 points
 Show data in the table where NAME contains ALEX using SQL query 3 points

2.4 OUTPUT DATA 40 POINTS

2.4.1 OUTPUT AMOUNT PER PARENT_NAME BETWEEN TWO DATES 10 POINTS
Create a select query to output the sum AMOUNT from table transactions grouped by
PARENT_NAME in hierarchy table between two dates (any value for dates filter is accepted)

To present :
 Show generated data 5 points
 Respect dates filter 2 points
 if a parent does not have correspondent in table transactions the value will be 0 2 points
 show explain plan for the query 1 point

2.4.2 OUTPUT COUNTS PER PARENT_NAME 10 POINTS

Create a select query to output the sum COUNT from table transactions grouped by PARENT_NAME
in hierarchy table between where date=’09-APR-19’ and hours between ‘10’ and ‘18’

To present :
 Show generated data 5 points
 Respect dates filter 2 points
 if a parent does not have correspondent in table transactions the value will be 0 2 points
 show explain plan for the query 1 point
2.4.3 OUTPUT AMOUNT SEGMENTED 10 POINTS
Create a select query to output the count AMOUNT from table transactions grouped by 4 tags as
shared below :
1. BRONZE if AMOUNT between 0 and 1000
2. SILVER if AMOUNT between 1001 and 9999
3. GOLD if AMOUNT between 10000 and 19999
4. PLATINUM if AMOUNT between 19999and 9999999999

To present :
 Show generated data 5 points
 Respect the 4 tags 5 points

2.4.4 CREATE TABLE “TRANSACTIONS_DETAILS” AS SELECT FROM TRANSACTIONS 10 POINTS

Create table as select all rows from TRANSACTIONS with columns NAME,PARENT and PHONE from
HIRARCHY table in a join relation .

To present :
 Show generated data 2 points
 Show new table structure 3 points
 Addition of new columns 3 points
 Perform above tasks using SQL command line 2 points
3 LABORATOR 02 POWER BI
The candidate will follow the steps from this chapter and he/she will output in a live session (if
needed) requirements marked as “To present” after completing them.

3.1 SOFTWARE INSTALLATION AND DATA CONNECTION 10 POINTS

 Power BI Desktop – Free Edition

To present: 10 points

 Print Screen for Power BI Desktop Installation 2 points

 Print Screen for PostgreSQL ODBC Connection to the database from Previous Chapter 3
points
 Print Screen for Connection String or Setup used for ODBC Connection 3 points
 Create a relationship in Power BI Desktop Data Model between the 2 tables from Previous
Chapter 2 points

3.2 DATA ANALYSIS 20 POINTS

 Present a few KPIs you think are relevant for the dataset 10 points
 Use Power BI Desktop to create a Report presenting these KPIs 10 points