Извлечение используемых и неиспользованных мер и столбцов из папок Power BI Project с использованием Python ⇐ Python

[Anonymous]

Я пытаюсь создать код Python, который читает модель. Bim и Report.json из Power Bi Project, чтобы получить использование и неиспользованные меры и столбцы, для моих мыслей < /p>
столбец отмечается как «Используется», если на ссылку на: < /p>
диаграмма (из отчета.json), отношения, параметр /рассчитанная таблица, фильтр или мера
и мера отмечен как «использованный» Если на ссылку на:
a диаграмма (из отчета.json), параметр /рассчитанная таблица или ссылка на другую меру < /p>
Это то, что у меня есть, и это не отмечает меры ни столбцы вообще < /p>
import json
import pandas as pd
import os

def parse_model_bim(model_bim_path):
with open(model_bim_path, "r", encoding="utf-8") as file:
model_data = json.load(file)

tables = {}
used_columns_measures = set()

for table in model_data["model"]["tables"]:
table_name = table["name"]
table_type = "Calculated" if table.get("type") == "calculated" else "Regular"
columns = {col["name"]: col.get("isHidden", False) for col in table.get("columns", [])}
measures = {m["name"]: (m["expression"], m.get("isHidden", False)) for m in table.get("measures", [])}

tables[table_name] = {
"type": table_type,
"columns": columns,
"measures": measures,
}

if table_type == "Calculated":
used_columns_measures.update(columns.keys())
used_columns_measures.update(measures.keys())

return tables, used_columns_measures

def parse_report_json(report_json_path):
with open(report_json_path, "r", encoding="utf-8") as file:
report_data = json.load(file)

used_columns_measures = set()

for page in report_data.get("sections", []):
for visual in page.get("visualContainers", []):
if "query" in visual:
for item in visual["query"].get("Select", []):
used_columns_measures.add(item.get("Name", ""))

if "filters" in visual:
for filter_item in visual["filters"]:
used_columns_measures.add(filter_item.get("column", ""))

return used_columns_measures

def analyze_metadata(model_bim_path, report_json_path, drop_unused=False):
tables, used_in_model = parse_model_bim(model_bim_path)
used_in_report = parse_report_json(report_json_path)

used_columns_measures = used_in_model | used_in_report

table_data = []
column_data = []
measure_data = []

for table_name, table_info in tables.items():
total_columns = len(table_info["columns"])
used_columns = sum(1 for col in table_info["columns"] if col in used_columns_measures)

total_measures = len(table_info["measures"])
used_measures = sum(1 for measure in table_info["measures"] if measure in used_columns_measures)

is_unused = (used_columns + used_measures) == 0
quality_score = (used_columns + used_measures) / max(1, (total_columns + total_measures))

table_data.append([table_name, total_columns, used_columns, total_columns - used_columns,
total_measures, used_measures, total_measures - used_measures,
is_unused, table_info["type"], quality_score])

for col_name, hidden in table_info["columns"].items():
column_data.append([table_name, col_name, col_name in used_columns_measures, hidden])

for measure_name, (dax_expr, hidden) in table_info["measures"].items():
measure_data.append([table_name, measure_name, dax_expr, measure_name in used_columns_measures, hidden])

output_file = "powerbi_metadata.xlsx"
with pd.ExcelWriter(output_file) as writer:
pd.DataFrame(table_data, columns=["table_name", "total_columns", "used_columns", "unused_columns", "total_measures", "used_measures", "unused_measures", "is_unused", "table_type", "quality_score"]).to_excel(writer, sheet_name="Tables", index=False)
pd.DataFrame(column_data, columns=["table_name", "column_name", "Used", "Hidden"]).to_excel(writer, sheet_name="Columns", index=False)
pd.DataFrame(measure_data, columns=["table_name", "measure_name", "dax_expression", "Used", "Hidden"]).to_excel(writer, sheet_name="Measures", index=False)

print(f"Excel report generated: {output_file}")

if drop_unused:
with open(model_bim_path, "r", encoding="utf-8") as file:
model_data = json.load(file)

for table in model_data["model"]["tables"]:
if "measures" in table:
table["measures"] = [m for m in table["measures"] if m["name"] in used_columns_measures]

with open(model_bim_path, "w", encoding="utf-8") as file:
json.dump(model_data, file, indent=2)

print("Unused measures removed from model.bim")

# Example usage
# analyze_metadata("path/to/model.bim", "path/to/report.json", drop_unused=True)



Подробнее здесь: https://stackoverflow.com/questions/794 ... ders-using