Last active
June 19, 2024 10:21
-
-
Save SqlWaldorf/4505037fa659cbd998c47b770ebefe53 to your computer and use it in GitHub Desktop.
This script uses graphviz to create diagrams from a Power BI project
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Code provided "as-is". Use at your own risk | |
# | |
# Requires graphviz python package: | |
# pip install graphviz | |
# | |
# Unless you run with the -s option, you must also install the graphviz software from | |
# https://www.graphviz.org/download/ | |
# | |
# Basic use: python CreateDiagram.py <path to folder with .SemanticModel> | |
# | |
# For advanced options: python CreateDiagram.py -h | |
# | |
import json | |
import graphviz | |
import argparse | |
import os | |
def load_diagrams (basefolder): | |
diagrams_folder = basefolder + "/diagramLayout.json" | |
f_diagrams = open(diagrams_folder) | |
diagrams = json.load(f_diagrams) | |
diags = {} | |
for it in diagrams["diagrams"]: | |
name = it["name"] | |
nodes = it["nodes"] | |
tables = [node["nodeIndex"] for node in nodes] | |
diags[name] =tables | |
return diags | |
def load_table (basefolder, tablename): | |
table_file = f"{basefolder}/definition/tables/{tablename}.tmdl" | |
tabletmdl = open(table_file).readlines() | |
collist = [] | |
for l in tabletmdl: | |
if l.strip().startswith("column "): | |
if l.rfind("=") > -1: | |
# calculated column with formula, only name is copied | |
collist.append(l[l.find("column")+6:l.rfind("=")].strip()) | |
else: | |
collist.append(l[l.find("column")+6:].strip()) | |
return collist | |
def load_relationships (basefolder): | |
relationship_folder = basefolder + "/definition/relationships.tmdl" | |
f_relationships = open(relationship_folder) | |
relationships = f_relationships.readlines() | |
rellist = [] | |
isactive = True | |
crossfilter = False | |
tomany = False | |
for l in relationships: | |
if l.lstrip().startswith("crossFilteringBehavior: bothDirections"): | |
crossfilter = True | |
if l.lstrip().startswith("toCardinality: many"): | |
tomany = True | |
if l.lstrip().startswith("isActive: false"): | |
isactive = False | |
if l.lstrip().startswith("fromColumn:"): | |
starttable = l[l.find(": ")+2:l.find(".")].strip() | |
startcol = l[l.find(".")+1:].strip() | |
if l.lstrip().startswith("toColumn:"): | |
endtable = l[l.find(": ")+2:l.find(".")].strip() | |
endcol = l[l.find(".")+1:].strip() | |
rellist.append((starttable,startcol,endtable, endcol,isactive,crossfilter,tomany)) | |
isactive = True | |
crossfilter = False | |
tomany = False | |
return rellist | |
parser=argparse.ArgumentParser(prog="CreateDiagram", description="Create simple diagrams from Power BI Project folder.\nProvide the path to the .SemanticModel folder, and optionally choose the desired graphic format.") | |
parser.add_argument("folder", help="Path to the <projectname>.SemanticModel folder of your Power BI Project. This folder is normally in the same folder where the .pbip file is stored.") | |
parser.add_argument("-f", "--format", default="svg", help="Graphic render format (gif, png, jpg, pdf, bmp, tif, webp, svg), defaults to svg") | |
parser.add_argument("-s", "--skiprender", help="Skip rendering the diagrams", action='store_true') | |
parser.add_argument("-r","--renderfolder", default=os.getcwd(), help=f"Folder where output files are produced. Defaults to {os.getcwd()}") | |
parser.add_argument("-m","--onlymain", action='store_true', help="Skip generating subdiagrams") | |
parser.add_argument("-i","--inactive", action='store_true', help="Also include inactive relationships in the diagram") | |
parser.add_argument("-b", "--generatebus", action="store_true", help="Generate a csv file with a data warehouse bus, using heuristics to determine facts and dimensions") | |
parser.add_argument("-c", "--showcardinality", action="store_true", help="Display cardinality on edges") | |
args=parser.parse_args() | |
folderpath = args.folder.replace("\\","/").rstrip("/") | |
if folderpath.find(".SemanticModel") == -1: | |
print(f"Failed to find the .SemanticModel folder, you provided as folder: {args.folder}") | |
exit() | |
projectname = folderpath[folderpath.rfind("/")+1:folderpath.rfind(".")] | |
rels = load_relationships(args.folder) | |
diags = load_diagrams(args.folder) | |
tableinfo = dict() | |
for t in diags['All tables']: | |
tableinfo[t] = load_table(args.folder, t) | |
# print(rels) | |
# exit() | |
# Rendering with dot -- needs to be installed in OS | |
graphattributes = dict() | |
graphattributes["overlap"] = "false" | |
graphattributes["ratio"] = "0.56" | |
graphattributes["comment"] = "Basic model for implicit measures" | |
graphattributes["layout"] = "neato" | |
graphattributes["splines"] = "curved" | |
nodeattributes = dict() | |
nodeattributes["shape"] = "box" | |
edgeattributes = dict() | |
edgeattributes["len"] = ("2" if args.showcardinality else "1.2") | |
edgeattributes["labeldistance"] = "1.8" | |
edgeattributes["labelangle"] = "20" | |
if args.generatebus: | |
facts = set() | |
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels: | |
facts.add(starttable) | |
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels: | |
if (endtable in facts) and (not tomany): | |
facts.remove(endtable) | |
dims = set() | |
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels: | |
if starttable in facts: | |
dims.add(endtable) | |
bus = [["" for x in dims] for y in facts] | |
factlist = list(facts) | |
dimlist = list(dims) | |
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels: | |
if (starttable in facts) and (endtable in dims) and isactive: | |
bus[factlist.index(starttable)][dimlist.index(endtable)] = ("m" if tomany else "1") | |
busfile = open(f"{args.renderfolder}/{projectname}_dwhbus.csv","w") | |
busfile.write(f"Facts,{",".join(dimlist)}\n") | |
for f in factlist: | |
busfile.write(f"{f},{",".join(bus[factlist.index(f)])}\n") | |
busfile.close() | |
if args.onlymain: | |
diaglist = ['All tables'] | |
else: | |
diaglist = [d for d in diags] | |
for diagram in diaglist: | |
dot = graphviz.Digraph(f"Power BI diagram {diagram}", engine="neato",graph_attr=graphattributes,node_attr=nodeattributes, edge_attr=edgeattributes) | |
#consider nop2 for fixed locations (with pos) | |
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels: | |
starttable = starttable.replace("'","") | |
endtable = endtable.replace("'","") | |
if (starttable in diags[diagram]) and (endtable in diags[diagram]): | |
dir = ("both" if crossfilter else "forward") | |
headlabel = taillabel = "" | |
if args.showcardinality: | |
taillabel = ("*" if tomany else "1") | |
headlabel = "*" | |
if isactive: | |
dot.edge(tail_name=endtable, head_name=starttable,dir=dir,headlabel=headlabel,taillabel=taillabel) | |
elif args.inactive: # show inactive relationships with dashed edge | |
dot.edge(tail_name=endtable, head_name=starttable,dir=dir, style="dashed",headlabel=headlabel,taillabel=taillabel) | |
if args.skiprender: | |
dot.save(f"{args.renderfolder}/{projectname}_{diagram}.gv") | |
else: | |
dot.render(format=args.format, view=False, outfile=f"{args.renderfolder}/{projectname}_{diagram}.{args.format}") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
More details on why and how to use it on linkedin: https://www.linkedin.com/pulse/exporting-power-bi-diagrams-nico-jacobs-esdge/