Scribd Logo
Upload

Welcome to Scribd!

  • 68 views

    BI Technical Test - 20200723 - MA - HT

    Uploaded by

    gearangelus
    The technical test involves designing an OLAP cube using Pentaho Data Integration to generate fact and dimension tables from SQL data on policies for 2018-2019, and then designing a Power BI dashboard using an Excel file on innovations data. Deliverables include an image of the star schema model, Pentaho ETL files to load the cube, and the Power BI dashboard file, all organized in a GitHub repository with a readme file.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    BI Technical Test - 20200723 - MA - HT

    Uploaded by

    gearangelus
    68 views3 pages
    The technical test involves designing an OLAP cube using Pentaho Data Integration to generate fact and dimension tables from SQL data on policies for 2018-2019, and then designing a Power BI dashboard using an Excel file on innovations data. Deliverables include an image of the star schema model, Pentaho ETL files to load the cube, and the Power BI dashboard file, all organized in a GitHub repository with a readme file.

    Original Title

    BI technical test_20200723_MA_HT

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The technical test involves designing an OLAP cube using Pentaho Data Integration to generate fact and dimension tables from SQL data on policies for 2018-2019, and then designing a Power BI dashboard using an Excel file on innovations data. Deliverables include an image of the star schema model, Pentaho ETL files to load the cube, and the Power BI dashboard file, all organized in a GitHub repository with a readme file.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    68 views3 pages

    BI Technical Test - 20200723 - MA - HT

    Uploaded by

    gearangelus
    The technical test involves designing an OLAP cube using Pentaho Data Integration to generate fact and dimension tables from SQL data on policies for 2018-2019, and then designing a Power BI dashboard using an Excel file on innovations data. Deliverables include an image of the star schema model, Pentaho ETL files to load the cube, and the Power BI dashboard file, all organized in a GitHub repository with a readme file.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    Technical Test 2020 – Business intelligence Analyst

    PART 1 – 50%

    Using the data model below, please design an OLAP cube using Pentaho Data Integration for creating
    ETLs that generate the fact table and dimensions of the 2018 and 2019 policies information.

    Please download the following SQL file, which contains the tables with the data that should be
    used in this exercise (Download SQL file). Execute it in a local MySql 8.0 local database.
    Descriptions for each table as follows:

    project_policy: Main table with the basic information about the policies. This table define if the
    policy was deleted (is_active=0). Fields to use in the cube: id

    project_policies_info: Detailed information about the policies. Fields to use in the cube: year,
    title and description.

    global_units: Information on the different Research programs leading the policies. Fields to use
    in the cube: id, smo_code, name and acronym.

    project_policy_regions: many-to-many table with the relation between policies and regions

    project_policy_countries: many-to-many table with the relation between policies and countries

    loc_elements: information about locations. There are the regions (element_type_id =1) and the
    countries (element_type_id = 2). Fields to use in the cube: id, name, iso_alpha_2, iso_numeric
    and parent_id.

    project_policy_geographic_scopes: many-to-many table with the relation between policies and


    geographic scopes

    rep_ind_geographic_scopes: list of geograpics scopes. Fields to use in the cube: id, name and
    definition

    project_policy_sub_idos: many-to-many table with the relation between policies and sub idos.
    Additionaly, this table indicates which subidos are primary (is_primary=1) or not (is_primary=0)

    srf_sub_idos: List of Sub-Intermediate Development Outcomes that define the main objectives
    defined by SMO of the policies. Fields to use in the cube: id, description, ido_id, smo_code.

    PART 2– 50%

    Using an excel sheet with the innovations information, design a dashboard in Power BI.
    (Download source file here)

    DELIVERABLES

    All information should be loaded on a personal github repository according to the following structure:
    Please include a readme file in the root of the folder with the instructions for us to install and run
    the test from our end.

    Deliverable #1: A PNG file with the star schema dimensional model of policies cube. (Folder “1. Policy
    cube model”)

    Deliverable #2: Pentaho data integration files (.kbj and .ktr) of policies ETLs. (Folder “2. Pentaho ETL”)

    Deliverable #3: Power bi file with the Innovation dashboard (Folder “3. Power BI files”)

    You might also like