Lights Out and Causes

CLEANING

This task was definitely challenging. It was like a puzzle searching through M-Code to find methods that optimized the query. I should add this was a three dimensional puzzle. Once the piece, or code, was found to fit the spot, the order in which to place this piece had to be considered.

Of course like any three dimensional puzzle requires, the foundation has to be considered to determine the cleaning required. How will the columns be used in the model?

Days Affected Column: The "Restoration" column can be used to determine length of Incident. Do we need to go to the level of hours or minutes? Customers get more upset and inconvenienced when electricity is out for days not hours or minutes. After a day, people are concerned with food in the freezer. Therefore, I chose to stay at the day level.

In the background, a Calendar table is created for each date column. No need for additional Calendar tables or to know the exact day Restoration took place. Therefore, I decided to assign the "Restoration" column as an integer type, length of days it took to restore power, "Day Affected". This developed more numbers than wanted. I needed something to hold a number as a bin in a Histogram Chart. This is when the "Day Affected" column took the data type as text and groups were labeled.

Weather Natural Disaster Column: This column was created to easily differentiate between Weather incidents and Natural Disasters incidents.

Weather Disaster Type Column: This column was created to easily differentiate Weather and Natural Disaster types. Weather outnumbered, by a considerable amount, Natural Disaster Types. Only weather types were used in this report.

State Table

This table I could not avoid from creating. The States could not be individually separated without offsetting other data like "Demand Loss MW" or "Number of Customers Affected". However, I wanted to count how many incidents occurred when one or more States were involved. This was accomplished by grouping States with the column "Number of States".

Index Column:

This Column was created as a key between Incidents used in both All Years Table (Primary Key) and States Table (Foreign Key).

Over all, the State Table was created for the interaction between the visuals on the Place and Time page of this report.

Here is a link to my working model: Outage Dashboard Working Model