#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Power BI Resources Generator ======================================= """ import json from pathlib import Path from datetime import datetime def main(): print("="*70) print("Power BI Resources Generator") print("="*70) # Create output directory output_dir = Path(__file__).parent / 'report' / 'powerbi_resources' output_dir.mkdir(exist_ok=True) print("\n[INFO] Generating Power BI resources...") print(f"[INFO] Output directory: {output_dir}") # 1. Create DAX measures file dax_file = output_dir / 'DAX_Measures.dax' with open(dax_file, 'w', encoding='utf-8') as f: f.write(DAX_MEASURES) print(f"[OK] Created: {dax_file.name}") # 2. Create Power Query M code files m_files = { 'Unified_Termination_Logs.m': M_CODE_LOGS, 'Daily_Summary.m': M_CODE_SUMMARY, 'Error_Details.m': M_CODE_ERRORS } for filename, content in m_files.items(): file_path = output_dir / filename with open(file_path, 'w', encoding='utf-8') as f: f.write(content) print(f"[OK] Created: 3 Power Query M code files") # 3. Create Power BI Template template = { "version": "1.0", "queries": POWERBI_QUERIES, "model": POWERBI_MODEL } pbit_file = output_dir / 'Termination_Dashboard.pbit' with open(pbit_file, 'w', encoding='utf-8') as f: json.dump(template, f, indent=2) print(f"[OK] Created: {pbit_file.name}") # 4. Create Quick Start Guide guide_file = output_dir / 'POWERBI_QUICKSTART.txt' with open(guide_file, 'w', encoding='utf-8') as f: f.write(QUICKSTART_GUIDE) print(f"[OK] Created: {guide_file.name}") print("\n" + "="*70) print("All resources generated successfully!") print("="*70) print(f"\nFiles saved to: {output_dir}") print("\nNext Steps:") print("1. Open Power BI Desktop") print("2. Load CSV files from output/ directory") print("3. Copy DAX measures from DAX_Measures.dax") print("4. Create visualizations (see POWERBI_GUIDE.md)") # DAX Measures DAX_MEASURES = """ // ======================================== // Overall Statistics // ======================================== Total Processed = COUNTROWS('Unified_Termination_Logs') Success Count = CALCULATE( COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = "SUCCESS" ) Failed Count = CALCULATE( COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = "FAILED" ) Skipped Count = CALCULATE( COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = "SKIPPED" ) Success Rate = DIVIDE([Success Count], [Total Processed]) // ======================================== // System Comparison // ======================================== MDP Processed = CALCULATE( [Total Processed], 'Unified_Termination_Logs'[System] = "MDP" ) HUB Processed = CALCULATE( [Total Processed], 'Unified_Termination_Logs'[System] = "HUB" ) POTS Processed = CALCULATE( [Total Processed], 'Unified_Termination_Logs'[System] = "POTS" ) WDTS Processed = CALCULATE( [Total Processed], 'Unified_Termination_Logs'[System] = "WDTS" ) // ======================================== // Time Analysis // ======================================== Today Processed = CALCULATE( [Total Processed], 'DateTable'[Date] = TODAY() ) Last 7 Days Avg = AVERAGEX( DATESINPERIOD('DateTable'[Date], TODAY(), -7, DAY), [Total Processed] ) MoM Growth = VAR ThisMonth = [Total Processed] VAR LastMonth = CALCULATE( [Total Processed], DATEADD('DateTable'[Date], -1, MONTH) ) RETURN DIVIDE(ThisMonth - LastMonth, LastMonth) """ # Power Query M Code M_CODE_LOGS = """ let Source = Csv.Document(File.Contents("output/Unified_Termination_Logs.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{ {"ExecutionDate", type date}, {"ExecutionTime", type time}, {"System", type text}, {"LogFile", type text}, {"EmployeeID", type text}, {"UserName", type text}, {"Action", type text}, {"Status", type text}, {"Message", type text}, {"Property", type text}, {"Position", type text}, {"ExtraInfo", type text} }) in #"Changed Type" """ M_CODE_SUMMARY = """ let Source = Csv.Document(File.Contents("output/Daily_Summary.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{ {"ExecutionDate", type date}, {"System", type text}, {"TotalProcessed", type number}, {"SuccessCount", type number}, {"FailedCount", type number}, {"SkippedCount", type number}, {"SuccessRate", type number} }) in #"Changed Type" """ M_CODE_ERRORS = """ let Source = Csv.Document(File.Contents("output/Error_Details.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{ {"ExecutionDate", type date}, {"System", type text}, {"ErrorType", type text}, {"ErrorCount", type number}, {"EmployeeCount", type number}, {"TopEmployees", type text}, {"FirstOccurrence", type datetime} }) in #"Changed Type" """ # Power BI Queries (for .pbit template) POWERBI_QUERIES = { "Unified_Termination_Logs": { "query": "let\n Source = Csv.Document(File.Contents(\"output/Unified_Termination_Logs.csv\"),[Delimiter=\",\", Encoding=65001, QuoteStyle=QuoteStyle.None]),\n #\"Promoted Headers\" = Table.PromoteHeaders(Source, [TextType.Double]),\n #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"ExecutionDate\", type date},{\"ExecutionTime\", type time},{\"System\", type text},{\"LogFile\", type text},{\"EmployeeID\", type text},{\"UserName\", type text},{\"Action\", type text},{\"Status\", type text},{\"Message\", type text},{\"Property\", type text},{\"Position\", type text},{\"ExtraInfo\", type text}})\nin\n #\"Changed Type\"", "queryOptions": { "FTA_QueryGroup": "Unified_Termination_Logs", "FTA_QuerySource": "output/Unified_Termination_Logs.csv" } }, "Daily_Summary": { "query": "let\n Source = Csv.Document(File.Contents(\"output/Daily_Summary.csv\"),[Delimiter=\",\", Encoding=65001, QuoteStyle=QuoteStyle.None]),\n #\"Promoted Headers\" = Table.PromoteHeaders(Source, [TextType.Double]),\n #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"ExecutionDate\", type date},{\"System\", type text},{\"TotalProcessed\", type number},{\"SuccessCount\", type number},{\"FailedCount\", type number},{\"SkippedCount\", type number},{\"SuccessRate\", type number}})\nin\n #\"Changed Type\"", "queryOptions": { "FTA_QueryGroup": "Daily_Summary", "FTA_QuerySource": "output/Daily_Summary.csv" } }, "Error_Details": { "query": "let\n Source = Csv.Document(File.Contents(\"output/Error_Details.csv\"),[Delimiter=\",\", Encoding=65001, QuoteStyle=QuoteStyle.None]),\n #\"Promoted Headers\" = Table.PromoteHeaders(Source, [TextType.Double]),\n #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"ExecutionDate\", type date},{\"System\", type text},{\"ErrorType\", type text},{\"ErrorCount\", type number},{\"EmployeeCount\", type number},{\"TopEmployees\", type text},{\"FirstOccurrence\", type datetime}})\nin\n #\"Changed Type\"", "queryOptions": { "FTA_QueryGroup": "Error_Details", "FTA_QuerySource": "output/Error_Details.csv" } } } POWERBI_MODEL = { "relationships": [], "tables": [] } QUICKSTART_GUIDE = """ Power BI Quick Start Guide =========================== STEP 1: Load Data --------------- 1. Open Power BI Desktop 2. Click 'Get Data' -> 'Text/CSV' 3. Navigate to output/ folder 4. Select and load all 3 CSV files: - Unified_Termination_Logs.csv - Daily_Summary.csv - Error_Details.csv STEP 2: Set Data Types ---------------------- In Data View: - Select 'Unified_Termination_Logs' table - Set 'ExecutionDate' to 'Date' - Set 'ExecutionTime' to 'Time' - Set other text fields as needed Repeat for Daily_Summary and Error_Details tables. STEP 3: Create Date Table --------------------------- Go to Modeling tab -> New Table -> paste: DateTable = CALENDAR(DATE(2026,1,1), TODAY()) Add calculated columns: Year = YEAR('DateTable'[Date]) Month = MONTH('DateTable'[Date]) MonthName = FORMAT('DateTable'[Date], "MMMM") Day = DAY('DateTable'[Date]) STEP 4: Add DAX Measures ----------------------- 1. Go to Modeling tab -> New Measure 2. Open DAX_Measures.dax 3. Copy and paste each measure 4. Click ✓ to save STEP 5: Create Visualizations ------------------------------- PAGE 1: Overview Dashboard - Card 1: Total Processed = [Total Processed] - Card 2: Success Count = [Success Count] - Card 3: Failed Count = [Failed Count] - Card 4: Success Rate = [Success Rate] - Clustered Bar Chart: X-axis: DateTable[Date] Y-axis: Total Processed Legend: System - Line Chart: X-axis: DateTable[Date] Y-axis: Success Rate - Donut Chart: Category: System Values: Total Processed PAGE 2: System Comparison - Stacked Column Chart: X-axis: System Y-axis: Total Processed Legend: Status - Table: Columns: ExecutionDate, System, TotalProcessed, SuccessCount, FailedCount, SuccessRate PAGE 3: Error Analysis - Bar Chart: Y-axis: ErrorType X-axis: ErrorCount From table: Error_Details - Table: Columns: ExecutionDate, System, ErrorType, ErrorCount, TopEmployees STEP 6: Format and Style --------------------------- - Use consistent colors: SUCCESS: Green (#107C10) FAILED: Red (#C80000) SKIPPED: Orange (#FF8C00) - Add title: "Employee Termination Dashboard" - Add logo if needed STEP 7: Save and Publish ----------------------- 1. Save as: Termination_Dashboard.pbix 2. (Optional) Publish to Power BI Service 3. Set up scheduled refresh Tips: ----- - Use bookmarks for different views - Add slicers for Date, System, Status - Enable drill-through on EmployeeID - Set up automatic page refresh (5 minutes) """ if __name__ == '__main__': main()