Tension Report Generation and Data Import Updates

Quartz Energy is monitoring stresses on a structure which has multiple towers.  The tensions are expressed in kips and are monitored by a series of cells which are recording and reporting the data.  There are generally 3 cells monitoring each tower and tension reading are reported at 12 hour intervals.  Our client generates a Tension Data report on a weekly basis to evaluate these tension readings. In this challenge, we’ll be generating this basic report and continuing to improve and make updates to our data import process as well.

We’re providing some data files which can be found in the forums.  The key to the data files and a description of the Platform, Tower, Cell hierarchy can be found here.

 

Reporting Requirements

The report has three types of worksheets.

1. A summary sheet.  This is the first page in the workbook.  

2. Raw data sheet -- there is one of these sheets for each tower.

3. Graph - there is one of these sheets for each tower as well.

The reports will be generated by querying the following data tables:

 

TABLE QuartzEnergy.dbo.Cell (

    Id bigint NOT NULL IDENTITY(1,1),

    Platform nvarchar(30) NOT NULL,

    DryTreeId nvarchar(20) NOT NULL,

    DEAPId nvarchar(20) NOT NULL,

    TargetTension decimal(10,2) NOT NULL,

    FirstLowAlert decimal(10,2) NOT NULL,

    SecondLowAlert decimal(10,2) NOT NULL,

    DescentAlert decimal(10,2) NOT NULL,

    CONSTRAINT PK_Cell_1 PRIMARY KEY (Id)

)

 

TABLE QuartzEnergy.dbo.Measurement (

    Id bigint NOT NULL IDENTITY(1,1),

    CellId bigint NULL,

    ClientName nvarchar(20) NOT NULL,

    PlatformName nvarchar(30) NOT NULL,

    DeviceName nvarchar(30) NOT NULL,

    SystemName nvarchar(30) NOT NULL,

    ChannelName nvarchar(20) NOT NULL,

    Date date NOT NULL,  ( ←- Please change this column type to Timestamp )

    DataLayer nvarchar(8) NOT NULL,

    Max decimal(10,7) NOT NULL,

    Mean decimal(10,7) NOT NULL,

    Min decimal(10,7) NOT NULL,

    SDev decimal(10,7) NOT NULL,

    MeasurementUnit nvarchar(8) NOT NULL,

    CONSTRAINT PK_Measurement_1 PRIMARY KEY (Id),

 CONSTRAINT FK_Measurement_1 FOREIGN KEY (CellId) REFERENCES QuartzEnergy.dbo.Cell(Id)

 

The summary worksheet of the report should include the following information.  There will be one record for each Cell in the Cell Table.

 

1. Label:  “Platform” Value: Cell.Platform

2. Label:  “Dry Tree Id”  Value: Cell.DryTreeId

3. Label: “DEAP Id”  Value: Cell.DEAPId

4. Label:  “Tension (kips)”  Value: Average of the Measurement.Mean Tension Readings for a particular tower during the designated time period.  All the mean cell values for a particular tower are summed. (e.g., Measurement.Mean for LC1, LC2, and LC3 - averaged over a week) Please color code this cell depending if reading is above the target tension values in Columns 5, 6, 7, and 8. (Green, Yellow, Orange or Red)

5. Label: “Target Tension (kips)”  Value: Cell.TargetTension Cell Background:  Green

6. Label: “First Low Alert (kips)” Value: Cell.FirstLowAlert  Cell Background: Yellow

7. Label: “Second Low Alert (kips)” Value:  Cell.SecondLowAlert Cell Background: Orange

8. Label: “Descent Alert (kips)” Value:  Cell.DescentAlert Cell Background: Red

9. Label:  “90 Day Trend” Value:   Slope of the regression line for the past X days of Measurement.Mean Values.  The formula for the value can be found here: https://support.office.com/en-us/article/slope-function-11fb8f97-3117-4813-98aa-61d7e01276b9   The Y values are Measurement.Mean values and the X values are the number of days in the trending window.   This label and the length of the regression slope period considered in days should be configurable from a config file.

10. Label:  “Stem Pressure” Value:  Cell.Mean where Cell.MeasurementUnit = ‘psi’

 

There should be a Raw Data worksheet for each platform.  They can be labeled by platform name. The amount of time covered by raw data worksheets should be configurable.  By default let’s show the past 30 days of data. There should be a section for each tower with the following columns:

 

1. Label:  “Date” Value: Measurement.Date (should be a timestamp value, not a date value) Converted to Central Time.

2. Label:  “Date (utc)” Value: Measurement.Date (should be a timestamp value, not a date value)  Displayed in UTC time.
   
   Repeated for each cell (typically 3, but sometimes 6)

3. Label:  “Max (kps)” Value: Measurement.Max

4. Label:  “Mean (kps)” Value: Measurement.Mean

5. Label:  “Min (kps)” Value: Measurement.Min

6. Label:  “Total Max (kps)” Value: Measurement.Max for each of the previous cells

7. Label:  “Total Mean (kps)” Value: Measurement.Mean for each of the previous cells.  Total Mean should be color coded based the cells values: Green, Yellow, Orange or Red  and based on the Cell.TargetTension, Cell.FirstLowAlert, Cell.SecondLowAlert, Cell.DescentAlert

There should also be a Graph worksheet for each platform.  On each worksheet there should be a chart for each tower. On each graph we should just line plot the mean tension values for each cell.   The Y axis is Measurement.Date, X Axis is Measurement.Mean

 

Data Import Updates

1. Our data import process should only keep one record per cell and timestamp.  It’s possible that a user might process files that have overlapping data in terms of time.  If new data is loaded, we can overwrite old data but we should not have duplicate records. The measurement data should be unique by cell and timestamp.

2. The measurement data might be a bit hard to relate to the cell data just by information contained in the raw data files provided.  Please create configuration files to do this mapping. The mapping is from Platform, Tower, and Cell (in the Cell table) to the Channel column in the measurement data files.  I think it would also be helpful to list the number of cells for a tower in each configuration file as well. Some of the towers have 3 cells and some have 6. (Alternatively, we can create a platform table in the database).

NOTE: data import tool is provided in the forums

Other Requirements

It is highly likely the the data source for this report will change in the not too distant future.    Please use software idioms (e.g. Base Classes, Facades, Decorators, Interfaces) which allow for the abstraction of data sources.  

Please generate a unit tests for your code and implement coverage.py so that units test cover at least 60% of the code base. PEP-8 code style is required - include lint check in your submission.

 

Submission Requirements

Python3
Please use the existing codebase as the starting point for your application code.  A link to this repository will be found in the forums.

Final Submission Guidelines

Submit the full source code
Submit a README.md with details on how to configure/build/run the application
Please use the existing codebase as the starting point for your application code.  A link to this repository will be found in the forums.